Help with Aggregate Query - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Help with Aggregate Query

Author

Topic

legacyvbc
Starting Member

37 Posts

Posted - 2007-10-09 : 13:12:22

I'm trying to avoid using functions since they take so long but I have table of orders with 3 different transaction types and am trying to select a sum of each for each month over the past several years
Table tOrder looks like this:
Date,TransId,Amount

I would like to select it to something like:
Date,TranId1,TranId2,TranId3
Where Dates are aggregated into months and TranId1 = Sum(TranId1) during the month and TranId2 = Sum(TranId2) etc....

Sample Data and Result I am trying to achieve are below.

Thanks


DECLARE @tbl TABLE(Date smalldatetime,TranId tinyint,Amt money)

INSERT @tbl
SELECT '20041209', 1, 83147 UNION ALL
SELECT '20041210', 1, 64771 UNION ALL
SELECT '20050427', 0, 21232 UNION ALL
SELECT '20050428', 0, 29270 UNION ALL
SELECT '20050502', 1, 91852 UNION ALL
SELECT '20050502', 1, 81108 UNION ALL
SELECT '20050708', 2, 108502 UNION ALL
SELECT '20050817', 2, 66506 UNION ALL
SELECT '20050818', 2, 32160 UNION ALL
SELECT '20050818', 0, 70197 UNION ALL
SELECT '20050819', 2, 29357

I Am Trying to Get the Query to Return:
DATE          TranId0	 TranId1	TranId2
12/31/2004	 -- 	 147,918 	 -- 
4/30/2005	 50,502  --       	 -- 
5/31/2005	 -- 	 172,960 	 -- 
7/31/2005	 -- 	 -- 	        108,502 
8/31/2005	 70,197  -- 	        128,023

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-09 : 13:30:33

DECLARE @tbl TABLE(Date smalldatetime,TranId tinyint,Amt money)

INSERT @tbl
SELECT '20041209', 1, 83147 UNION ALL
SELECT '20041210', 1, 64771 UNION ALL
SELECT '20050427', 0, 21232 UNION ALL
SELECT '20050428', 0, 29270 UNION ALL
SELECT '20050502', 1, 91852 UNION ALL
SELECT '20050502', 1, 81108 UNION ALL
SELECT '20050708', 2, 108502 UNION ALL
SELECT '20050817', 2, 66506 UNION ALL
SELECT '20050818', 2, 32160 UNION ALL
SELECT '20050818', 0, 70197 UNION ALL
SELECT '20050819', 2, 29357

SELECT [Date] = DATEADD(month, DATEDIFF(month, -1, date), -1)
/* I stole the above line from somebody else on this site, he had a whole list of them*/
,TranId0 = SUM(CASE WHEN TranId = 0 THEN amt ELSE 0 END)
,TranId1 = SUM(CASE WHEN TranId = 1 THEN amt ELSE 0 END)
,TranId2 = SUM(CASE WHEN TranId = 2 THEN amt ELSE 0 END)
FROM @tbl

GROUP BY DATEADD(month, DATEDIFF(month, -1, date), -1)

Jim

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-09 : 13:32:29

Oops. This will format the date for you
[Date] = CONVERT(varchar(10), DATEADD(month, DATEDIFF(month, -1, date), -1) ,101)

Jim

legacyvbc
Starting Member

37 Posts

Posted - 2007-10-09 : 16:44:57

Thank you so much - works great!

legacyvbc
Starting Member

37 Posts

Posted - 2007-10-09 : 23:22:44

I thought this solution was going to work, however it is more complicated than I thought because I need to join some tables and DATE=DATEADD(month,DATEDIFF(month,-1,b.DATE),-1)
returns the last day of the month but I actually need the last business day of the month since the info I am joining and even the orders use the last business day not the last day of the month.

As an example, 6/28/1996 is the last business day of the month yet the table below actually returns 6/30/1996 which I cannot use when joining other tables.

Any ideas?

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-10 : 10:21:19

This actually makes the problem a lot harder because last business day of month isn't well defined. For example, if Dec 30th is a Thursday, Dec 31st might not be a business
day. Your best bet is to make a calendar table with a column that determines whether a given day is business day or not. If you search this site for "business days" or "calendar"
you might come up with something useful. There's some pretty smart people on this site that have addressed your problem.

Good Luck

Jim

legacyvbc
Starting Member

37 Posts

Posted - 2007-10-10 : 12:16:59

Ok Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources