SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

	CREATE TABLE [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,
	)
    insert into [stage_routine_medications_Test] 
	(
		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
	)
	values
	 ( 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' );
14 rows affected

; 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


dim_routine_meds_presc_key
Order_Start_Date
Order_End_Date
Total_Dose


11
2016-05-26
2016-05-26
875.00

11
2016-05-27
2016-06-27
2100.00

11
2016-06-28
2016-06-28
1175.00

11
2016-06-29
2016-07-27
400.00

11
2016-07-28
2016-07-28
575.00

11
2016-07-29
2016-09-15
750.00

11
2016-09-16
2016-09-16
525.00

11
2016-09-17
2016-09-16
300.00

11
2016-09-17
2016-09-17
450.00

11
2016-09-18
2016-09-17
600.00

11
2016-09-18
2016-09-18
900.00

11
2016-09-19
2016-09-18
1200.00

11
2016-09-19
2016-09-19
1650.00

11
2016-09-20
2017-01-08
2100.00

11
2017-01-09
2017-01-09
1225.00

dim_routine_meds_presc_key	Order_Start_Date	Order_End_Date	Total_Dose
11	2016-05-26	2016-05-26	875.00
11	2016-05-27	2016-06-27	2100.00
11	2016-06-28	2016-06-28	1175.00
11	2016-06-29	2016-07-27	400.00
11	2016-07-28	2016-07-28	575.00
11	2016-07-29	2016-09-15	750.00
11	2016-09-16	2016-09-16	525.00
11	2016-09-17	2016-09-16	300.00
11	2016-09-17	2016-09-17	450.00
11	2016-09-18	2016-09-17	600.00
11	2016-09-18	2016-09-18	900.00
11	2016-09-19	2016-09-18	1200.00
11	2016-09-19	2016-09-19	1650.00
11	2016-09-20	2017-01-08	2100.00
11	2017-01-09	2017-01-09	1225.00