SQLTeam.com | Weblogs | Forums

Query help

Hi all,

I am new here an not certain I'am at the right place.

I am tying to figure something out.

I have a list of encounters (with unique encounter ID) and events within this encounter. I need/ would like to create the following partitions based on whether the time between events is more than 24h. I need this in order to calculate the duration of one patitition.

|Encounter|Event|Time between events is > 24 h|partiontions|

Is there a way to do this using SQL?

Your helpt is grately appreciated!

With kind regards,


how would i understand this ?

doing it will be very simple

Dear Harishgg1,

Thank you for repying. Unfoutunately, I do not understand your reply.

To clarify, I would like to create the partitions column based on the first 3 columns. If you have any suggestions on how to do this, I would be most apriciative.

With kind regards,


1 Like

see i have to understand what the logic is based on the three columns to generate partition

looking at it looks like a bunch of gibberish

if i understand writing the SQL is easy

please help me understand


here 1,2 because its 1,2 column 3 is 1
2,1 when you add 4 to 1 is 5 ..what 4 is its leap year

Dear Harishgg1,

It is mostly about column 3;
For each encounter (column 1) I want to look at the time between events.

So, as long as the time between events is less then 24h, so the third column = 1 then the fourth collumn should be X. If there is more than 24h between events so the third column becomes 1 then the fourth column should be X+1. As long as the time between the substequent events is < 24 (hence third column being 0) then it should remain X+1 untill the 1 in column 3.

For example 1 encounter with 10 events:

Time between events > 24h: 0 0 1 0 0 1 0 0 0 1 1
Partition:_______________: 1 1 2 2 2 3 3 3 3 4 5

Is this more clear?

With kind regards,


Your posts are very muddled. You should post consumable test data and the result you want.
Repost the following replacing -1 with the value you want to see.

	Encounter int NOT NULL
	,[Event] int NOT NULL
	,Over24 int NOT NULL
	,[Partition] int NOT NULL
	,ExpectedResult int NOT NULL
	,PRIMARY KEY (Encounter, [Event])


hope this helps

create data script

drop table #Temp

create table #Temp ( Encounter int ,Event int ,[Time between events is > 24 h] int )

insert into #temp select 1,1,0
insert into #temp select 1,2,0
insert into #temp select 1,3,1
insert into #temp select 1,4,0
insert into #temp select 2,1,0
insert into #temp select 2,2,0
insert into #temp select 2,3,1
insert into #temp select 2,4,0
insert into #temp select 2,5,1
insert into #temp select 2,6,0
insert into #temp select 2,7,0
insert into #temp select 3,1,0
insert into #temp select 3,2,0

select * from #Temp

; WITH cte_rn
     AS (SELECT *,
                  OVER(ORDER BY encounter, event) AS rn
         FROM   #temp),
     AS (SELECT encounter,
                [time between events is > 24 h],
                1 AS ok,
         FROM   cte_rn
         WHERE  rn = 1
         UNION ALL
         SELECT a.encounter,
                a.[time between events is > 24 h],
                CASE WHEN a.encounter <> b.encounter THEN 1 ELSE b.ok END +
                a.[time between events is > 24 h],
         FROM   cte_rn a
                JOIN rec_cte b
                  ON a.rn = b.rn + 1)
FROM   rec_cte

1 Like