Total by Group.
-
Dear All,
I am trying to bring the group total .below my query,
WITH Prep AS(SELECT preffered_name as Department ,email_address1 as subdepartment,friday,saturday,government_num as dutyhours,ci.person_num, ci.full_name, ci.event_name, CAST(DATEADD(hour,1,dbo.UtcToLocal(ci.creation_date)) AS DATE) AS event_date,DATEname(weekday,dbo.UtcToLocal(co.creation_date)) AS Day,
CAST(dbo.UtcToLocal(ci.creation_date) AS date) AS in_date, CAST(dbo.UtcToLocal(ci.creation_date) AS time) AS in_time,
CAST(dbo.UtcToLocal(co.creation_date) AS date) AS out_date, CAST(dbo.UtcToLocal(co.creation_date) AS time) AS out_time,
DATEDIFF(minute, ci.creation_date, co.creation_date) AS minutes
FROM TEST ci
OUTER APPLY (SELECT TOP 1 creation_date FROM TEST t WHERE t.person_num = ci.person_num AND t.creation_date >= ci.creation_date AND t.event_name = 'CLOCK_OUT' ORDER BY t.creation_date) co
WHERE ci.event_name = 'CLOCK_IN' AND preffered_name='finance' ),
CalcPart AS
(SELECT department,subdepartment,dutyhours,person_num, full_name, event_date, in_time, out_time, minutes,day,friday,saturday,
ROW_NUMBER() OVER (PARTITION BY event_date, person_num ORDER BY in_time) AS daypart
FROM Prep)
SELECT subdepartment,--full_name,
SUM(minutes) / 60.0 AS HoursWorked,
dutyhours,Day,friday,saturday,
SUM(minutes) / 60.0 AS HoursWorked,
case when ( [friday] = 1 and [day] = 'Friday' )
or ( [saturday] = 1 and [day] = 'Saturday' )
then SUM(minutes) / 60.0
else sum(( [minutes] /60.0)) - [Dutyhours]
end as OT
FROM CalcPart
GROUP BY subdepartment,dutyhours,day,friday,saturday
CURRENT RESULTS
EXPECTED OUTPUT
-
Use Grouping Sets.
FROM CalcPart
GROUP BY GROUPING SETS ((subdepartment), (subdepartment,dutyhours,day,friday,saturday))
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Result same any suggestion..
-
If you want tested results, post sample data and expected results as insert scripts as outlined in the first link in my signature. A picture of data is no good. It's like showing a picture of food to a starving man.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
here you go with DDL & DML,
DDL
CREATE TABLE action (
action_id int NOT NULL,
creation_date datetime,
PRIMARY KEY (action_id)
);
CREATE TABLE action_history (
action_id int NOT NULL,
person_id nvarchar(15),
person_num nvarchar(40),
event_name nvarchar(40),
PRIMARY KEY (action_id)
);
CREATE TABLE person (
person_id int NOT NULL,
person_num nvarchar(40),
government_num nvarchar(40),
preffered_name nvarchar(40),
email_address1 nvarchar(80),
PRIMARY KEY (person_id)
);
CREATE TABLE SHIFTDEFINATION(
person_id int NOT NULL,
friday nvarchar(40),
saturday nvarchar(40),
PRIMARY KEY (person_id)
);
CREATE VIEW [dbo].[TEST]
AS
SELECT dbo.action.action_id, dbo.person.person_id, dbo.action.creation_date, dbo.person.preffered_name, dbo.person.government_num, dbo.action.event_name AS EXP16, dbo.person.person_num,
dbo.action_history.event_name, dbo.person.email_address1, dbo.SHIFTDEFINATION.friday, dbo.SHIFTDEFINATION.saturday
FROM dbo.person FULL OUTER JOIN
dbo.SHIFTDEFINATION ON dbo.person.person_id = dbo.SHIFTDEFINATION.person_id INNER JOIN
dbo.action INNER JOIN
dbo.action_history ON dbo.action.action_id = dbo.action_history.action_id ON dbo.person.person_id = dbo.action_history.person_id
GO
DML
GOINSERT INTO action(action_id, creation_date)VALUES (206540, '2016-09-05 10:02:46.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206541, '2016-09-05 15:07:29.0' )
GO
INSERT INTO action(action_id, creation_date)VALUES (206542, '2016-09-06 10:07:32.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206543, '2016-09-06 15:07:38.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206544, '2016-09-07 11:07:58.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206545, '2016-09-07 15:08:07.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206546, '2016-09-08 10:08:14.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206547, '2016-09-08 15:08:19.0' )
GO
INSERT INTO action(action_id, creation_date)VALUES (206548, '2016-09-05 10:08:24.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206549, '2016-09-05 15:14:09.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206550, '2016-09-04 10:16:10.0' )
GO
INSERT INTO action(action_id, creation_date)VALUES (206551, '2016-09-04 15:16:11.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206552, '2016-09-06 10:16:12.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206553, '2016-09-06 15:16:21.0' )
GO
INSERT INTO action(action_id, creation_date)VALUES (206554, '2016-09-07 10:16:23.0' )
GO
INSERT INTO action(action_id, creation_date)VALUES (206555, '2016-09-07 15:16:23.0' )
GO
INSERT INTO action(action_id, creation_date)VALUES (206556, '2016-09-08 10:16:27.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206557, '2016-09-08 15:16:27.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206558, '2016-09-09 10:17:00.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206531, '2016-09-09 15:17:02.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206532, '2016-09-10 10:17:07.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206533, '2016-09-10 15:17:34.0')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206540', N'16483', N'00010082', N'CLOCK_IN')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206541', N'16483', N'00010082', N'CLOCK_OUT')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206542' ,N'16483', N'00010082', N'CLOCK_IN')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206543', N'16483', N'00010082', N'CLOCK_OUT')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206544', N'16483', N'00010082', N'CLOCK_IN')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206545', N'16483', N'00010082', N'CLOCK_OUT')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206546', N'16483', N'00010082', N'CLOCK_IN')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206547', N'16483', N'00010082', N'CLOCK_OUT')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206548', N'16483', N'00010082', N'CLOCK_IN')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206549', N'16483', N'00010082', N'CLOCK_OUT')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206550', N'16483', N'00010082', N'CLOCK_IN')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206551' ,N'16483', N'00010082', N'CLOCK_OUT')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206552', N'16483', N'00010082', N'CLOCK_IN')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206553', N'16483', N'00010082', N'CLOCK_OUT')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206554', N'16483', N'00010082', N'CLOCK_IN')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206555', N'16483', N'00010082', N'CLOCK_OUT')
GO
INSERT INTO person(person_id, person_num, government_num, preffered_name,email_address1) VALUES (164843, 00010089, 8, commercial,sales)
GO
CURRENT RESULT
Duty hours suppose to be 56 hours where is its shows only 48.
-
philand3 (11/2/2016)
here you go with DDL & DML,DDL
CREATE TABLE action (
action_id int NOT NULL,
creation_date datetime,
PRIMARY KEY (action_id)
);
CREATE TABLE action_history (
action_id int NOT NULL,
person_id nvarchar(15),
person_num nvarchar(40),
event_name nvarchar(40),
PRIMARY KEY (action_id)
);
CREATE TABLE person (
person_id int NOT NULL,
person_num nvarchar(40),
government_num nvarchar(40),
preffered_name nvarchar(40),
email_address1 nvarchar(80),
PRIMARY KEY (person_id)
);
CREATE TABLE SHIFTDEFINATION(
person_id int NOT NULL,
friday nvarchar(40),
saturday nvarchar(40),
PRIMARY KEY (person_id)
);
CREATE VIEW [dbo].[TEST]
AS
SELECT dbo.action.action_id, dbo.person.person_id, dbo.action.creation_date, dbo.person.preffered_name, dbo.person.government_num, dbo.action.event_name AS EXP16, dbo.person.person_num,
dbo.action_history.event_name, dbo.person.email_address1, dbo.SHIFTDEFINATION.friday, dbo.SHIFTDEFINATION.saturday
FROM dbo.person FULL OUTER JOIN
dbo.SHIFTDEFINATION ON dbo.person.person_id = dbo.SHIFTDEFINATION.person_id INNER JOIN
dbo.action INNER JOIN
dbo.action_history ON dbo.action.action_id = dbo.action_history.action_id ON dbo.person.person_id = dbo.action_history.person_id
GO
DML
GOINSERT INTO action(action_id, creation_date)VALUES (206540, '2016-09-05 10:02:46.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206541, '2016-09-05 15:07:29.0' )
GO
INSERT INTO action(action_id, creation_date)VALUES (206542, '2016-09-06 10:07:32.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206543, '2016-09-06 15:07:38.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206544, '2016-09-07 11:07:58.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206545, '2016-09-07 15:08:07.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206546, '2016-09-08 10:08:14.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206547, '2016-09-08 15:08:19.0' )
GO
INSERT INTO action(action_id, creation_date)VALUES (206548, '2016-09-05 10:08:24.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206549, '2016-09-05 15:14:09.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206550, '2016-09-04 10:16:10.0' )
GO
INSERT INTO action(action_id, creation_date)VALUES (206551, '2016-09-04 15:16:11.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206552, '2016-09-06 10:16:12.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206553, '2016-09-06 15:16:21.0' )
GO
INSERT INTO action(action_id, creation_date)VALUES (206554, '2016-09-07 10:16:23.0' )
GO
INSERT INTO action(action_id, creation_date)VALUES (206555, '2016-09-07 15:16:23.0' )
GO
INSERT INTO action(action_id, creation_date)VALUES (206556, '2016-09-08 10:16:27.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206557, '2016-09-08 15:16:27.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206558, '2016-09-09 10:17:00.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206531, '2016-09-09 15:17:02.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206532, '2016-09-10 10:17:07.0')
GO
INSERT INTO action(action_id, creation_date)VALUES (206533, '2016-09-10 15:17:34.0')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206540', N'16483', N'00010082', N'CLOCK_IN')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206541', N'16483', N'00010082', N'CLOCK_OUT')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206542' ,N'16483', N'00010082', N'CLOCK_IN')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206543', N'16483', N'00010082', N'CLOCK_OUT')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206544', N'16483', N'00010082', N'CLOCK_IN')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206545', N'16483', N'00010082', N'CLOCK_OUT')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206546', N'16483', N'00010082', N'CLOCK_IN')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206547', N'16483', N'00010082', N'CLOCK_OUT')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206548', N'16483', N'00010082', N'CLOCK_IN')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206549', N'16483', N'00010082', N'CLOCK_OUT')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206550', N'16483', N'00010082', N'CLOCK_IN')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206551' ,N'16483', N'00010082', N'CLOCK_OUT')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206552', N'16483', N'00010082', N'CLOCK_IN')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206553', N'16483', N'00010082', N'CLOCK_OUT')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206554', N'16483', N'00010082', N'CLOCK_IN')
GO
INSERT INTO action_history(action_id,person_id,person_num,event_name) values ( '206555', N'16483', N'00010082', N'CLOCK_OUT')
GO
INSERT INTO person(person_id, person_num, government_num, preffered_name,email_address1) VALUES (164843, 00010089, 8, commercial,sales)
GO
CURRENT RESULT
Duty hours suppose to be 56 hours where is its shows only 48.
Did you happen to notice the negative 8 hours in the OT column ? I suspect there's a datetime value out of order somewhere, which might need to be accounted for, but that -8 may well be part of the summation that produces the 48 figure. I didn't dig into the code to see what was going on, but I strongly suspect that either there's problem data or the order of two datetime values in a DATEDIFF function somewhere got reversed.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
You really should test your sample data scripts before posting.
* Your action table is missing the field event_name referenced in the view.
* You're view is missing the full_name field referenced in your initial query.
* Your person table is missing the preffered_name field mentioned in your view. (PS: the correct spelling is preferred.)
* Your action_history table contains actions for person 16483, but your person table contains only person id 164843.
* The FULL OUTER JOIN in your view is being turned into a LEFT OUTER JOIN by the subsequent JOINS in the view.
* Your query uses a UDF that you did not provide the code for.
Also, you haven't given enough information to troubleshoot the problem. There are just too many steps between your raw data and the expected results to know where the problem lies. You should treat the last CTE that produces correct results as your base table for the sample. So instead of giving action, action_history, person, and SHIFTDEFINATION, you should just treat CalcPart as a table and script the inserts into that.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Hi,
i am using following query to bring the following result,
;WITH Prep AS(
SELECT
preffered_name as Department ,email_address1 as subdepartment,friday,saturday,government_num as dutyhours,ci.person_num, ci.event_name, CAST(DATEADD(hour,1,dbo.UtcToLocal(ci.creation_date)) AS DATE) AS event_date,DATEname(weekday,dbo.UtcToLocal(co.creation_date)) AS Day,
CAST(dbo.UtcToLocal(ci.creation_date) AS date) AS in_date, CAST(dbo.UtcToLocal(ci.creation_date) AS time) AS in_time,
CAST(dbo.UtcToLocal(co.creation_date) AS date) AS out_date, CAST(dbo.UtcToLocal(co.creation_date) AS time) AS out_time,
DATEDIFF(minute, ci.creation_date, co.creation_date) AS minutes
FROm dbo.test ci
OUTER APPLY
(
SELECT TOP 1 creation_date
FROM dbo.test t
WHERE t.person_num = ci.person_num AND t.creation_date >= ci.creation_date AND t.event_name = 'CLOCK_OUT'
ORDER BY t.creation_date
) co
WHERE ci.event_name = 'CLOCK_IN' AND preffered_name='COMMERCIAL'
)
,CalcPart AS
(
SELECT
department,subdepartment,dutyhours,person_num, event_date, in_time, out_time, minutes,day,friday,saturday,
ROW_NUMBER() OVER (PARTITION BY event_date, person_num ORDER BY in_time) AS daypart
FROM Prep
)
,tmpResult as
(
SELECT
subdepartment,--full_name,
SUM(minutes) / 60.0 AS HoursWorked,
dutyhours,Day,friday,saturday,
--SUM(minutes) / 60.0 AS HoursWorked,-----This column is duplicate and could be removed
case
when ( [friday] = 1 and [day] = 'Friday' ) or ( [saturday] = 1 and [day] = 'Saturday' ) then
SUM(minutes) / 60.0
else
sum(( [minutes] /60.0)) - [Dutyhours]
end as OT
FROM CalcPart
GROUP BY subdepartment,dutyhours,day,friday,saturday
)
select
subdepartment AS SUBDEPARTMENT,
SUM(cast(DutyHours as decimal(10,0))) as DUTYHOURS,
cast(sum(HoursWorked) as int) as HOURSWORKED,
cast(sum(OT) as int) as OT
from tmpResult
group by subdepartment
incorrect result,
-
philand3 (11/2/2016)
Hi,i am using following query to bring the following result,
incorrect result,
The ONLY way you can get a NEGATIVE VALUE for a SUM is when at least one of the values it's trying to do the sum on is negative. I'd suggest that you either have BAD DATA, or your MINUTES calculation is somehow operating on a reversed set of date/time values. Why not try running a query that does nothing but calculate the minutes and see which rows come up with negative values ? And here's another thing... what do you do when DutyHours is greater than the actual hours worked? That will definitely give you a negative value for the OT calculation. Seems to me that this whole query needs to be re-thought from the ground up. First order of business is to establish a set of clock-in and clock-out times that do not have overlap problems or sequence issues. Without that, there's going to be problems of one kind or another. Once you have good clock-in and clock-out times, the rest might actually be relatively easy.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
hi here OT is computed from dutyhours vs hoursworked.
i am not looking t OT instead my issue is with dutyhours incorrect computation.
Checking again to see where went wrong with what,
-
philand3 (11/2/2016)
hi here OT is computed from dutyhours vs hoursworked.i am not looking t OT instead my issue is with dutyhours incorrect computation.
Checking again to see where went wrong with what,
Now that I've had time to look more closely at the query, I need to understand how the Daypart value figures into HoursWorked. What I'm seeing is that if there are multiple clock-in and clock-out event pairs for the same group, ALL the hours on ALL the clock-in / clock-out event pairs get summed, and thus I don't really understand what qualifies any particular clock-in / clock-out event pairs' hours as OT, either in part or in full, because that summation within the group could just as easily be less than 8 hours or considerably greater. Does 1 event pair represent one actual person? And does multiple event pairs for the same group represent the same person? If so, how does one treat the hours from the different Daypart values, both for OT and for summation at the group level ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
philand3 (11/2/2016)
hi here OT is computed from dutyhours vs hoursworked.i am not looking t OT instead my issue is with dutyhours incorrect computation.
Checking again to see where went wrong with what,
For this query, if you're going to group everything down by subdepartment in the end, then why bother doing the CalcPart subquery, which you were using to put the multiple CLOCK events for the same day into columns in a different query?
I also notice in your query the dutyhours comes directly from the government_num column (NVARCHAR(40) ???) in the Person table. It's not clear what your expected results are when summed and grouped by subdepartment. My initial guess with the limited data available is that since a person can clock in and out multiple times in the same day that you need to sum the hours worked by day, then compare that to the dutyhours, so you ensure you're only calculating dutyhours once per day instead of each clock in / out pair.
-
Given the numerous threads the OP has started on what appears to be the same exact query, I think that this task is simply too complex to be handled in a free forum. The OP should look into hiring a consultant.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Hello Chris,
Thanks i am able see the result . thinking & applied same your logic,
Added in select (cast(government_num as decimal(10,0))) as dutyhours and further added in subquery cast(sum(dutyhours) as int) as dutyhours,
which resulted correctly. was thought of counting creation date and further multiply with 8 hours. however without this i got the result.
-
drew.allen (11/2/2016)
Given the numerous threads the OP has started on what appears to be the same exact query, I think that this task is simply too complex to be handled in a free forum. The OP should look into hiring a consultant.Drew
Each one has actually been slightly different serving a different purpose. The bigger problem each time though has been getting the DDL and source data to effectively test the queries, which makes it harder for anyone to help. They seem to be getting a little closer each time though.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply