• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 484
  • Last Modified:

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
0
susan1978
Asked:
susan1978
1 Solution
 
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now