Subtotal every 7 days in RS2005

[jodjim]

One particular payroll report prints out daily payroll record of an employee for a selected date range (which could be for a month or years). Selection of employee and start/end date is through a parameter. Now I want to create a sub-total of hours worked and wages every 7 days starting from the start date.

I tried creating another dataset with week numbers with corresponding start and endDate but I don't know how to "join" it with my payroll details dataset.

Is there a better way of doing it? I'd really appreciate any suggestions or thoughts.

jodjim

 

[jodjim]

I got this response in another forum and thought it might be worthwhile sharing. I created a dataset using the following query and group the daily payroll by WeekNumber.

SELECT PayRollDate, PayRollAmount, DATEDIFF ( day, YourStardDate, PayRollDate ),
CAST ( DATEDIFF ( day, YourStardDate, PayRollDate ) / 7 AS integer ) AS WeekNumber
FROM PAYROLL
WHERE PayRollDate BETWEEN YourStartDate AND YourEndDate
ORDER BY WeekNumber