[ad_1]
I have this problem: I need to calculate the time in seconds of a labor time, excepting the time of interval, for different turns.
So, I have this table:
cdLaborTime nmLaborTime beginLaborTime endLaborTime beginInterval endInterval
----------- ----------- ------------- ------------ ------------- --------------
1 1st Turn 07:30 16:45 11:00 12:00
2 2nd Turn 16:45 01:30 20:00 21:00
The main problem: Sometimes we have turns that start in one day and finish in the next day.
I have created this view to help me:
CREATE VIEW v_LaborTime
AS
SELECT CASE
WHEN CAST(beginLaborTime AS TIME) < CAST(endLaborTime AS TIME)
THEN DATEDIFF(SECOND, beginLaborTime, endLaborTime) - DATEDIFF(SECOND, beginInterval, endInterval)
ELSE (DATEDIFF(SECOND, beginLaborTime, '23:59') + DATEDIFF(SECOND, '00:00', endLaborTime)) - DATEDIFF(SECOND, beginInterval, endInterval)
END AS TotalSeconds
, CASE
WHEN CAST(beginLaborTime AS TIME) < CAST(GETDATE() AS TIME) AND CAST(beginInterval AS TIME) < CAST(GETDATE() AS TIME) AND CAST(GETDATE() AS TIME) > endInterval
THEN DATEDIFF(SECOND, beginLaborTime, CAST(GETDATE() AS TIME)) - DATEDIFF(SECOND, beginInterval, endInterval)
WHEN CAST(beginLaborTime AS TIME) < CAST(GETDATE() AS TIME) AND CAST(beginInterval AS TIME) < CAST(GETDATE() AS TIME)
THEN DATEDIFF(SECOND, beginLaborTime, CAST(GETDATE() AS TIME)) - DATEDIFF(SECOND, beginInterval, CAST(GETDATE() AS TIME))
WHEN CAST(beginLaborTime AS TIME) < CAST(GETDATE() AS TIME) AND CAST(beginInterval AS TIME) > CAST(GETDATE() AS TIME)
THEN DATEDIFF(SECOND, beginLaborTime, CAST(GETDATE() AS TIME))
WHEN CAST(beginLaborTime AS TIME) > CAST(GETDATE() AS TIME) AND CAST(beginInterval AS TIME) < CAST(GETDATE() AS TIME) AND CAST(GETDATE() AS TIME) > endInterval
THEN (DATEDIFF(SECOND, beginLaborTime, '23:59') + DATEDIFF(SECOND, '00:00', CAST(GETDATE() AS TIME))) - DATEDIFF(SECOND, beginInterval, endInterval)
WHEN CAST(beginLaborTime AS TIME) > CAST(GETDATE() AS TIME) AND CAST(beginInterval AS TIME) < CAST(GETDATE() AS TIME)
THEN (DATEDIFF(SECOND, beginLaborTime, '23:59') + DATEDIFF(SECOND, '00:00', CAST(GETDATE() AS TIME))) - DATEDIFF(SECOND, beginInterval, CAST(GETDATE() AS TIME))
ELSE (DATEDIFF(SECOND, beginLaborTime, '23:59') + DATEDIFF(SECOND, '00:00', CAST(GETDATE() AS TIME)))
END AS CurrentSeconds
,CASE
WHEN CAST(GETDATE() AS TIME) >= beginLaborTime AND (CAST(GETDATE() AS TIME) < endLaborTime OR (beginLaborTime > endLaborTime))
THEN 'OPEN'
ELSE
'CLOSE'
END AS 'cdState'
, cdLaborTime
, nmLaborTime
, beginLaborTime
, endLaborTime
, beginInterval
, endInterval
FROM [laborTime]
It is working, but it is too big and strange, is there any better way to solve this problem?
[ad_2]
لینک منبع
