How to Make Groups Reset by ID number

Question

I wrote a SQL script that numbers a new group every time either 1) The ID number changes compared to the one before it or 2) The order date is more than 15 minutes longer than the one before it. However, instead of having numbers that go from 0 to infinity, I want the group # to reset to 0 every time the ID number changes. Please advise.

This code creates a dummy table of values:

CREATE TABLE #pretemp (
 [hospital_code] varchar(255),
s_visit_ident varchar(255),
 orderdt datetime
 ); 

 INSERT INTO #pretemp ([hospital_code],s_visit_ident,orderdt)
 VALUES ('ClevelandClinic', '1', '01-02-2020 12:30'),
 ('ClevelandClinic', '1','01-02-2020 12:35'),
 ('ClevelandClinic', '1', '01-02-2020 12:42'),
 ('ClevelandClinic', '1', '01-02-2020 12:55'),
 ('ClevelandClinic', '1','01-05-2020 18:44'),
 ('ClevelandClinic', '2','01-05-2020 07:09'),
 ('ClevelandClinic', '1','01-05-2020 07:12'),
 ('ClevelandClinic', '1','01-05-2020 07:18'),
 ('ClevelandClinic', '1', '01-07-2020 00:00'),
 ('ClevelandClinic', '3','01-07-2020 00:03'),
 ('ClevelandClinic', '3','01-07-2020 00:10');

This script creates and adds a group number.

 select hospital_code, s_visit_ident, orderdt,
 lead(orderdt,1) over (order by hospital_code, s_visit_ident,orderdt) as 'Lead Time',
 lead(s_visit_ident,1) over (order by hospital_code,s_visit_Ident,orderdt) as 'Lead pt_id',
 lag(Orderdt,1) over (order by hospital_Code, s_visit_ident,orderdt) as 'Lag',
 lag(s_visit_ident,1) over (order by hospital_code, s_visit_ident,orderdt) as 'Lag pt_id'
 into #temp
 from #pretemp
 order by hospital_code, s_visit_ident, orderdt,lead(orderdt,1) over (order by 
 hospital_code,s_visit_ident,orderdt)

 select *, 
 case when [lead pt_id] <> [s_visit_ident] then null else [lead time] end as 'Updated Lead Time',
 case when [lead pt_id] <> [s_visit_ident] then null else datediff(mi,orderdt,[lead time]) end as 
 'Timediff', 
  case when [lead pt_id] <> s_visit_ident then 0
  when datediff(mi,orderdt,[lead time]) <= 15 then 1 else 0 end as 'Tag'
  into #temp2
  From #temp
  order by s_Visit_ident,orderdt,[lead time],hospital_code


   ALTER TABLE #temp2 ADD [group] INT

   DECLARE @group INT
   SELECT @group = 0

   UPDATE ss SET [group] = @group, @group = CASE WHEN 
   s.[lag pt_id] is null then @group
   when s.[lead pt_id] is null then @group
   when
   s.[lag pt_Id] <> s.s_visit_ident then @group + 1
   --If it's the first, then if the lag time 
   when datediff(mi,ss.[lag],s.[orderdt]) > 15 then @group+1
   --If it's in the middle
   when datediff(mi,ss.[lag],s.orderdt) <= 15 and datediff(mi,s.orderdt,ss.[lead time]) <= 15 then        @group
   --If it's the last of the group
   when datediff(mi,ss.[lag],s.orderdt) <=15 and datediff(mi,s.orderdt,ss.[lead time]) > 15 then @group
   else @group + 1
   END
   FROM #temp2 
   ss JOIN (SELECT hospital_Code, s_Visit_ident, orderdt,[lag pt_id],[lead pt_id]
FROM #temp2
) s ON (ss.hospital_Code = s.hospital_Code AND ss.s_visit_ident = s.s_visit_Ident AND ss.orderdt = s.orderdt)

SELECT hospital_code AS Hospital, s_visit_Ident AS Patient, orderdt,[group]
   FROM #temp2
   GROUP BY hospital_Code,s_Visit_ident,orderdt,[group]
   ORDER BY [group] asc

These are the results I get:

  Hospital                Patient            orderdt                    group
  ClevelandClinic             1                  2020-01-02 12:30:00.000    0
  ClevelandClinic             1                  2020-01-02 12:35:00.000    0
  ClevelandClinic             1                  2020-01-02 12:42:00.000    0
  ClevelandClinic             1                  2020-01-02 12:55:00.000    0
  ClevelandClinic             1                  2020-01-05 07:12:00.000    1
  ClevelandClinic             1                  2020-01-05 07:18:00.000    1
  ClevelandClinic             1                  2020-01-05 18:44:00.000    2
  ClevelandClinic             1                  2020-01-07 00:00:00.000    3
  ClevelandClinic             2                  2020-01-05 07:09:00.000    4
  ClevelandClinic             3                  2020-01-07 00:03:00.000    5
  ClevelandClinic             3                  2020-01-07 00:10:00.000    5

These are the results I want:

  Hospital                Patient            orderdt                    group
  ClevelandClinic             1                  2020-01-02 12:30:00.000    0
  ClevelandClinic             1                  2020-01-02 12:35:00.000    0
  ClevelandClinic             1                  2020-01-02 12:42:00.000    0
  ClevelandClinic             1                  2020-01-02 12:55:00.000    0
  ClevelandClinic             1                  2020-01-05 07:12:00.000    1
  ClevelandClinic             1                  2020-01-05 07:18:00.000    1
  ClevelandClinic             1                  2020-01-05 18:44:00.000    2
  ClevelandClinic             1                  2020-01-07 00:00:00.000    3
  ClevelandClinic             2                  2020-01-05 07:09:00.000    0
  ClevelandClinic             3                  2020-01-07 00:03:00.000    0
  ClevelandClinic             3                  2020-01-07 00:10:00.000    0

Instead of the group number being added every time, I want the group number to reset to 0 every time I have a new patient ID number. I appreciate any help you can give me.

score 1 · Accepted Answer · answered Sep 21 '20 at 08:28

Looks like this should do it in a single query. The solution uses a common table expression (cte) to calculation when to increment the group. After that there is a large sum() function that produces a running total of the group increments. The -1 makes the group numbers start from 0 instead of 1.

with cte as
(
select p.hospital_code,
       p.s_visit_ident,
       p.orderdt,
       case when coalesce(datediff(
         minute,
         lag(p.orderdt) over(partition by p.s_visit_ident order by p.orderdt),
         p.orderdt), 15) < 15
         then 0 else 1 end as increment_group
from #pretemp p
)
select cte.hospital_code,
       cte.s_visit_ident as patient,
       cte.orderdt,
       sum(cte.increment_group)
         over(partition by cte.s_visit_ident
              order by cte.orderdt
              rows between unbounded preceding and current row) - 1 as [group]
from cte
order by cte.s_visit_ident, cte.orderdt;

Fiddle

Happy to help. If this concludes your question, then you could formally "accept" this answer in order to remove your question from the big pile of "unanswered questions". — Sander, Sep 21 '20 at 17:49

How to Make Groups Reset by ID number

1 Answers1