Monday, February 10, 2014

Grouping by Week, Month, and Quarter in SQL

We had a need for a report that showed how often entries were put in the database.  We also needed the report to show be able to show the totals by the week, the month, or the quarter depending on what the end user was looking for.  Here's my stored procedure:

CREATE PROCEDURE dbo.sp_carGraphTotalCAR
    (
        @start datetime,
        @stop datetime, 
        @dateGroup varchar(25)    
    )
AS


if @dateGroup = 'week'
  BEGIN
    select DateAdd(d, 1 - DATEPART(dw, entrydate), CONVERT(datetime, CONVERT(varchar,entrydate,112) ) ) as Week, count(carid) as Count from caritem
    where @start < entryDate and @stop > entryDate
    group by DateAdd(d, 1 - DATEPART(dw, entrydate), CONVERT(datetime, CONVERT(varchar, entrydate,112) ))
  END

else if @dateGroup = 'month'
  BEGIN
    select  DATEADD(MONTH, DATEDIFF(MONTH, 0, entrydate), 0),COUNT(carid) TotalCount from caritem
    where @start < entryDate and @stop > entryDate
    group by  DATEADD(MONTH, DATEDIFF(MONTH, 0, entrydate), 0)
  END

else --group by quarter
  BEGIN
    select  DATEADD(QUARTER, DATEDIFF(QUARTER, 0, entrydate), 0),COUNT(carid) TotalCount from caritem
    where @start < entryDate and @stop > entryDate
    group by  DATEADD(QUARTER, DATEDIFF(QUARTER, 0, entrydate), 0) 
  END

    RETURN

No comments:

Post a Comment