November 29, 2005 at 6:49 am
Looking for SQL to render:
1) a count of ROWS for a Given Month
2) for Multiple Months
User inputs the Begin Month and End Month
declare @MonthStartDate datetime, @MonthEndDate datetime
--SAMPLE Begin/End MONTH entry:
select @MonthStartDate = '2005-09-01'
select @MonthEndDate = '2005-11-30 23:59:59.997'
select count(*) from Applications where DateSubmitted between @MonthStartDate and @MonthEndDate
-- (a GROUP BY DateSubmitted is breaking it by DAY: I need it by MONTH)
Need the SQL to breakout counts by MONTH:
Date Applications
---- ------------
September 2005 243
October 2005 1,241
November 2005 755
November 29, 2005 at 6:57 am
select Year(DateSubmitted) as SubmittedYear
, DATENAME ( mm , DateSubmitted ) as SubmittedMonth
, count(*) as ApplCount
from Applications
where DateSubmitted between @MonthStartDate and @MonthEndDate
group by Year(DateSubmitted)
, Month(DateSubmitted)
, DATENAME ( mm , DateSubmitted )
order by group by Year(DateSubmitted)
, Month(DateSubmitted)
SQL = Scarcely Qualifies as a Language
November 29, 2005 at 7:28 am
Carl ~ thanks. Your solution worked PERFECTLY!!!
-bt
December 1, 2005 at 4:09 am
If you have a LOT of data to summarise, then you would be better off having some simple dates table structed as
MONTH START FINISH Sep 05 2005-09-01 2005-10-01 Oct 05 2005-10-01 2005-11-01 etc
Then for your summary, simply do an inner join to this table (make sure you set clustered index on Dates table to Start, Finish, Month and have an index on your DateSubmitted column in Applications).
select D.[Month], count(*)
from Applications A
inner join Dates D
on (A.DateSubmitted >= D.Start and A.DateSubmitted < D.Finish)
where DateSubmitted between @MonthStartDate and @MonthEndDate
group by D.Start
Same idea applies to the use of "numbers" tables - search for similar methods on this site. The groupby on month name is probably sufficient, but often the months table (even if made as a simple table variable temporarily) can help SQL use indices and speed up the whole process significantly!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply