alculate the total daily medication dosage for each patient between specified start and end dates to generate a daily report.

DreSQL · February 22, 2025, 11:17am

			CREATE TABLE [stage].[stage_routine_medications_Test](
			[patid] [varchar](20) NULL,
			[dim_generic_with_repor_key] [int] NULL,
			[dim_routine_meds_presc_key] [int] NOT NULL,
			[Order_Start_Date] [date] NULL,
			[Order_End_Date] [date] NULL,
			[Total_Dose_First][numeric](15, 2) NULL,--First_Day_Dose_Count * Ordered_Dose
			[First_Day_Dose_count] [int] NOT NULL,
			[Ordered_Dose] [numeric](15, 2) NULL,
			[Last_Day_Dose_Count] [int] NOT NULL,
			[Total_Dose_Last][numeric](15, 2) NULL,---Last_Day_Dose_Count * Ordered_Dose 
			[Ordered_Dose_Unit] [varchar](50) NULL,
			[Doses_per_day] [int] NULL,
			[TOTAL_DOSE] [numeric](15, 2) NULL,---Doses_per_day*Ordered_Dose
			[Order_Category_code] [varchar](50) NULL,

			)

 INPUT DATA:
 
 |dim_generic_with_repor_key|patid|dim_routine_meds_presc_key|Order_Start_Date|Order_End_Date|Total_Dose_First|First_Day_Dose_count|Ordered_Dose|Last_Day_Dose_Count|Total_Dose_Last|Doses_per_day|TOTAL_DOSE|Ordered_Dose_Unit|
 |25|103|11|'5/26/2016'|'6/28/2016'|125|5|25  |5|125|12|300 |'MG'|
 |25|103|11|'5/26/2016'|'6/28/2016'|250|5|50  |5|250|12|600 |'mg'|
 |25|103|11|'5/26/2016'|'6/28/2016'|500|5|100 |5|500|12|1200|'mg'|
 |25|103|11|'6/28/2016'|'7/28/2016'|300|3|100 |2|200|4 |400 |'mg'|
 |25|103|11|'7/28/2016'|'9/16/2016'|25 |2|12.5|2|25 |4 |50  |'MG'|
 |25|103|11|'7/28/2016'|'9/16/2016'|50 |2|25  |2|50 |4 |100 |'mg'|
 |25|103|11|'7/28/2016'|'9/16/2016'|100|2|50  |2|100|4 |200 |'mg'|
 |25|103|11|'7/28/2016'|'9/16/2016'|200|2|100 |2|200|4 |400 |'mg'|
 |25|103|11|'9/16/2016'|'9/17/2016'|150|6|25  |6|150|12|300 |'MG'|
 |25|103|11|'9/17/2016'|'9/18/2016'|300|6|50  |6|300|12|600 |'mg'|
 |25|103|11|'9/18/2016'|'9/19/2016'|600|6|100 |6|600|12|1200|'mg'|
 |25|103|11|'9/19/2016'|'1/9/2017' |150|6|25  |7|175|12|300 |'MG'|
 |25|103|11|'9/19/2016'|'1/9/2017' |300|6|50  |7|350|12|600 |'mg'|
 |25|103|11|'9/19/2016'|'1/9/2017' |600|6|100 |7|700|12|1200|'mg'|

Tested Query:

		select 
        patid,
		dim_routine_meds_presc_key,
		dim_generic_with_repor_key,
		Order_Start_Date,
		Order_End_Date,
	    LAG(Order_Start_Date,1) OVER (partition by  [patid],dim_generic_with_repor_key ORDER BY Order_Start_Date, Order_End_Date) AS PreviousStartDate
       , LAG(Order_End_Date,1) OVER (partition by  [patid],dim_generic_with_repor_key ORDER BY Order_Start_Date, Order_End_Date) AS PreviousEndDate
		,Total_Dose_First,
		Total_Dose_Last,
		LAG(Total_Dose_Last,1) OVER (partition by [patid],dim_generic_with_repor_key ORDER BY Order_Start_Date, Order_End_Date) AS PreviousLastDose,
		TOTAL_DOSE
	   , sum(Total_Dose_First) over (partition by patid, dim_routine_meds_presc_key, dim_generic_with_repor_key, order_start_date, order_end_date) as Total_First
       , sum(Total_Dose_Last) over (partition by patid, dim_routine_meds_presc_key, dim_generic_with_repor_key, order_start_date, order_end_date) as Total_Last
	   , sum(TOTAL_DOSE) over (partition by patid, dim_routine_meds_presc_key, dim_generic_with_repor_key, order_start_date, order_end_date) as Total_Dose
FROM [OSH_Repository].[stage].[stage_routine_medications_Test]

EXPECTED RESULT:
dim_routine_meds_presc_key	Order_Start_Date	Order_End_Date	TOTAL_DOSE	Total Dose Unit	Discharge Medication indicator
11	                        5/26/2016	            5/26/2016	875	        MG	
11	                        5/27/2016	            6/27/2016	2100	    MG	
11	                        6/28/2016	            6/28/2016	1175	    MG	
11	                        6/29/2016	            7/27/2016	400	        MG	
11	                        7/28/2016	            7/28/2016	575	        MG	
11	                        7/29/2016	            9/15/2016	750	        MG	
11	                        9/16/2016	            9/16/2016	525	        MG	
11	                        9/17/2016	            9/17/2016	450	        MG	
11	                        9/18/2016	            9/18/2016	900	        MG	
11	                        9/19/2016	            9/19/2016	1650	    MG	
11	                        9/20/2016	            1/8/2017	2100	    MG	
11	                        1/9/2017	                   1/9/2017	1225	    MG

khtan · February 24, 2025, 6:55am

please explain the necessary logic required to obtain the expected result

DreSQL · February 24, 2025, 8:42am

The way the logic works for start date =5/26/2016 total record=875(sum Total_Dose_First) since that was the first day of the record that will be the total dose but the end date =6/28/2016(which is the start date of the next group of record but with an end date of 7/28/2016. The total dose for the date 6/28/2016=(875(sum Total_Dose_Last+300(sum Total_Dose_First) next group of record=1175 but with an additional row created in between the start date and end date i,e 5/27/2016 to 6/27/2016 (sum TOTAL_DOSE=300+600+1200)=2100

khtan · February 25, 2025, 1:46am

there are 3 rows with Order_Start_Date = 5/26/2016, how do you determine the ordering or sequencing of these 3 rows ?

 |25|103|11|'5/26/2016'|'6/28/2016'|125|5|25  |5|125|12|300 |'MG'|
 |25|103|11|'5/26/2016'|'6/28/2016'|250|5|50  |5|250|12|600 |'mg'|
 |25|103|11|'5/26/2016'|'6/28/2016'|500|5|100 |5|500|12|1200|'mg'|

Blockquote

DreSQL · February 25, 2025, 2:06am

for this date='5/26/2016'. The ordering does not matter as long as we getting the total number of dose at the end date of the day=125+250+500=875

khtan · February 25, 2025, 3:03am

the description is rather hard to follow. Can you rephrase it and explain how do you get the TOTAL_DOSE for first 4 rows ?

Row_No Order_Start_Date	Order_End_Date	TOTAL_DOSE	
1      5/26/2016	        5/26/2016	     875	        
2      5/27/2016	        6/27/2016	    2100
3      6/28/2016	        6/28/2016	    1175
4      6/29/2016	        7/27/2016	     400

So far I understand that Row 1 875 is from 125 + 250 + 500 SUM (Total_Dose_First) with Order_Start_Date = 5/26/2016. But why Row 4 is not 300 ?

And the rest of the rows, I don't really follow your logic.

DreSQL · February 25, 2025, 6:00am

There is a field called
[TOTAL_DOSE] [numeric](15, 2) NULL,---Doses_per_day*Ordered_Dose
. That field is one of the requirements assigned to the created rows between start date and end date. The same result for row 2 produces the same result for row 4 [TOTAL_DOSE]=Doses_per_day*Ordered_Dose`=(4x100)=400 i.e. in between start date and end date of '6/28/2016'|'7/28/2016'. The dose of 300 has been accounted for on the 3rd row as I
explained to account for the day '6/28/2016 as Total_Dose_First=300 +sum of(Total_Dose_Last) 125+250+500)=1175 but for the 4th row= 400 because its will be creating a new start date and end date pair of (6/29/2016-/27/2016) which is between '6/28/2016'|'7/28/2016'. Now for the 5th row of (7/28/2016 to 7/28/2016) which is to account for the day 7/28/2016 its going to be the sum of (Total_Dose_Last=200 +sum of(Total_Dose_First) 25+50+100+200)=575 .Is it clear now?

khtan · February 25, 2025, 11:00am

First, you summarize it by start and end date (cte)

Then, you split it by first, in between and last (cross apply)

And finally, you sum and group by start and end date

; with
doses as
(	
	select	dim_routine_meds_presc_key, Order_Start_Date, Order_End_Date, 
		    Total_Dose_First = sum(Total_Dose_First),
		    Total_Dose       = sum(TOTAL_DOSE),
		    Total_Dose_Last  = sum(Total_Dose_Last)
	from	[stage_routine_medications_Test]
	group by dim_routine_meds_presc_key, Order_Start_Date, Order_End_Date
)
select	d.dim_routine_meds_presc_key, t.Order_Start_Date, t.Order_End_Date, Total_Dose = sum(t.Total_Dose)
from	doses d
	    cross apply
	    (
		  select  Order_Start_Date = d.Order_Start_Date, 
			      Order_End_Date	 = d.Order_Start_Date, 
			      Total_Dose	 = d.Total_Dose_First

          union all

		select Order_Start_Date = dateadd(day, 1, d.Order_Start_Date), 
			   Order_End_Date   = dateadd(day, -1, d.Order_End_Date), 
			   Total_Dose	 = d.Total_Dose
		where d.Order_Start_Date	< d.Order_End_Date

		union all

		select	Order_Start_Date = d.Order_End_Date, 
			Order_End_Date	 = d.Order_End_Date, 
			Total_Dose	 = d.Total_Dose_Last
	) t
group by d.dim_routine_meds_presc_key, t.Order_Start_Date, t.Order_End_Date
order by t.Order_Start_Date, t.Order_End_Date

db<>fiddle demo

DreSQL · February 25, 2025, 6:43pm

Thanks a lot, your solution really helps but there are 3 additional records to your solution that shouldn't be included when compared to the expected result, and they are shown below
dim_routine_meds_presc_key Order_Start_Date Order_End_Date Total_Dose
11 2016-09-17 2016-09-16 300.00
11 2016-09-18 2016-09-17 600.00
11 2016-09-19 2016-09-18 1200.00
In all 3 cases I also observed that the start date > the end date.

DreSQL · February 25, 2025, 9:41pm

I added the following filter where t.Order_End_Date>=t.Order_Start_Date and it gave me the result, but I will have to test it with other data to see if its consistent.