on 06-06-2011 12:25 PM
Hi,
This is my SQLQuery.
SELECT distinct OINV.DocDueDate AS 'Due date ',OCRD.GroupNum AS 'Payment Terms',(OINV.Doctotal)- (OCRD.balance) as 'Credit 7'
FROM OINV INNER JOIN
OCRD ON OINV.CardCode = OCRD.CardCode
WHERE (OINV.DocDueDate BETWEEN '04/20/2011' and '06/06/2011' and OINV.CardCode='c00032')
union all
SELECT distinct ORCT.DocDueDate AS 'Due date',OCRD.GroupNum AS 'Payment Terms',(ORCT.NoDocSum)- (OCRD.balance) as 'Credit 7'
FROM ORCT INNER JOIN
OCRD ON ORCT.CardCode = OCRD.CardCode
WHERE (ORCT.DocDueDate BETWEEN '04/20/2011' and '06/06/2011' and ORCT.CardCode ='c00032')
-
when i execute this Query ,then it will display :- Date = "2011-05-21" , Payment terms = "7 " and Credit 7 = "-74660.00"
Now i want to add two more Columns Credit 5 and Credit 9 in this Query and
I want if Payment terms =5 , the it should display the Credit in another new column Credit 5
and if Payment terms =9 , the it should display the Credit in another new column Credit 9
and if Credit displays in one Credit column then other Credit Column's value becomes zero.
Kindly give me the solution to solve the above problem
Regards
Rahul
HI ,
I tried first half of this at the back end and it worked. You may try this.
SELECT T1.[DocDueDate] ,
CASE WHEN T0.GROUPNUM = 9 THEN (T1.DocTotal - T0.BALANCE) ELSE 0 END AS Test,
CASE WHEN T0.GROUPNUM = 10 THEN (T1.DOCTOTAL -T0.BALANCE) ELSE 0 END AS Ttest1
FROM OCRD T0 INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode
WHERE T1.DocDueDate BETWEEN '04/20/11' AND '06/06/11'
AND T1.CardCode = 'CM0247'
Thanks
Malhaar
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Rahul,
Try this:
SELECT distinct T1.DocDueDate 'Due date',
T0.GroupNum 'Payment Terms',
(T1.Doctotal- T0.balance) 'Credit 5',
(T2.Doctotal- T0.balance) 'Credit 7',
(T3.Doctotal- T0.balance) 'Credit 9'
FROM OCRD T0
LEFT JOIN OINV T1 ON T1.CardCode = T0.CardCode AND T1.GroupNum = 5
LEFT JOIN OINV T2 ON T2.CardCode = T0.CardCode AND T2.GroupNum = 7
LEFT JOIN OINV T3 ON T3.CardCode = T0.CardCode AND T3.GroupNum = 9
WHERE T1.DocDueDate BETWEEN [%0\] and [%1\] and T1.CardCode='[%2\]'
I just show you the first part. If works, you can try union the other part.
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Thanks, Your solution is really working, but now i have one question
i want if GroupNum =7 then ,it should display the data in Credit 7 Column and rest Credit 5 and Credit 9 should be hide (Credit 5 and Credit 9 columns should not be shown in the data)
Similarly, if GroupNum =5 then, it should display the data is Credit 5 Column and rest Credit 7 and Credit 9 should be hide
Kindly give me the solution for this problem.
Regards
Rahul
Try this:
SELECT distinct T1.DocDueDate 'Due date',
T0.GroupNum 'Payment Terms',
CASE WHEN T0.GROUPNUM = 5 THEN (T1.Doctotal- T0.balance) ELSE 0 END 'Credit 5',
CASE WHEN T0.GROUPNUM = 7 THEN (T1.Doctotal- T0.balance) ELSE 0 END 'Credit 7',
CASE WHEN T0.GROUPNUM = 9 THEN (T1.Doctotal- T0.balance) ELSE 0 END 'Credit 9'
FROM OCRD T0
LEFT JOIN OINV T1 ON T1.CardCode = T0.CardCode
WHERE T1.DocDueDate BETWEEN [%0\] and [%1\] and T1.CardCode='[%2\]'
User | Count |
---|---|
89 | |
7 | |
6 | |
3 | |
3 | |
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.