Result:
This is to figure out the total number of doses within a time spam given to a client as dose are being added and falls off. In some cases, new start dates, and end date may be created. as explained for patid=147
The logic is if e,g 01/01/2024 to 01/07/2024 a patient receives 25mg dose and he receives an additional 30 mg dose from 01/04/20204 -01/17/2024. That means an accurate count with new assigned time frame will be 01/01/2024-01/03/2024=25 mg 01/04/2024 -01/07/2024 = 55mg, Then from 01/08/2024-01/17/2024 =30mg
drop table #prescribed_dose
drop table #prescribed_dose_Result
CREATE TABLE #prescribed_dose (
[patid] [varchar](20) NULL
,[Generic_Name] [varchar](256) NULL
,[Route] [varchar](50) NULL
,[s_date] DATE
,[e_date] DATE
,[Ordered_Dose] [numeric](15, 2) NULL
,[cadance_value] int--if not null
--,[ext_date] DATE--end date plus candance value
)
INSERT INTO #prescribed_dose
VALUES
('125','Haloperidol Decanoate','R','2016-07-21','2016-07-25',100.00,14)
,('125','Paliperidone Palmitate','O','2016-07-21','2016-07-22',234.00,null)
,('125','risperiDONE Consta','R','2016-01-27','2016-03-01',12.50,14)
,('125','risperiDONE Consta','0','2016-02-03' ,'2016-02-04',25.00,null)
,('125','risperiDONE Consta','R','2016-02-17','2016-03-01' ,25.00,14)
,('125','risperiDONE Consta','R','2016-07-27','2016-08-09',25.00,null)
,('141','Haloperidol Decanoate','R','2016-05-14','2016-07-26',250.00,30)
,('141','Haloperidol Decanoate','R','2016-08-05','2016-08-10',100.00,14)
,('141','Paliperidone Palmitate','O','2016-08-05','2016-08-06',234.00,null)
,('141','risperiDONE Consta','R','2016-05-25','2016-07-26' ,50.00,14)
,('141','risperiDONE Consta','R','2016-07-27','2016-08-10',25.00,21)
,('147','Paliperidone Palmitate','R','2019-10-03','2020-10-02',234.00,30)
,('147','risperiDONE Consta','R','2019-10-15','2019-10-15',12.50,14)
,('147','risperiDONE Consta','R','2019-10-15' ,'2020-10-14',12.50,14)
,('147','risperiDONE Consta','R','2019-10-15','2020-10-14' ,12.50,14)
,('147','risperiDONE Consta','R','2019-11-04','2020-11-03',25.00,14)
------desired result
CREATE TABLE #prescribed_dose_Result (
[patid] [varchar](20) NULL
,[Generic_Name] [varchar](256) NULL
,[Route] [varchar](50) NULL
,[s_date] DATE
,[e_date] DATE
,[Ordered_Dose] [numeric](15, 2) NULL----Total Dose - if multiple orders for same drug overlap, add them together
,[cadance_value] int--if not null
--,[ext_date] DATE--end date plus candance value
)
INSERT INTO #prescribed_dose_Result
VALUES
('125','Haloperidol Decanoate','R','2016-07-21','2016-07-25',100.00,14)
,('125','Paliperidone Palmitate','O','2016-07-21','2016-07-22',234.00,null)
,('125','risperiDONE Consta','R','2016-01-27','2016-02-16',12.50,14)
,('125','risperiDONE Consta','R','2016-02-17' ,'2016-03-01',37.50,null)
----- 37.50 includes the order for 12.5 MG that begins on 1/27 and ends on 3/1
--and the order for 25 MG that begins on 2/17 and ends on 3/1.
,('125','risperiDONE Consta','R','2016-07-27','2016-08-09' ,25.00,14)
,('125','risperiDONE Consta','O','2016-02-03','2016-02-04',25.00,null)
,('141','Haloperidol Decanoate','R','2016-05-14','2016-07-26',250.00,30)
--This is another example of separate orders sharing a parent,
--in this case there is a gap between the end and start of the next order,|
--but the next order begins within the frequency cadence of the first record,|
--so it is correct that there is only one parent order but there will be two different dose records.|
,('141','Haloperidol Decanoate','R','2016-08-05','2016-08-10',100.00,14)
,('141','Paliperidone Palmitate','O','2016-08-05','2016-08-06',234.00,null)
,('141','risperiDONE Consta','R','2016-05-25','2016-07-26' ,50.00,14)
,('141','risperiDONE Consta','R','2016-07-27','2016-08-10',25.00,21)
,('147','Paliperidone Palmitate','R','2019-10-03','2020-10-02',234.00,30)
,('147','risperiDONE Consta','R','2019-10-15','2019-10-15',37.50,14)
---There are 3 orders for 12.5 MG dose active on 10/15.
--One is only for 10/15 the other two continue until 10/14/2020.
--Another order for 25 mg begins on 11/4/2019 and continues until 11/3/2020.
,('147','risperiDONE Consta','R','2019-10-16' ,'2019-11-03',25.00,14)
,('147','risperiDONE Consta','R','2019-11-04','2020-10-14' ,50.00,14)
,('147','risperiDONE Consta','R','2020-10-15','2020-11-03',25.00,14)
select * from #prescribed_dose
order by patid,Generic_Name,S_Date,Route
select * from #prescribed_dose_Result
order by patid,Generic_Name,S_Date,Route