on 12-05-2014 11:34 AM
Hi - I want to report each month on the value of invoiced sales by new customers. I define a new customer who was added to SAP in a time frame say 1 month. I also want to see what Item Group they have purchased to match against marketing costs.
I have this so far:
SELECT T0.[CardCode], T0.[CardName], T4.[ItmsGrpNam], sum(T2.[LineTotal] - T2.[LineVat]) as Value
FROM OCRD T0 INNER JOIN OINV T1 ON T0.[CardCode] = T1.[CardCode] INNER JOIN INV1 T2 ON T1.[DocEntry] = T2.[DocEntry] INNER JOIN OITM T3 ON T2.[ItemCode] = T3.[ItemCode] INNER JOIN OITB T4 ON T3.[ItmsGrpCod] = T4.[ItmsGrpCod]
WHERE T0.[CreateDate] >= [%0] and T1.[DocDate] >= [%1] and T1.[DocDate] <= [%2] and T4.[ItmsGrpCod] <> 126
GROUP BY T0.[CardCode], T0.[CardName], T4.[ItmsGrpNam]
But is doesn't look right. Any ideas are appreciated.
Hi Tim,
Try:
SELECT T0.[CardCode], T0.[CardName], T4.[ItmsGrpNam], sum(T1.Linetotal - T1.LineVat) as Value
FROM OINV T0 INNER JOIN INV1 T1 ON T1.[DocEntry] = T0.[DocEntry] INNER JOIN OITM T3 ON T3.[ItemCode] = T1.[ItemCode] INNER JOIN OITB T4 ON T3.[ItmsGrpCod] = T4.[ItmsGrpCod]
WHERE T0.[CardCode] in (Select [CardCode] From OCRD Where CreateDate >= [%0]) and T0.[DocDate] >= [%1] and T0.[DocDate] <= [%2] and T4.[ItmsGrpCod] <> 126
GROUP BY T0.[CardCode], T0.[CardName], T4.[ItmsGrpNam]
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 Tim,
Please advice your expected result from your query.
Try:
SELECT T0.[CardCode], T0.[CardName], T4.[ItmsGrpNam], sum(T2.[LineTotal] - T2.[LineVat]) as Value
FROM OCRD T0 INNER JOIN OINV T1 ON T0.[CardCode] = T1.[CardCode] INNER JOIN INV1 T2 ON T1.[DocEntry] = T2.[DocEntry] INNER JOIN OITM T3 ON T2.[ItemCode] = T3.[ItemCode] INNER JOIN OITB T4 ON T3.[ItmsGrpCod] = T4.[ItmsGrpCod]
WHERE T0.[CreateDate] >= [%0] and T1.[DocDate] >= [%1] and T1.[DocDate] <= [%2] and T4.[ItmsGrpCod] <> 126
GROUP BY T0.[CardCode], T0.[CardName], T4.[ItmsGrpNam]
order by T0.[CardCode], T0.[CardName], T4.[ItmsGrpNam]
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 Tim,
Try this
SELECT datename(mm,t0.CreateDate) as 'Month',T0.[CardCode], T0.[CardName], T4.[ItmsGrpNam], sum(T2.[LineTotal] - T2.[LineVat]) as Value
FROM OCRD T0
INNER JOIN OINV T1 ON T0.[CardCode] = T1.[CardCode]
INNER JOIN INV1 T2 ON T1.[DocEntry] = T2.[DocEntry]
INNER JOIN OITM T3 ON T2.[ItemCode] = T3.[ItemCode]
INNER JOIN OITB T4 ON T3.[ItmsGrpCod] = T4.[ItmsGrpCod]
WHERE WHERE T0.[CreateDate] >= [%0] and T1.[DocDate] >= [%1] and T1.[DocDate] <= [%2] and T4.[ItmsGrpCod] <> 126
GROUP BY t0.CreateDate,T0.[CardCode], T0.[CardName], T4.[ItmsGrpNam]
ORDER BY t0.CreateDate
Regards,
Manish
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
11 | |
10 | |
6 | |
5 | |
5 | |
5 | |
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.