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
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:
Labels:
SQL,
Stored Procedure
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment