September 28, 2016 at 12:57 pm
My table look like this,
INSERT INTO dbo.test (`action_id`,`person_num`,`person_id`,`creation_date`,`event_name`,`SHIFT`,`middle_name`,`full_name`) VALUES ('168448','10148','16477','7/21/2016 11:38','CLOCK_IN','1','DAY','RICHAR');
INSERT INTO dbo.test (`action_id`,`person_num`,`person_id`,`creation_date`,`event_name`,`SHIFT`,`middle_name`,`full_name`) VALUES ('168780','10148','16477','7/21/2016 15:00','CLOCK_OUT','1','DAY','RICHAR');
INSERT INTO dbo.test (`action_id`,`person_num`,`person_id`,`creation_date`,`event_name`,`SHIFT`,`middle_name`,`full_name`) VALUES ('169121','10148','16477','7/23/2016 7:00','CLOCK_IN','1','DAY','RICHAR');
INSERT INTO dbo.test (`action_id`,`person_num`,`person_id`,`creation_date`,`event_name`,`SHIFT`,`middle_name`,`full_name`) VALUES ('169146','10148','16477','7/23/2016 9:00','CLOCK_OUT','1','DAY','RICHAR');
INSERT INTO dbo.test (`action_id`,`person_num`,`person_id`,`creation_date`,`event_name`,`SHIFT`,`middle_name`,`full_name`) VALUES ('169148','10148','16477','7/23/2016 13:00','CLOCK_IN','1','DAY','RICHAR');
INSERT INTO dbo.test (`action_id`,`person_num`,`person_id`,`creation_date`,`event_name`,`SHIFT`,`middle_name`,`full_name`) VALUES ('169340','10148','16477','7/23/2016 17:00','CLOCK_OUT','1','DAY','RICHAR');
INSERT INTO dbo.test (`action_id`,`person_num`,`person_id`,`creation_date`,`event_name`,`SHIFT`,`middle_name`,`full_name`) VALUES ('169650','10148','16477','7/24/2016 5:42','CLOCK_IN','1','DAY','RICHAR');
INSERT INTO dbo.test (`action_id`,`person_num`,`person_id`,`creation_date`,`event_name`,`SHIFT`,`middle_name`,`full_name`) VALUES ('175112','10148','16477','7/24/2016 16:45','CLOCK_OUT','1','DAY','RICHAR');
Expected output;
CREATION_DATE|PREFERRED_NAME|PERSON_NUM|FULL_NAME|CLOCK IN |CLOCK OUT|CLOCK IN |CLOCK OUT|CLOCK IN |CLOCK OUT|CLOCK IN |CLOCK OUT|CLOCK IN |CLOCK OUT|CLOCK IN |CLOCK OUT|TOTAL WORKED HOURS |
21/07/2016|FINANCE|10148|richard|9:00:00 AM|10:00:00 AM|11:00:00 AM|23.45:00 PM|13.45|
23/07/2016|FINANCE|10148|richard|7:00:00 AM|9:00:00 AM|1:00:00 PM|05.00:00 PM|6
September 28, 2016 at 1:23 pm
This should not be hard, I'm looking at this now.
In the meantime, in case people want easily consumable sample data...
SELECT * INTO dbo.test
FROM
(
VALUES ('168780','10148','16477','7/21/2016 15:00','CLOCK_OUT','1','DAY','RICHAR'),
('168448','10148','16477','7/21/2016 11:38','CLOCK_IN','1','DAY','RICHAR'),
('169121','10148','16477','7/23/2016 7:00','CLOCK_IN','1','DAY','RICHAR'),
('169146','10148','16477','7/23/2016 9:00','CLOCK_OUT','1','DAY','RICHAR'),
('169148','10148','16477','7/23/2016 13:00','CLOCK_IN','1','DAY','RICHAR'),
('169340','10148','16477','7/23/2016 17:00','CLOCK_OUT','1','DAY','RICHAR'),
('169650','10148','16477','7/24/2016 5:42','CLOCK_IN','1','DAY','RICHAR'),
('175112','10148','16477','7/24/2016 16:45','CLOCK_OUT','1','DAY','RICHAR')
) t(action_id,person_num,person_id,creation_date,event_name,[SHIFT],middle_name,full_name);
-- Itzik Ben-Gan 2001
September 28, 2016 at 1:48 pm
Provided you can get away with up to 2 clock_in's and clock_out's per person/date you can do this:
WITH Prep AS
(
SELECT
creation_date = CAST(creation_date AS date),
person_num,
full_name,
CLOCK_IN1 =
MIN(CASE event_name WHEN 'CLOCK_IN' THEN CAST(creation_date AS TIME) END),
CLOCK_OUT1 =
MIN(CASE event_name WHEN 'CLOCK_OUT' THEN CAST(creation_date AS TIME) END),
CLOCK_IN2 = NULLIF
(
MAX(CASE event_name WHEN 'CLOCK_IN' THEN CAST(creation_date AS TIME) END),
MIN(CASE event_name WHEN 'CLOCK_IN' THEN CAST(creation_date AS TIME) END)
),
CLOCK_OUT2 = NULLIF
(
MAX(CASE event_name WHEN 'CLOCK_OUT' THEN CAST(creation_date AS TIME) END),
MIN(CASE event_name WHEN 'CLOCK_OUT' THEN CAST(creation_date AS TIME) END)
)
FROM dbo.test
GROUP BY ALL
CAST(creation_date AS date),
person_num,
full_name
)
SELECT *, HoursWorked =
DATEDIFF(MINUTE,CLOCK_IN1,CLOCK_OUT1)/60 +
ISNULL(DATEDIFF(MINUTE,CLOCK_IN2,CLOCK_OUT2)/60,0)
FROM prep
-- Itzik Ben-Gan 2001
September 28, 2016 at 2:11 pm
Hello Alan,
Its Good One ...Thanks.
Also how to achieve the following,
Req 1 : 11:38:32.000000011:38:27.0000000 (.0000000 decimal need to be removed)
Req 2 : creation date need to be convert to ToLocalTime.
Thanks,
September 28, 2016 at 2:49 pm
>> My table look like this, <<
Your DDL did not print; all we got was a gray bar in your posting. But what you did post in the way of insertion statement needs a lot of work. You got the the wrong temporal display for your dates, and you do not know how to do a row value constructor statement. But more than that, your data model is completely wrong. Clocking in and out are part of what the ISO standards and basic data modeling: interval data type. It is made up of a pair of values. Your clock_in_timestamp and clock_out_timestamp should not be split or turned into an metadata flag problem. You also do not know that the only display format for dates in ANSI/ISO standard SQL is ISO 8601; the local dialect, you picked is one of the most ambiguous and hard to use because of variable spacing. Let us redo your test data. Okay, I think it will help
We cannot have a column called "person" because it is just too general; what role do they play in the data model? Did you ever think about normalizing your table and pulling this out? It looks like what you have done is copy into a table the stuff it would appear on the printed paper form, instead of building a valid data model. It is also sort of weird to have both a full name in the middle name as attributes. The middle name, last name and first name are the usual split of full name and a table.
Why do you have both an employee number and an employee id? What is the difference? It looks like your employee number is redundant, but we have no specs
CREATE TABLE Personnel
(–- emp_num CHAR(5) NOT NULL looks redundant
emp_id CHAR(5) NOT NULL PRIMARY KEY,
emp_middle_name VARCHAR(35) NOT NULL,
emp_full_name VARCHAR(35) NOT NULL);
INSERT INTO Personnel
VALUES ('16477’, 'Day’, 'Richard’);
I know it is a dummy for posting, try to give even the dummy tables a meaningful name. It just shows that you know what you are doing and you are thinking clearly. I am not sure what an action_id is, but I assume it is somehow important and marks going on off shift. Did I guess right?
The ISO 8601 temporal model is based on half open intervals. We need a pair of start and stop temporal points. This is like the (longitude, latitude) pairs that locate a position in space. If you have trouble with temporal SQL, then download the PDF of the Rick Snodgrass book from the University of Arizona. He is a classic, and it is free.
By definition, a table must have a primary key (you completely fail to post DDL; please read the forum rules).
CREATE TABLE Timecards
(emp_id CHAR(5) NOT NULL
REFERENCES Personnel
ON UPDATE CASCADE
ON UPDATE CASCADE,
clock_in_action_id CHAR(6) NOT NULL,
clock_in_timestamp DATETIME2(0) NOT NULL,
PRIMARY KEY (emp_id, clock_in_action_id), --- guessing!
clock_out_action_id CHAR(6) DEFAULT ?? NOT NULL,
clock_out_timestamp DATETIME2(0),
CHECK (clock_in_timestamp <= clock_out_timestamp),
shift_nbr SMALLINT DEFAULT 1 NOT NULL);
INSERT INTO Timecards
('16477', 168448', '2016-07-21 11:38:00', '168780', '2016-07-21 15:00:00'),
('16477', 169121', '2016-07-23 07:00:00', '169146', '2016-07-23 09:00:00'),
('16477', 169148', '2016-07-23 13:00:00', '169340', '2016-07-23 17:00:00'),
('16477', 169650', '2016-07-24 05:42:00', '175112', '2016-07-24 16:45:00');
You are trying to use SQL to write a report, with a variable number of columns. It does not work that way; we pass a result set to a presentation layer in this language and let it handle the formatting (like putting the old a.m. and p.m. markers on times, converting the dates into some local dialect, etc.)
I see somehow, we got a "preferred_name" which can be "finance" in your expected output. That is a very weird nickname 🙂
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
October 12, 2016 at 3:44 pm
sscrazy,
thanks its worked.
but some time we do have more than 2 clock in and clock out and yours cover only 2 clock in and clock out. Could you assist to display more then 2 clock in and clock out.
also if any time cross 00:01 am than sql query shows -value example , and this this should be positive number instead of negative,
ex: clock in :11:43 am and clock out 00:13 am shows total hours = -11 should be 11
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply