on 12-16-2014 7:14 AM
Hi Experts.
I need a query to check individual ledger reports please help me to write a query.
Regards
Vinoth
Hi,
What you mean by individual ledger?
Thanks & Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
I need individual ledger report query that is individual ledger report means in COA selecting asset in that am selecting particular ledger.After selecting the ledger clicking the balance link.it will open particular ledger transactions
In that TansNo link if i click it will open JE in that i need Account code, account name and amount
In your above query it showing as memo second column instead of that i need a ledger name
my query
Posting Date AcctName(ledgerName) Debit credit
Regards
Vinoth
Hi
Based on your query report shows only particular ledger alone fully..I need in that particular ledger what are the transactions to be made.
I need a report like this guide me
Ledger Name:Cash In Bangalore
Openning Balance
Date AccountName(ledgerName) Debit Credit
2-Nov-14 Computer sales 5000
2-nov-14 Keyboards Sales 1000
4-nov-14 mouse sales 3000
6-nov-14 Other Accesories 7000
7-nov-14 Postage & courier expense 100
8-Nov-14 Stationery & Xerox Exp. 300
9-Nov-14 Indian Bank BangaloreA/c (40648678511) 2000
10-Nov-14 Repairs& Maintainance 1500
Closing Balance
Regards
Vinoth
Try:
SELECT T0.[RefDate], T2.[AcctName], T1.[Project], T0.[Ref1], T0.[Ref2], T1.[Debit], T1.[Credit] FROM [dbo].[OJDT] T0 INNER JOIN [dbo].[JDT1] T1 ON T0.TransId = T1.TransId INNER JOIN OACT T2 ON T1.Account = T2.AcctCode WHERE T0.refdate between [%1] and [%2] and T2.[GroupMask] = 1 and T2.[AcctName] =[%0]
Hi
The above query also openning only particular ledger name alone in full report.
In COA if i select the balance link list of transactions will be openning in another form linke that i need in query
Here i attached the screen shot for reference.
In that second screen shot it showing as offset account i need that account name in another column like that i need query.
Hi
Finally i got a query with expected output thanks for your continous support
SELECT t0.account[Acct Code], t1.AcctName[Acct Name], convert(varchar,t0.RefDate,103) as Date,
t0.ContraAct[Offset Acct],(select acctname from oact where acctcode=t0.contraAct) as AcctName,
(t0.debit), (t0.credit),t2.Ref2,t2.Ref3,t2.Project
FROM jdt1 t0 inner join oact t1 on
t1.acctcode = t0.account
inner join OJDT t2 on
t0.TransId=t2.TransId
where t1.AcctName='General Fund - Inland' and t0.refdate between {?FromDate} and {?ToDate}
order by t0.refdate
Thanks & Regards
Vinoth
Hi,
Please check JDT1 Table.
Example :
Select * From JDT1 T0 Where T0.[Account]='[%1]'
Thanks,
Nithi
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
8 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.