Reputation: 93

Get records where there is another record within a week for 5 weeks consecutively

So i have a table of users at an office and they start at one day and finnish the same or a later day. I need to find where a user has been at an office at least once for 5 weeks in a row, e.g. user 1 has been to office 1 for the last 5 weeks.

Heres some sample data I'm working with:

DECLARE @visits table 
(
    UserId int,
    OfficeId int,
    Start datetime,
    [End] datetime
)

INSERT INTO @visits (UserId, OfficeId, Start, [End])
VALUES (1, 1, '2013-07-11', '2013-07-13'), 
       (1, 1, '2013-07-02', '2013-07-03'),
       (1, 1, '2013-06-26', '2013-06-28'),
       (1, 2, '2013-06-19', '2013-06-19'),
       (1, 1, '2013-06-17', '2013-06-17'),
       (1, 1, '2013-06-13', '2013-06-13'),
       (2, 1, '2013-07-09', '2013-07-10'),
       (2, 1, '2013-07-01', '2013-07-02'),
       (2, 1, '2013-06-27', '2013-06-28'),
       (2, 1, '2013-06-13', '2013-06-14'),
       (2, 1, '2013-06-04', '2013-06-04')

I should only get back UserId 1 since he has been for 5 weeks to office 1, user 2 missed a week so shouldn't come back.

This needs to work for 12 weeks but just for simplicity I picked 5.

So far I have this which is almost there, I just need a way to group by consecutive weeks and then add having Count > 4

SELECT *, 
        dateadd(day, -datepart(dw, V1.Start) + 1, V1.Start) MondayOfStart,
        DATEADD(day, 7 - DATEPART(dw, V1.[End]), V1.[End]) as SundayOfEnd,
        DATEADD(day, -1, dateadd(day, -datepart(dw, V1.Start) + 1, V1.Start)) StartLess1
FROM @visits V1
INNER JOIN @visits V2 ON 
        V2.UserId = V1.UserId AND
        V2.OfficeId = V1.OfficeId AND
        DATEADD(day, 7 - DATEPART(dw, V2.[End]), V2.[End]) = DATEADD(day, -1, dateadd(day, -datepart(dw, V1.Start) + 1, V1.Start))

EDIT: Some of my actual data to test:

VALUES 
(2777, 2248, '2013-05-23 00:00:00.000', '2013-05-23 00:00:00.000'),
(2777, 2248, '2013-05-24 00:00:00.000', '2013-05-24 00:00:00.000'),
(2777, 2248, '2013-05-27 00:00:00.000', '2013-05-27 00:00:00.000'),
(2777, 2248, '2013-05-28 00:00:00.000', '2013-05-28 00:00:00.000'),
(2777, 2248, '2013-05-29 00:00:00.000', '2013-05-29 00:00:00.000'),
(2777, 2248, '2013-05-30 00:00:00.000', '2013-05-30 00:00:00.000'),
(2777, 2248, '2013-05-31 00:00:00.000', '2013-05-31 00:00:00.000'),
(2777, 2248, '2013-06-03 00:00:00.000', '2013-06-03 00:00:00.000'),
(2777, 2248, '2013-06-04 00:00:00.000', '2013-06-04 00:00:00.000'),
(2777, 2248, '2013-06-05 00:00:00.000', '2013-06-05 00:00:00.000'),
(2777, 2248, '2013-06-06 00:00:00.000', '2013-06-06 00:00:00.000'),
(2777, 2248, '2013-06-07 00:00:00.000', '2013-06-07 00:00:00.000'),
(2777, 2248, '2013-06-10 00:00:00.000', '2013-06-10 00:00:00.000'),
(2777, 2248, '2013-06-11 00:00:00.000', '2013-06-11 00:00:00.000'),
(2777, 2248, '2013-06-12 00:00:00.000', '2013-06-12 00:00:00.000'),
(2777, 2248, '2013-06-13 00:00:00.000', '2013-06-13 00:00:00.000'),
(2777, 2248, '2013-06-14 00:00:00.000', '2013-06-14 00:00:00.000'),
(2777, 2248, '2013-06-17 00:00:00.000', '2013-06-17 00:00:00.000'),
(2777, 2248, '2013-06-18 00:00:00.000', '2013-06-18 00:00:00.000'),
(2777, 2248, '2013-06-19 00:00:00.000', '2013-06-19 00:00:00.000'),
(2777, 2248, '2013-06-20 00:00:00.000', '2013-06-20 00:00:00.000'),
(2777, 2248, '2013-06-21 00:00:00.000', '2013-06-21 00:00:00.000'),
(2777, 2248, '2013-06-24 00:00:00.000', '2013-06-24 00:00:00.000'),
(2777, 2248, '2013-06-25 00:00:00.000', '2013-06-25 00:00:00.000'),
(2777, 2248, '2013-06-26 00:00:00.000', '2013-06-26 00:00:00.000'),
(2777, 2248, '2013-06-27 00:00:00.000', '2013-06-27 00:00:00.000'),
(2777, 2248, '2013-06-28 00:00:00.000', '2013-06-28 00:00:00.000')

Upvotes: 2

Answers (2)

okrumnow

Reputation: 2416

I used a temporary table to solve the problem. I filled dates of Monday and Sunday for the last 5 weeks into it (starting from the date '2013-07-16').

I then joined via dates and count the number of records for the given User-Office combination.

Please note that I needed to adjust your Monday and Sunday formulas, probably because of different locale settings. Please adjust to your needs.

Also note that I did use a TABLE and not a table variable for the Visits table but that should not cause havoc.

create table #Weeks (
  Monday Date,
  Sunday Date
)
GO

DECLARE @date DATE = CONVERT(DATE, '2013-07-16')

INSERT INTO #Weeks(Monday, Sunday)
VALUES 
  (dateadd(DAY, -datepart(dw, DATEADD(WEEK, -1, @date)) + 2, DATEADD(WEEK, -1, @date)), 
   dateadd(DAY, -datepart(dw, DATEADD(WEEK, -1, @date)) + 8, DATEADD(WEEK, -1, @date))),
  (dateadd(DAY, -datepart(dw, DATEADD(WEEK, -2, @date)) + 2, DATEADD(WEEK, -2, @date)), 
   dateadd(DAY, -datepart(dw, DATEADD(WEEK, -2, @date)) + 8, DATEADD(WEEK, -2, @date))),
  (dateadd(DAY, -datepart(dw, DATEADD(WEEK, -3, @date)) + 2, DATEADD(WEEK, -3, @date)), 
   dateadd(DAY, -datepart(dw, DATEADD(WEEK, -3, @date)) + 8, DATEADD(WEEK, -3, @date))),
  (dateadd(DAY, -datepart(dw, DATEADD(WEEK, -4, @date)) + 2, DATEADD(WEEK, -4, @date)), 
   dateadd(DAY, -datepart(dw, DATEADD(WEEK, -4, @date)) + 8, DATEADD(WEEK, -4, @date))),
  (dateadd(DAY, -datepart(dw, DATEADD(WEEK, -5, @date)) + 2, DATEADD(WEEK, -5, @date)), 
   dateadd(DAY, -datepart(dw, DATEADD(WEEK, -5, @date)) + 8, DATEADD(WEEK, -5, @date)))
GO

SELECT UserId, OfficeId, COUNT(*) AS WeeksAttended
FROM Visits JOIN #Weeks ON Start <= Sunday AND [End] >= Monday
GROUP BY UserId, OfficeId
HAVING COUNT(*) = 5
GO

DROP TABLE #Weeks

This returns

USERID  OFFICEID    WEEKSATTENDED
1           1           5

Upvotes: 1

gotqn

Reputation: 43636

This a simple example of how this can be done using Recursive CTE statement.

I am not sure what exactly you need as "OUTPUT", so I am displaying the user and its last week start and end dates. You are free to redesign this in order to match your needs:

DECLARE @visits TABLE 
(
     [UserId] INT
    ,[OfficeId] INT
    ,[Start] DATETIME
    ,[End] DATETIME
)

INSERT INTO @visits (UserId, OfficeId, Start, [End])
VALUES (1, 1, '2013-07-11', '2013-07-13'), 
       (1, 1, '2013-07-02', '2013-07-03'),
       (1, 1, '2013-06-26', '2013-06-28'),
       (1, 2, '2013-06-19', '2013-06-19'),
       (1, 1, '2013-06-17', '2013-06-17'),
       (1, 1, '2013-06-13', '2013-06-13'),
       (2, 1, '2013-07-09', '2013-07-04'),
       (2, 1, '2013-07-01', '2013-07-02'),
       (2, 1, '2013-06-27', '2013-06-28'),
       (2, 1, '2013-06-13', '2013-06-14'),
       (2, 1, '2013-06-04', '2013-06-04')

;WITH DataSource ([UserId], [OfficeId], [Start], [End], [Level]) AS
(
    SELECT AnchorMember.[UserId]
          ,AnchorMember.[OfficeId]
          ,DATEADD(DAY, -(DATEPART(WEEKDAY, AnchorMember.[Start])-1), AnchorMember.[Start]) 
          ,DATEADD(DAY, 7-(DATEPART(WEEKDAY, AnchorMember.[End])), AnchorMember.[End]) 
          ,1 AS [Level]
    FROM @visits AS AnchorMember
    UNION ALL
    SELECT RecursiveMember.[UserId]
          ,RecursiveMember.[OfficeId]
          ,DATEADD(DAY, -(DATEPART(WEEKDAY, RecursiveMember.[Start])-1), RecursiveMember.[Start]) 
          ,DATEADD(DAY, 7-(DATEPART(WEEKDAY, RecursiveMember.[End])), RecursiveMember.[End]) 
          ,DS.[Level] + 1
    FROM @visits AS RecursiveMember
    INNER JOIN DataSource DS
        ON RecursiveMember.[UserId] = DS.[UserId]
        AND RecursiveMember.[OfficeId] = DS.[OfficeId]
    -- This is the important part: The "Week StartDate" + 1 day should  be eaual to previous "Week EndDate"
    WHERE DATEADD(DAY, -(DATEPART(WEEKDAY, RecursiveMember.[End])-1), RecursiveMember.[End]) = DATEADD(DAY, 8-(DATEPART(WEEKDAY, DS.[Start])), DS.[Start])
)
SELECT [UserId]
      ,[OfficeId]
      ,[Start]
      ,[End]
FROM DataSource
WHERE [Level] = 5
ORDER BY [UserId]
        ,[OfficeId]
        ,[Start]
        ,[End]

What we are doing in the expression can be describe with the following steps:

SELECT all records and define their star and end WEEK days.
FOR EACH of the records above, check if there is other record for the same user id and office id, but from the next week. If such records exists, return it.
DO STEP 1, but for the results from STEP 2, until no records are found in step 2

The [Level] column displays how many consecutive weeks there are before the current record. So, if you need this to work for 12 weeks, replace the "5" with "12" in the final "WHERE" clause.

EDIT:

As you need only the distinct users with at least one set of consecutive weeks, we can reduce the number of columns as follows:

;WITH DataSource ([UserId], [OfficeId], [Start], [Level]) AS
(
    SELECT AnchorMember.[UserId]
          ,AnchorMember.[OfficeId]
          ,DATEADD(DAY, -(DATEPART(WEEKDAY, AnchorMember.[Start])-1), AnchorMember.[Start]) 
          ,1 AS [Level]
    FROM @visits AS AnchorMember
    UNION ALL
    SELECT RecursiveMember.[UserId]
          ,RecursiveMember.[OfficeId]
          ,DATEADD(DAY, -(DATEPART(WEEKDAY, RecursiveMember.[Start])-1), RecursiveMember.[Start]) 
          ,DS.[Level] + 1
    FROM @visits AS RecursiveMember
    INNER JOIN DataSource DS
        ON RecursiveMember.[UserId] = DS.[UserId]
        AND RecursiveMember.[OfficeId] = DS.[OfficeId]
    WHERE DATEADD(DAY, -(DATEPART(WEEKDAY, RecursiveMember.[Start])-1), RecursiveMember.[Start]) = DATEADD(DAY, 7, DS.[Start]) 
)
SELECT DISTINCT [UserId]
               ,[OfficeId]
FROM DataSource
WHERE [Level] = 5
ORDER BY [UserId]
        ,[OfficeId]

As I have not access to your data, I can not be sure what is causing the delay, so this could not help.

If the query performance is good until you add the group clauses, you can try to INSERT the result from the CTE in temporary table or table variable and "group" it then.

If this is fixing the performance issue, you can post the query execution plan.

Upvotes: 2

Get records where there is another record within a week for 5 weeks consecutively

Answers (2)

Related Questions