Combine records based on date in between date columns as a continuous single record

Transact-SQL
1

1

-I am expecting a compression/combination of records to be considered as a continuously record. The goal is to figure out how long a patient was on the same drug/order type. considering continuous orders in the sense, that for the same generic_name and route type with the candence = nulll it retains the same value but if for the same type of generic_name and route and cadence is not null in one of the records that means the drug has been extended for the number of days = Cadence_value from the end date of the prior order

for the example of patid=135 , Cadence_value is not null and equals to 30 the e_date (extended date) will be 08/04/2017=(2017-07-05 plus 30 days).All records with the same generic and type ='R' will collapse/fold up into a single record as long as they have a s_date <= extended_date (08/04/2017) .If the s_date extends beyound the highest cadence_value even with the same generi_name and type, that will fall under be a different group.

            drop table #prescribed_data
            drop table #prescribed_data_Result

            CREATE TABLE #prescribed_data (
            [patid] [varchar](20) NULL
            ,[Generic_Name] [varchar](256) NULL
            ,[Route] [varchar](50) NULL
            ,[Route_value] [varchar](50) NULL
            ,[s_date] DATE
            ,[e_date] DATE
            ,[cadance_value]  int--if not null
            ,[ext_date] DATE--end date plus candance value


            )

            INSERT INTO #prescribed_data
            VALUES
            ('135','Haloperidol Decanoate','R','IM','2017-04-05','2017-07-05',14,'2017-07-19')
            ,('135','Paliperidone Palmitate','O','IM','2017-03-29','2017-03-29',null,'')
            ,('135','Paliperidone Palmitate','R','IM','2017-02-16','2017-07-05',null,'')
            ,('135','Paliperidone Palmitate','R','IM','2017-04-07','2017-07-05',30,'2017-08-04')---as long as start date is within the extended date 
            ,('135','Paliperidone Palmitate','R','IM','2017-07-20','2017-07-21',null,'')
            ,('135','Paliperidone Palmitate','R','IM','2017-07-27','2018-07-27',null,'')
            ,('135','risperiDONE Consta','R','IM','2017-04-05','2017-07-05',null,'')

            ,('106','risperiDONE Consta','IM','R','2017-01-12','2017-01-31' ,NULL,'')
            ,('106','risperiDONE Consta','IM','R','2017-02-01','2017-04-13',NULL,'')
            ,('106','risperiDONE Consta','IM','R','2017-04-13','2018-04-13' ,NULL,'')
            ,('106','risperiDONE Consta','IM','R','2018-04-13','2018-07-18',NULL,'')
            ,('106','risperiDONE Consta','IM','R','2018-07-18','2019-07-18' ,NULL,'')
            ,('106','risperiDONE Consta','IM','R','2020-06-11','2020-06-11' ,NULL,'')
            ,('106','risperiDONE Consta','IM','R','2020-06-23','2020-08-18' ,NULL,'')
            ,('106','risperiDONE Consta','IM','R','2020-08-18','2021-08-18' ,NULL,'')
            ,('106','risperiDONE Consta','IM','R','2021-08-18', '2022-08-18',NULL,'')

            ,('123','Haloperidol Decanoate','IM','R','2016-07-22','2016-07-26',14,'2016-08-09')
            ,('123','Paliperidone Palmitate','IM','o','2016-07-22','2016-07-23',NULL,   NULL)
            ,('123','risperiDONE Consta',   'IM','o','2016-02-03','2016-02-04', NULL,NULL)
            ,('123','risperiDONE Consta',   'IM','R','2016-02-17','2016-04-04', 14,'2016-04-18')
            ,('123','risperiDONE Consta',   'IM','R','2016-07-27','2016-08-10',21,'2016-08-31') 


            ,('128','Haloperidol Decanoate','IM','R','2016-07-21','2016-07-26',14,'2016-08-09')
            ,('128','Paliperidone Palmitate','IM','o','2016-07-23','2016-07-24',NULL,NULL)
            ,('128','risperiDONE Consta','IM','R','2016-01-27','2016-04-01',14,'2016-04-15')
            ,('128','risperiDONE Consta','IM','R','2016-06-07','2016-06-07',14  ,'2016-06-21')
            ,('128','risperiDONE Consta','IM','R','2016-06-14','2016-07-18',14  ,'2016-08-01')
            ,('128','risperiDONE Consta','IM','R','2016-08-03','2016-08-09',NULL,NULL)



            ------desired result

            CREATE TABLE #prescribed_data_Result (
            [patid] [varchar](20) NULL
            ,[Generic_Name] [varchar](256) NULL
            ,[Route] [varchar](50) NULL
            ,[Route_value] [varchar](50) NULL
            ,[s_date] DATE
            ,[e_date] DATE
            ,[cadance_value]  int

            )

            INSERT INTO #prescribed_data_Result
            VALUES
            ('135','Haloperidol Decanoate','R','IM','2017-04-05','2017-07-05',14)
            ,('135','Paliperidone Palmitate','O','IM','2017-03-29','2017-03-29',null)
            ,('135','Paliperidone Palmitate','R','IM','2017-02-16','2018-07-27',30)
            ,('135','risperiDONE Consta','R','IM','2017-04-05','2017-07-05',null)

            ,('106','risperiDONE Consta','IM','R','2017-01-12','2019-07-18' ,NULL)
            ,('106','risperiDONE Consta','IM','R','2020-06-11','2020-06-11' ,NULL)
            ,('106','risperiDONE Consta','IM','R','2020-06-23','2020-08-18' ,NULL)

            
            ,('123','Haloperidol Decanoate INTRAMUSCULAR','IM','R','2016-07-22','2016-07-26',14)
            ,('123','Paliperidone Palmitate INTRAMUSCULAR','IM','o','2016-07-22','2016-07-23',NULL)
            ,('123','risperiDONE Consta INTRAMUSCULAR', 'IM','o','2016-02-03','2016-02-04', NULL)
            ,('123','risperiDONE Consta INTRAMUSCULAR', 'IM','R','2016-02-17','2016-04-04', 14)
            ,('123','risperiDONE Consta INTRAMUSCULAR', 'IM','R','2016-07-27','2016-08-10',21)  

            ,('128','Haloperidol Decanoate','IM','R','2016-07-21','2016-07-26',14)
            ,('128','Paliperidone Palmitate','IM','o','2016-07-23','2016-07-24',NULL)
            ,('128','risperiDONE Consta','IM','R','2016-01-27','2016-04-01',14)
            ,('128','risperiDONE Consta','IM','R','2016-06-07','2016-07-18',14)
            ,('128','risperiDONE Consta','IM','R','2016-08-03','2016-08-09',NULL)
        


            select * from #prescribed_data
            --order by patid,Route,Generic_Name,Route,S_Date

            select * from #prescribed_data_Result
            --order by patid,Route,Generic_Name,Route,S_Date
2

I am not sure of the rules but the following should get you started:

WITH Boundaries
AS
(
	SELECT patid, Generic_Name, [Route], Route_value, s_date, e_date, cadance_value
		,CASE
			WHEN s_date <= 
				MAX(DATEADD(d, ISNULL(cadance_value, 0) + 1, e_date))
				OVER
				(
					PARTITION BY patid, [Route], Generic_Name, Route_value
					ORDER BY s_date
					ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
				)
			THEN 0
			ELSE 1
		END AS Boundary
	FROM #prescribed_data
)
,Grps
AS
(
	SELECT patid, Generic_Name, [Route], Route_value, s_date, e_date, cadance_value
		,SUM(Boundary)
		OVER
		(
			PARTITION BY patid, [Route], Generic_Name, Route_value
			ORDER BY s_date
			ROWS UNBOUNDED PRECEDING
		) AS Grp
	FROM Boundaries
)
SELECT patid, Generic_Name, [Route], Route_value
	,MIN(s_date) AS s_date
	,MAX(e_date) AS e_date
	,MAX(cadance_value) AS cadance_value
FROM Grps
GROUP BY patid, [Route], Generic_Name, Route_value, Grp;
1 Like
3

Thank you. It worked well