Query help

Transact-SQL
1

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|
|1|1|0|1|
|1|2|0|1|
|1|3|1|2|
|1|4|0|2|
|2|1|0|1|
|2|2|0|1|
|2|3|1|2|
|2|4|0|2|
|2|5|1|3|
|2|6|0|3|
|2|7|0|3|
|3|1|0|1|
|3|2|0|1|

Is there a way to do this using SQL?

Your helpt is grately appreciated!

With kind regards,

Myriam

2

how would i understand this ?

doing it will be very simple

3

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,

Myriam

1 Like
4

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

example
1,2,1
2,1,5
3,2,3

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

5

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,

Myriam

6

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.

CREATE TABLE #t
(
	Encounter int NOT NULL
	,[Event] int NOT NULL
	,Over24 int NOT NULL
	,[Partition] int NOT NULL
	,ExpectedResult int NOT NULL
	,PRIMARY KEY (Encounter, [Event])
);
GO
INSERT INTO #t
VALUES
	 (1,1,0,1,-1)
	,(1,2,0,1,-1)
	,(1,3,1,2,-1)
	,(1,4,0,2,-1)
	,(2,1,0,1,-1)
	,(2,2,0,1,-1)
	,(2,3,1,2,-1)
	,(2,4,0,2,-1)
	,(2,5,1,3,-1)
	,(2,6,0,3,-1)
	,(2,7,0,3,-1)
	,(3,1,0,1,-1)
	,(3,2,0,1,-1);
GO
2 Likes
8

hi

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 *,
                Row_number()
                  OVER(ORDER BY encounter, event) AS rn
         FROM   #temp),
     rec_cte
     AS (SELECT encounter,
                event,
                [time between events is > 24 h],
                1 AS ok,
                rn
         FROM   cte_rn
         WHERE  rn = 1
         UNION ALL
         SELECT a.encounter,
                a.event,
                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],
                a.rn
         FROM   cte_rn a
                JOIN rec_cte b
                  ON a.rn = b.rn + 1)
SELECT *
FROM   rec_cte

image
image677×672 8.64 KB

1 Like