Calculate total count of dose

Transact-SQL
, ,
1

Result:

image
image873×492 16.3 KB

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
2

In your description of the logic, you have give example that is 2024 but this is not present in your sample data that you have shown. You should instead reference to the sample data that you have provided and explain the necessary logic and calculation. This will help the reader to better understand your requirement. Please also ensure that the sample data and expected result does correspond to each other.

3

---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.

5

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)

	,('7','Haloperidol Decanoate','R','2016-03-12','2017-03-11',100.00,31)
	,('7','Haloperidol Decanoate','R','2016-03-14','2017-03-13',100.00,30)

	,('103','risperiDONE Consta','R','2017-01-12','2017-02-01',25.00,null)
	,('103','risperiDONE Consta','R','2017-02-01','2017-07-20',25.00,null)
	,('103','risperiDONE Consta','R','2017-07-20','2018-07-18',25.00,null)
	,('103','risperiDONE Consta','R','2018-07-18','2019-07-18',25.00,null)
	,('103','risperiDONE Consta','R','2020-08-21','2021-08-21',25.00,null)          
	,('103','risperiDONE Consta','R','2021-08-21','2022-08-21',25.00,null)


	,('121','risperiDONE Consta','R','2017-01-13','2017-01-31',25.00,null)
	,('121','risperiDONE Consta','R','2017-02-01','2017-04-11',25.00,null)
	,('121','risperiDONE Consta','R','2017-04-13','2018-04-13',25.00,null)
	,('121','risperiDONE Consta','R','2018-04-13','2018-07-18',25.00,null)
	,('121','risperiDONE Consta','R','2018-07-18','2019-07-18',25.00,null)          
	,('121','risperiDONE Consta','R','2021-09-26','2022-09-26',25.00,null)




	------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,with differnt cadence- 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)
	,('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)


	
	,('7','Haloperidol Decanoate','R','2016-03-12','2017-03-11',100.00,31)
	,('7','Haloperidol Decanoate','R','2016-03-14','2017-03-13',100.00,30)

	,('103','risperiDONE Consta','R','2017-01-12','2019-07-18',25.00,null)
	,('103','risperiDONE Consta','R','2020-08-21','2022-08-21',25.00,null)

	,('121','risperiDONE Consta','R','2017-01-13','2017-04-11',25.00,null)
	,('121','risperiDONE Consta','R','2017-04-13','2019-07-18',25.00,null)        
	,('121','risperiDONE Consta','R','2021-09-26','2022-09-26',25.00,null)





	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
6

@Ifor. Need your assistance

7

I do not see anyone helping you when even a quick look at the test data shows inconsistencies. eg How the boundary dates are dealt with for patid 125 and patid 103 are different.

The easy, if inefficient, way to deal with these sort of problems in SQL is to use a calendar table, or date range function, to expand the date ranges to one row per day and then wrap them up again with group by.

You could also look at using the windowed functions but with these it is difficult to predict all the boundary conditions and it can get complicated if, which you current test data does not show, rows still need to be added. I suspect your skill level is not really up to maintaining this in a production environment.

It may be better to use something like a C# loop on an application server.

1 Like
8

rule 1:

To calculate the amount of dose given to a client in a time span.So for Ike record 103 .we can see it was reduced to 2 records because the dose of 25 is the same from 2017-02-01 to 2019-07-18 because the start date is either equal to end date or +1.but there is a gap for the second record 2020-08-21 to 2022-08-21.

rule 2:

How to figure out the total number of doses within a time span given to a client as dose are being added and stopped. In some cases, new start dates, and end date may be created. as explained for patid=147

  • 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)

for 125:
the result with total dose of 37.5 includes the order for 12.5 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

9

What have you tried and why do you think it does not work?

10

; with dates as (
select *, s_date as dt, 1 as direction, Ordered_Dose as dose_change
from #prescribed_dose
union all
select *, dateadd(day, 1, e_date), -1, -Ordered_Dose from #prescribed_dose

)
,
nodes as (
select patid, generic_name, cadance_value, dt,
sum(direction) as lvl, sum(dose_change) as daily_dose_change

from dates
group by patid, generic_name,cadance_value, dt

)
, cumulative as (
select *,
sum(lvl) over (
partition by patid, generic_name order by dt) as total_orders,
sum(daily_dose_change) over (
partition by patid, generic_name order by dt) as total_dose,
lead(dt) over (
partition by patid, generic_name order by dt) as next_dt
from nodes
)
, final_presecribed_dose as (

select patid, generic_name,
cadance_value,
dt as s_date, dateadd(day, -1, next_dt) as e_date, total_dose, total_orders
from cumulative
where total_orders > 0)

 select

*,
--Discharge_Medication_indicator,
datediff(d,fpd.S_Date,fpd.e_date) as length_LA_treatment
FROM final_presecribed_dose fpd
where datediff(d,fpd.S_Date,fpd.e_date) <> -1
and patid=147
Group by patid,Generic_Name,cadance_value,s_date,e_date,total_dose,total_orders

11

Didn't you already got a solution over at stackoverflow ?

12

@khtan I did but it did not work for pati=103 and patid=7

13

You should leave a comment there and explain what is wrong with the result. Others will take a look at it.

14

This function calculates the total count of doses administered based on the input data provided. It iterates through the list of doses, sums them up, and returns the total count. This helps in keeping track of the number of doses given for accurate record-keeping and analysis.

15

Great post, learned a lot.

16 
Hi 

Hope this helps 

# Calculate Total Count of Dose

DROP TABLE IF EXISTS #prescribed_dose;
DROP TABLE IF EXISTS #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
);

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','O','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);

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,
    [cadance_value] int
);

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),
    ('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),
    ('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),
    ('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;