CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Mandar Jogalekar
Mandar Jogalekar

Reputation: 3281

sql server table peak time

I have a application which has 1000's of users logging into at various times for various purposes. the task now is to somehow figure out "number of users at peak time" what we record in sql is userLoginTime,timespent. the problem here is

How to actually calculate peak time for the app. and how to calculate number of users at peak time.

Is it possible in Sql

Upvotes: 0

Views: 913

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

I've had a play around - I'm working with sessions with a recorded start and end datetime2 values, but hopefully you can adapt your current data to conform to this:

Sample data (if I've got the answer wrong, maybe you can adopt this, add it to your question, and add more samples and expected outputs):

create table #Sessions (
    --We'll treat this as a semi-open interval - the session was "live" at SessionStart, and "dead" at SessionEnd
    SessionStart datetime2 not null,
    SessionEnd datetime2 null
)
insert into #Sessions (SessionStart,SessionEnd) values
('20120101','20120105'),
('20120103','20120109'),
('20120107','20120108')

And the query:

--Logically, the highest number of simultaneous users was reached at some point when a session started
;with StartTimes as (
    select distinct SessionStart as Instant from #Sessions
), Overlaps as (
    select
        st.Instant,COUNT(*) as Cnt,MIN(s.SessionEnd) as SessionEnd
    from
        StartTimes st
            inner join
        #Sessions s
            on
                st.Instant >= s.SessionStart and
                st.Instant < s.SessionEnd
    group by
        st.Instant
), RankedOverlaps as (
    select Instant as SessionStart,Cnt,SessionEnd,RANK() OVER (ORDER BY Cnt desc) as rnk
    from Overlaps
)
select * from RankedOverlaps where rnk = 1

drop table #Sessions

Which, with my sample data gives:

SessionStart           Cnt         SessionEnd             rnk
---------------------- ----------- ---------------------- --------------------
2012-01-03 00:00:00.00 2           2012-01-05 00:00:00.00 1
2012-01-07 00:00:00.00 2           2012-01-08 00:00:00.00 1

An alternative approach, still using the above, but if you also want to analyze "not quite peak" values also, is as follows:

--An alternate approach - arrange all of the distinct time values from Sessions into order
;with Instants as (
    select SessionStart as Instant from #Sessions
    union --We want distinct here
    select SessionEnd from #Sessions
), OrderedInstants as (
    select Instant,ROW_NUMBER() OVER (ORDER BY Instant) as rn
    from Instants
), Intervals as (
    select oi1.Instant as StartTime,oi2.Instant as EndTime
    from
        OrderedInstants oi1
            inner join
        OrderedInstants oi2
            on
                oi1.rn = oi2.rn - 1
), IntervalOverlaps as (
    select
        StartTime,
        EndTime,
        COUNT(*) as Cnt
    from
        Intervals i
            inner join
        #Sessions s
            on
                i.StartTime < s.SessionEnd and
                s.SessionStart < i.EndTime
    group by
        StartTime,
        EndTime
)
select * from IntervalOverlaps order by Cnt desc,StartTime

This time, I'm outputting all of the time periods, together with the number of simultaneous users at the time (order from highest to lowest):

StartTime              EndTime                Cnt
---------------------- ---------------------- -----------
2012-01-03 00:00:00.00 2012-01-05 00:00:00.00 2
2012-01-07 00:00:00.00 2012-01-08 00:00:00.00 2
2012-01-01 00:00:00.00 2012-01-03 00:00:00.00 1
2012-01-05 00:00:00.00 2012-01-07 00:00:00.00 1
2012-01-08 00:00:00.00 2012-01-09 00:00:00.00 1

Upvotes: 2

Related Questions