June 15, 2010 at 9:27 am
If more than 1 JobID exists in my [GroupData,GroupDetail] one-to-many relationship, I need to show this with a 'Multiple Jobs' indicator instead of the actual JobName value, as well as sum the Hours for those multiple jobs.
I am trying to get the desired output as indicated at the bottom of the posted TSQL. Rather than show all of my failed TSQL attempts, I thought I'd provide the test data and hope someone is kind enough to point me in the right CASE or CTE direction.
Regards,
Steve
--
-- Drop tables if they already exist
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GroupData]') AND type in (N'U'))
DROP TABLE [dbo].[GroupData]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GroupDetail]') AND type in (N'U'))
DROP TABLE [dbo].[GroupDetail]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GroupJobs]') AND type in (N'U'))
DROP TABLE [dbo].[GroupJobs]
GO
--
-- Create the test tables
--
CREATE TABLE [dbo].[GroupData] (
GroupID int NOT NULL PRIMARY KEY,
WeekEnding smalldatetime NULL
)
GO
CREATE TABLE [dbo].[GroupDetail] (
GroupDetailID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
GroupID int NULL,
MemberID int NULL,
JobID int NULL,
TotalHours decimal(5, 2) NULL
)
GO
CREATE TABLE [dbo].[GroupJobs] (
JobID int NOT NULL PRIMARY KEY,
JobName varchar(50) NULL
)
GO
--
-- Create GroupData test records
-- NOTE: Each GroupID in this table will
-- have a unique WeekEnding value
--
INSERT INTO GroupData
SELECT 1,'5/16/2010' UNION ALL
SELECT 2,'5/20/2010' UNION ALL
SELECT 3,'5/23/2010'
--
-- Create GroupDetail test records
--
INSERT INTO GroupDetail
SELECT 1,22,50,40 UNION ALL
SELECT 1,23,50,40 UNION ALL
SELECT 1,24,50,40 UNION ALL
SELECT 2,22,66,20 UNION ALL
SELECT 2,23,67,20 UNION ALL
SELECT 2,24,68,20 UNION ALL
SELECT 3,22,77,40
--
-- Create GroupJobs test records
--
INSERT INTO GroupJobs
SELECT 50,'Job50' UNION ALL
SELECT 66,'Job66' UNION ALL
SELECT 67,'Job67' UNION ALL
SELECT 68,'Job68' UNION ALL
SELECT 77,'Job7
--
-- Query to show what data looks like using SUM(TotalHours)
--
SELECT t1.GroupID,t1.WeekEnding
, 'Hours'=SUM(t2.TotalHours)
, t2.JobID
, t3.JobName
FROM GroupData t1
JOIN GroupDetail t2 ON t2.GroupID = t1.GroupID
JOIN GroupJobs t3 ON t3.JobID = t2.JobID
GROUP BY t1.GroupID,t1.WeekEnding,t2.JobID,t3.JobName
--
-- The output from above query shows that GroupID #2 has three jobs totaling 60 hours.
-- We need to sum the hours for those three rows into a single row that will show
-- the WeekEnding, Hours and the literal 'Multiple Jobs' for the JobName.
-- We do not need the JobID in the result set.
--
GroupIDWeekEndingHoursJobIDJobName
==================================
12010-05-16 00:00:00120.0050Job50
22010-05-20 00:00:0020.0066Job66
22010-05-20 00:00:0020.0067Job67
22010-05-20 00:00:0020.0068Job68
32010-05-23 00:00:0040.0077Job77
--
-- This is the desired output needed
--
GroupIDWeekEndingHoursJobName
12010-05-16 00:00:00120.00Job50
22010-05-20 00:00:0060.00Multiple Jobs
32010-05-23 00:00:0040.00Job77
June 15, 2010 at 9:51 am
try:
;WITH JobSum
AS
( SELECT t1.GroupID
,t1.WeekEnding
,SUM(t2.TotalHours) as [Hours]
,t2.JobID
,t3.JobName
FROM GroupData t1
JOIN GroupDetail t2 ON t2.GroupID = t1.GroupID
JOIN GroupJobs t3 ON t3.JobID = t2.JobID
GROUP BY t1.GroupID,t1.WeekEnding,t2.JobID,t3.JobName
)
SELECT js.GroupID
,js.WeekEnding
,SUM(js.[Hours]) AS [Hours]
,CASE WHEN COUNT(*) = 1 THEN MAX(js.JobName) ELSE 'Multiple Jobs' END AS JobName
FROM JobSum js
GROUP BY js.GroupID
,js.WeekEnding
June 15, 2010 at 10:12 am
Awesome! I could not get the right case statement.
Thanks for your quick response Eugene.
June 15, 2010 at 10:14 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply