|
lahsiv2004
Starting Member
6 Posts |
 Posted - 05/03/2012 : 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 ?
|
|
Grouping within a join
