You know, after looking at my solution again, I am not sure that I should have included "keycol" in it, it performs much better without it (leveraging the indexes, no doubt).

RBarryYoung 12/1/2009 9:06:59 AM

Heh. That dog-gone Peter Larsson almost always manages to one-up me! :-)

RBarryYoung 11/28/2009 10:51:56 PM

My solution which is not as good as the one already posted reduced the r/2 factor based on the maximum duration found for each application. Not exactly linear, but close:

WITH AppTimestamps AS
(SELECT app, starttime AS ts FROM dbo.Sessions),
MaxDurs AS
(SELECT app, max(endtime-starttime) AS maxdur FROM dbo.Sessions group by app),
Both AS
(Select A.app, ts, maxdur From AppTimeStamps A
Join MaxDurs M on A.app = M.app),
Counts AS
(SELECT app,
(SELECT count(*)
FROM dbo.Sessions AS S
WHERE T.app = S.app
AND S.starttime > (T.ts-T.maxdur)
AND T.ts >= S.starttime
AND T.ts < S.endtime)
AS cnt
FROM Both AS T)

SELECT app, MAX(cnt) AS mx
FROM Counts
GROUP BY app

techtwk 11/12/2009 9:51:50 AM

Hmm, not sure how you post code here, all of my codes line-breaks got stripped-out.

RBarryYoung 10/26/2009 11:19:35 AM

This seems to work for me:

;WITH cteSplitStartStop AS
(
SELECT app, endtime as daytim, 0 as IsStart, keycol
FROM IBGSessions
UNION ALL
SELECT app, starttime, 1 as IsStart, keycol
FROM IBGSessions
)
, cteRunningCounts AS
(
SELECT app
, IsStart
, ROW_NUMBER() OVER(PARTITION BY app ORDER BY daytim, IsStart, keycol) as AllEvents
, ROW_NUMBER() OVER(PARTITION BY app, IsStart ORDER BY daytim, keycol) as TypEvents
FROM cteSplitStartStop
)
SELECT app
, MAX( 2*TypEvents - AllEvents ) AS MaxSessions
FROM cteRunningCounts
WHERE IsStart = 1 -- only check the starts
GROUP BY app;

[Reply:

Excellent! I got similar solutions from Ben Flanaghan and Peter Larsson on behalf of Arnold Fribble. This type of solution warrants its own article, and I’m planning to cover it in a future article as the third part in the series.

Cheers,
Itzik]


RBarryYoung 10/26/2009 11:14:17 AM