SAP B1 Query Question

[Former Member]

Hi,

I was wondering if is possible to add an order count/total weight per day for the query below.

Thanks,

Steve

SELECT T0.DocDueDate, T0.DocNum, T0.CardName, T1.whscode, T0.DocDate, SUM(T1.weight1) 'Weight'

FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.DocDueDate>=[%0] and T0.DocDueDate<=[%1] and T1.whscode LIKE '%[%2]%' and T0.DocStatus = 'C'

GROUP BY T0.DocDueDate, T0.DocNum, T0.CardName, T1.whscode, T0.DocDate,

ORDER BY T0.DocDueDate, T0.DocNum

[Former Member]

Hi Steve,

If you need that info. It can't be co-exist. Try:

SELECT T0.DocDueDate, T0.CardName, T1.whscode, SUM(T1.weight1) 'Weight',Count(T0.DocNum)/SUM(T1.weight1) 'Avg Weight / Order'

FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.DocDueDate>=[%0\] and T0.DocDueDate<=[%1\] and T1.whscode LIKE '%[%2\]%' and T0.DocStatus = 'C'

GROUP BY T0.DocDueDate, T0.CardName, T1.whscode,

Thanks,

Gordon

[Former Member]

Hi Gordon,

Thank you for your reply. I am not sure if is possible or not, but what i am looking for is to get the total weight shipped per day and a total order count per day.

Thanks,

Steve

Edited by: sava113 on Dec 8, 2011 4:46 PM

[Former Member]

Try:

SELECT T0.DocDueDate, T0.CardName, SUM(T1.weight1) 'Total Weight/Day',Count(T0.DocNum) 'Order/Day'

FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.DocDueDate>=[%0\] and T0.DocDueDate<=[%1\] and T1.whscode LIKE '%[%2\]%' and T0.DocStatus = 'C'

GROUP BY T0.DocDueDate, T0.CardName