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


----added records
 (74,103,107,'6/28/2016','7/5/2016' ,30,2,15 ,1,15,2,30,'mg'),
 (74,103,107,'7/5/2016','7/28/2016' ,30,2,15 ,2,30,4,60 ,'MG'),
 (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');

18 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
),
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;



dim_routine_meds_presc_key
Order_Start_Date
Order_End_Date
Total_Dose


107
2016-06-28
2016-06-28
30.00

107
2016-06-29
2016-07-04
30.00

107
2016-07-05
2016-07-05
45.00

107
2016-07-06
2016-07-27
60.00

107
2016-07-28
2016-07-28
90.00

107
2016-07-29
2016-08-01
120.00

107
2016-08-02
2016-08-02
15.00

107
2016-09-06
2016-09-06
30.00
dim_routine_meds_presc_key	Order_Start_Date	Order_End_Date	Total_Dose
107	2016-06-28	2016-06-28	30.00
107	2016-06-29	2016-07-04	30.00
107	2016-07-05	2016-07-05	45.00
107	2016-07-06	2016-07-27	60.00
107	2016-07-28	2016-07-28	90.00
107	2016-07-29	2016-08-01	120.00
107	2016-08-02	2016-08-02	15.00
107	2016-09-06	2016-09-06	30.00