How can I select records on the basis of their timings between specific times in SQL server

Question

I have this sample data and part of the query See the Fiddle. It shows the segment start time and end time. The task is get the sum of all the time segments between 6am to 10pm in seconds. I have filter it on the day but how can I restrict between 6am and 10 pm is difficult for me (May be another condition in the Case statement). The second difficulty is how to get part of a segment when its start time is 5am and endtime is 7am then I want to display 3600 seconds for the part which lies between 6am to 10pm. Similarly if a segment starts at 9pm and finished at 5am then I want to get 3600 seconds i.e. the part before 10pm.

Select 
  userid,
  CASE
    WHEN DateDiff(day, starttime, endtime) = 0 THEN datediff(second, starttime, endtime) 
  END As TotalSecondsFromSixAMToTenPM      
FROM
  Test

Here is the expected result, Note the first and second last value is obtained by taking these values

Select datediff(second,  '2015-01-14 06:00:00.000', '2015-01-14 09:25:54.000')
Select datediff(second, '2015-01-14 21:30:14.000', '2015-01-14 22:00:00.000')


╔════════╦══════════════════════════════╗
║ userid ║ TotalSecondsFromSixAMToTenPM ║
╠════════╬══════════════════════════════╣
║ abc    ║ 12354                        ║
║ abc    ║ 3600                         ║
║ abc    ║ 15150                        ║
║ abc    ║ 14684                        ║
║ abc    ║ 1786                         ║
║ abc    ║ NULL                         ║
╚════════╩══════════════════════════════╝

Answer 1

WITH splittedDates AS 
(
    SELECT
        userid,
        CAST(StartTime as date) 'StartDate',
        CAST(EndTime as date) 'EndDate',
        CAST(StartTime as time) 'stime',
        CAST(EndTime as time) 'etime',
        StartTime,
        EndTime
    FROM Test
), correctedTimes AS (
    SELECT userid,
    CASE WHEN StartDate=EndDate AND stime<etime AND stime<'10pm' THEN
        CASE 
            WHEN stime>='6am' THEN StartTime
            ELSE cast(StartDate as datetime) + cast('6am' as datetime)
        END
    END 'correctedStartTime',
    CASE WHEN StartDate=EndDate AND stime<etime AND etime>'6am' THEN
        CASE 
            WHEN etime<='10pm' THEN EndTime
            ELSE cast(StartDate as datetime) + cast('10pm' as datetime)
        END
    END 'correctedEndTime'
    FROM splittedDates
)
SELECT 
  userid,
  datediff(second, correctedStartTime, correctedEndTime) TotalSecondsFromSixAMToTenPM
FROM correctedTimes   

fiddle

result:

| userid | TotalSecondsFromSixAMToTenPM |
|--------|------------------------------|
|    abc |                        12354 |
|    abc |                         3600 |
|    abc |                        15150 |
|    abc |                        14684 |
|    abc |                         1786 |
|    abc |                       (null) |

EDIT

I added conditions stime<'10pm' and etime>'6am' to resolve the issue from comments

Answer 2

Your data:

CREATE TABLE TEST
  (
    Userid nvarchar(20),
    FirstDay date,
    LastDay date,
    StartTime time,
    EndTime time
    )

    INSERT INTO TEST(Userid, FirstDay, LastDay, StartTime, EndTime)
    VALUES (  N'abc', '20150113', '20150924', '20150114 05:30:00', '20150114 09:25:54'),
           (  N'abc', '20150113', '20150924', '20150114 09:25:54', '20150114 10:25:54'),
           (  N'abc', '20150113', '20150924', '20150114 13:13:00', '20150114 17:25:30'),
           (  N'abc', '20150113', '20150924', '20150114 17:25:30', '20150114 21:30:14'),
           (  N'abc', '20150113', '20150924', '20150114 21:30:14', '20150114 23:25:54'),
           (  N'abc', '20150113', '20150924', '20150114 23:25:54', '20150115 07:40:10')

Query:

Select *
    , T1 = Case When DATEDIFF(DAY, FirstDay, LastDay) > 1 Then (DATEDIFF(DAY, FirstDay, LastDay)-1)*(22-6)*60*60 Else 0 End
    , DATEDIFF(SECOND, Case When StartTime < '06:00:00' then '06:00:00' When StartTime < '22:00:00' then StartTime end
        , Case When FirstDay = LastDay Then 
            Case When EndTime > '22:00:00' Then '22:00:00' Else EndTime End
            Else '22:00:00' end
    )
    , T3 = Case When FirstDay <> LastDay Then
        DATEDIFF(SECOND, '06:00:00', Case When EndTime > '22:00:00' Then '22:00:00'
            When EndTime > '06:00:00' Then EndTime
            End)
    Else 0 End
From test

• T1 is the number of seconds for full days
• T2 is the number of seconds for the first day
• T3 is the number of seconds for the last day

Your total is T1+T2+T3

Output:

Userid  FirstDay    LastDay     StartTime         EndTime           T1          T2      T3
abc     2015-01-13  2015-09-24  05:30:00.0000000  09:25:54.0000000  14572800    57600   12354
abc     2015-01-13  2015-09-24  09:25:54.0000000  10:25:54.0000000  14572800    45246   15954
abc     2015-01-13  2015-09-24  13:13:00.0000000  17:25:30.0000000  14572800    31620   41130
abc     2015-01-13  2015-09-24  17:25:30.0000000  21:30:14.0000000  14572800    16470   55814
abc     2015-01-13  2015-09-24  21:30:14.0000000  23:25:54.0000000  14572800    1786    57600
abc     2015-01-13  2015-09-24  23:25:54.0000000  07:40:10.0000000  14572800    NULL    6010

If you don't want seconds accross several day, which is not clear from your question and your samples, this query is enough:

Select *
    , DATEDIFF(Second, Case When Cast(StartTime as time) < '06:00:00' then '06:00:00' 
                        When Cast(StartTime as time) < '22:00:00' then Cast(StartTime as time) end
        , Case When Cast(EndTime as time) > '22:00:00' then '22:00:00' 
            When Cast(EndTime as time) > '06:00:00' then Cast(EndTime as time) end
    )
From test

Output:

abc 12354
abc 3600
abc 15150
abc 14684
abc 1786
abc NULL

Answer 3

I don't have MS SQL in front of me so I can't test this, but how about something like the following?

Select 
  userid,
  CASE
    WHEN DateDiff(day, starttime, endtime) = 0 and starttime>=6am and endTime<=10am

    THEN datediff(second, starttime, endtime) 
  END As TotalSecondsFromSixAMToTenPM      
FROM
  Test