TSQL DateRange counting day by day

Question

I solve the problem which counts for 1 day but when I split the days into 1 day I got problem.

    declare @Start datetime
    declare @Finish datetime
    declare @TimeRange int

    set @Start = N'2012-10-16 00:00:00.000'
    set @Finish = N'2012-10-19 00:00:00.000'
    set @TimeRange = 1 

    declare @TimeRanges as TABLE (SessionStart datetime, SessionEnd datetime);

    with TimeRanges as 
    (   select @Start as StartTime, DATEADD(day, @TimeRange, @Start) as EndTime
        union all
        select DATEADD(day, @TimeRange, StartTime), DATEADD(day, @TimeRange, EndTime)
        from TimeRanges
        where EndTime  < @Finish 
    )

timeRanges tables split the @Start and @Finish into days.

Here is the what the problem starts:

 select StartTime, EndTime,TMP.NoOfCalls    
    from (SELECT TOP 1 DATEADD(SECOND, Number, c.SessionStartTime) CallTime, COUNT(C.ScenarioID) NoOfCalls FROM test c
INNER JOIN Numbers N ON N.Number <= DATEDIFF(SECOND, C.SessionStartTime, C.SessionCloseTime) 
where c.SessionStarttime >= @Start and  c.SessionCloseTime <= @Finish
GROUP BY DATEADD(SECOND, Number, c.SessionStartTime)
ORDER BY NoOfCalls DESC
)as TMP left outer join TimeRanges as TR on @Start <= TR.StartTime   and   TR.EndTime <= @Finish
        group by TR.StartTime, TR.EndTime,TMP.NoOfCalls
        order by TR.StartTime

Here is the result I got:

enter image description here

Actually 260 is the result of between N'2012-10-17 00:00:00.000' and N'2012-10-18 00:00:00.000' but I want result seperately.

My sample test table :

SessionID    SessionStartTime              SessionCloseTime
24       2012-10-16 01:00:06.000           2012-10-16 01:01:22.000
24       2012-10-16 01:00:08.000           2012-10-16 01:01:10.000
24       2012-10-16 01:00:16.000           2012-10-16 01:01:12.000
24       2012-10-16 01:00:30.000           2012-10-16 01:01:48.000
24       2012-10-16 01:00:41.000           2012-10-16 01:02:08.000
24       2012-10-16 01:00:48.000           2012-10-16 01:01:34.000
24       2012-10-16 01:00:56.000           2012-10-16 01:03:09.000
24       2012-10-16 01:01:02.000           2012-10-16 01:02:13.000
24       2012-10-16 01:01:05.000           2012-10-16 01:03:16.000
24       2012-10-16 01:01:09.000           2012-10-16 01:02:42.000
24       2012-10-16 01:01:15.000           2012-10-16 01:02:48.000
24       2012-10-16 01:01:18.000           2012-10-16 01:02:14.000
24       2012-10-16 01:01:18.000           2012-10-16 01:02:06.000
24       2012-10-16 01:01:42.000           2012-10-16 01:03:16.000
24       2012-10-16 01:01:45.000           2012-10-16 01:03:04.000

in your problem `SELECT` you are not using the `TimeRanges` CTE defined earlier. Also, what is the `Numbers` table? — Cristian Lupascu, Nov 02 '12 at 09:05
sorry I corrected it Numbers is just sample which I use while counting Numbers table starts 1,2,3....100000 — cihadakt, Nov 02 '12 at 09:13
I'm still trying to figure out what you want to achieve with this. I made an online running version at http://www.sqlfiddle.com/#!3/4970c/11 . Can you please take a look and describe what is the desired output in that case? — Cristian Lupascu, Nov 02 '12 at 09:30

score 0 · Answer 1 · answered Nov 02 '12 at 09:35

Your problem is the left join, but your query seems over complex.

Try

   with TimeRanges as 
    (   select @Start as StartTime, DATEADD(day, @TimeRange, @Start) as EndTime
        union all
        select DATEADD(day, @TimeRange, StartTime), DATEADD(day, @TimeRange, EndTime)
        from TimeRanges
        where EndTime  < @Finish 
    )
        select TimeRanges.StartTime, TimeRanges.EndTime, COUNT(*)
        from TimeRanges
            inner join YourTable Sess
                on sess.sessionstarttime>TimeRanges.StartTime 
                and sess.sessionclosetime< TimeRanges.EndTime
        group by TimeRanges.StartTime, TimeRanges.EndTime

TSQL DateRange counting day by day

1 Answers1