|
|
Posted by Scott Marquardt on 09/28/25 11:25
AK opined thusly on Aug 30:
> That done, you need to match every beginning to its corresponding end.
> This is very simple using row_number() available in SQL 2005
> In earlier versions, you can either emulate row_number() using
> identity() column in a result set, or use a join condition like this:
>
> ...
> from beginnings b join ends e on b.time<e.time
> where not exists(select 1 from beginnings b1 where b.time< b1.time and
> b1.time<e.time)
> and not exists(select 1 from endings e1 where b.time< e1.time and
> e1.time<e.time)
OK, here's the result in all its gory(sic). Still playing. It's interesting
to vary @interval and see the consequences. Now I have to figure out how to
justify any particular value for that. Geeez . . .
| CREATE FUNCTION ie_begin(@interval datetime)
| RETURNS TABLE
| AS
|
| Return
| (
| select i0.Username, i0.vDate
| from ieHist i0 where not exists
| (
| select vDate from ieHist where vDate > i0.vDate - @interval and vDate < i0.vDate and i0.username = username
| )
| or exists
| (
| select vDate from ieHist where vDate < i0.vDate - @interval and vDate > i0.vDate and i0.username = username
| )
| group by i0.username, i0.vDate
| )
| go
| CREATE FUNCTION ie_end(@interval datetime)
| RETURNS TABLE
| AS
|
| Return
| (
| select i0.Username, i0.vDate
| from ieHist i0 where not exists
| (
| select vDate from ieHist where vDate < i0.vDate + @interval and vDate > i0.vDate and i0.username = username
| )
| or exists
| (
| select vDate from ieHist where vDate > i0.vDate + @interval and vDate < i0.vDate and i0.username = username
| )
| group by i0.username, i0.vDate
| )
| go
And here's my sandbox:
| declare @interval as datetime
| declare @beginnings table (username varchar(30), vtime datetime)
| declare @ends table (username varchar(30), vtime datetime)
| set @interval = '00:10'
| insert into @beginnings (username, vtime) select * from ie_begin(@interval)
| insert into @ends (username, vtime) select * from ie_end(@interval)
| select b.username, b.vtime, e.vtime, datediff(minute, b.vtime, e.vtime) as duration
| from @beginnings b
| join @ends e
| on b.vtime < e.vtime and b.username = e.username
|Aaugh where
| not exists
| (
| select 1 from @beginnings b1 where b.vtime < b1.vtime and b1.vtime < e.vtime
| )
| and
| not exists
| (
| select 1 from @ends e1 where b.vtime < e1.vtime and e1.vtime < e.vtime
| )
| go
With 5 minutes for @interval, this was typical:
User_one 8/30/2005 1:28 PM 8/30/2005 1:30 PM 2
User_one 8/30/2005 1:36 PM 8/30/2005 1:37 PM 1
User_two 8/26/2005 12:40 PM 8/26/2005 12:42 PM 2
User_two 8/29/2005 6:52 AM 8/29/2005 6:55 AM 3
User_two 8/29/2005 10:34 AM 8/29/2005 10:38 AM 4
User_three 8/30/2005 3:52 PM 8/30/2005 3:59 PM 7
User_three 8/30/2005 4:06 PM 8/30/2005 4:07 PM 1
User_four 8/25/2005 12:17 PM 8/25/2005 12:18 PM 1
User_four 8/25/2005 1:33 PM 8/25/2005 2:02 PM 29
User_four 8/25/2005 2:02 PM 8/25/2005 2:21 PM 19
User_four 8/25/2005 2:28 PM 8/25/2005 2:32 PM 4
User_four 8/25/2005 2:44 PM 8/25/2005 3:27 PM 43
User_four 8/25/2005 4:28 PM 8/25/2005 4:30 PM 2
User_four 8/26/2005 3:17 PM 8/26/2005 3:19 PM 2
User_four 8/30/2005 4:28 PM 8/30/2005 4:29 PM 1
There's a LOT of work to do yet on this. Not bad for starters though.
Thanks again for pulling the cord on this old lawn-mower.
--
Scott
Navigation:
[Reply to this message]
|