complex date range gaps - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

complex date range gaps

Author

Topic

ThomasG
Starting Member

3 Posts

Posted - 2008-03-05 : 00:41:20

Hi,

I have a table with date ranges for activitys. I need to get the userid , firstdayidle, lastdayidle for when a user has been idle in the last 90 days.
it gets tricky since it also must include users that have no records in activity table or only one record so it has 2 idle periods, etc

I have a hard time wrapping my head around the logic, weither using a datetables or not, so any help would be appreaciated

--my first step is to get the activitys that enter the last 90 days
select * from Activity2
where (enddate > DateAdd(d, -90, getDate()) AND enddate < getDate()) OR (startdate > DateAdd(d, -90, getDate()) AND startdate < getDate())

CREATE TABLE [dbo].[InternalUser](
[userid] [int],-- IDENTITY(1,1) NOT NULL,
[fullname] [varchar](50)
}
insert into [InternalUser] values(1, 'a')
insert into [InternalUser] values(2, 'b')
insert into [InternalUser] values(3, 'c')
insert into [InternalUser] values(4, 'd');
insert into [InternalUser] values(5, 'e');
insert into [InternalUser] values(6, 'f');

CREATE TABLE [dbo].[Activity2](
[activityid] [int] NOT NULL, --IDENTITY(1,1)
[userid] [int] NOT NULL,
[startdate] [datetime] NULL,
[enddate] [datetime] NULL
)

insert into [Activity2] values(1, 1, '2007-02-15 00:00:00.000', '2008-03-15 00:00:00.000')
insert into [Activity2] values(2, 2, '2007-01-01 00:00:00.000', '2008-01-02 00:00:00.000')
insert into [Activity2] values(3, 2, '2008-01-20 00:00:00.000', '2008-04-10 00:00:00.000')
insert into [Activity2] values(4, 3, '2008-02-20 00:00:00.000', '2008-10-10 00:00:00.000')
insert into [Activity2] values(5, 4, '2007-01-01 00:00:00.000', '2008-01-16 00:00:00.000')

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-05 : 04:37:35

Try this

select userid,min(startdate),max(enddate) from [Activity2]
where startdate < DateAdd(d,90, getDate())
group by userid

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-03-05 : 12:49:41

Interesting problem. I got it as far as listing all the start and end dates where there is no activity. I'm sure these results can be pivoted to group the periods together somehow, but I'm running short on time this morning. I'm using the F_TABLE_DATE function that you can find here on the forums.

WITH InactiveDates(UserId, Date) AS
(
SELECT u.userid, f.date
FROM internaluser u, f_table_date(dateadd(d,-90,getdate()),getdate()) f
WHERE NOT EXISTS(SELECT * FROM Activity2 WHERE userid = u.userid AND f.date BETWEEN startdate and enddate)
)
SELECT i1.*, 'Start' AS "No Activity" FROM InactiveDates i1 LEFT OUTER JOIN InactiveDates i2 ON i1.userid = i2.userid AND i1.Date = dateadd(d,1,i2.date)
WHERE i2.date IS NULL
UNION
SELECT i1.*, 'End' AS "No Activity" FROM InactiveDates i1 LEFT OUTER JOIN InactiveDates i2 ON i1.userid = i2.userid AND i1.Date = dateadd(d,-1,i2.date)
WHERE i2.date IS NULL

Hope this helps!

ThomasG
Starting Member

3 Posts

Posted - 2008-03-06 : 01:46:19

that was very helpfull, i used it as a base to get this one. the query handles most properly, but when the activity is in the center of the time period it only shows one of the idle periods

WITH InactiveDates(userid, Date) AS
(
SELECT u.userid, f.date
FROM InternalUser u, f_table_date(dateadd(d,-90,getdate()),getdate()) f
WHERE NOT EXISTS(SELECT * FROM Activity2 WHERE userid = u.userid AND f.date BETWEEN startdate and enddate)
)
select craft.userid, starts.date as start, ends.date as [end]
from InternalUser craft
inner join
(SELECT i1.date, i1.userid FROM InactiveDates i1 LEFT OUTER JOIN InactiveDates i2 ON i1.userid = i2.userid AND i1.Date = dateadd(d,1,i2.date)
WHERE i2.date IS NULL) starts ON starts.userid = craft.userid
inner join
(SELECT i1.date, i1.userid FROM InactiveDates i1 LEFT OUTER JOIN InactiveDates i2 ON i1.userid = i2.userid AND i1.Date = dateadd(d,-1,i2.date)
WHERE i2.date IS NULL) ends ON ends.userid = craft.userid

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-03-06 : 11:41:01

Your query wasn't working because you were only joining the tables on userid. You need to tie the starts and ends together.

Try this:

WITH InactiveDates(UserId, Date) AS
(
SELECT u.userid, f.date
FROM internaluser u, f_table_date(dateadd(d,-90,getdate()),getdate()) f
WHERE NOT EXISTS(SELECT * FROM Activity2 WHERE userid = u.userid AND f.date BETWEEN startdate and enddate)
)
SELECT craft.userid, starts.date as start, ends.date as [end]
FROM InternalUser craft
INNER JOIN
(SELECT i1.*, ROW_NUMBER() OVER (ORDER BY i1.UserId, i1.Date ASC) AS ROWID, 'Start' AS "No Activity" FROM InactiveDates i1 LEFT OUTER JOIN InactiveDates i2 ON i1.userid = i2.userid AND i1.Date = dateadd(d,1,i2.date)
WHERE i2.date IS NULL) starts ON starts.userid = craft.userid
INNER JOIN
(SELECT i1.*, ROW_NUMBER() OVER (ORDER BY i1.UserId, i1.Date ASC) AS ROWID, 'End' AS "No Activity" FROM InactiveDates i1 LEFT OUTER JOIN InactiveDates i2 ON i1.userid = i2.userid AND i1.Date = dateadd(d,-1,i2.date)
WHERE i2.date IS NULL) ends ON ends.userid = craft.userid
WHERE starts.ROWID = ends.ROWID

I think this actually works. But I'd monitor performance if you have a ton of InternalUser records.

ThomasG
Starting Member

3 Posts

Posted - 2008-03-06 : 22:25:12

sweet, thanks, it does work.
and it does take a while to run, but the report thats gona be based off it will only get run every 3 months so i dont care too much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources