CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
wootscootinboogie
wootscootinboogie

Reputation: 8695

Grouping by timeframes with a modifier that changes over time

After poring over a similar problem and finding it never provided a complete solution I finally have gotten to the heart of the problem I can't solve. I'm looking for the consecutive amount of days that a person can be prescribed a certain amount of drugs. Because the prescriptions begin and end, there can be multiple, non-contiguous intervals that a person is on X number of drugs. The following SQL script produces the result set of the query I'll post momentarily: Also, I don't have SQL Server 2012.

create table test
(pat_id int, cal_date date, grp_nbr int, drug_qty int,[ranking] int)
go
insert into test(pat_id,cal_date, grp_nbr,drug_qty,[ranking])
values
(1, '1/8/2007',7,2, 1),
(1, '1/9/2007',7,2, 1),
(1, '1/10/2007',7,  2,1),
(1, '1/11/2007',7,  2,1),
(1, '1/12/2007',7,  2,1),
(1, '1/13/2007',7,  2,1),
(1, '1/14/2007',7,  2,1),
(1, '1/15/2007',7,  2,1),
(1, '6/1/2007',7,2, 1),
(1, '6/2/2007',7,2, 1),
(1, '6/3/2007',7,2, 1)

Notice here that there are two non-contiguous intervals where this person was on two drugs at once. In the days that are omitted,drug_qty was more than two. The last column in this example was my attempt at adding another field that I could group by to help solve the problem (didn't work).

Query to create tables:

 CREATE TABLE [dbo].[rx](
            [pat_id] [int] NOT NULL,
            [fill_Date] [date] NOT NULL,
            [script_End_Date]  AS (dateadd(day,[dayssup],[filldate])),
            [drug_Name] [varchar](50) NULL,
            [days_Sup] [int] NOT NULL,
            [quantity] [float] NOT NULL,
            [drug_Class] [char](3) NOT  NULL,
            CHECK(fill_Date <=script_End_Date
PRIMARY KEY CLUSTERED 
(
            [clmid] ASC
)


CREATE TABLE [dbo].[Calendar](
             [cal_date] [date] PRIMARY KEY,
[Year] AS YEAR(cal_date) PERSISTED,
[Month] AS MONTH(cal_date) PERSISTED,
[Day] AS DAY(cal_date) PERSISTED,
             [julian_seq] AS 1+DATEDIFF(DD, CONVERT(DATE, CONVERT(varchar,YEAR(cal_date))+'0101'),cal_date),
     id int identity);

the query I'm using to produce my result sets:

;WITH x 
     AS (SELECT rx.pat_id, 
                c.cal_date, 
                Count(DISTINCT rx.drug_name) AS distinctDrugs 
         FROM   rx, 
                calendar AS c 
         WHERE  c.cal_date BETWEEN rx.fill_date AND rx.script_end_date 
                AND rx.ofinterest = 1 
         GROUP  BY rx.pat_id, 
                   c.cal_date 
         --the query example I used having count(1) =2, but to illustrate the non-contiguous intervals, in practice I need the below having statement
         HAVING Count(*) > 1), 
     y 
     AS (SELECT x.pat_id, 
                x.cal_date 
                --c2.id is the row number in the calendar table. 
                , 
                c2.id - Row_number() 
                          OVER( 
                            partition BY x.pat_id 
                            ORDER BY x.cal_date) AS grp_nbr, 
                distinctdrugs 
         FROM   x, 
                calendar AS c2 
         WHERE  c2.cal_date = x.cal_date) 
SELECT *, 
       Rank() 
         OVER( 
           partition BY pat_id, grp_nbr 
           ORDER BY distinctdrugs) AS [ranking] 
FROM   y 
WHERE  y.pat_id = 1604012867 
       AND distinctdrugs = 2

Besides the fact that I shouldn't have a column in the calendar table named 'id', is there anything egregiously wrong with this approach? I can get the query to show me the distinct intervals of distinctDrugs=x, but it will only work for that integer and not anything >1. By this I mean that I can find the separate intervals where a patient is on two drugs, but only when I use =2 in the having clause, not >1. I can't do something like

SELECT pat_id, 
       Min(cal_date), 
       Max(cal_date), 
       distinctdrugs 
FROM   y 
GROUP  BY pat_id, 
          grp_nbr

because this will pick up that second group of non-contiguous dates. Does anyone know of an elegant solution to this problem?

Upvotes: 0

Views: 137

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

The key to this is a simple observation. If you have a sequence of dates, then the difference between them and an increasing sequence is constant. The following does this, assuming you are using SQL Server 2005 or greater:

select pat_id, MIN(cal_date), MAX(cal_date), MIN(drug_qty)
from (select t.*,
             cast(cal_date as datetime) - ROW_NUMBER() over (partition by pat_id, drug_qty order by cal_date) as grouping
      from #test t
     ) t
group by pat_id, grouping

Upvotes: 1

Related Questions