Query & groupnig problems - 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

General SQL Server Forums

New to SQL Server Programming

Query & groupnig problems

Author

Topic

glennandrewcooper
Starting Member

5 Posts

Posted - 2005-09-01 : 15:21:16

Hi there,

I have a relatively simple problem with a query I'm working on.

There is a query in my database that returns the following fields

Date
Client_Job_ID
Client_ID
Client_Name
Job_Type_ID
Chargeable (Boolean)
Hours

The Client_ID, Client_Name, Job_Type_ID and Chargeable are dependant on the Client_Job_ID field so it might be worthwhile forgetting about those.

Anyway, for a report that I need to produce, I will need to query the view for a weeks worth of data. I will need the data grouping so that I have a total for each day of the week for each Job_ID.

I've found a way to split the data into different columns for each day (Monday, Tuesday, Wednesday) Using the DAYOFWEEK function. However, the totals for each day are showing in different results, and I would like these to be summarised in one result. I.e. if there are results for Monday and Tuesday, this would return two records with a Monday total in that column, and the Tuesday total in the other. I would like these results to be summarised across.

Here's the SQL statement I have so far, maybe someone can help me with structuring the whole thing too!



SELECT Client_Job_ID,
Monday = CASE { fn DAYOFWEEK([Date])}
WHEN 1 THEN Sum(Hours)
END,
Tuesday = CASE { fn DAYOFWEEK([Date])}
WHEN 2 THEN Sum(Hours)
END,
Wednesday = CASE { fn DAYOFWEEK([Date])}
WHEN 3 THEN SUM(Hours)
END,
Thursday = CASE { fn DAYOFWEEK([Date])}
WHEN 4 THEN SUM(Hours)
END,
Friday = CASE { fn DAYOFWEEK([Date])}
WHEN 5 THEN Sum(Hours)
END,
Saturday = CASE { fn DAYOFWEEK([Date])}
WHEN 6 THEN SUM(Hours)
END,
Sunday = CASE { fn DAYOFWEEK([Date])}
WHEN 7 THEN SUM(Hours)
END
FROM Timesheetsummary
GROUP BY Client_Job_ID, [Date]
ORDER BY Client_Job_ID

This query would return something like:

CJID, Mon, Tues, Wed, Thurs, Fri, Sat, Sun
48 ,NULL, NULL,NULL, 5.0,NULL,NULL,NULL
48 ,NULL, NULL,NULL, NULL,NULL, 3.0,NULL

However, I would need the data to be returned as:
CJID, Mon, Tues, Wed, Thurs, Fri, Sat, Sun
48 ,NULL, NULL,NULL, 5.0,NULL, 3.0,NULL

I hope that clearly identifies my problem! Any ideas?

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-09-02 : 04:51:24

Hi,
Wrap each case statement in a MAX() aggregate function and remove [Date] from the GROUP BY clause.

Mark

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-09-02 : 04:58:48

Hi
Sorry, forgot about your use of SUM(). This will error as you can't perform an aggregate function on an expression containing an aggregate or subquery. Give me a minute...

Mark

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-09-02 : 05:45:40

Ok, here you go:

SELECT
	tss.Client_Job_id, 
	MAX(CASE 
			WHEN tss.dayofweek = 1
			THEN tss.Hours
		END) AS Monday, 
	MAX(CASE 
			WHEN tss.dayofweek = 2
			THEN tss.Hours
		END) AS Tuesday, 
	MAX(CASE 
			WHEN tss.dayofweek = 3
			THEN tss.Hours
		END) AS Wednesday, 
	MAX(CASE 
			WHEN tss.dayofweek = 4
			THEN tss.Hours
		END) AS Thursday, 
	MAX(CASE 
			WHEN tss.dayofweek = 5
			THEN tss.Hours
		END) AS Friday
FROM
	(
		SELECT 
			tss.Client_Job_ID,
			DATEPART(weekday, tss.Date)-1 AS dayofweek, 
			SUM(tss.Hours) AS hours
		
		FROM 
			dbo.Timesheetsummary AS tss
		GROUP BY 
			tss.Client_Job_ID, 
			DATEPART(weekday, tss.Date)-1
	) AS tss
GROUP BY
	tss.Client_Job_ID

This sums up by day and job id within the derived table and then uses an aggregate function along with the case statements to cross-tab the data. A couple of points to note: I'm subtracting 1 from the value returned by the DATEPART function to keep Monday = 1 as, by default, Monday = 0. You could equally use SET DATEFIRST 1 at the start of the procedure. You say you're producing this for a week's worth of data, but I'm not sure where or how you're constraining this.

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources