Group by  days weeks or months

Posted on 2007-10-07
Last Modified: 2008-01-09
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


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


thank you
Question by:susan1978
    LVL 22

    Accepted Solution

    You could use CUBE/ROLLUP for this. For example:

    SELECT vendorid, yr, mo,
     SUM(quantity) quantity
     (SELECT vendorid,
      YEAR(dt) AS yr,
      MONTH(dt) AS mo,
      DAY(dt) AS dy,
      FROM tbl) t
    GROUP BY vendorid, yr, mo, dy
    LVL 30

    Expert Comment

    If you want to do reports with SQL Server with drops downs etc. you are best of checking out Reporting Services.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now