cases statement with dates and times using T-SQL in SQL-Server.

Question

Hi All,

I have the below set of data (see image)

The data in question is from columns EMP_ID to Stop_Moment.

Highlighted in Yellow is the results set I am looking for.

What I am trying to do maybe is to write a case statement

so when Start_moment and stop_moment >= 18:00 then start_moment
when start_moment < 18:00 and Stop_moment > 18:00 then 18:00

this will become the new_start column

when stop_moment > 18:00 and < 00:00 then stop_moment
when stop_moment >= 00:00 then 00:00

this will be the new_end column

then new_end - new_start will give me the duration in minutes

Is there a query that I can write to produce this?

The data format is as follows

CREATE TABLE [dbo].tblTest ON [PRIMARY]

Any assistance will be greatly appreciated.

Answer 1

WITH CTE AS (
   SELECT *, IIF(datepart(hour, START_MOMENT) >= 18, 
                    START_MOMENT,
                    dateadd(HOUR, 18, convert(smalldatetime, convert(date, START_MOMENT)))) AS New_start,
             IIF(datediff(DAY, START_MOMENT, STOP_MOMENT) = 0,
                    STOP_MOMENT,
                    dateadd(DAY, 1, convert(date, START_MOMENT))) AS New_end
   FROM   tblTest
)
SELECT *, datediff(MINUTE, New_start, New_end)
FROM   CTE

Had you posted the sample data as INSERT statements, I would have verified that this produce the expected result.