Complex column summing (solved)

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

General SQL Server Forums

New to SQL Server Programming

Complex column summing (solved)

Author

Topic

mholman
Starting Member

8 Posts

Posted - 2009-11-24 : 10:09:03

I have a table that has fields username, client_num, mon, tues, wed, thurs, fri, sat, sun, thisweek and active. I'm trying to sum the mon-fri grouping by month and client number (and possibly username).

I've been trying the following, but the query doesn't seem to execute. I get no errors, but the query never finishes execution (or at least takes extremely long).

--declare @client int
declare @date datetime
--set @client = 155;
set @date = '9/5/2009';

SELECT card.client_num, SUM(m.monday + t.tuesday + w.wednesday + r.thursday + f.friday + s.saturday + d.sunday)
FROM card
LEFT JOIN (
	SELECT client_num, SUM(mon) AS monday FROM card
		WHERE MONTH(@date)=MONTH(thisweek) AND active='True'
	GROUP BY	card.client_num
)	AS m ON card.client_num=m.client_num
LEFT JOIN (
	SELECT client_num, SUM(tues) AS tuesday FROM card
		WHERE MONTH(@date)=MONTH(DATEADD(DAY,1,thisweek)) AND active='True'
	GROUP BY	card.client_num
)	AS t ON card.client_num=t.client_num

LEFT JOIN (
	SELECT client_num, SUM(wed) AS wednesday FROM card
		WHERE MONTH(@date)=MONTH(DATEADD(DAY,2,thisweek)) AND active='True'
	GROUP BY	card.client_num
)	AS w ON card.client_num=w.client_num

LEFT JOIN (
	SELECT client_num, SUM(thurs) AS thursday FROM card
		WHERE MONTH(@date)=MONTH(DATEADD(DAY,3,thisweek)) AND active='True'

	GROUP BY	card.client_num
)	AS r ON card.client_num=w.client_num

LEFT JOIN (
	SELECT client_num, SUM(fri) AS friday FROM card
		WHERE MONTH(@date)=MONTH(DATEADD(DAY,4,thisweek)) AND active='True'
	GROUP BY	card.client_num
)	AS f ON card.client_num=w.client_num

LEFT JOIN (
	SELECT client_num, SUM(sat) AS saturday FROM card
		WHERE MONTH(@date)=MONTH(DATEADD(DAY,5,thisweek)) AND active='True'
	GROUP BY	card.client_num
)	AS s ON card.client_num=w.client_num

LEFT JOIN (
	SELECT client_num, SUM(sun) AS sunday FROM card
		WHERE MONTH(@date)=MONTH(DATEADD(DAY,6,thisweek)) AND active='True'
	GROUP BY	card.client_num
)	AS d ON card.client_num=w.client_num

GROUP BY	card.client_num
ORDER BY	card.client_num ASC

edit: I've run parts of it and it seems to run as expected for monday-wednesday. On thursday it returns each row in thurs as well as the total, and this is output for each group (and is the same), and the execute time is much slower.

For example:
I get this when I run without thursday


client	monday	tuesday
100	10.5	16
105	6.5	7
...

With Thursday I get:


client	monday	tuesday	thursday
100	10.5	16	2.5
100	10.5	16	1
100	10.5	16	3.5
105	6.5	7	1
105	6.5	7	2.5
105	6.5	7	3.5
...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-24 : 11:14:49

After wednesday you are always joining on w.client_num that's the problem...

No, you're never too old to Yak'n'Roll if you're too young to die.

mholman
Starting Member

8 Posts

Posted - 2009-11-24 : 11:20:31

Oh wow can't believe I missed that, thank you!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-24 : 11:21:17

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources