Conditional Sum in SQL
-
Hello,
I am trying to determine the total count of catheter days in February. My SQL query below intentionally adds a +1 to the # of days summing code to account for each day a patient had a catheter in.
But for patients that changed status several times in a month, it is adding 1 day for each row, so that when I want to sum the total days for the month it is adding 2 extra days. I need to identify
patients that have had their status change more than once and then subtract one day for each time (each extra row). I have tried to do this in SQL Server Reporting Services in a group expression
but I can't get it to not count those extra days. A solution either with a straight SQL Query or in SSRS would work. Results for one sample patient below, full query below that.
PatID StartDate EndDate Type of Line Insert Dt Remv Dt Pt Status # Cath Days Cath Days for Feb
10215 2016-09-30 2022-02-21 Power Port 2016-11-22 NULL Active 21 30 (Should be 28)
10215 2022-02-21 2022-02-22 Power Port 2016-11-22 NULL Hospitalized 2
10215 2022-02-22 NULL Power Port 2016-11-22 NULL Active 7
Hello,
I am trying to determine the total count of catheter days in February. My SQL query below intentionally adds a +1 to the # of days summing code to account for each day a patient had a catheter in.
But for patients that changed status several times in a month, it is adding 1 day for each row, so that when I want to sum the total days for the month it is adding 2 extra days. I need to identify
patients that have had their status change more than once and then subtract one day for each time (each extra row). I have tried to do this in SQL Server Reporting Services in a group expression
but I can't get it to not count those extra days. A solution either with a straight SQL Query or in SSRS would work. Results for one sample patient below, full query below that.
PatIDStartDate EndDateType of Line Insert DtRemv Dt Pt Status# Cath Days Cath Days for Feb
102152016-09-30 2022-02-21Power Port 2016-11-22 NULL Active21 30 (Should be 28)
102152022-02-21 2022-02-22Power Port 2016-11-22 NULL Hospitalized2
102152022-02-22 NULL Power Port 2016-11-22 NULL Active7
WITH STAT AS
(
select
Pt.PtKey, ptid, PtPtInfusionStatus.ModifiedDate, PtInfusionStatus.Name, Pt.PersonKey, PtInfusionStatus.PtInfusionStatusKey, PtPtInfusionStatus.StartDate, PtPtInfusionStatus.EndDate
from Pt left join PtPtInfusionStatus on PtPtInfusionStatus.PtKey = pt.PtKey
left join PtInfusionStatus on ptinfusionstatus.PtInfusionStatusKey = PtPtInfusionStatus.PtInfusionStatusKey
where
ptptinfusionstatus.PtInfusionStatusKey IN (1,5)
)
,
PAT as
(
SELECT
STAT.PtID
,stat.name
,PtKey
,Person.LastName
,Person.FirstName
,stat.StartDate
,stat.EndDate
,STAT.PtInfusionStatusKey
FROM STAT
JOIN Person ON STAT.PersonKey = Person.personkey
)
,
ACCESS1 AS
(
SELECT d.Name
,d.PharmacyEventAndOutcomeTypeDetailKey
,T.PharmacyEventAndOutcomeTypeKey
FROM PharmacyEventAndOutcomeTypeDetail d WITH (NOLOCK)
left join PharmacyEventAndOutcomeType t WITH (NOLOCK) on t.PharmacyEventAndOutcomeTypeKey = d.PharmacyEventAndOutcomeTypeKey
)
,
ACCESS2 AS
(
SELECT PAT.ptkey, PAT.ptid, PAT.LastName, PAT.FirstName, PAT.StartDate, PAT.EndDate, PAT.PtInfusionStatusKey,
ISNULL(devicetype.Name, '') [Access Device_Type],
ppad.InsertionDate [Access Device_Insertion Date],
ppad.RemovalDate [Access Device_Removal Date],
FROM
PAT WITH (NOLOCK)
join PharmacyPtAccessDevice ppad WITH(NOLOCK) ON ppad.PtKey = PAT.PtKey
left join ACCESS1 devicetype WITH (NOLOCK) ON devicetype.PharmacyEventAndOutcomeTypeDetailKey = ppad.AccessDeviceTypeKey and devicetype.PharmacyEventAndOutcomeTypeKey = 4
)
--***MAIN QUERY***
SELECT
ACCESS2.StartDate as 'SOC Date'
,ACCESS2.[PtID] as 'Patient ID'
,ACCESS2.[LastName] + ', ' + ACCESS2.FirstName AS 'Patient'
,ACCESS2.StartDate
,ACCESS2.EndDate
,ACCESS2.[Access Device_Type] as 'Type of Line'
,ACCESS2.[Access Device_Insertion Date] as 'Insertion Date'
,ACCESS2.[Access Device_Removal Date] as 'Removal Date'
,ACCESS2.PtInfusionStatusKey
,DATEDIFF(d,
CASE WHEN [Access Device_Insertion Date] >= @start_date AND [Access Device_Insertion Date] >=ACCESS2.StartDate THEN ACCESS2.[Access Device_Insertion Date]
WHEN ACCESS2.StartDate >= ACCESS2.[Access Device_Insertion Date] AND ACCESS2.StartDate >= @start_date THEN ACCESS2.StartDate
ELSE @start_date END,
CASE WHEN @end_date <= ISNULL(ACCESS2.EndDate, @end_date) AND @end_date <= ISNULL(ACCESS2.[Access Device_Removal Date], @end_date) THEN @end_date
WHEN ACCESS2.EndDate IS NOT NULL AND ACCESS2.EndDate < @end_date AND ACCESS2.EndDate <= ISNULL(ACCESS2.[Access Device_Removal Date], ACCESS2.EndDate) THEN ACCESS2.EndDate
ELSE ACCESS2.[Access Device_Removal Date] END) + 1 AS '# of Cath Days'
FROM ACCESS2
WHERE
ACCESS2.StartDate <= @end_date
AND (ACCESS2.EndDate >= @start_date OR ACCESS2.EndDate IS NULL)
AND ACCESS2.[Access Device_Insertion Date] <= @end_date
AND (ACCESS2.[Access Device_Removal Date] >= @start_date OR ACCESS2.[Access Device_Removal Date] IS NULL)
-
It's somewhat difficult to understand exactly what is going on here, because you've posted a fairly large chunk of code, most of which is not relevant to the problem you are having.
If you are able to provide the following, I think things will be clearer for other people reading this:
a) Sample data, in the form of CREATE TABLE and INSERT statements. Ideally, simplify your table structure so that non-relevant columns are excluded.
b) Desired results, based on the sample data. This could be as simple as a screenshot from Excel.
Armed with this and the description from your initial post, we'll have more of a chance of solving this for you.
This link expands on what I have written above.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
What Phil said, though it seems that this question has surfaced in many places with many suggestions being guessed. I'd guess that date overlaps are an issue, changing logic around the +1 of your guess.
-
I'm looking at a part of what you posted..
PatID StartDate EndDate Type of Line Insert Dt Remv Dt Pt Status # Cath Days Cath Days for Feb
10215 2016-09-30 2022-02-21 Power Port 2016-11-22 NULL Active 21 30 (Should be 28)
10215 2022-02-21 2022-02-22 Power Port 2016-11-22 NULL Hospitalized 2
10215 2022-02-22 NULL Power Port 2016-11-22 NULL Active 7... and I don't know how you came up with any of the values. For example, on the first row, it says that the catheter "Insert Dt" is 2016-11-22 and the start date was almost 2 months before that. The end date is 21 days into Feb 21. How do you come up with only 21 "# Cath Days" and why are you saying "Cath Days for Feb" should be 28 when the end date was on Feb 21st?
--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) -
The StartDate EndDate overlaps and possible insert/remove multiples make this data fun. I'm familiar with medical data cauti/clabsi counting, and this specific example is looking for February. The patient (10215) is in the system from 2016-09-30, and has not left the system (since the last EndDate is null). Catheter inserted in the first record on 2016-11-22, but has not been removed since all Remv Dt are null. Basically, this specific example just counts the number of days in February, though reality gets trickier if the dates have gaps or the catheter goes in and out multiple times across the records.
-
Jeff, to answer you're question, the StartDate (and the EndDate) can be one of 3 dates, determined by the datediff code earlier in the query (see query snip below). StartDate/EndDate are the therapy dates, Insertion/Removal are the catheter dates, and @start_date/@end_date are the parameter dates for the whole query (usually one full month of data).
I have figured out how to do a conditional count of the therapy StartDate and EndDate, using the over/partition and lag functions. However, that does not account for the other possibilities with start and end dates based off of that datediff code. I will cover that in the next post.
Datediff(d, CASE WHEN >= @start_date AND
>=access2.startdate THEN
access2. WHEN access2.startdate >= --ins. dt. later than @month start and in month = INS DATE
access2. AND
access2.startdate >= @start_date THEN access2.startdate ELSE @start_date -- stat. start later than ins dt and in month = stat. start if not then Month Start
END,
CASE WHEN @end_date <= Isnull(access2.enddate, @end_date) AND @end_date
<= Isnull(access2., @end_date) THEN @end_date -- End month earlier than stat end and also less than remove dt = Month End
WHEN access2.enddate IS NOT NULL AND access2.enddate < @end_date AND -- stat end earlier than end month and also earlier than remove date = stat end if not then remove date +1
access2.enddate <= Isnull(access2.,
access2.enddate) THEN access2.enddate ELSE
access2. END) + 1 AS '# of Cath Days' -
DateDiff code didn't paste properly in previous post. Here is the correct code:
-
So the lag function that is helping to shave off a day from the count of days to avoid double-counting works when StartDate is used as the beginning date in the datediff calculation. But when insertion date or @start_date are being used that function won't work. I need to come up with something that evaluates all 3 scenarios for a start and end date and applies the count adjustment accordingly. Entire query is attached including dataset.
Any help or ideas would be much appreciated.
-
You appear to be mixing data types in the dates of the txt file. StartDate and EndDate are DATE, but Insertion_Date and Removal_Date are VARCHAR(23) that look like datetime, so those won't compare well without some conversions. It's hard to tell without underlying data or structures. Also, the two data examples are quite different, so any representative data or rules would be helpful.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply