Please check if my code is Ok - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Please check if my code is Ok

Author

Topic

kiddoOnSQL
Starting Member

16 Posts

Posted - 2013-06-05 : 22:05:02

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

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-05 : 22:53:19

The following code should do what your current code is doing:
[CODE]
----------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;

[/CODE]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-05 : 23:00:25

[code]
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
[/code]

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

KH
[spoiler]Time is always against us[/spoiler]

kiddoOnSQL
Starting Member

16 Posts

Posted - 2013-06-06 : 02:09:54

Thanks for the replies.

I went with 'khtan' answer as it was much more compact

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources