sql server reporting services: I have data that is stored by individual dates and I count to get a total of
how many units are produced on a single date. I need to get weekly and
monthly totals instead of daily and group the data by week and month to
display in a report. I also need to keep a rolling history of 5 weeks of
data. Is any of this possible??

I am not sure how to do this any help would be appreciated please.

ex: of data

7/12/05 12 units produced
7/13/05 12 units produced

for the week 24 units produced

Andy,
You basically need to find the start off the week for that record, then
group by that start date.

Here is a sample - you need to replace myDateField with the date field from
your table

SELECT Weekstart=DATEADD(ww, DATEDIFF(ww,0,myDateField), 0),
WeeklyTotal=SUM(Units)
FROM mytable
GROUP BY DATEADD(ww, DATEDIFF(ww,0, myDateField), 0)



For months:

SELECT Monthstart=DATEADD(mm, DATEDIFF(mm,0,myDateField), 0),
MonthTotal= SUM(Units)
FROM mytable
GROUP BY DATEADD(mm, DATEDIFF(mm,0, myDateField), 0)

Hope this helps

Andre


[quoted text, click to view]