SQL Conditional Sum

[agorjest1]

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




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)