I want to aggregate the sum of cash transactions year to date, weekly.
I have the following code:
SELECT SUM(T2.Debit-T2.Credit), datepart(wk, T1.refdate)
FROM OACT INNER JOIN
JDT1 T1 ON T1.Account = OACT.AcctCode Join JDT1 T2 on T1.transid = T2.transid
and datePart(wk, T2.RefDate)<= DatePart(wk, T1.RefDate)
and datePart(wk, T2.RefDate)>= CAST(CAST(YEAR(T1.RefDate) as CHAR(4)) + '0101' AS DateTime)
WHERE (OACT.FatherNum = N'1000') AND (T1.RefDate > CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
Group by datepart(wk, T1.refdate)
Order by datepart(wk, T1.refdate)
-
This is not working, I get all zero values.
Any help is appreciated.
Rob