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

Transact-SQL
1 
			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
2

please explain the necessary logic required to obtain the expected result

1 Like
3

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

4

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

5

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

6

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.

7

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?

8

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

1 Like
9

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.

10

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.

11

HI Khtan, the code is working for at least 80-85% but for this specific condition it’s not giving the desired result. Can you please take a 2nd look.

stage_test_data
stage_test_data1948Ă—349 21.5 KB

desired_result
desired_result408Ă—137 2.66 KB

current_undesired_output
current_undesired_output903Ă—354 8.6 KB

12

can you please create a db<>fiddle like what I did in my earlier reply and include the new sample data in.

13

Hope this works I added the new records to the previous ones. Please look at the attached the record for the desired records.

14

Thanks for creating the fiddle. It makes things easier.

The 2 lines of July 29 is coming from

(74,103,107,'7/28/2016','8/2/2016' ,30,2,15 ,1,15,4,60 ,'mg'),
(74,103,107,'7/28/2016','9/6/2016' ,30,2,15 ,2,30,4,60,'mg');

You can perform another GROUP BY of the current result and sum up the `Total Dose`

Updated query

; 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
),
-- Added to summarize the result
cte as 
(  
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
where   d.dim_routine_meds_presc_key=107
group by d.dim_routine_meds_presc_key, t.Order_Start_Date, t.Order_End_Date
)
select dim_routine_meds_presc_key, Order_Start_Date,
       Order_End_Date = min(Order_End_Date),
       Total_Dose = sum(Total_Dose)
from   cte
group by dim_routine_meds_presc_key, Order_Start_Date
order by Order_Start_Date, Order_End_Date;

db<>fiddle demo

16

But it’s not giving me the desired output and timeline. The first 2 records is supposed to be rolled up into one because it is the same dose of 30. Timeline 8/3/2016-9/15/2016 is missing from your result and 08/02 is showing up as 15 in yours mine is 75…..Below is the breakdown of the logic behind the desired output 1) Regarding the first two records: we're trying to show how long a patient was on the same dose, so the first two records need to stitch together as they represent a continuous dose date range.

  1. Regarding 8/2: there are two orders that cover that date, both starting on 7/28. The first one ends on 8/2 and on that date, there was 1 dose of 15 mg. The other order continues to 9/16 and is absent in two ways in the above data. On 8/2 that order has the full 4 doses of 15 mg. That order continues between 8/3 and 9/15 at 4 doses of 15 mg.

On 8/2 the 75 mg is from the order ending on 8/2 with 15 mg on the end date plus the 60 mg of the order that continues on.

From 8/3 to 9/15 there should be a record for the ongoing 15 *4 = 60 mg record that started on 7/28 and ends on 9/16. I see it reflected in the range from 7/29 to 8/01, and I see the record indicating it ends on 9/16, but it is missing between 8/2 and 9/15.

18

hi DreSQL

this the same golden subject since “age old times” of mankind

how do you make e x t r e m e l y COMPLEX subjects easy to understand for OTHERS

cannot put a whole bunch of text with ”in consistencies”

has to be Neat Compact Organized

“in first Glance” without much strain to the eyes and the BRAIN == the other person has to get it

if i say abc means zyx then xyz is def and then 234 is 89

can you make sense of it ?