Skip to Content
0

grouping per week on sap business one query

Oct 22, 2016 at 03:21 AM

27

avatar image
Former Member

Dear Experts,

I made this query to total all our expenses and group them on a per 7 days based on the date parameters.

SELECT max (T2.[FrgnName]) as ACCOUNT,

CASE WHEN DATEDIFF(DAY,max(t0.refdate),[%1]) BETWEEN 0 and 7 THEN
sum (t1.debit - t1.credit)
END AS '1 WEEK',

CASE WHEN DATEDIFF(DAY, max(t0.refdate),[%1]) BETWEEN 8 and 14 THEN
sum (t1.debit - t1.credit)
END AS '2 WEEK',

CASE WHEN DATEDIFF(DAY, max(t0.refdate),[%1]) > 30 THEN
sum (t1.debit - t1.credit)
END AS '3 WEEK'

FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId INNER JOIN OACT T2 ON T1.Account = T2.AcctCode WHERE T2.[GroupMask] = 6 AND t0.[Refdate] BETWEEN [%0] and [%1] GROUP BY T2.[FrgnName]

but then when run the query ' no data found'.

Please Help.

Thanks!

Carlo

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers