SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Please check if my code is Ok
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kiddoOnSQL
Starting Member

Australia
16 Posts

Posted - 06/05/2013 :  22:05:02  Show Profile  Reply with Quote
I have a requirement where we have many start / finish times being sent to our systems (in different formats) but where a break time is not explicitly sent. We have to then calculate the break time based on start\finish time and hours worked.

We have situations where someone works "normal" day time hours, but sometimes people work night shifts where they clock out after midnight.
I have sql code that I have written to handle both situations. Can I please ask SQL experts to cast their expert eye on this and let me know if I have left something out or if I need to be mindful of something??
Thanks in advance

----------code snippet----

DECLARE @Stdate datetime
DECLARE @Findate datetime
Declare @nvUnits nvarchar(10) --hours worked sent as decimal representation by client

Declare @nvRes decimal(5,0)

--EXAMPLE DATA WHERE FINISH TIME IS AFTER MIDNIGHT
--SELECT @Stdate = '20:00:00.583'
--SELECT @Findate = '02:00:00.583'
--SELECT @nvUnits = '4.50'

--EXAMPLE DATA WHERE FINISH TIME IS BEFORE MIDNIGHT
SELECT @Stdate = '12:30'
SELECT @Findate = '16:30'
SELECT @nvUnits = '03.50'

If (@Findate < @Stdate)
BEGIN
--returns minutes to midnight for @StDate, returns minutes past midnight for @findate, adds the 2 results, then subtracts @nvunits (time worked) to get break (minutes)

Select @nvRes =(DATEDIFF(mi,@Stdate,'23:59') +1 + DATEDIFF(mi,CONVERT(varchar(10),@Findate,112),@Findate)) - (convert(decimal(5,2),@nvUnits))* 60

--should return 90 minutes break
Select @nvRes
END

ELSE
BEGIN
--gets minutes past midnight representation for @StDate, gets minutes past midnight representation for @findate, deducts one from the other, then subtracts @nvunits (time worked) to get break (minutes)

Select @nvRes = ((Convert(float,(DATEDIFF(mi, MIN(convert(datetime,@Stdate, 103)) ,
MAX(convert(datetime, @Findate, 103)))))/60) - Sum(convert(decimal(5,2),@nvUnits))) * 60

--should return 30 minutes break
Select @nvRes
END



Edited by - kiddoOnSQL on 06/05/2013 22:06:45

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/05/2013 :  22:53:19  Show Profile  Reply with Quote
The following code should do what your current code is doing:

----------code snippet----

DECLARE @Stdate datetime
DECLARE @Findate datetime
Declare @nvUnits nvarchar(10) --hours worked sent as decimal representation by client
DECLARE @MidNight datetime

Declare @nvRes decimal(5,0)

--EXAMPLE DATA WHERE FINISH TIME IS BEFORE MIDNIGHT
--SELECT @Stdate = '12:30'
--SELECT @Findate = '16:30'
--SELECT @nvUnits = '03.50'
--SELECT @MidNight = '00:00'

--EXAMPLE DATA WHERE FINISH TIME IS AFTER MIDNIGHT
SELECT @Stdate = '20:00:00.583'
SELECT @Findate = '02:00:00.583'
SELECT @nvUnits = '4.50'
SELECT @MidNight = '00:00'


SELECT (CASE WHEN @Stdate < @FinDate THEN DATEDIFF(mi, @Stdate, @Findate) 
			ELSE (1440 - DATEDIFF(mi, @MidNight, @Stdate) + DATEDIFF(mi, @MidNight, @Findate)) END) 
			- (convert(decimal(5,2),@nvUnits))* 60  AS TimeDiffInMin;

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17682 Posts

Posted - 06/05/2013 :  23:00:25  Show Profile  Reply with Quote

select	case	when	@Stdate	<= @Findate
		then	datediff(minute, @Stdate, @Findate) - convert(decimal(10,2), @nvUnits) * 60
		else	datediff(minute, @Stdate, dateadd(day, 1, @Findate)) - convert(decimal(10,2), @nvUnits) * 60
		end


Haven't been posting for quite a while, forgotten about F5



KH
Time is always against us


Edited by - khtan on 06/05/2013 23:01:56
Go to Top of Page

kiddoOnSQL
Starting Member

Australia
16 Posts

Posted - 06/06/2013 :  02:09:54  Show Profile  Reply with Quote
Thanks for the replies.

I went with 'khtan' answer as it was much more compact
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000