query or stored proc to summarize data by date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query or stored proc to summarize data by date

Author

Topic

djfiii
Starting Member

13 Posts

Posted - 2009-02-24 : 18:12:00

Good evening,

I've been wrestling with this for a bit now, and I'm stumped as to how I should proceed.

I have a table (Exceptions) from which I want to summarize the number of records by month/year for two date fields, and fit it into one recordset. I'm aiming to have my recordset look like the following:

yr mo opened closed
2005 1 124 223
2005 2 156 76
2005 3 44 25
2005 4 77 99
.. .. .. ..
.. .. .. ..
.. .. .. ..
.. .. .. ..
2009 1 35 82
2009 2 88 107
2009 3 198 33

My table has a number of fields, but the two date fields I'm concerned with are exceptionReportDate and exceptionCloseDate. An additional snag is that, if exceptionCloseDate is before exceptionReportDate, then I want to display exceptionCloseDate = exceptionReportDate.

I'm pulling this data into an asp script, so I'm trying to avoid a loop that sends hundreds of queries to the db server. So I've been playing with stored proc's, but haven never really used them before.

I've got the following skeleton but it's giving me multiple result sets, and is limited to one of the two date fields I'm looking to consolidate.


declare @yr int
declare @mo int

set @yr = 2003
set @mo = 1

while @yr <= YEAR(GETDATE())
begin

	while @mo <= 12
	begin
		select count(*) as num from exceptions where year(exceptionCloseDate) = @yr and month(exceptionCloseDate) = @mo
	
		print 'The counter is ' + cast(@yr as char(4)) + '-' + cast(@mo as char(2))
		set @mo = @mo + 1
	end
	
	set @yr = @yr + 1
	set @mo = 1
end
GO

any help is appreciated!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-02-25 : 09:43:46

How about posting your table structure and some sample data, for a start.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-25 : 09:53:14

see this to understand how to post the required info

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources