Given the following two tables:

CREATE TABLE #t1 (ID varchar(1), SpecDate date)
CREATE TABLE #t2 (ID varchar(1), Alert varchar(1), AlertDate date)

INSERT INTO #t1 (ID, SpecDate) VALUES ('A', '2021-05-10')
INSERT INTO #t1 (ID, SpecDate) VALUES ('B', '2021-05-10')
INSERT INTO #t1 (ID, SpecDate) VALUES ('B', '2021-05-12')

INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('A', 'N', '2021-05-06')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('A', 'Y', '2021-05-06')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('A', 'Y', '2021-05-07')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('A', 'Y', '2021-05-08')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('A', 'N', '2021-05-09')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('A', 'N', '2021-05-10')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('A', 'Y', '2021-05-11')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('B', 'N', '2021-05-07')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('B', 'Y', '2021-05-08')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('B', 'N', '2021-05-09')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('B', 'N', '2021-05-10')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('B', 'N', '2021-05-11')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('B', 'N', '2021-05-12')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('B', 'N', '2021-05-13')

#t1:

#t2:

I need to find the count of consecutive 'Y' Alerts from #t2 where at least 1 of the 'Y' is within 2 days prior through equal to SpecDate of #t1, ignoring anything after SpecDate and grouped by ID. There can be multiple values of Alert for each ID and AlertDate (if sensor is moved). The final output I am looking for is:

I have gotten to finding the 'Y' within the specified window, but can't figure out how to count consecutive, especially when the count leaves the window:

SELECT DISTINCT t1.ID
    , t1.SpecDate
    , t2.Alert
FROM #t1 AS t1
INNER JOIN #t2 AS t2
    ON t1.ID = t2.ID
WHERE t2.Alert = 'Y' AND
    t2.AlertDate BETWEEN  DATEADD(DAY, -2, t1.SpecDate) AND t1.SpecDate

Edit I was trying to keep the problem as simple and generic as possible, but I think some context might help. I'm working with healthcare data and, for a given day (#t1), I need to know if a patient has had a urinary catheter within the last two days. If yes, then I need to know how long they have had it. #t2 has a daily flag 'Y'/'N' indicating if they had one. There is always at least one daily record, but there can be multiple entries if the patient transfers or has a catheter status changes.

CodePudding user response：

Gaps and islands problem. Sounds like you need to aggregate first if multiple rows per day are possible:

with t2 as (
    select ID, AlertDate, max(Alert) as Alert
    from #t2
    group by ID, AlertDate
), data as (
    select t1.ID, t1.SpecDate,
        row_number() over (partition by t1.ID order by t2.AlertDate) as rn,
        datediff(day, t1.SpecDate, t2.AlertDate) as dn,
        min(t2.AlertDate) over (partition by t1.ID, t1.SpecDate) as firstAlert  
    from #t1 t1 inner join #t2 t2
        on t2.ID = t1.ID
            and t2.Alert = 'Y'
            and t2.AlertDate >= dateadd(day, -2, t1.SpecDate)
), streaks as (
    select ID, SpecDate, count(*) as cnt
    from data
    where firstAlert <= SpecDate
    group by ID, SpecDate, dn - rn
)
select ID, SpecDate, max(cnt) as ConsecutiveAlertDays
from streaks
group by ID, SpecDate;

CodePudding user response：

I think this is what you're after - thank you for the table structure and sample data, but I suspect you'll have other edge cases you didn't include:

;WITH src AS -- group in case more than one 'Y' on a day
(
  SELECT ID, AlertDate
    FROM #t2 
    WHERE Alert = 'Y'
    GROUP BY ID, AlertDate
),
groups AS -- make some islands of consecutive days
(
  SELECT ID, AlertDate, day_group = DATEADD(DAY, 
    1-ROW_NUMBER() OVER (PARTITION BY ID ORDER BY AlertDate), AlertDate)
  FROM src
),
agg AS -- get the count and last day in each island
(
  SELECT ID, AlertDate, 
    c = COUNT(*)       OVER (PARTITION BY ID, day_group),
    m = MAX(AlertDate) OVER (PARTITION BY ID, day_group) 
  FROM groups
)
SELECT t1.ID, t1.SpecDate, ConsecutiveAlertDays = agg.c
  FROM agg
  INNER JOIN #t1 AS t1
    ON agg.m >= DATEADD(DAY, -2, t1.SpecDate)
   AND agg.m <= t1.SpecDate
   AND t1.ID = agg.ID
   AND agg.m = agg.AlertDate
ORDER BY t1.ID, t1.SpecDate; 

Results:

• Example db<>fiddle