September 18, 2016 at 2:20 am
Dear All i need sql query to know the total hours spend,
My table looks like this .
action id ,creation_date,event_name,emp_number,
1,7/21/2016 10:37:21 AM,IN,9234
2,7/21/2016 11:37:21 AM,OUT,9234
output ,
em_number,creation_Date,total hours spend
9234 , 21/07/2016,1:00:21
could you advise to achieve the same
September 18, 2016 at 4:27 am
i asked a similar question way back.
here is my post and how it got solved
i hope it helps.
http://www.sqlservercentral.com/Forums/Topic1435292-391-1.aspx
September 18, 2016 at 4:36 am
hi thanks. but i am expecting something different...
September 18, 2016 at 4:46 am
philand3 (9/18/2016)
Dear All i need sql query to know the total hours spend,My table looks like this .
action id ,creation_date,event_name,emp_number,
1,7/21/2016 10:37:21 AM,IN,9234
2,7/21/2016 11:37:21 AM,OUT,9234
output ,
em_number,creation_Date,total hours spend
9234 , 21/07/2016,1:00:21
could you advise to achieve the same
this is a very small set of example data.......so I have a few q's.....
assuming you have more than one emp_number to calculate for....
will there always be only two rows per emp/date?
will there ever be an IN row without a corresponding OUT row or viceversa?
will IN and OUT rows span across midnight?
will IN and OUT span multiple days?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 18, 2016 at 6:12 am
Hi Livingston,
Find below my reply.
will there always be only two rows per emp/date? apart from this action_id is very important.(and Many rows which includes name,email id ,timezone,etc)
will there ever be an IN row without a corresponding OUT row or viceversa? (system will be reject this duplicate entry from device itself therefore it ill be IN row with corresponding OUT vice versa with help of (Action_id)
will IN and OUT rows span across midnight? Yes
will IN and OUT span multiple days? Yes
September 18, 2016 at 6:18 am
philand3 (9/18/2016)
Hi Livingston,Find below my reply.
will there always be only two rows per emp/date? apart from this action_id is very important.(and Many rows which includes name,email id ,timezone,etc)
will there ever be an IN row without a corresponding OUT row or viceversa? (system will be reject this duplicate entry from device itself therefore it ill be IN row with corresponding OUT vice versa with help of (Action_id)
will IN and OUT rows span across midnight? Yes
will IN and OUT span multiple days? Yes
sorry but "will there always be only two rows per emp/date? apart from this action_id is very important.(and Many rows which includes name,email id ,timezone,etc)" is not at all clear to me....maybe if you provide a larger set of sample data and the expected results of the sample data it will become clearer.
here is a link to how to properly provide sample data
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 18, 2016 at 6:36 am
action_idcreation_dateevent_nameEmp_Num
1676087/21/2016 10:37:21IN9500
1676097/21/2016 10:37:21IN9372
1676107/21/2016 10:37:22IN9517
1676117/21/2016 10:37:22IN9339
1676127/21/2016 10:37:22OUT9234
1676137/21/2016 10:37:23OUT9311
1676147/21/2016 10:37:24OUT9311
1676157/21/2016 10:37:24OUT9525
1676177/21/2016 10:37:25OUT9270
1676187/21/2016 10:37:25OUT9202
1676197/21/2016 10:37:25IN9179
1676207/21/2016 10:37:26IN9159
1676217/21/2016 10:37:26IN9275
1676227/21/2016 10:37:27IN9400
1676237/21/2016 10:37:27IN9299
1676247/21/2016 10:37:27OUT9247
1676257/21/2016 10:37:27IN9233
1676267/21/2016 10:37:28IN9165
1676277/21/2016 10:37:28OUT9279
1676287/21/2016 10:37:28IN9211
1676297/21/2016 10:37:30OUT9310
1676317/21/2016 10:37:30IN9310
1676327/21/2016 10:37:30OUT9310
1676337/21/2016 10:37:31OUT9265
1676347/21/2016 10:37:31OUT9265
1676357/21/2016 10:37:31OUT9235
1676367/21/2016 10:37:32OUT9235
1676377/21/2016 10:37:32OUT9235
1676387/21/2016 10:37:32OUT9217
1676397/21/2016 10:37:32OUT9217
1676407/21/2016 10:37:33OUT9357
1676417/21/2016 10:37:34IN9185
1676427/21/2016 10:37:34IN9197
1676447/21/2016 10:37:35OUT9209
1676457/21/2016 10:37:35OUT9209
1676467/21/2016 10:37:35OUT9209
1676477/21/2016 10:37:35OUT9198
1676487/21/2016 10:37:36OUT9198
1676497/21/2016 10:37:36IN9198
1676507/21/2016 10:37:36OUT9198
1676517/21/2016 10:37:37OUT9503
1676527/21/2016 10:37:37IN9326
1676537/21/2016 10:37:37IN9389
1676547/21/2016 10:37:37IN9266
1676557/21/2016 10:37:38IN9354
1676567/21/2016 10:37:38OUT9313
1676577/21/2016 10:37:38OUT9112
1676587/21/2016 10:37:39IN9354
1676607/21/2016 10:37:39OUT9312
1676617/21/2016 10:37:40OUT9165
1676627/21/2016 10:37:40OUT9130
1676637/21/2016 10:37:40OUT9130
1676647/21/2016 10:37:40IN9130
1676657/21/2016 10:37:41OUT9130
1676667/21/2016 10:37:41OUT9402
1676677/21/2016 10:37:41IN9350
1676687/21/2016 10:37:42OUT9274
1676697/21/2016 10:37:42OUT9329
1676707/21/2016 10:37:42OUT9124
1676717/21/2016 10:37:43OUT9124
1676727/21/2016 10:37:43OUT9126
1676737/21/2016 10:37:43OUT9347
1676757/21/2016 10:37:44OUT9349
1676767/21/2016 10:37:44OUT9304
1676777/21/2016 10:37:45OUT9119
1676787/21/2016 10:37:45OUT9386
1676797/21/2016 10:37:45OUT9145
1676807/21/2016 10:37:45OUT9145
1676817/21/2016 10:37:46IN9145
1676827/21/2016 10:37:46OUT9145
1676837/21/2016 10:37:46OUT9197
1676847/21/2016 10:37:47OUT9401
1676857/21/2016 10:37:47OUT9114
1676867/21/2016 10:37:48OUT9114
1676877/21/2016 10:37:48IN9114
1676897/21/2016 10:37:49OUT9114
1676907/21/2016 10:37:49OUT9114
1676917/21/2016 10:37:49OUT9114
1676927/21/2016 10:37:50OUT9289
1676937/21/2016 10:37:50OUT9517
1676947/21/2016 10:37:50OUT9179
1676957/21/2016 10:37:51IN9179
1676967/21/2016 10:37:51OUT9179
1676977/21/2016 10:37:51OUT9266
1676987/21/2016 10:37:52OUT9389
1676997/21/2016 10:37:52OUT9326
1677007/21/2016 10:37:52OUT9374
1677017/21/2016 10:37:52OUT9350
1677027/21/2016 10:37:53OUT9331
1677037/21/2016 10:37:54OUT9348
1677057/21/2016 10:37:54OUT9339
1677067/21/2016 10:37:54OUT9372
1677077/21/2016 10:37:55IN9337
1677087/21/2016 10:37:55IN9337
1677097/21/2016 10:37:55IN9394
1677107/21/2016 10:37:56IN9248
1677117/21/2016 10:37:56IN9248
1677127/21/2016 10:37:56OUT9248
1677137/21/2016 10:37:56IN9248
1677147/21/2016 10:37:57IN9408
1677157/21/2016 10:37:57IN9408
1677167/21/2016 10:37:57IN9298
1677177/21/2016 10:37:58IN9230
1677187/21/2016 10:37:58OUT9511
1677197/21/2016 10:37:58OUT9511
1677207/21/2016 10:37:59IN9188
1677227/21/2016 10:37:59IN9217
1677237/21/2016 10:37:59OUT9219
1677247/21/2016 10:38:00IN9219
1677257/21/2016 10:38:00IN9140
September 18, 2016 at 7:05 am
as per my previous post and the associated link...please see below the best way to provide sample data.
so...for you and others who may wish to try and help you
USE tempdb
GO
CREATE TABLE yourtable(
action_id INTEGER NOT NULL
,creation_date DATETIME NOT NULL
,event_name VARCHAR(3) NOT NULL
,Emp_Num INTEGER NOT NULL
);
INSERT INTO yourtable(action_id,creation_date,event_name,Emp_Num) VALUES
(167608,'7/21/2016 10:37:21','IN',9500),(167609,'7/21/2016 10:37:21','IN',9372),(167610,'7/21/2016 10:37:22','IN',9517)
,(167611,'7/21/2016 10:37:22','IN',9339),(167612,'7/21/2016 10:37:22','OUT',9234),(167613,'7/21/2016 10:37:23','OUT',9311)
,(167614,'7/21/2016 10:37:24','OUT',9311),(167615,'7/21/2016 10:37:24','OUT',9525),(167617,'7/21/2016 10:37:25','OUT',9270)
,(167618,'7/21/2016 10:37:25','OUT',9202),(167619,'7/21/2016 10:37:25','IN',9179),(167620,'7/21/2016 10:37:26','IN',9159)
,(167621,'7/21/2016 10:37:26','IN',9275),(167622,'7/21/2016 10:37:27','IN',9400),(167623,'7/21/2016 10:37:27','IN',9299)
,(167624,'7/21/2016 10:37:27','OUT',9247),(167625,'7/21/2016 10:37:27','IN',9233),(167626,'7/21/2016 10:37:28','IN',9165)
,(167627,'7/21/2016 10:37:28','OUT',9279),(167628,'7/21/2016 10:37:28','IN',9211),(167629,'7/21/2016 10:37:30','OUT',9310)
,(167631,'7/21/2016 10:37:30','IN',9310),(167632,'7/21/2016 10:37:30','OUT',9310),(167633,'7/21/2016 10:37:31','OUT',9265)
,(167634,'7/21/2016 10:37:31','OUT',9265),(167635,'7/21/2016 10:37:31','OUT',9235),(167636,'7/21/2016 10:37:32','OUT',9235)
,(167637,'7/21/2016 10:37:32','OUT',9235),(167638,'7/21/2016 10:37:32','OUT',9217),(167639,'7/21/2016 10:37:32','OUT',9217)
,(167640,'7/21/2016 10:37:33','OUT',9357),(167641,'7/21/2016 10:37:34','IN',9185),(167642,'7/21/2016 10:37:34','IN',9197)
,(167644,'7/21/2016 10:37:35','OUT',9209),(167645,'7/21/2016 10:37:35','OUT',9209),(167646,'7/21/2016 10:37:35','OUT',9209)
,(167647,'7/21/2016 10:37:35','OUT',9198),(167648,'7/21/2016 10:37:36','OUT',9198),(167649,'7/21/2016 10:37:36','IN',9198)
,(167650,'7/21/2016 10:37:36','OUT',9198),(167651,'7/21/2016 10:37:37','OUT',9503),(167652,'7/21/2016 10:37:37','IN',9326)
,(167653,'7/21/2016 10:37:37','IN',9389),(167654,'7/21/2016 10:37:37','IN',9266),(167655,'7/21/2016 10:37:38','IN',9354)
,(167656,'7/21/2016 10:37:38','OUT',9313),(167657,'7/21/2016 10:37:38','OUT',9112),(167658,'7/21/2016 10:37:39','IN',9354)
,(167660,'7/21/2016 10:37:39','OUT',9312),(167661,'7/21/2016 10:37:40','OUT',9165),(167662,'7/21/2016 10:37:40','OUT',9130)
,(167663,'7/21/2016 10:37:40','OUT',9130),(167664,'7/21/2016 10:37:40','IN',9130),(167665,'7/21/2016 10:37:41','OUT',9130)
,(167666,'7/21/2016 10:37:41','OUT',9402),(167667,'7/21/2016 10:37:41','IN',9350),(167668,'7/21/2016 10:37:42','OUT',9274)
,(167669,'7/21/2016 10:37:42','OUT',9329),(167670,'7/21/2016 10:37:42','OUT',9124),(167671,'7/21/2016 10:37:43','OUT',9124)
,(167672,'7/21/2016 10:37:43','OUT',9126),(167673,'7/21/2016 10:37:43','OUT',9347),(167675,'7/21/2016 10:37:44','OUT',9349)
,(167676,'7/21/2016 10:37:44','OUT',9304),(167677,'7/21/2016 10:37:45','OUT',9119),(167678,'7/21/2016 10:37:45','OUT',9386)
,(167679,'7/21/2016 10:37:45','OUT',9145),(167680,'7/21/2016 10:37:45','OUT',9145),(167681,'7/21/2016 10:37:46','IN',9145)
,(167682,'7/21/2016 10:37:46','OUT',9145),(167683,'7/21/2016 10:37:46','OUT',9197),(167684,'7/21/2016 10:37:47','OUT',9401)
,(167685,'7/21/2016 10:37:47','OUT',9114),(167686,'7/21/2016 10:37:48','OUT',9114),(167687,'7/21/2016 10:37:48','IN',9114)
,(167689,'7/21/2016 10:37:49','OUT',9114),(167690,'7/21/2016 10:37:49','OUT',9114),(167691,'7/21/2016 10:37:49','OUT',9114)
,(167692,'7/21/2016 10:37:50','OUT',9289),(167693,'7/21/2016 10:37:50','OUT',9517),(167694,'7/21/2016 10:37:50','OUT',9179)
,(167695,'7/21/2016 10:37:51','IN',9179),(167696,'7/21/2016 10:37:51','OUT',9179),(167697,'7/21/2016 10:37:51','OUT',9266)
,(167698,'7/21/2016 10:37:52','OUT',9389),(167699,'7/21/2016 10:37:52','OUT',9326),(167700,'7/21/2016 10:37:52','OUT',9374)
,(167701,'7/21/2016 10:37:52','OUT',9350),(167702,'7/21/2016 10:37:53','OUT',9331),(167703,'7/21/2016 10:37:54','OUT',9348)
,(167705,'7/21/2016 10:37:54','OUT',9339),(167706,'7/21/2016 10:37:54','OUT',9372),(167707,'7/21/2016 10:37:55','IN',9337)
,(167708,'7/21/2016 10:37:55','IN',9337),(167709,'7/21/2016 10:37:55','IN',9394),(167710,'7/21/2016 10:37:56','IN',9248)
,(167711,'7/21/2016 10:37:56','IN',9248),(167712,'7/21/2016 10:37:56','OUT',9248),(167713,'7/21/2016 10:37:56','IN',9248)
,(167714,'7/21/2016 10:37:57','IN',9408),(167715,'7/21/2016 10:37:57','IN',9408),(167716,'7/21/2016 10:37:57','IN',9298)
,(167717,'7/21/2016 10:37:58','IN',9230),(167718,'7/21/2016 10:37:58','OUT',9511),(167719,'7/21/2016 10:37:58','OUT',9511)
,(167720,'7/21/2016 10:37:59','IN',9188),(167722,'7/21/2016 10:37:59','IN',9217),(167723,'7/21/2016 10:37:59','OUT',9219)
,(167724,'7/21/2016 10:38:00','IN',9219),(167725,'7/21/2016 10:38:00','IN',9140);
using this data for emp_num for 9114 I get these rows....what are your expected results....
+------------------------------------------------------------+
¦ Emp_Num ¦ creation_date ¦ event_name ¦ action_id ¦
¦---------+-------------------------+------------+-----------¦
¦ 9114 ¦ 2016-07-21 10:37:47.000 ¦ OUT ¦ 167685 ¦
¦---------+-------------------------+------------+-----------¦
¦ 9114 ¦ 2016-07-21 10:37:48.000 ¦ OUT ¦ 167686 ¦
¦---------+-------------------------+------------+-----------¦
¦ 9114 ¦ 2016-07-21 10:37:48.000 ¦ IN ¦ 167687 ¦
¦---------+-------------------------+------------+-----------¦
¦ 9114 ¦ 2016-07-21 10:37:49.000 ¦ OUT ¦ 167689 ¦
¦---------+-------------------------+------------+-----------¦
¦ 9114 ¦ 2016-07-21 10:37:49.000 ¦ OUT ¦ 167690 ¦
¦---------+-------------------------+------------+-----------¦
¦ 9114 ¦ 2016-07-21 10:37:49.000 ¦ OUT ¦ 167691 ¦
+------------------------------------------------------------+
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 18, 2016 at 7:17 am
Hello Livi.
Thanks for your efforts.
I would like to know the total hours details like this ( Total hours = first IN - first OUT )
each day i need to see per employee.
September 18, 2016 at 7:26 am
philand3 (9/18/2016)
Hello Livi.Thanks for your efforts.
I would like to know the total hours details like this ( Total hours = first IN - first OUT )
each day i need to see per employee.
in this sample data the first IN is after the first OUT.
we also have duplicate entries (if actionId is ignored), which I thought you said previously couldnt happen in the app
+------------------------------------------------------------+
¦ Emp_Num ¦ creation_date ¦ event_name ¦ action_id ¦
¦---------+-------------------------+------------+-----------¦
¦ 9114 ¦ 2016-07-21 10:37:47.000 ¦ OUT ¦ 167685 ¦
¦---------+-------------------------+------------+-----------¦
¦ 9114 ¦ 2016-07-21 10:37:48.000 ¦ OUT ¦ 167686 ¦
¦---------+-------------------------+------------+-----------¦
¦ 9114 ¦ 2016-07-21 10:37:48.000 ¦ IN ¦ 167687 ¦
¦---------+-------------------------+------------+-----------¦
¦ 9114 ¦ 2016-07-21 10:37:49.000 ¦ OUT ¦ 167689 ¦
¦---------+-------------------------+------------+-----------¦
¦ 9114 ¦ 2016-07-21 10:37:49.000 ¦ OUT ¦ 167690 ¦
¦---------+-------------------------+------------+-----------¦
¦ 9114 ¦ 2016-07-21 10:37:49.000 ¦ OUT ¦ 167691 ¦
+------------------------------------------------------------+
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 18, 2016 at 7:33 am
Hi,
Yes this is sample.
Please ignore duplicate entries.
September 18, 2016 at 9:52 am
philand3 (9/18/2016)
Hi,Yes this is sample.
Please ignore duplicate entries.
ok lets start again......here is some sample data that covers your possible permutation.....based on what I think I undestand so far.
if this is not correct, or there are other possibilites...please amend my code accordingly.
USE tempdb
GO
DROP TABLE IF EXISTS yourtable;
CREATE TABLE yourtable(
action_id INT NOT NULL
,creation_date DATETIME NOT NULL
,event_name VARCHAR(3) NOT NULL
,Emp_Num INT NOT NULL
,comments VARCHAR(25)
);
INSERT INTO yourtable(action_id,creation_date,event_name,Emp_Num,comments) VALUES
(1,'2016/07/21 10:37','IN',1,NULL)
,(2,'2016/07/21 11:37','OUT',1,NULL)
,(3,'2016/07/21 10:37','IN',2,'two entries on single day')
,(4,'2016/07/21 11:37','OUT',2,'two entries on single day')
,(5,'2016/07/21 15:37','IN',2,'two entries on single day')
,(6,'2016/07/21 16:37','OUT',2,'two entries on single day')
,(7,'2016/07/21 20:37','IN',3,'span over midnight')
,(8,'2016/07/22 02:37','OUT',3,'span over midnight')
,(9,'2016/07/21 10:37','IN',4,'span over days')
,(10,'2016/07/23 08:37','OUT',4,'span over days')
,(11,'2016/07/21 10:37','IN',5,'No OUT')
,(12,'2016/07/21 10:37','OUT',6,'No IN')
,(13,'2016/07/21 10:37','IN',7,'less than 1 hour')
,(14,'2016/07/21 10:47','OUT',7,'less than 1 hour');
SELECT * FROM yourtable
can I please ask you to provide a table of the results that you would expect for this sample data.....I mean by this actual figures, not just words explaining.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 28, 2016 at 9:05 am
Hi,
Find below my table ,
action_idperson_numperson_idcreation_datebadge_numevent_nameSHIFTmiddle_namefull_namepreffered_name
16844810148164777/21/16 11:38 AM9372CLOCK_IN1DAYRICHARDFINANCE
16844910148164777/21/16 3:56 PM9372CLOCK_OUT1DAYRICHARDFINANCE
16845010148164777/23/16 7:41 AM9372CLOCK_IN1DAYRICHARDFINANCE
16845110148164777/23/16 5:22 PM9372CLOCK_OUT1DAYRICHARDFINANCE
16845210148164777/24/16 5:42 AM9372CLOCK_IN1DAYRICHARDFINANCE
16845310148164777/24/16 4:45 PM9372CLOCK_OUT1DAYRICHARDFINANCE
16845410148164777/25/16 5:46 AM9372CLOCK_IN1DAYRICHARDFINANCE
16845510148164777/25/16 5:57 PM9372CLOCK_OUT1DAYRICHARDFINANCE
16845610148164777/26/16 5:45 AM9372CLOCK_IN1DAYRICHARDFINANCE
16845710148164777/26/16 3:42 PM9372CLOCK_OUT1DAYRICHARDFINANCE
16845810148164777/27/16 6:33 AM9372CLOCK_IN1DAYRICHARDFINANCE
16845910148164777/27/16 6:48 PM9372CLOCK_OUT1DAYRICHARDFINANCE
16846010148164777/28/16 5:20 AM9372CLOCK_IN1DAYRICHARDFINANCE
16846110148164777/28/16 5:51 PM9372CLOCK_OUT1DAYRICHARDFINANCE
Expected output,
CREATION_DATEPREFERRED_NAMEPERSON_NUMFULL_NAMECLOCK IN CLOCK OUTCLOCK IN CLOCK OUTCLOCK IN CLOCK OUTCLOCK IN CLOCK OUTCLOCK IN CLOCK OUTCLOCK IN CLOCK OUTTOTAL WORKED HOURS
21/07/2016FINANCE10148richard9:00:00 AM10:00:00 AM11:00:00 AM23.45:00 PM13.45
22/07/2016FINANCE10148richard9:00:00 AM10:00:00 AM11:00:00 AM23.45:00 PM13.45
23/07/2016FINANCE10148richard9:00:00 AM10:00:00 AM11:00:00 AM23.45:00 PM13.45
24/07/2016FINANCE10148richard9:00:00 AM10:00:00 AM11:00:00 AM23.45:00 PM13.45
25/07/2016FINANCE10148richard9:00:00 AM10:00:00 AM11:00:00 AM23.45:00 PM13.45
Thanks,
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply