on 11-21-2014 7:47 AM
Hi All
Please help me in query report wherein I want to have all the entries where account name has TDS in it.
T0.[TransId], T0.[BtfStatus], T0.[TransType], T0.[BaseRef], T0.[RefDate], T0.[Memo],
.[Ref1], T0.[Ref2], T0.[Ref2], T0.[Number], T1.[Line_ID], T1.[Account],T2.CardName,T3.AcctName, T1.[ShortName],
.[Debit], T1.[Credit] FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId
JOIN OCRD T2 ON T1.[Account]=T2.[CardCode]
JOIN OACT T3 ON T1.[Account]=T3.[AcctCode] WHERE T0.[RefDate] = '20140331'
T0.[TransId]=(Select T0.[TransId] from JDT1 where T3.AcctName LIKE '%TDS%')
But the above query gives only that line which has this account name but I want to have complete it (i.e. the corresponding accounts also which has been debited or credit with this account).
Thanks In Advance
Thanks & Regards
Preeti
Hi,
Try this:
select T0.[TransId], T0.[BtfStatus], T0.[TransType], T0.[BaseRef], T0.[RefDate], T0.[Memo], t0.[Ref1], T0.[Ref2], T0.[Ref2], T0.[Number], T1.[Line_ID], T1.[Account],T2.CardName,T3.AcctName, T1.[ShortName], t1.[Debit], T1.[Credit]
FROM OJDT T0 left JOIN JDT1 T1 ON T0.TransId = T1.TransId
left JOIN OCRD T2 ON T1.[Account] =T2.[CardCode]
left JOIN OACT T3 ON T1.[Account]=T3.[AcctCode]
WHERE T0.[RefDate] = '20140331'
and T1.[TransId] in (Select a.[TransId] from JDT1 a left join OACT b on a.Account=b.AcctCode where b.AcctName LIKE '%TDS%')
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 Nagarajan
Thank you very much it works. One small condition also I need to add-
I want BP PAN no.
I am trying below but could not get the desired result as PAN no in getting multiple in CRD7 table
with address.
Select
T0.[TransId] AS [JE NO], T0.[RefDate] as [Posting Date], T0.[Number], T1.[Line_ID],
Account=(CASE WHEN T2.cardname IS NULL THEN t3.AcctName ELSE T2.cardname END), (Isnull(T4.taxid1,'')),
t1.[Debit], T1.[Credit],T0.[Memo] as [Details], t0.[Ref1], T0.[Ref2], T0.[Ref2]
FROM OJDT T0 left JOIN JDT1 T1 ON T0.TransId = T1.TransId
left JOIN OCRD T2 ON T1.[ShortName] =T2.[CardCode]
left JOIN OACT T3 ON T1.[Account]=T3.[AcctCode]
LEFT JOIN CRD7 T4 ON T2.CardCode=T4.CardCode AND T2.cardcode= (Select Top 1 Cardcode from CRD7 where T1.[ShortName] =T2.[CardCode])
WHERE T0.[RefDate] = '20141121' AND T0.[TransType]='30'
--AND T1.[TransId] in (Select a.[TransId] from JDT1 a left join OACT b on a.Account=b.AcctCode where b.AcctName LIKE '%TDS%')
--AND (T0.[RefDate] >= [%0] AND T0.[RefDate] <=[%1]) OR =T3.AcctName =[%2]
Many Thanks
Regards
Preeti
Hi,
Try this
Select T0.[TransId], T0.[BtfStatus], T0.[TransType], T0.[BaseRef], T0.[RefDate], T0.[Memo],
t0.[Ref1], T0.[Ref2], T0.[Ref2], T0.[Number], T1.[Line_ID], T1.[Account],T3.AcctName, T1.[ShortName],
t1.[Debit], T1.[Credit] FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId
left outer JOIN OCRD T2 ON T1.[ShortName]=T2.[CardCode]
inner JOIN OACT T3 ON T1.[Account]=T3.[AcctCode]
WHERE T0.[RefDate] = '20140331'
and T0.[TransId]=(Select T0.[TransId] from JDT1 where T3.AcctName LIKE '%TDS%')
Regards,
Manish
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
First try this
Select T0.[TransId], T0.[BtfStatus], T0.[TransType], T0.[BaseRef], T0.[RefDate], T0.[Memo],
t0.[Ref1], T0.[Ref2], T0.[Ref2], T0.[Number], T1.[Line_ID], T1.[Account],T3.AcctName, T1.[ShortName],t1.[Debit], T1.[Credit]
FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId
INNER JOIN OCRD T2 ON T1.[ShortName]=T2.[CardCode]
inner JOIN OACT T3 ON T1.[Account]=T3.[AcctCode]
WHERE T0.[RefDate] = '20140331'
If above query is correct then let me know.
Regards,
Manish
Hi ,
Try This ,
select T0.[TransId], T0.[BtfStatus], T0.[TransType], T0.[BaseRef], T0.[RefDate], T0.[Memo], t0.[Ref1], T0.[Ref2], T0.[Ref2], T0.[Number], T1.[Line_ID], T1.[Account],T2.CardName,T3.AcctName, T1.[ShortName], t1.[Debit], T1.[Credit]
FROM OJDT T0 left JOIN JDT1 T1 ON T0.TransId = T1.TransId
left JOIN OCRD T2 ON T1.[Account] =T2.[CardCode]
left JOIN OACT T3 ON T1.[Account]=T3.[AcctCode]
WHERE T0.[RefDate] = '20140331'
and T1.[TransId]in (Select a.[TransId] from JDT1 a left join OACT b on a.Account=b.AcctCode where b.AcctName LIKE ''%TDS%')
-Rajesh NS
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.