days between and start date
-
I'm doing some work on vaccinations. Need to identify "fully vaccinated" individuals. The days between the 1st and 2nd have to be less than 40 days apart or the process starts over. How would I do this for each PatID? TIA
IF OBJECT_ID('TmpDB..#vac','U') IS NOT NULL
DROP TABLE #vac
create table #vac
(PatID varchar(10)
,VacNm varchar(10)
,VacDate date
)
Insert into #vac
values
(15,'Moderna', '2020-08-05')
,(15, 'Moderna','2020-09-04')
,(15, 'Moderna', '2021-07-28')
,(10, 'Pfizer','2020-09-06')
,(10, 'Pfizer','2021-03-04')
,(10, 'Pfizer', '2020-04-03')
,(10, 'Pfizer','2020-10-04')
,(10, 'Pfizer','2021-04-04')
Select
v.PatID
,v.vacNm
,v.VacDate
,DATEDIFF(day, Lag(v.VacDate, 1) OVER(PARTITION BY v.patid ORDER BY v.vacdate),v.VacDate) as diff
from #vac v
order by v.PatID, v.VacDate
-
I'm almost certain there's a more efficient way to do this, but I can't think of it now.
;WITH cte_valid_first_dose AS (
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY PatID ORDER BY VacDate) AS DoseNumber
FROM (
SELECT *,
DATEDIFF(DAY, v.VacDate, LEAD(v.VacDate, 1) OVER(PARTITION BY v.PatID ORDER BY v.VacDate)) AS DaysDiff
FROM #vac v
) AS derived
WHERE DaysDiff <= 40 OR DaysDiff IS NULL
) AS derived2
WHERE DoseNumber = 1
) --SELECT * FROM cte_valid_first_dose
SELECT cv.PatID, v.VacDate, ROW_NUMBER() OVER(PARTITION BY cv.PatID ORDER BY v.VacDate) AS DoseNumber
FROM cte_valid_first_dose cv
INNER JOIN #vac v ON v.PatID = cv.PatID AND v.VacDate >= cv.VacDate
ORDER BY cv.PatID, v.VacDateSQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
As a bit of a sidebar, the 40 day thing isn't correct for things like the Moderna booster, which requires you to wait for 6 months before getting it. I don't know about the others but I'm pretty sure the guidelines for a booster are quite different than for the first 2 shots and I'm not sure the 40 day thing applies even there and I doubt that they're the same across the board.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This was removed by the editor as SPAM
-
Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
julianize_day_nbr INTEGER NOT NULL,
...);
Fill out your table for one or two centuries, depending on how long you think the feds are going to keep the Covid scare going, then simply use a procedure that takes two dates and subtracts their Julianized numbers.
SELECT (C2.julianize_day_nbr - C1.julianize_day_nbr)
FROM Calendar AS C1, Calendar AS C2
WHERE C1.cal_date = @vaccination_date_1
AND C2.cal_date = @vaccination_date_2;
It will also help in the future if you follow the netiquette we have for posting. That means use the only format allowed in ANSI/ISO standard SQL (yyyy-mm-dd), and insertion statements, not spreadsheets. People want to be able to cut-and-paste instead of having to re-transcribe a posting. This is just good manners.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply