Share via

How to figure out the total number of doses within a time span given to a client as dose are being added and stopped.?

andrew omofonma 61 Reputation points
2024-07-22T22:24:04.8633333+00:00

If the cadence values are different, even if the dates overlap the records must be separate.

If an order for the same dose and cadence value (including nulls) starts on the same day or the next day (+or-1 day) the record should continue.

  outlook of source data:       
source_data.PNG

Result data expected:

expected_output.PNG

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

    drop table #prescribed_dose
  
  sql
    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','2016-07-21','2016-07-25',100.00,14)
  
  
    ,('125','Paliperidone Palmitate','2016-07-21','2016-07-22',234.00,null)
  
  
    ,('125','risperiDONE Consta','2016-01-27','2016-03-01',12.50,14)
  
  
    ,('125','risperiDONE Consta','2016-02-03' ,'2016-02-04',25.00,null)
  
  
    ,('125','risperiDONE Consta','2016-02-17','2016-03-01' ,25.00,14)
  
  
    ,('125','risperiDONE Consta','2016-07-27','2016-08-09',25.00,null)
  
  
    ,('141','Haloperidol Decanoate','2016-05-14','2016-07-26',250.00,30)
  
  
    ,('141','Haloperidol Decanoate','2016-08-05','2016-08-10',100.00,14)
  
  
    ,('141','Paliperidone Palmitate','2016-08-05','2016-08-06',234.00,null)
  
  
    ,('141','risperiDONE Consta','2016-05-25','2016-07-26' ,50.00,14)
  
  
    ,('141','risperiDONE Consta','2016-07-27','2016-08-10',25.00,21)
  
  
  
  
  
  
    ,('147','Paliperidone Palmitate','2019-10-03','2020-10-02',234.00,30)
  
  
    ,('147','risperiDONE Consta','2019-10-15','2019-10-15',12.50,14)
  
  
    ,('147','risperiDONE Consta','2019-10-15' ,'2020-10-14',12.50,14)
  
  
    ,('147','risperiDONE Consta','2019-10-15','2020-10-14' ,12.50,14)
  
  
    ,('147','risperiDONE Consta','2019-11-04','2020-11-03',25.00,14)
  
  
    ,('7','Haloperidol Decanoate','2016-03-12','2017-03-11',100.00,31)
  
  
    ,('7','Haloperidol Decanoate','2016-03-14','2017-03-13',100.00,30)
  
  
    ,('103','risperiDONE Consta','2017-01-12','2017-02-01',25.00,null)
  
  
    ,('103','risperiDONE Consta','2017-02-01','2017-07-20',25.00,null)
  
  
    ,('103','risperiDONE Consta','2017-07-20','2018-07-18',25.00,null)
  
  
    ,('103','risperiDONE Consta','2018-07-18','2019-07-18',25.00,null)
  
  
    ,('103','risperiDONE Consta','2020-08-21','2021-08-21',25.00,null)          
  
  
    ,('103','risperiDONE Consta','2021-08-21','2022-08-21',25.00,null)
  
  
    ,('121','risperiDONE Consta','2017-01-13','2017-01-31',25.00,null)
  
  
    ,('121','risperiDONE Consta','2017-02-01','2017-04-11',25.00,null)
  
  
    ,('121','risperiDONE Consta','2017-04-13','2018-04-13',25.00,null)
  
  
    ,('121','risperiDONE Consta','2018-04-13','2018-07-18',25.00,null)
  
  
    ,('121','risperiDONE Consta','2018-07-18','2019-07-18',25.00,null)          
  
  
    ,('121','risperiDONE Consta','2021-09-26','2022-09-26',25.00,null)
  
  
    ------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,with differnt cadence- 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','2016-07-21','2016-07-25',100.00,14)
  
  
    ,('125','Paliperidone Palmitate','2016-07-21','2016-07-22',234.00,null)
  
  
    ,('125','risperiDONE Consta','2016-01-27','2016-02-16',12.50,14)
  
  
    ,('125','risperiDONE Consta','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','2016-07-27','2016-08-09' ,25.00,14)
  
  
    ,('125','risperiDONE Consta','2016-02-03','2016-02-04',25.00,null)
  
  
    ,('141','Haloperidol Decanoate','2016-05-14','2016-07-26',250.00,30)
  
  
    ,('141','Haloperidol Decanoate','2016-08-05','2016-08-10',100.00,14)
  
  
    ,('141','Paliperidone Palmitate','2016-08-05','2016-08-06',234.00,null)
  
  
    ,('141','risperiDONE Consta','2016-05-25','2016-07-26' ,50.00,14)
  
  
    ,('141','risperiDONE Consta','2016-07-27','2016-08-10',25.00,21)
  
  
  
  
  
  
    ,('147','Paliperidone Palmitate','2019-10-03','2020-10-02',234.00,30)
  
  
    ,('147','risperiDONE Consta','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','2019-10-16' ,'2019-11-03',25.00,14)
  
  
    ,('147','risperiDONE Consta','2019-11-04','2020-10-14' ,50.00,14)
  
  
    ,('147','risperiDONE Consta','2020-10-15','2020-11-03',25.00,14)
  
  
  
    
  
  
    ,('7','Haloperidol Decanoate','2016-03-12','2017-03-11',100.00,31)
  
  
    ,('7','Haloperidol Decanoate','2016-03-14','2017-03-13',100.00,30)
  
  
    ,('103','risperiDONE Consta','2017-01-12','2019-07-18',25.00,null)
  
  
    ,('103','risperiDONE Consta','2020-08-21','2022-08-21',25.00,null)
  
  
    ,('121','risperiDONE Consta','2017-01-13','2017-04-11',25.00,null)
  
  
    ,('121','risperiDONE Consta','2017-04-13','2019-07-18',25.00,null)        
  
  
    ,('121','risperiDONE Consta','2021-09-26','2022-09-26',25.00,null)
  
  
    select * from #prescribed_dose
  
  
    order by patid,Generic_Name,S_Date
  
  
    select * from #prescribed_dose_Result
  
  
    --where patid in ('103','7','121')
  
  
    order by patid,Generic_Name,S_Date
  tsql
Azure SQL Database
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
102 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,660 questions
SQL Server Training
SQL Server Training
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Training: Instruction to develop new skills.
15 questions
0 comments
Accepted answer
  1. Erland Sommarskog 112.8K Reputation points
    2024-07-23T08:15:28.7133333+00:00

    This seems to be a gaps-and-islands problem. Rather than giving you an exact solution, I like to point you to this article by Ed Pollack, that can help you to find the solution yourself: https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/introduction-to-gaps-and-islands-analysis/

    0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.