May 22, 2017 at 6:04 am
I am looking to calculate the Night_Start_Time,Night_Shift_End,Night_Shift_Duration
We have defined the Night_Shift_Start_Time = 22:00 hrs and Nght_Shift_End_Time = 06:00 hrs.
To qualify for a night shift the employee should have worked minimum 30 minutes or else it will not be consider night shift
for eg; 03:00 pm - 22:29 pm in this case the employee work only 29 mins which is less than 30 mins it doesn't come under night shifts
03:00 pm -- 22:30 pm it is night shift as the employe worked for 30 mins.
05:29 am -- 2:00 pm as the employee worked for 31 mins(06:00 - 05:29) it comes under night shift
05:31 am -- 2:00 pm as the employee worked for 29 mins night shift duration it can not consider as night shift.
We have Shift_Details table for all employees where all the shifts inserted.
Eid Shift_Start Shift_End
1 22-05-2017 20:00 23-05-2017 06:00
2 22-05-2017 02:00 22-05-2017 12:00
3 22-05-2017 23:00 23-05-2017 08:00
4 22-05-2017 23:00 23-05-2017 00:00
5 22-05-2017 00:00 22-05-2017 05:00
6 22-05-2017 15:00 22-05-2017 21:00
This is the output i want
Eid Shift_Start Shift_End Night_Start_Time Night_Shift_End Night_Shift_Duration(hrs)
1 22-05-2017 20:00 23-05-2017 06:00 22-05-2017 22:00 23-05-2017 06:00 8
2 22-05-2017 02:00 22-05-2017 12:00 22-05-2017 02:00 22-05-2017 06:00 4
3 22-05-2017 23:00 23-05-2017 08:00 22-05-2017 23:00 23-05-2017 06:00 7
4 22-05-2017 23:00 23-05-2017 00:00 22-05-2017 23:00 23-05-2017 00:00 1
5 22-05-2017 00:00 22-05-2017 05:00 22-05-2017 00:00 22-05-2017 05:00 5
6 22-05-2017 15:00 22-05-2017 21:00 - - 0
Hope all the scenarios covered
May 22, 2017 at 6:25 am
So where have you started? What do you have so far? What about some table DDL -- that will make it easier for people to help you. I'm assuming your fields have a data type of datetime. Have you taken a look at the DATEDIFF() function ( https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql ) -- a good starting point.
Good luck,
Rob
May 22, 2017 at 6:28 am
Usable DDL & DLM:CREATE TABLE #Shift
(Eid int,
Shift_Start datetime,
Shift_End datetime);
GO
INSERT INTO #Shift
VALUES
(1,'20170522 20:00:00.000','20170523 06:00:00.000'),
(2,'20170522 02:00:00.000','20170522 12:00:00.000'),
(3,'20170522 23:00:00.000','20170523 08:00:00.000'),
(4,'20170522 23:00:00.000','20170523 00:00:00.000'),
(5,'20170522 00:00:00.000','20170522 05:00:00.000'),
(6,'20170522 15:00:00.000','20170522 21:00:00.000');
GO
SELECT *
FROM #Shift;
GO
--Clean up
DROP TABLE #Shift;
GO
Readable Expected output:Eid Shift_Start Shift_End Night_Start_Time Night_Shift_End Night_Shift_Duration
1 2017-05-22 20:00:00.000 2017-05-23 06:00:00.000 2017-05-22 22:00:00.000 2017-05-23 06:00:00.000 8
2 2017-05-22 02:00:00.000 2017-05-22 12:00:00.000 2017-05-22 02:00:00.000 2017-05-22 06:00:00.000 4
3 2017-05-22 23:00:00.000 2017-05-23 08:00:00.000 2017-05-22 23:00:00.000 2017-05-23 06:00:00.000 7
4 2017-05-22 23:00:00.000 2017-05-23 00:00:00.000 2017-05-22 23:00:00.000 2017-05-23 00:00:00.000 1
5 2017-05-22 00:00:00.000 2017-05-22 05:00:00.000 2017-05-22 00:00:00.000 2017-05-22 05:00:00.000 5
6 2017-05-22 15:00:00.000 2017-05-22 21:00:00.000 NULL NULL 0
Try to remember to provide your data is a meaningful and usable format. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 22, 2017 at 2:54 pm
SQL006 - Monday, May 22, 2017 6:04 AMI am looking to calculate the Night_Start_Time,Night_Shift_End,Night_Shift_Duration
We have defined the Night_Shift_Start_Time = 22:00 hrs and Nght_Shift_End_Time = 06:00 hrs.
To qualify for a night shift the employee should have worked minimum 30 minutes or else it will not be consider night shift
for eg; 03:00 pm - 22:29 pm in this case the employee work only 29 mins which is less than 30 mins it doesn't come under night shifts
03:00 pm -- 22:30 pm it is night shift as the employe worked for 30 mins.
05:29 am -- 2:00 pm as the employee worked for 31 mins(06:00 - 05:29) it comes under night shift
05:31 am -- 2:00 pm as the employee worked for 29 mins night shift duration it can not consider as night shift.We have Shift_Details table for all employees where all the shifts inserted.
Eid Shift_Start Shift_End
1 22-05-2017 20:00 23-05-2017 06:00
2 22-05-2017 02:00 22-05-2017 12:00
3 22-05-2017 23:00 23-05-2017 08:00
4 22-05-2017 23:00 23-05-2017 00:00
5 22-05-2017 00:00 22-05-2017 05:00
6 22-05-2017 15:00 22-05-2017 21:00This is the output i want
Eid Shift_Start Shift_End Night_Start_Time Night_Shift_End Night_Shift_Duration(hrs)
1 22-05-2017 20:00 23-05-2017 06:00 22-05-2017 22:00 23-05-2017 06:00 8
2 22-05-2017 02:00 22-05-2017 12:00 22-05-2017 02:00 22-05-2017 06:00 4
3 22-05-2017 23:00 23-05-2017 08:00 22-05-2017 23:00 23-05-2017 06:00 7
4 22-05-2017 23:00 23-05-2017 00:00 22-05-2017 23:00 23-05-2017 00:00 1
5 22-05-2017 00:00 22-05-2017 05:00 22-05-2017 00:00 22-05-2017 05:00 5
6 22-05-2017 15:00 22-05-2017 21:00 - - 0
Hope all the scenarios covered
can you please provide some example data and expected results for these scenarios....you havent included them in your sample data set.
03:00 pm - 22:29 pm in this case the employee work only 29 mins which is less than 30 mins it doesn't come under night shifts
03:00 pm -- 22:30 pm it is night shift as the employe worked for 30 mins.
05:29 am -- 2:00 pm as the employee worked for 31 mins(06:00 - 05:29) it comes under night shift
05:31 am -- 2:00 pm as the employee worked for 29 mins night shift duration it can not consider as night shift.
I am not sure whether you are expecting these durations to be in/excluded from your results
also you are asking for hours only...is this correct or do you require hh:mm results?
please refer to the example above by Thom on how to post sample data.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 22, 2017 at 3:07 pm
You will drive yourself insane processing this in days and hours. By viewing shift times as offsets in minutes from Sunday 12 am everything will become super easy.
May 22, 2017 at 10:47 pm
Thank u guys for the reply.
Thanks Thom for providing the DDL's
Sorry for not providing any DDL's, As i am working in client VM and there is no internet in that VM, so i have to come outside that and post this query where sql server is not installed(it jst a desktop to connect the client VM).
I am trying to use multi case statement to get that, in case i get any better idea here i will try that out.
Night Duration is in H.M like 0.5,1.5,1.0
This is what i had done...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
WHEN Time > = 30mins
THEN
CASE when shift_Start>= Night_Shift_Start_Time then shift_Start
WHEN shift_Start < Night_Shift_Start_Time THEN Night_Shift_Start
END
ELSE Night_Shift_Start_Time END as 'SHift_Start'