Calculate total count of dose

Result:

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

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.

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