Group by days weeks or months

I would like to group date by weeks within a date range. For example i would like the user to specify a date range and if that date range is a 4 week period i would ike to show the history by week. sorted descending by the latest week first. Here is an example:
 
week 4  10
week 3  20
week 2  30
week 1  5

I would like to the same for days month and year. I have the following fields

vendorid
quantity
rate
itemid
date

I would like to show a history of what was purchased from the vendors purchase grouped by day, week, month, year. Unless someone knows of a better way of doing this i will build sepperate reports for each type of grouping. THe best scenario would be if i could give the customer a way to choose how he wants it grouped for example a drop down showing group by

day
month
week
year

thank you
susan1978Asked:
Who is Participating?
 
dportasCommented:
You could use CUBE/ROLLUP for this. For example:

SELECT vendorid, yr, mo,
 SUM(quantity) quantity
 FROM
 (SELECT vendorid,
  YEAR(dt) AS yr,
  MONTH(dt) AS mo,
  DAY(dt) AS dy,
  quantity
  FROM tbl) t
GROUP BY vendorid, yr, mo, dy
WITH ROLLUP;
0
 
nmcdermaidCommented:
If you want to do reports with SQL Server with drops downs etc. you are best of checking out Reporting Services.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.