Displaying Multiple rows clock event Under a different Column view
-
I would like to query the data were table output is by each row but i need need display the query with single line and different column like (Clock in 1, clock out 1, clock in 2 , clock out2,clock in 3, clock out 3, clock in 4,clock out 4 for each day.
my table look like this,
creation_date event_name person_num full_name
7/21/2016 18:00:00:00 PM CLOCK_IN 40014 OSU
7/21/2016 19:00:00:00 PM CLOCK_OUT 40014 OSU
7/21/2016 20:00:00:00 PM CLOCK_IN 40014 OSU
7/21/2016 21:00:00:00 PM CLOCK_OUT 40014 OSU
7/21/2016 21:10:00:00 PM CLOCK_IN 40014 OSU
7/21/2016 21:30:00:00 PM CLOCK_OUT 40014 OSU
Expected output,
creation_Date person_num full_name CLOCK_IN1 CLOCK_OUT1 CLOCK_IN2 CLOCK_OUT2 CLOCK_IN3 CLOCK_OUT3 HoursWorked
7/21/2016 40014 OSU 18:00 19:00 20:00 21:00 21:10 21:30 2.2
my current code display only clock in 1, clock out 1, clock in2, clock out 2 . also TIME SPAN is not working in this.
WITH Prep AS (SELECT CAST(dbo.UtcToLocal(creation_date) AS date) AS creation_date, person_num, full_name, MIN(CASE event_name WHEN 'CLOCK_IN' THEN CAST(dbo.UtcToLocal(creation_date) AS TIME) END) AS CLOCK_IN1, MIN(CASE event_name WHEN 'CLOCK_OUT' THEN CAST(dbo.UtcToLocal(creation_date) AS TIME) END) AS CLOCK_OUT1, NULLIF (MAX(CASE event_name WHEN 'CLOCK_IN' THEN CAST(dbo.UtcToLocal(creation_date) AS TIME) END), MIN(CASE event_name WHEN 'CLOCK_IN' THEN CAST(dbo.UtcToLocal(creation_date) AS TIME) END)) AS CLOCK_IN2, NULLIF (MAX(CASE event_name WHEN 'CLOCK_OUT' THEN CAST(dbo.UtcToLocal(creation_date) AS TIME) END), MIN(CASE event_name WHEN 'CLOCK_OUT' THEN CAST(dbo.UtcToLocal(creation_date) AS TIME) END)) AS CLOCK_OUT2 FROM TEST GROUP BY ALL CAST(dbo.UtcToLocal(creation_date) AS date), person_num, full_name, ) SELECT creation_Date, person_num,, full_name, CLOCK_IN1, CLOCK_OUT1, CLOCK_IN2, CLOCK_OUT2, DATEDIFF(MINUTE, CLOCK_IN1, CLOCK_OUT1) / 60 + ISNULL(DATEDIFF(MINUTE, CLOCK_IN2, CLOCK_OUT2) / 60, 0) AS HoursWorked FROM Prep
-
sounds like you could use what's called cross-tab queries, such as presented by Jeff Moden:
-
here's a quick example query based on the info you provided:
WITH Prep AS
(SELECT CAST(creation_date AS date) AS creation_date, CAST(creation_date AS time) AS creation_time, person_num, full_name, event_name,
ROW_NUMBER() OVER (PARTITION BY CAST(creation_date AS date), person_num, full_name, event_name ORDER BY creation_date) AS daypart
FROM TEST)
SELECT creation_date, person_num, full_name,
MAX(CASE WHEN daypart = 1 AND event_name = 'CLOCK_IN' THEN creation_time END) AS CLOCK_IN1,
MAX(CASE WHEN daypart = 1 AND event_name = 'CLOCK_OUT' THEN creation_time END) AS CLOCK_OUT1,
MAX(CASE WHEN daypart = 2 AND event_name = 'CLOCK_IN' THEN creation_time END) AS CLOCK_IN2,
MAX(CASE WHEN daypart = 2 AND event_name = 'CLOCK_OUT' THEN creation_time END) AS CLOCK_OUT2,
MAX(CASE WHEN daypart = 3 AND event_name = 'CLOCK_IN' THEN creation_time END) AS CLOCK_IN3,
MAX(CASE WHEN daypart = 3 AND event_name = 'CLOCK_OUT' THEN creation_time END) AS CLOCK_OUT3,
MAX(CASE WHEN daypart = 4 AND event_name = 'CLOCK_IN' THEN creation_time END) AS CLOCK_IN4,
MAX(CASE WHEN daypart = 4 AND event_name = 'CLOCK_OUT' THEN creation_time END) AS CLOCK_OUT4
FROM Prep
GROUP BY creation_date, person_num, full_name
in the prep I group the IN/OUT pairs into a "daypart", then use that and the event_name to pivot the data into columns.
-
chris thanks for your useful query . but i am seeing timespan issue. how to overcome this can you help?
WITH Prep AS
(SELECT CAST(dbo.UtcToLocal(creation_date) AS date) AS creation_date, CAST(dbo.UtcToLocal(creation_date) AS time) AS creation_time, person_num, full_name, event_name,
ROW_NUMBER() OVER (PARTITION BY CAST(dbo.Utctolocal(creation_date) AS date), person_num, full_name, event_name ORDER BY creation_date) AS daypart
FROM TEST)
SELECT creation_date, person_num, full_name,
MAX(CASE WHEN daypart = 1 AND event_name = 'CLOCK_IN' THEN creation_time END) AS CLOCK_IN1,
MAX(CASE WHEN daypart = 1 AND event_name = 'CLOCK_OUT' THEN creation_time END) AS CLOCK_OUT1,
MAX(CASE WHEN daypart = 2 AND event_name = 'CLOCK_IN' THEN creation_time END) AS CLOCK_IN2,
MAX(CASE WHEN daypart = 2 AND event_name = 'CLOCK_OUT' THEN creation_time END) AS CLOCK_OUT2,
MAX(CASE WHEN daypart = 3 AND event_name = 'CLOCK_IN' THEN creation_time END) AS CLOCK_IN3,
MAX(CASE WHEN daypart = 3 AND event_name = 'CLOCK_OUT' THEN creation_time END) AS CLOCK_OUT3,
MAX(CASE WHEN daypart = 4 AND event_name = 'CLOCK_IN' THEN creation_time END) AS CLOCK_IN4,
MAX(CASE WHEN daypart = 4 AND event_name = 'CLOCK_OUT' THEN creation_time END) AS CLOCK_OUT4
FROM Prep
GROUP BY creation_date, person_num, full_name
timespan output
2016-07-30000107 KATH 23:51:24.1870000NULL
2016-07-31000107 KATH23:31:24.187000008:14:24.1870000
2016-08-01000107 KATH23:53:24.187000008:04:24.1870000
2016-08-02000107 KATH23:53:24.187000008:00:24.1870000
2016-08-03000107 KATH 23:54:24.187000008:07:24.1870000
2016-08-04000107 KATH23:45:24.187000008:05:24.1870000
2016-08-05000107 KATH19:51:24.187000010:03:24.1870000
as per above employee clock in 23:51 and date 07/30 and clock out 08:14 and date i 07/31 respectively next also same clock in is 23:31 and date is 07/31 and clock out is 08:04 and date os 08/01.,,
we have got three shift 1, 8 am -4pm 2,4pm -12am 3,00:00am to 8 am
-
OK, I was unaware there were shifts that would cause the clock-in clock-out to potentially overlap the day boundary. Do you have a table that defines the shift start and end times? That would be more useful for you than doing the PARTITION BY creation_date method for grouping the "days"
-
chris,
Got on suggestion, table column name is shift starts from 1 to 10
If it is 1 = 8 am to 4pm
if it is 2 =4 pm to 12 am
if it is 3 = 00:00 am to 8 am
if it is 4 = 9 am to 5.30 pm
if it is 5 = 2 pm to 10 pm
if it is 6 = 4 pm to 12 am
if this work i can change each empoyee shift name from 1 to 10 whenever they changed.
is it possible to incorporate in SQL query ?
-
OK, sorry for the delay in getting back to you, busy day at work today. If they can change shifts that would make it difficult to work with the shift hours table. If there's always a clock_out event for every clock_in event, then maybe we can pair those together before the pivot. We'd probably need to determine an event_date since it looks like the creation_date may be slightly before midnight, so maybe something like this?
WITH Prep AS(SELECT creation_date, CAST(DATEADD(hour,1,creation_date) AS date) AS event_date, person_num, full_name, event_name,
ROW_NUMBER() OVER (PARTITION BY person_num, full_name, event_name ORDER BY creation_date) AS event_sort
FROM TEST),
Paired AS
(SELECT ci.event_date, ci.event_sort, ci.person_num, ci.full_name, ci.creation_date AS in_time, co.creation_date AS out_time, DATEDIFF(minute, ci.creation_date, co.creation_date) AS minutes,
ROW_NUMBER() OVER (PARTITION BY ci.event_date, ci.person_num, ci.full_name ORDER BY ci.event_sort) AS daypart
FROM Prep ci
INNER JOIN Prep co ON ci.person_num = co.person_num AND ci.full_name = co.full_name AND ci.event_sort = co.event_sort
WHERE ci.event_name = 'CLOCK_IN' AND co.event_name = 'CLOCK_OUT')
SELECT event_date, person_num, full_name,
CAST(MAX(CASE WHEN daypart = 1 THEN in_time END) AS time) AS CLOCK_IN1,
CAST(MAX(CASE WHEN daypart = 1 THEN out_time END) AS time) AS CLOCK_OUT1,
CAST(MAX(CASE WHEN daypart = 2 THEN in_time END) AS time) AS CLOCK_IN2,
CAST(MAX(CASE WHEN daypart = 2 THEN out_time END) AS time) AS CLOCK_OUT2,
CAST(MAX(CASE WHEN daypart = 3 THEN in_time END) AS time) AS CLOCK_IN3,
CAST(MAX(CASE WHEN daypart = 3 THEN out_time END) AS time) AS CLOCK_OUT3,
CAST(MAX(CASE WHEN daypart = 4 THEN in_time END) AS time) AS CLOCK_IN4,
CAST(MAX(CASE WHEN daypart = 4 THEN out_time END) AS time) AS CLOCK_OUT4,
SUM(minutes) / 60.0 AS HoursWorked
FROM Paired
GROUP BY event_date, person_num, full_name
-
Hello chris,
Thanks but bit diffcult to come desired output due to UTCTOLOCAL .
i am trying but failed but in your previous information i was able to add dbo.Utctolocal. but in your last query i am not able to fetch the UTC.
could you help ?
-
That must be a custom function you have locally, it's not part of SQL Server 2014. I don't know enough about it to understand what problem you are having with it.
-
hello chris as per you query,
2016-07-2100010079JOHN STEVENS13:52:56.000000014:30:33.000000013:53:02.000000004:36:30.0000000NULLNULLNULLNULL39.350000
total hours shows 39:35 which is not correct.
what you think?
-
I'd have to see the original rows from your TEST table for this person. Is there a mismatch on the number of CLOCK_IN vs CLOCK_OUT entries for this person?
-
TABLE OUTPUT
query output,
-
chris pls note that ,creation_Date is UTC datettime. which i am converting with this scalar valued function ,
USE [test]
GO
/****** Object: UserDefinedFunction [dbo].[UtcToLocal] Script Date: 10/19/2016 12:33:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[UtcToLocal]
(
@p_utcDatetime DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), @p_utcDatetime), GETDATE())
END
-
I'm able to create this function and add it to my test database fine, I'm not sure what problem you had:
WITH Prep AS
(SELECT dbo.UtcToLocal(creation_date) AS creation_date, CAST(DATEADD(hour,1,dbo.UtcToLocal(creation_date)) AS date) AS event_date, person_num, full_name, event_name,
ROW_NUMBER() OVER (PARTITION BY person_num, full_name, event_name ORDER BY creation_date) AS event_sort
FROM TEST),
Paired AS
(SELECT ci.event_date, ci.event_sort, ci.person_num, ci.full_name, ci.creation_date AS in_time, co.creation_date AS out_time, DATEDIFF(minute, ci.creation_date, co.creation_date) AS minutes,
ROW_NUMBER() OVER (PARTITION BY ci.event_date, ci.person_num, ci.full_name ORDER BY ci.event_sort) AS daypart
FROM Prep ci
INNER JOIN Prep co ON ci.person_num = co.person_num AND ci.full_name = co.full_name AND ci.event_sort = co.event_sort
WHERE ci.event_name = 'CLOCK_IN' AND co.event_name = 'CLOCK_OUT')
SELECT event_date, person_num, full_name,
CAST(MAX(CASE WHEN daypart = 1 THEN in_time END) AS time) AS CLOCK_IN1,
CAST(MAX(CASE WHEN daypart = 1 THEN out_time END) AS time) AS CLOCK_OUT1,
CAST(MAX(CASE WHEN daypart = 2 THEN in_time END) AS time) AS CLOCK_IN2,
CAST(MAX(CASE WHEN daypart = 2 THEN out_time END) AS time) AS CLOCK_OUT2,
CAST(MAX(CASE WHEN daypart = 3 THEN in_time END) AS time) AS CLOCK_IN3,
CAST(MAX(CASE WHEN daypart = 3 THEN out_time END) AS time) AS CLOCK_OUT3,
CAST(MAX(CASE WHEN daypart = 4 THEN in_time END) AS time) AS CLOCK_IN4,
CAST(MAX(CASE WHEN daypart = 4 THEN out_time END) AS time) AS CLOCK_OUT4,
SUM(minutes) / 60.0 AS HoursWorked
FROM Paired
GROUP BY event_date, person_num, full_name
-
I also don't get the same results for the data you've shown. Here's my complete test setup with your sample data:
CREATE TABLE TEST (creation_date datetime, event_name varchar(10), person_num int, full_name varchar(20))
INSERT INTO TEST
VALUES
('7/21/2016 18:00:00:00 PM', 'CLOCK_IN', 40014, 'OSU'),
('7/21/2016 19:00:00:00 PM', 'CLOCK_OUT', 40014, 'OSU'),
('7/21/2016 20:00:00:00 PM', 'CLOCK_IN', 40014, 'OSU'),
('7/21/2016 21:00:00:00 PM', 'CLOCK_OUT', 40014, 'OSU'),
('7/21/2016 21:10:00:00 PM', 'CLOCK_IN', 40014, 'OSU'),
('7/21/2016 21:30:00:00 PM', 'CLOCK_OUT', 40014, 'OSU');
INSERT INTO TEST
VALUES
('2016-07-30 11:51:24 PM', 'CLOCK_IN', 000107, 'KATH'),
('2016-07-31 08:14:24 AM', 'CLOCK_OUT', 000107, 'KATH'),
('2016-07-31 11:31:24 PM', 'CLOCK_IN', 000107, 'KATH'),
('2016-08-01 08:04:24 AM', 'CLOCK_OUT', 000107, 'KATH'),
('2016-08-01 11:31:24 PM', 'CLOCK_IN', 000107, 'KATH'),
('2016-08-02 08:00:24 AM', 'CLOCK_OUT', 000107, 'KATH'),
('2016-08-02 11:53:24 PM', 'CLOCK_IN', 000107, 'KATH'),
('2016-08-03 08:07:24 AM', 'CLOCK_OUT', 000107, 'KATH'),
('2016-08-03 11:54:24 PM', 'CLOCK_IN', 000107, 'KATH'),
('2016-08-04 08:05:24 AM', 'CLOCK_OUT', 000107, 'KATH'),
('2016-08-04 11:45:24 PM', 'CLOCK_IN', 000107, 'KATH'),
('2016-08-05 10:03:24 AM', 'CLOCK_OUT', 000107, 'KATH'),
('2016-08-05 07:51:24 PM', 'CLOCK_IN', 000107, 'KATH'),
('2016-08-06 04:11:24 AM', 'CLOCK_OUT', 000107, 'KATH');
INSERT INTO TEST
VALUES
('2016-07-26 03:57:34 AM', 'CLOCK_IN', 00010079, 'JOHN'),
('2016-07-26 03:57:38 AM', 'CLOCK_OUT', 00010079, 'JOHN'),
('2016-07-26 03:57:41 AM', 'CLOCK_IN', 00010079, 'JOHN'),
('2016-07-26 04:41:09 AM', 'CLOCK_OUT', 00010079, 'JOHN'),
('2016-07-26 02:08:43 PM', 'CLOCK_IN', 00010079, 'JOHN'),
('2016-07-26 03:06:48 PM', 'CLOCK_OUT', 00010079, 'JOHN'),
('2016-07-27 03:56:55 AM', 'CLOCK_IN', 00010079, 'JOHN'),
('2016-07-27 04:48:19 AM', 'CLOCK_OUT', 00010079, 'JOHN'),
('2016-07-27 02:35:40 PM', 'CLOCK_IN', 00010079, 'JOHN'),
('2016-07-27 03:24:30 PM', 'CLOCK_OUT', 00010079, 'JOHN'),
('2016-07-28 03:55:11 AM', 'CLOCK_IN', 00010079, 'JOHN'),
('2016-07-28 04:51:54 AM', 'CLOCK_OUT', 00010079, 'JOHN'),
('2016-07-28 01:57:37 PM', 'CLOCK_IN', 00010079, 'JOHN'),
('2016-07-28 02:37:19 PM', 'CLOCK_OUT', 00010079, 'JOHN');
WITH Prep AS
(SELECT creation_date, CAST(DATEADD(hour,1,creation_date) AS date) AS event_date, person_num, full_name, event_name,
ROW_NUMBER() OVER (PARTITION BY person_num, full_name, event_name ORDER BY creation_date) AS event_sort
FROM TEST),
Paired AS
(SELECT ci.event_date, ci.event_sort, ci.person_num, ci.full_name, ci.creation_date AS in_time, co.creation_date AS out_time, DATEDIFF(minute, ci.creation_date, co.creation_date) AS minutes,
ROW_NUMBER() OVER (PARTITION BY ci.event_date, ci.person_num, ci.full_name ORDER BY ci.event_sort) AS daypart
FROM Prep ci
INNER JOIN Prep co ON ci.person_num = co.person_num AND ci.full_name = co.full_name AND ci.event_sort = co.event_sort
WHERE ci.event_name = 'CLOCK_IN' AND co.event_name = 'CLOCK_OUT')
SELECT event_date, person_num, full_name,
CAST(MAX(CASE WHEN daypart = 1 THEN in_time END) AS time) AS CLOCK_IN1,
CAST(MAX(CASE WHEN daypart = 1 THEN out_time END) AS time) AS CLOCK_OUT1,
CAST(MAX(CASE WHEN daypart = 2 THEN in_time END) AS time) AS CLOCK_IN2,
CAST(MAX(CASE WHEN daypart = 2 THEN out_time END) AS time) AS CLOCK_OUT2,
CAST(MAX(CASE WHEN daypart = 3 THEN in_time END) AS time) AS CLOCK_IN3,
CAST(MAX(CASE WHEN daypart = 3 THEN out_time END) AS time) AS CLOCK_OUT3,
CAST(MAX(CASE WHEN daypart = 4 THEN in_time END) AS time) AS CLOCK_IN4,
CAST(MAX(CASE WHEN daypart = 4 THEN out_time END) AS time) AS CLOCK_OUT4,
SUM(minutes) / 60.0 AS HoursWorked
FROM Paired
GROUP BY event_date, person_num, full_name
gives these results:
event_date person_num full_name CLOCK_IN1 CLOCK_OUT1 CLOCK_IN2 CLOCK_OUT2 CLOCK_IN3 CLOCK_OUT3 CLOCK_IN4 CLOCK_OUT4 HoursWorked
---------- ----------- ---------- --------- ---------- --------- ---------- --------- ---------- --------- ---------- -----------
2016-07-21 40014 OSU 18:00:00 19:00:00 20:00:00 21:00:00 21:10:00 21:30:00 NULL NULL 2.333333
2016-07-26 10079 JOHN 03:57:34 03:57:38 03:57:41 04:41:09 14:08:43 15:06:48 NULL NULL 1.700000
2016-07-27 10079 JOHN 03:56:55 04:48:19 14:35:40 15:24:30 NULL NULL NULL NULL 1.683333
2016-07-28 10079 JOHN 03:55:11 04:51:54 13:57:37 14:37:19 NULL NULL NULL NULL 1.600000
2016-07-31 107 KATH 23:51:24 08:14:24 NULL NULL NULL NULL NULL NULL 8.383333
2016-08-01 107 KATH 23:31:24 08:04:24 NULL NULL NULL NULL NULL NULL 8.550000
2016-08-02 107 KATH 23:31:24 08:00:24 NULL NULL NULL NULL NULL NULL 8.483333
2016-08-03 107 KATH 23:53:24 08:07:24 NULL NULL NULL NULL NULL NULL 8.233333
2016-08-04 107 KATH 23:54:24 08:05:24 NULL NULL NULL NULL NULL NULL 8.183333
2016-08-05 107 KATH 23:45:24 10:03:24 19:51:24 04:11:24 NULL NULL NULL NULL 18.633333
in yours, the CLOCK_OUT1 time for 7/26 person 10079 looks like it's from 7/28?
What results do you get for this query?
SELECT creation_date, CAST(DATEADD(hour,1,creation_date) AS date) AS event_date, person_num, full_name, event_name,
ROW_NUMBER() OVER (PARTITION BY person_num, full_name, event_name ORDER BY creation_date) AS event_sort
FROM TEST
WHERE person_num = '00010079' AND creation_date BETWEEN '2016-07-26' AND '2016-07-27'
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply