SQL/SSRS- Grouping and Counting # of Days w/o Overlapping Days

SSAS and SSRS
1

I am trying to group a total count of days within a month that a patient had a catheter line inserted. The data is broken down into stints so is not contiguous throughout the month. I also do not to count overlapping days between the stints.
I tried grouping by patient and adding a sum of days at the patient group level in SQL Reporting Services, but could not get around the overlapping days so the counts are all wrong.
See screenshot and query below.

image
image696×298 8.88 KB 

DECLARE @start_date DATETIME
DECLARE @end_date DATETIME

SET @start_date = '2/1/2022'
SET @end_date = '2/28/2022';

CREATE TABLE mytable(
   Patient_ID     INTEGER  NOT NULL PRIMARY KEY 
  ,startdate      DATE  NOT NULL
  ,enddate        DATE  NOT NULL
  ,Type_of_Line   VARCHAR(4) NOT NULL
  ,Insertion_Date DATE  NOT NULL
  ,Removal_Date   DATE 
  ,_of_Cath_Days  INTEGER  NOT NULL
);
INSERT INTO mytable(Patient_ID,startdate,enddate,Type_of_Line,Insertion_Date,Removal_Date,_of_Cath_Days) VALUES (10247,'2022-01-16','2022-02-11','Port','2021-08-03 00:00:00.000',NULL,11);
INSERT INTO mytable(Patient_ID,startdate,enddate,Type_of_Line,Insertion_Date,Removal_Date,_of_Cath_Days) VALUES (10247,'2022-02-11','2022-02-15','Port','2021-08-03 00:00:00.000',NULL,5);
INSERT INTO mytable(Patient_ID,startdate,enddate,Type_of_Line,Insertion_Date,Removal_Date,_of_Cath_Days) VALUES (10247,'2022-02-15','2022-02-24','Port','2021-08-03 00:00:00.000',NULL,10);
INSERT INTO mytable(Patient_ID,startdate,enddate,Type_of_Line,Insertion_Date,Removal_Date,_of_Cath_Days) VALUES (10247,'2022-02-24','2022-03-23','Port','2021-08-03 00:00:00.000',NULL,5);


WITH stat
     AS (SELECT pt.ptkey,
                ptid,
                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 )),

     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
                LEFT JOIN pharmacyeventandoutcomelist l WITH (nolock)
                       ON l.pharmacyeventandoutcomelistkey =
                          t.pharmacyeventandoutcomelistkey
         WHERE  l.pharmacyeventandoutcomelistkey = 2),
     
	 access2
     AS (SELECT stat.ptkey,
                stat.ptid,
                stat.startdate,
                stat.enddate,
                Isnull(devicetype.NAME, '') [Access Device_Type],
                ppad.insertiondate          [Access Device_Insertion Date],
                ppad.removaldate            [Access Device_Removal Date]
         FROM   stat WITH (nolock)
                JOIN pharmacyptaccessdevice ppad WITH(nolock)
                  ON ppad.ptkey = stat.ptkey
                          LEFT JOIN access1 devicetype WITH (nolock)
                       ON devicetype.pharmacyeventandoutcometypedetailkey =
                          ppad.accessdevicetypekey
                          AND devicetype.pharmacyeventandoutcometypekey = 4)
       
	   

--***MAIN QUERY***
SELECT access2.[ptid] AS 'Patient ID',
       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',
       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 )
       AND access2.ptid = '10247'
2 

;WITH prelim AS (
    SELECT *, LAG(enddate, 1) OVER(PARTITION BY Patient_ID, Type_of_Line ORDER BY startdate) AS previous_enddate
    FROM mytable
)
SELECT 
    Patient_ID, MIN(startdate) AS Minstartdate, MAX(enddate) AS Maxenddate, 
    Type_of_Line, Insertion_Date, MAX(Removal_Date) AS Removal_Date,
    SUM(#_of_Cath_Days - CASE WHEN startdate = previous_enddate THEN 1 ELSE 0 END) AS #_of_Cath_Days
FROM prelim
GROUP BY Patient_ID, Type_of_Line, Insertion_Date
1 Like
3

Thank you Scott!

4

You're welcome!

5

Scott,

Your suggestion of using the over/partition and lag functionsI helped me to do a conditional count of the therapy StartDate and EndDate, so thanks again. However, in addition to the StartDate
(and the EndDate), there is a possibility of 2 other dates that can be used as the official 'StartDate' of the days calculation. These dates are determined by the datediff code earlier in the query (see query snip below). StartDate/EndDate are actually 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).

DateDiff code that determines what the Start Date of the # Days calculation is:
in the query below.

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.

DECLARE @start_date DATETIME
DECLARE @end_date DATETIME

SET @start_date = '3/1/2022'
SET @end_date = '3/31/2022';

CREATE TABLE mytable(
   Patient_ID         INTEGER  NOT NULL PRIMARY KEY 
  ,StartDate          DATE  NOT NULL
  ,EndDate            DATE 
  ,Type_of_Line       VARCHAR(9) NOT NULL
  ,Insertion_Date     VARCHAR(23) NOT NULL
  ,Removal_Date       VARCHAR(23)
  ,Total_of_Cath_Days INTEGER  NOT NULL
);
INSERT INTO mytable(Patient_ID,StartDate,EndDate,Type_of_Line,Insertion_Date,Removal_Date,Total_of_Cath_Days) VALUES (10063,'2021-12-22','2022-03-10','PowerLine','2022-01-24 00:00:00.000','2022-03-18 00:00:00.000',10);
INSERT INTO mytable(Patient_ID,StartDate,EndDate,Type_of_Line,Insertion_Date,Removal_Date,Total_of_Cath_Days) VALUES (10063,'2022-03-10','2022-03-23','PowerLine','2022-01-24 00:00:00.000','2022-03-18 00:00:00.000',8);
INSERT INTO mytable(Patient_ID,StartDate,EndDate,Type_of_Line,Insertion_Date,Removal_Date,Total_of_Cath_Days) VALUES (10063,'2022-03-10','2022-03-23','PowerLine','2022-03-22 00:00:00.000',NULL,2);
INSERT INTO mytable(Patient_ID,StartDate,EndDate,Type_of_Line,Insertion_Date,Removal_Date,Total_of_Cath_Days) VALUES (10063,'2022-03-23',NULL,'PowerLine','2022-03-22 00:00:00.000',NULL,9);
;

WITH stat
     AS (SELECT pt.ptkey,
                ptid,
				person.LastName,
				person.FirstName,
				ptptinfusionstatus.startdate as 'SOC Date',
                ptptinfusionstatus.startdate,
                ptptinfusionstatus.enddate
                
         FROM   pt
                LEFT JOIN ptptinfusionstatus
                       ON ptptinfusionstatus.ptkey = pt.ptkey
                LEFT JOIN ptinfusionstatus
                       ON ptinfusionstatus.ptinfusionstatuskey =
                          ptptinfusionstatus.ptinfusionstatuskey
				JOIN Person on person.PersonKey = pt.PersonKey
                
         WHERE  ptptinfusionstatus.ptinfusionstatuskey IN ( 1, 5 )),

     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
                LEFT JOIN pharmacyeventandoutcomelist l WITH (nolock)
                       ON l.pharmacyeventandoutcomelistkey =
                          t.pharmacyeventandoutcomelistkey
         WHERE  l.pharmacyeventandoutcomelistkey = 2),
     
	 access2
     AS (SELECT stat.ptkey,
                stat.ptid,
                stat.startdate,
                stat.enddate,
				Isnull(devicetype.NAME, '') [Access Device_Type],
                ppad.insertiondate          [Access Device_Insertion Date],
                ppad.removaldate            [Access Device_Removal Date]
         FROM   stat WITH (nolock)
                JOIN pharmacyptaccessdevice ppad WITH(nolock)
                  ON ppad.ptkey = stat.ptkey
                          LEFT JOIN access1 devicetype WITH (nolock)
                       ON devicetype.pharmacyeventandoutcometypedetailkey =
                          ppad.accessdevicetypekey
                          AND devicetype.pharmacyeventandoutcometypekey = 4)
,       
ACCESS3 AS	   
(
SELECT access2.[ptid] AS 'Patient ID',
       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',
       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 >= --ins. dt. later than @month start and in month = INS DATE 
       access2.[access device_insertion date] 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.[access device_removal date], @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.[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 )
       AND access2.ptid = '10063' 
)
,

prelim AS (
    SELECT *, LAG(enddate, 1) OVER(PARTITION BY [Patient ID], [Type of Line] ORDER BY startdate) AS previous_enddate
	FROM ACCESS3
)


--***MAIN QUERY***

SELECT 
    [Patient ID],  
    prelim.[StartDate],
	prelim.[EndDate],
	[Type of Line], 
   	[Insertion Date],
	[Removal Date]
	,SUM([# of Cath Days] - CASE WHEN startdate = previous_enddate THEN 1 ELSE 0 END) AS 'Total #_of_Cath_Days'

FROM prelim

GROUP BY [Patient ID], [Type of Line], [Insertion Date], [Removal Date]
,prelim.[StartDate],prelim.[EndDate]--,  prelim.previous_enddate
6

If that query gives you what you need, and the performance you need, then use it.