How to check if two Datetime variables (from and to) belongs to a specific range?

I Have a table which stores a log of user sessions in a machine. It has 3 columns which i would use. time_from, time_to and the username. What I was trying to do is to calculate the peak usage hours of that computer. For that I would get the total number of minutes used per hour and the number of users. I tried to get the durations of sessions which fall between a specific time range like time_from > 0:00 and time_to < 1:00 but that only worked for sessions less than one hour. in my table the开发者_开发问答re are sessions that go upto 6 hours and these sessions will go overnight. My basic requirement is to get the durations of the sessions that belong to each 24 hours in a speicific date but i haven't been able to deal with sessions that go over 1 hour in length. Is there any way i can get this through purely using SQL or would i have to deal it with another program?

I'm using ms sql server btw.

Basically, my solution entails building a calendar of all hours between your start datetime and end datetime, then calculating the amount of minutes during each hour interval and finally totally by interval.

Declare @StartDate datetime
Declare @EndDate datetime

Set @StartDate = ...
Set @EndDate = ...

;With Calendar As
    (
    Select 1 As Num, DateAdd(d, DateDiff(d, 0, @StartDate), 0) As [Date]
    Union All
    Select Num + 1, DateAdd(hh, 1, [Date])
    From Calendar
    Where [Date] <= @EndDate
    )
    , Ranges As
    (
    Select C1.Num, C1.Date As StartDate, Coalesce(C2.Date, @EndDate) As EndDate
    From Calendar As C1
        Left Join Calendar As C2
            On C2.[Num] = C1.Num + 1
    )
    , UsageByHour As
    (
    Select U.username, R.Num As RangeNum, DateDiff( mi, R.StartDate, U.time_to ) As UsageMinutes
    From user_sessions As U
        Join Ranges As R
            On U.time_from Between R.StartDate And R.EndDate
                And U.time_to <= R.EndDate
    Union All       
    Select U.username, R.Num, DateDiff( mi, U.time_from, R.EndDate )
    From user_sessions As U
        Join Ranges As R
            On U.time_from Between R.StartDate And R.EndDate
                And U.time_to > R.EndDate
    Union All       
    Select U.username, R.Num, DateDiff( mi, R.StartDate, U.time_to )
    From user_sessions As U
        Join Ranges As R
            On U.time_to Between R.StartDate And R.EndDate
                And U.time_from < R.StartDate
    )
Select U.username, C.StartDate, C.EndDate, Sum( U.UsageMinutes )
From Usage As U
    Join Calendar As C
        On C.Num = U.RangeNum
Group By U.username, C.StartDate, C.EndDate
Option ( MaxRecursion 0 );

If you then wanted to find the peak minutes, I would put the above final query into another CTE and query against that:

...
    , UsageByInterval As
    (
    Select U.username, C.StartDate, C.EndDate, Sum( U.UsageMinutes ) As UsageMinutes
    From Usage As U
        Join Calendar As C
            On C.Num = U.RangeNum
    Group By U.username, C.StartDate, C.EndDate
    )
    , UsageRanking As
    (
    Select U.username, C.StartDate, C.EndDate, UsageMinutes
        , Row_Number() Over( Partition By U.username 
                                            Order By UsageMinutes Desc ) As Rnk
    From UsageByInterval
    )
Select username, StartDate, EndDate, UsageMinutes
From UsageRanking
Where Rnk = 1

Make a table of intervals and then:

SELECT interval
  , count(*) as sessions
  , count(distinct username) as users
  , SUM(DATEDIFF(s, time_to, time_from)) as seconds_used
FROM (
    SELECT user_sessions.username
      , intervals.interval
      , CASE
          WHEN intervals.time_from < user_sessions.time_from
          THEN user_sessions.time_from
          ELSE intervals.time_from
        END as time_from
      , CASE
        CASE
          WHEN user_sessions.time_to < intervals.time_to
          THEN user_sessions.time_to
          ELSE intervals.time_to
        END as time_from
    FROM user_sessions
      JOIN intervals
        ON intervals.time_from < user_sessions.time_to
          AND user_sessions.time_from < intervals.time_to
  )
GROUP BY interval;

Note that I don't use MS SQL server so I made this SQL generic and fairly portable. There may be a more compact way to do this. I also didn't test, so there may be obvious (hopefully easily fixed) bugs.