SQL Conditional sum

Question

Thank you for your suggestion. I have incorporated your suggestion into a more streamlined query below. The results of this query are here:

PtID Sts StartDate EndDate DaysCount 10247 1 2022-02-01 2022-02-11 11 10247 5 2022-02-11 2022-02-15 5 10247 1 2022-02-15 2022-02-24 10 10247 5 2022-02-24 2022-02-28 5

When the sum of the DaysCount column is totaled at the Patient ID group level in SQL Server Reporting Services, it totals to 31 days for this patient. This is not correct. If the previous EndDate is the same as the next start date, it is counting an extra day for each time the patient had a new start date (which I want to keep so that each day is counted after the datediff calculation). What I want to do is sum all 4 of these rows by DaysCount, but NOT count that extra day for the 3 rows that come after the 1st StartDate of 2/1/2022. So the total in the above results set should be 28 instead of 31. If I can do it SQL or SSRS, either way that is the result set I want. Thank you again for your help!

    CREATE TABLE mytable(
   PtID                INTEGER  NOT NULL PRIMARY KEY 
  ,PtInfusionStatusKey INTEGER  NOT NULL
  ,StartDate           DATE  NOT NULL
  ,EndDate             DATE  NOT NULL
  ,DaysCount           INTEGER  NOT NULL
);
INSERT INTO mytable(PtID,PtInfusionStatusKey,StartDate,EndDate,DaysCount) VALUES (10247,2022-01-16,'2022-02-11','27',NULL);
INSERT INTO mytable(PtID,PtInfusionStatusKey,StartDate,EndDate,DaysCount) VALUES (10247,2022-02-11,'2022-02-15','5',NULL);
INSERT INTO mytable(PtID,PtInfusionStatusKey,StartDate,EndDate,DaysCount) VALUES (10247,2022-02-15,'2022-02-24','10',NULL);
INSERT INTO mytable(PtID,PtInfusionStatusKey,StartDate,EndDate,DaysCount) VALUES (10247,2022-02-24,'2022-03-23','28',NULL);

WITH seq(n) AS 
(
  SELECT 0 UNION ALL SELECT n + 1 FROM seq
  WHERE n < DATEDIFF(DAY, convert(date,'1/1/2004'), convert(date, '1/1/2054'))
),
d(d) AS 
(
  SELECT DATEADD(DAY, n, convert(date,'1/1/2004')) FROM seq
),
src AS
(
  SELECT
    TheDate         = CONVERT(date, d),
    TheDay          = DATEPART(DAY,       d),
    TheDayName      = DATENAME(WEEKDAY,   d),
    TheWeek         = DATEPART(WEEK,      d),
    TheISOWeek      = DATEPART(ISO_WEEK,  d),
    TheDayOfWeek    = DATEPART(WEEKDAY,   d),
    TheMonth        = DATEPART(MONTH,     d),
    TheMonthName    = DATENAME(MONTH,     d),
    TheQuarter      = DATEPART(Quarter,   d),
    TheYear         = DATEPART(YEAR,      d),
    TheFirstOfMonth = DATEFROMPARTS(YEAR(d), MONTH(d), 1),
    TheLastOfYear   = DATEFROMPARTS(YEAR(d), 12, 31),
    TheDayOfYear    = DATEPART(DAYOFYEAR, d)
  FROM d
)
,

STAT AS
(
select
Pt.PtKey, ptid, PtPtInfusionStatus.ModifiedDate, PtInfusionStatus.Name, Pt.PersonKey, PtInfusionStatus.PtInfusionStatusKey, PtPtInfusionStatus.StartDate, PtPtInfusionStatus.EndDate
--,ROW_NUMBER() OVER(PARTITION BY pt.ptkey ORDER BY PtPtInfusionStatus.ModifiedDate asc) AS 'PtStatusRank'

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

WHERE 1=1
AND STAT.PtInfusionStatusKey in (1,5) 
)
,

DATEPAT as
(
SELECT pat.PtID
,pat.PtInfusionStatusKey
, COUNT(DISTINCT src.TheDate) AS DaysCount
,pat.StartDate
,pat.EndDate
    FROM PAT
    left JOIN src on src.TheDate BETWEEN pat.StartDate AND pat.EndDate
    GROUP BY pat.PtID, pat.StartDate, pat.EndDate, pat.PtInfusionStatusKey
)




--***MAIN QUERY***

SELECT 
DATEPAT.PtID
,DATEPAT.PtInfusionStatusKey
,DATEPAT.StartDate
,DATEPAT.EndDate
,DATEPAT.DaysCount
    
FROM DATEPAT 
WHERE datepat.ptid = 10247
and DATEPAT.StartDate >= convert(date,'1/16/2022') and DATEPAT.EndDate <= convert(date,'3/23/2022')


  ORDER BY PtID
  OPTION (MAXRECURSION 0);

Answer 1

There may be more elegant ways of doing this but I would tackle this with a date table. If you don't have one there are plenty of examples of how to generate one in a few seconds.

Once you have your date table, you could refactor the query to something simple like this... (Note: this is a really cut down example for illustration but you should get the idea)

SELECT p.PatID, COUNT(DISTINCT dt.TheDate) AS DaysCount
    FROM myPatientRecords p 
    JOIN myDateTable dt on dt.TheDate BETWEEN p.StartDate AND p.EndDate
    GROUP BY p.PatID

By using a DISTINCT COUNT, we don't need to worry about the overlapping dates