Search code examples
sqlsql-servert-sql

Calculate total count based on time span

Added a new data set because it's not producing the intended output. 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.

enter image description here

expected additional dataset output: enter image description here

current output for patid=103 with the current code: enter image description here

previous test data source data example

Result data expected: Result expectedHow to figure out the total number of doses within a time span given to a client as dose are being added and stopped. In some cases, new start dates, and end date may be created. as explained for patid=147

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

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 date will be 01/01/2024-01/03/2024=25 mg 01/04/2024 -01/07/2024 = 55mg (25+30) mg, then from 01/08/2024-01/17/2024 =30mg because the client has stopped taking the 25 mg dose.

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)

    ,('7','Haloperidol Decanoate','R','2016-03-12','2017-03-11',100.00,31)
    ,('7','Haloperidol Decanoate','R','2016-03-14','2017-03-13',100.00,30)

    ,('103','risperiDONE Consta','R','2017-01-12','2017-02-01',25.00,null)
    ,('103','risperiDONE Consta','R','2017-02-01','2017-07-20',25.00,null)
    ,('103','risperiDONE Consta','R','2017-07-20','2018-07-18',25.00,null)
    ,('103','risperiDONE Consta','R','2018-07-18','2019-07-18',25.00,null)
    ,('103','risperiDONE Consta','R','2020-08-21','2021-08-21',25.00,null)          
    ,('103','risperiDONE Consta','R','2021-08-21','2022-08-21',25.00,null)


    ,('121','risperiDONE Consta','R','2017-01-13','2017-01-31',25.00,null)
    ,('121','risperiDONE Consta','R','2017-02-01','2017-04-11',25.00,null)
    ,('121','risperiDONE Consta','R','2017-04-13','2018-04-13',25.00,null)
    ,('121','risperiDONE Consta','R','2018-04-13','2018-07-18',25.00,null)
    ,('121','risperiDONE Consta','R','2018-07-18','2019-07-18',25.00,null)          
    ,('121','risperiDONE Consta','R','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','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)
    ,('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)


    
    ,('7','Haloperidol Decanoate','R','2016-03-12','2017-03-11',100.00,31)
    ,('7','Haloperidol Decanoate','R','2016-03-14','2017-03-13',100.00,30)

    ,('103','risperiDONE Consta','R','2017-01-12','2019-07-18',25.00,null)
    ,('103','risperiDONE Consta','R','2020-08-21','2022-08-21',25.00,null)

    ,('121','risperiDONE Consta','R','2017-01-13','2017-04-11',25.00,null)
    ,('121','risperiDONE Consta','R','2017-04-13','2019-07-18',25.00,null)        
    ,('121','risperiDONE Consta','R','2021-09-26','2022-09-26',25.00,null)





    select * from #prescribed_dose
    order by patid,Generic_Name,S_Date,Route

    select * from #prescribed_dose_Result
    where patid in ('103','7','121')
    order by patid,Generic_Name,S_Date,Route
Solution

  • Use a union to combine all the dates into a single list. Start dates will be treated as increments to the total dosages while end dates will decrement. Since these values will offset each other, the total dosage at any point is the running total calculating from the beginning. (Don't use float as the column type as it might not reliably cancel out.)

    with dates as (
    select *, s_date as dt, 1 as direction, ordered_dose as dose_change
        from #prescribed_dose union all
    -- adjust end dates so they take effect the following day
    select *, dateadd(day, 1, e_date), -1, -ordered_dose from #prescribed_dose
), nodes as (
    select patid, generic_name, route, dt,
        sum(direction) as lvl, sum(dose_change) as daily_dose_change
    from dates
    group by patid, generic_name, route, dt
), cumulative as (
    select *,
        sum(lvl) over (
            partition by patid, generic_name, route order by dt) as total_orders,
        sum(daily_dose_change) over (
            partition by patid, generic_name, route order by dt) as total_dose,
        lead(dt) over (
            partition by patid, generic_name, route order by dt) as next_dt
    from nodes
)
select patid, generic_name, route,
    dt as s_date, dateadd(day, -1, next_dt) as e_date, total_dose, total_orders
from cumulative
where total_orders > 0
order by patid, generic_name, route, dt;

    https://dbfiddle.uk/QSlWX071