Skip to Content
0
Former Member
Apr 23, 2009 at 05:44 PM

Year to Date Aggregation

32 Views

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