Formula Require

Transact-SQL
1

Hi All,

Good days.

I have this: -
SELECT [START]='13:00:00',[END]='18:00:00',[StartBreakHr1st]=NULL,[EndBreakHr1st]=NULL,[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='1:15:00',LateOTHr=NULL union all
SELECT [START]='9:00:00',[END]='19:30:00',[StartBreakHr1st]='12:00:00',[EndBreakHr1st]='13:00:00',[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='2:00:00',LateOTHr=NULL union all
SELECT [START]='12:30:00',[END]='17:15:00',[StartBreakHr1st]=NULL,[EndBreakHr1st]=NULL,[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='1:00:00',LateOTHr=NULL union all
SELECT [START]='9:00:00',[END]='20:00:00',[StartBreakHr1st]='12:00:00',[EndBreakHr1st]='13:00:00',[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='2:30:00',LateOTHr=NULL union all
SELECT [START]='13:45:00',[END]='17:30:00',[StartBreakHr1st]=NULL,[EndBreakHr1st]=NULL,[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr=NULL,LateOTHr=NULL union all
SELECT [START]='13:30:00',[END]='20:30:00',[StartBreakHr1st]='18:20:00',[EndBreakHr1st]='19:00:00',[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='2:35:00',LateOTHr=NULL union all
SELECT [START]='13:45:00',[END]='23:30:00',[StartBreakHr1st]='19:00:00',[EndBreakHr1st]='20:00:00',[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr=NULL,LateOTHr='1:30:00' union all
SELECT [START]='14:00:00',[END]='17:50:00',[StartBreakHr1st]=NULL,[EndBreakHr1st]=NULL,[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='00:05:00',LateOTHr=NULL

Rules:
Start and End fulfilled under 13:45 to 17:30 >> Consider Working Hour 07:30:00
If outside of this range 13:45 to 17:30 >> Consider Over Time (OT)
If Start, End, 1st Break, 2nd Break fallen under 22:00 to 05:00 >> Consider Late Over Time

Above query last 3 column showing the expected result for Working Hour, OT & Late OT. If 1st Break ([StartBreakHr1st] & [EndBreakHr1st]) or/and 2nd Break ([StartBreakHr] & [EndBreakHr]) IS NULL mean no break taken

How to write the formula to cater these needs?

Please advise.

Regards,
Micheale

2

hi

here is the create script

create data script

drop table if exists #Data

create table #Data ( st time , ed time ,[StartBreakHr1st] time , [EndBreakHr1st] time , [StartBreakHr] time, [EndBreakHr] time , WorkingHr time , OTHr time , LateOTHr time )

insert into #Data SELECT st='13:00:00',ed ='18:00:00',[StartBreakHr1st]=NULL,[EndBreakHr1st]=NULL,[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='1:15:00',LateOTHr=NULL
insert into #Data SELECT st='9:00:00',ed ='19:30:00',[StartBreakHr1st]='12:00:00',[EndBreakHr1st]='13:00:00',[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='2:00:00',LateOTHr=NULL
insert into #Data SELECT st='12:30:00',ed ='17:15:00',[StartBreakHr1st]=NULL,[EndBreakHr1st]=NULL,[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='1:00:00',LateOTHr=NULL
insert into #Data SELECT st='9:00:00',ed ='20:00:00',[StartBreakHr1st]='12:00:00',[EndBreakHr1st]='13:00:00',[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='2:30:00',LateOTHr=NULL
insert into #Data SELECT st='13:45:00',ed ='17:30:00',[StartBreakHr1st]=NULL,[EndBreakHr1st]=NULL,[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr=NULL,LateOTHr=NULL
insert into #Data SELECT st='13:30:00',ed ='20:30:00',[StartBreakHr1st]='18:20:00',[EndBreakHr1st]='19:00:00',[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='2:35:00',LateOTHr=NULL
insert into #Data SELECT st='13:45:00',ed ='23:30:00',[StartBreakHr1st]='19:00:00',[EndBreakHr1st]='20:00:00',[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr=NULL,LateOTHr='1:30:00'
insert into #Data SELECT st='14:00:00',ed ='17:50:00',[StartBreakHr1st]=NULL,[EndBreakHr1st]=NULL,[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='00:05:00',LateOTHr=NULL

select * from #Data

3

Hi,

I don't see any formula you define in query? You only populated my snapshot into a temp table.
I need the formula to calculate the Working Hour, OT Hour and Late OT Hour.
There are a set of rules mentioned at the original post.

Thanks

Regards,
Micheale

5

Hi,

Sorry. I didn't mean to be rude.
Just curious why no formula but populate the data into temp table. I re-explain what I would like to achieve.

drop table if exists #TempData

create table #TempData ( [Start] time(0) , [End] time(0) ,[StartBreakHr1st] time(0) , [EndBreakHr1st] time(0) , [StartBreakHr] time(0), [EndBreakHr] time(0) , WorkingHr time(0) , OTHr time(0) , LateOTHr time(0) )

insert into #TempData
SELECT [START]='13:00:00',[END]='18:00:00',[StartBreakHr1st]=NULL,[EndBreakHr1st]=NULL,[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='1:15:00',LateOTHr=NULL union all
SELECT [START]='9:00:00',[END]='19:30:00',[StartBreakHr1st]='12:00:00',[EndBreakHr1st]='13:00:00',[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='2:00:00',LateOTHr=NULL union all
SELECT [START]='12:30:00',[END]='17:15:00',[StartBreakHr1st]=NULL,[EndBreakHr1st]=NULL,[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='1:00:00',LateOTHr=NULL union all
SELECT [START]='9:00:00',[END]='20:00:00',[StartBreakHr1st]='12:00:00',[EndBreakHr1st]='13:00:00',[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='2:30:00',LateOTHr=NULL union all
SELECT [START]='13:45:00',[END]='17:30:00',[StartBreakHr1st]=NULL,[EndBreakHr1st]=NULL,[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr=NULL,LateOTHr=NULL union all
SELECT [START]='13:30:00',[END]='20:30:00',[StartBreakHr1st]='18:20:00',[EndBreakHr1st]='19:00:00',[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='2:35:00',LateOTHr=NULL union all
SELECT [START]='13:45:00',[END]='23:30:00',[StartBreakHr1st]='19:00:00',[EndBreakHr1st]='20:00:00',[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr=NULL,LateOTHr='1:30:00' union all
SELECT [START]='14:00:00',[END]='17:50:00',[StartBreakHr1st]=NULL,[EndBreakHr1st]=NULL,[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='00:05:00',LateOTHr=NULL

select * from #TempData

Thanks for your kind help.

Regards,
Micheale

6

hi

this is a starting point

please check ..let me know

SELECT
     *
	 , case 
	     when St >=  '13:45' and ed <= '17:30' then 'Working Hour 07:30:00'   
		 when St >=  '22:00' and ed <= '05:00' then 'Over Time (OT)'        
		 else 'Late Over Time (OT)' end 
     , case 
        when  ( [StartBreakHr1st] IS NULL AND [EndBreakHr1st] IS NULL AND [StartBreakHr] IS NULL AND [EndBreakHr] is NULL ) then 'no break taken' end 
FROM 
   #Data

image
image1340×411 27.8 KB

7

Hi,

I tried to populate the scenario like what you do. I think I still cannot cover the full rules of possibilities especially late overt time if user key in break time for Late Over Time. I am stuck on getting the possibilities rules. Your help highly appreciated.

Here you go my draft modification, I think still lacking of some rules not cover:

drop table if exists #Data

create table #Data ( st time(0) , ed time(0) ,[StartBreakHr1st] time(0) , [EndBreakHr1st] time(0) , [StartBreakHr] time(0), [EndBreakHr] time(0) , WorkingHr time(0) , OTHr time(0) , LateOTHr time(0) )

insert into #Data
SELECT [START]='13:00:00',[END]='18:00:00',[StartBreakHr1st]=NULL,[EndBreakHr1st]=NULL,[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='1:15:00',LateOTHr=NULL union all
SELECT [START]='9:00:00',[END]='19:30:00',[StartBreakHr1st]='12:00:00',[EndBreakHr1st]='13:00:00',[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='2:00:00',LateOTHr=NULL union all
SELECT [START]='12:30:00',[END]='17:15:00',[StartBreakHr1st]=NULL,[EndBreakHr1st]=NULL,[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='1:00:00',LateOTHr=NULL union all
SELECT [START]='9:00:00',[END]='20:00:00',[StartBreakHr1st]='12:00:00',[EndBreakHr1st]='13:00:00',[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='2:30:00',LateOTHr=NULL union all
SELECT [START]='13:45:00',[END]='17:30:00',[StartBreakHr1st]=NULL,[EndBreakHr1st]=NULL,[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr=NULL,LateOTHr=NULL union all
SELECT [START]='13:30:00',[END]='20:30:00',[StartBreakHr1st]='18:20:00',[EndBreakHr1st]='19:00:00',[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='2:35:00',LateOTHr=NULL union all
SELECT [START]='13:45:00',[END]='23:30:00',[StartBreakHr1st]='19:00:00',[EndBreakHr1st]='20:00:00',[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr=NULL,LateOTHr='1:30:00' union all
SELECT [START]='14:00:00',[END]='17:50:00',[StartBreakHr1st]=NULL,[EndBreakHr1st]=NULL,[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='00:05:00',LateOTHr=NULL union all
SELECT [START]='21:00:00',[END]='02:30:00',[StartBreakHr1st]=NULL,[EndBreakHr1st]=NULL,[StartBreakHr]=NULL,[EndBreakHr]=NULL,WorkingHr='7:30:00',OTHr='00:45:00',LateOTHr='04:30:00' union all
SELECT [START]='10:40:00',[END]='05:50:00',[StartBreakHr1st]='12:00:00',[EndBreakHr1st]='13:00:00',[StartBreakHr]='19:00:00',[EndBreakHr]='22:10:00',WorkingHr='7:30:00',OTHr=NULL,LateOTHr=NULL

SELECT
*
, Rule1=case
when St >= '13:45' and ed <= '17:30' then 'Working Hour 07:30:00'
else 'Working Hour not in range 13:45 till 17:30' end
, Rule2=case when DateDiff(minute,st,ed)>DateDiff(minute,'13:45:00','17:30:00') then 'Over Time'
else 'No Over Time (OT)' end
,Rule3 = case when (St between '22:00:00' and '23:59:59') then 'Late Over Time (OT)'
when (Ed between '22:00:00' and '23:59:59') then 'Late Over Time (OT)'
when (Ed between '00:00:00' and '05:00:00') then 'Late Over Time (OT)'
else 'No Late Over Time (OT)' end
, Rule4=case
when ( [StartBreakHr1st] IS NULL AND [EndBreakHr1st] IS NULL AND [StartBreakHr] IS NULL AND [EndBreakHr] is NULL ) then 'no break taken' end
FROM
#Data

If this scenario (Possibilities Scenario)
SELECT [START]='10:40:00',[END]='05:50:00',[StartBreakHr1st]='12:00:00',[EndBreakHr1st]='13:00:00',[StartBreakHr]='22:00:00',[EndBreakHr]='05:00:00',WorkingHr='7:30:00',OTHr='???',LateOTHr=NULL
Mean have OT but no Over Time

Thanks.

Regards,
Micheale

8

Hi

maybe

taking a notepad and doing some thinking (checklist) will help

Try to think of and write down all the scenarios

After that
we can think of converting to SQL

9

SQL formula to calculate the Working Hour, OT, and Late OT based on the given rules:

SQLWITH shifts AS ( SELECT *, DATEDIFF(MINUTE, [START], [END]) AS [TotalDuration], CASE WHEN [START] BETWEEN '13:45:00' AND '17:30:00' THEN 'Working Hour' WHEN [START] < '13:45:00' OR [END] > '17:30:00' THEN 'OTHr' ELSE NULL END AS [HourType], CASE WHEN [START] BETWEEN '22:00:00' AND '05:00:00' OR [StartBreakHr1st] BETWEEN '22:00:00' AND '05:00:00' OR [EndBreakHr1st] BETWEEN '22:00:00' AND '05:00:00' OR [StartBreakHr] BETWEEN '22:00:00' AND '05:00:00' OR [EndBreakHr] BETWEEN '22:00:00' AND '05:00:00' THEN 'LateOTHr' ELSE NULL END AS [LateHourType] FROM your_table ) SELECT [START], [END], [StartBreakHr1st], [EndBreakHr1st], [StartBreakHr], [EndBreakHr], CASE WHEN [HourType] = 'Working Hour' THEN '07:30:00' WHEN [HourType] = 'OTHr' THEN [TotalDuration] ELSE NULL END AS [WorkingHr], CASE WHEN [HourType] = 'OTHr' THEN [TotalDuration] - '07:30:00' WHEN [HourType] = 'LateOTHr' THEN [TotalDuration] - '07:30:00' ELSE NULL END AS [OTHr], CASE WHEN [LateHourType] = 'LateOTHr' THEN [TotalDuration] - '07:30:00' ELSE NULL END AS [LateOTHr] FROM shifts;