Grouping within a join - 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

Grouping within a join

Author

Topic

lahsiv2004
Starting Member

13 Posts

Posted - 2012-05-03 : 10:27:02

Hi Eugene,

I am trying to resolve this last requirement with the query. The problem I am facing is while calculating the sum of cat2_hours for each individual category2_id(Grouped by each category2_id).

When I try to group it just groups the cat2_hours as a whole for each employee (commented out code below). I want it to be grouped as per the category2_id and can be created as 4 different columns like

1. S_cat2hours
2. M_cat2hours
3. B_cat2hours
4. R_cat2hours

Would that be possible ?

This is the query that I had created with your help before:

-------------------------------------------------------------

;with BetterToBeATable
as
(
SELECT Staff_Id, LEFT(WeekDayName,3) WeekDayName, WorkHours
FROM
(SELECT Staff_Id, MonHours, TueHours, WedHours, ThuHours, FriHours
FROM Staff) p
UNPIVOT
(WorkHours FOR WeekDayName IN
(MonHours, TueHours, WedHours, ThuHours, FriHours)
)AS unpvt
)

Select

max(st.Cardholder_name) as [Employee Name],
max(datename(month,s.[Date])) as [Month],
max(datepart(yyyy,s.[Date])) as [Year],

/*
case when max(category2_id) = 'S' then CONVERT(varchar(6), (sum((datepart(hour,ISNULL(cat2_hours,'00:00:00')) * 3600)) + sum(datepart(minute,ISNULL(cat2_hours,'00:00:00')) * 60) + sum(datepart(second,ISNULL(cat2_hours,'00:00:00')))) / 3600)
+ ':' + RIGHT('0' + CONVERT(varchar(2), (sum((datepart(hour,ISNULL(cat2_hours,'00:00:00')) * 3600)) + sum(datepart(minute,ISNULL(cat2_hours,'00:00:00')) * 60)+
sum(datepart(second,ISNULL(cat2_hours,'00:00:00')))) % 3600 / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), (sum((datepart(hour,ISNULL(cat2_hours,'00:00:00')) * 3600)) +
sum(datepart(minute,ISNULL(cat2_hours,'00:00:00')) * 60) + sum(datepart(second,ISNULL(cat2_hours,'00:00:00')))) % 60), 2) end as [Cat2_Hours],
*/

CONVERT(varchar(10), (Sum((datepart(hour,b.WorkHours) * 3600)) + sum(datepart(minute,b.WorkHours) * 60) + sum(datepart(second,b.WorkHours))) / 3600)

+ ':' + RIGHT('0' + CONVERT(varchar(2), (sum((datepart(hour,b.WorkHours) * 3600)) + sum(datepart(minute,b.WorkHours) * 60) +
sum(datepart(second,b.WorkHours))) % 3600 / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), (sum((datepart(hour,b.WorkHours) * 3600)) +
sum(datepart(minute,b.WorkHours) * 60) + sum(datepart(second,b.WorkHours))) % 60), 2) as [Standard Hours],

CONVERT(varchar(6), (Sum((datepart(hour,Timenetin) * 3600)) + sum((datepart(hour,ISNULL(cat2_hours,'00:00:00')) * 3600)) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(minute,ISNULL(cat2_hours,'00:00:00')) * 60) + sum(datepart(second,Timenetin)) + sum(datepart(second,ISNULL(cat2_hours,'00:00:00')))) / 3600)
+ ':' + RIGHT('0' + CONVERT(varchar(2), (sum((datepart(hour,Timenetin) * 3600)) + sum((datepart(hour,ISNULL(cat2_hours,'00:00:00')) * 3600)) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(minute,ISNULL(cat2_hours,'00:00:00')) * 60)+
sum(datepart(second,Timenetin)) + sum(datepart(second,ISNULL(cat2_hours,'00:00:00')))) % 3600 / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), (sum((datepart(hour,Timenetin) * 3600)) + sum((datepart(hour,ISNULL(cat2_hours,'00:00:00')) * 3600)) +
sum(datepart(minute,Timenetin) * 60) + sum(datepart(minute,ISNULL(cat2_hours,'00:00:00')) * 60) + sum(datepart(second,Timenetin)) + sum(datepart(second,ISNULL(cat2_hours,'00:00:00')))) % 60), 2) as [Actual Hours],

convert(int,((sum(datepart(hour,Timenetin) * 3600) + sum(datepart(hour,ISNULL(cat2_hours,'00:00:00')) * 3600) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(minute,ISNULL(cat2_hours,'00:00:00')) * 60) + sum(datepart(second,Timenetin) * 1) + sum(datepart(second,ISNULL(cat2_hours,'00:00:00')) * 1))))/3600 -

((Sum(datepart(hour,b.WorkHours) * 3600) + sum(datepart(minute,b.WorkHours) * 60) + sum(datepart(second,b.WorkHours) * 1)))/3600 As Hrs_Diff,

convert(int,((sum(datepart(hour,Timenetin) * 3600) + sum(datepart(hour,ISNULL(cat2_hours,'00:00:00')) * 3600) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(minute,ISNULL(cat2_hours,'00:00:00')) * 60) + sum(datepart(second,Timenetin) * 1) + sum(datepart(second,ISNULL(cat2_hours,'00:00:00')) * 1)) -

((Sum(datepart(hour,b.WorkHours) * 3600) + sum(datepart(minute,b.WorkHours) * 60) + sum(datepart(second,b.WorkHours) * 1)))))% 3600/60 As Min_Diff

from StaffDay s
join BetterToBeATable b
on b.staff_id = s.staff_id
and b.WeekDayName = s.[Dayname]
join Staff st
on st.staff_id = s.staff_id

where s.Dayname NOT IN ('Sun','Sat')
and datepart(yyyy,s.[Date]) IN ('2012')
and datename(month,s.[Date]) in ('April')

group by s.Cardholder_name, datepart(month,s.[Date])
order by s.Cardholder_name, datepart(month,s.[Date]) asc
-----------------------------------------------------------

Please find the table structures for the Staff and Staffday tables below:

-------------------------------------------------
CREATE TABLE [dbo].[Staff](
[Staff_id] [int] NOT NULL,
[Cardholder_name] [varchar](50) NULL,
[MonHours] [time](0) NULL,
[TueHours] [time](0) NULL,
[WedHours] [time](0) NULL,
[ThuHours] [time](0) NULL,
[FriHours] [time](0) NULL,
CONSTRAINT [PK_Staff] PRIMARY KEY CLUSTERED
(
[Staff_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

-----------------------------------------------------
CREATE TABLE [dbo].[StaffDay](
[Staff_id] [int] NOT NULL,
[Cardholder_name] [varchar](50) NULL,
[Date] [date] NOT NULL,
[Dayname] [char](3) NULL,
[TimeNetIn] [time](7) NULL,
[category2_id] [char](2) NOT NULL,
[cat2_hours] [time](7) NULL,
CONSTRAINT [PK_StaffDay] PRIMARY KEY CLUSTERED
(
[Staff_id] ASC,
[Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
---------------------------------------------

Please find the sample data below:

INSERT INTO [Staff]
VALUES('30', 'Graham', '07:00:00', '07:00:00', '07:00:00', '07:00:00', '07:00:00')
INSERT INTO [Staff]
VALUES('35', 'Paul', '08:00:00', '08:00:00', '08:00:00', '08:00:00', '08:00:00')

INSERT INTO [StaffDay]
VALUES('30', 'Graham', '2012-04-10', 'Mon', '08:52:20.0000000','S','07:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham', '2012-04-11', 'Tue', '07:30:45.0000000','S','08:30:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham', '2012-04-12', 'Wed', '09:41:32.0000000','M','09:30:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham', '2012-04-13', 'Thu', '08:52:27.0000000','M','08:30:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham', '2012-04-14', 'Fri', '08:11:18.0000000','B','07:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham', '2012-04-15', 'Mon', '07:54:37.0000000','B','06:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham', '2012-04-16', 'Tue', '07:19:02.0000000','B','05:30:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham', '2012-04-17', 'Wed', '08:55:46.0000000','R','10:30:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham', '2012-04-18', 'Thu', '07:29:52.0000000','R','09:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham', '2012-04-18', 'Fri', '07:29:52.0000000','R','07:30:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham', '2012-04-18', 'Mon', '07:29:52.0000000','R','08:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('35', 'Paul', '2012-04-10', 'Mon', '07:59:20.0000000','S','09:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('35', 'Paul', '2012-04-11', 'Tue', '09:38:45.0000000','S','07:35:00.0000000')
INSERT INTO [StaffDay]
VALUES('35', 'Paul', '2012-04-12', 'Wed', '07:41:32.0000000','M','08:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('35', 'Paul', '2012-04-13', 'Thu', '08:52:27.0000000','M','08:35:00.0000000')
INSERT INTO [StaffDay]
VALUES('35', 'Paul', '2012-04-14', 'Fri', '08:11:18.0000000','B','07:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('35', 'Paul', '2012-04-15', 'Mon', '06:29:37.0000000','B','10:35:00.0000000')
INSERT INTO [StaffDay]
VALUES('35', 'Paul', '2012-04-16', 'Tue', '07:19:02.0000000','B','06:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('35', 'Paul', '2012-04-17', 'Wed', '08:45:46.0000000','R','10:35:00.0000000')
INSERT INTO [StaffDay]
VALUES('35', 'Paul', '2012-04-18', 'Thu', '07:29:52.0000000','R','09:35:00.0000000')
INSERT INTO [StaffDay]
VALUES('35', 'Paul', '2012-04-18', 'Fri', '07:29:52.0000000','R','06:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('35', 'Paul', '2012-04-18', 'Mon', '07:29:52.0000000','R','07:35:00.0000000')
----------------------------------------------------------------
Apart from the columns that the query creates, I need to create another 4 new columns after summing the cat2_hours as per the sample data as there are 4 different category2_id values (S,M,B,R) for e.g. :

For Graham it should be displayed as :

1. S_cat2hours = 15:30:00 (Grouped by category2_id = S)
2. M_cat2hours = 18:00:00 (Grouped by category2_id = M)
3. B_cat2hours = 18:30:00 (Grouped by category2_id = B)
4. R_cat2hours = 35:00:00 (Grouped by category2_id = R)

For Paul:

1. S_cat2hours = 16:35:00 (Grouped by category2_id = S)
2. M_cat2hours = 16:35:00 (Grouped by category2_id = M)
3. B_cat2hours = 23:35:00 (Grouped by category2_id = B)
4. R_cat2hours = 33:45:00 (Grouped by category2_id = R)

I tried to achieve this through this code and also by adding category2_id in the group by but that didnt work. :

case when category2_id = 'S' then SUM(cat2hours) end as S_cat2hours
case when category2_id = 'M' then SUM(cat2hours) end as M_cat2hours
case when category2_id = 'B' then SUM(cat2hours) end as B_cat2hours
case when category2_id = 'R' then SUM(cat2hours) end as R_cat2hours

I need to do the grouping somewhere in between the query but I dont know how to do that.

Am I able to explain the scenario clearly ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 14:33:05

i think what you're after is a way of crosstabbing cat2hours for each of categories.

Try something like

SUM(case when category2_id = 'S' then cat2hours end) as S_cat2hours
SUM(case when category2_id = 'M' then cat2hours end) as M_cat2hours
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources