May 29, 2013 at 10:42 am
I have a list of events that occur for a patient. I need to count the number of events that occur by patient by type but ONLY if the timestamp on the events are greater than 60 min. apart. (i.e. I need to ignore duplicates that occur within one hour of each other.) Initially, I approached it by getting the MIN and comparing each row to it to see if it was more than 60 minutes apart but I quickly realized that was dumb. I need to compare each consecutive row against the previous row to see if it is within 60 minutes. I am able to get the rowId but I'm not quite sure how to proceed. Should I create a temp table and fill it as I go? Here's the code to create a sample table and fill it will data.
CREATE TABLE [dbo].[la_test_endcall](
[pmd_patient_id] [bigint] NOT NULL,
[endcallcd] [int] NOT NULL,
[eventid] [bigint] NOT NULL,
[createdat] [datetime] NOT NULL
) ON [PRIMARY]
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128137, 1001, 9999995, '2013-04-16 12:02:14.000')
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128137, 1001, 9999996, '2013-04-16 17:02:14.000')
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128137, 1001, 9999997, '2013-04-16 17:12:14.000')
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1001, 5093634, '2013-04-16 17:17:14.000')
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1001, 5099268, '2013-04-26 13:48:53.000')
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1002, 5121175, '2013-05-16 16:43:51.000')
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1002, 5133281,'2013-05-23 17:53:46.000')
Here's how I'm getting a RowID by patient by type:
SELECT pmd_patient_Id,
EndCallCd,
EventId,
CreatedAt,
ROW_NUMBER() OVER (PARTITION BY PMD_Patient_ID, endcallcd ORDER BY CreatedAt) AS RowId
FROM la_test_endcall
ORDER BY pmd_patient_id,endcallcd, CreatedAt
May 29, 2013 at 11:11 am
I think this will meet your requirements based on the information you provided.
WITH tests
AS (
SELECT
pmd_patient_Id,
EndCallCd,
EventId,
CreatedAt,
ROW_NUMBER() OVER (PARTITION BY PMD_Patient_ID, endcallcd ORDER BY CreatedAt) AS RowId
FROM
la_test_endcall
),
timeDiff
AS (
SELECT
T1.eventid,
T1.pmd_patient_Id,
T1.EndCallCd,
DATEDIFF(MINUTE, T1.createdAt, T2.createdAt) AS timeDIff
FROM
tests AS T1
LEFT JOIN tests AS T2
ON T1.pmd_patient_Id = T2.pmd_patient_Id AND
T1.EndCallCd = T2.EndCallCd AND
T1.RowId = T2.RowId - 1
)
SELECT
COUNT(*),
pmd_patient_Id,
EndCallCd
FROM
timeDiff
WHERE
timeDiff.timeDIff > 60
GROUP BY
pmd_patient_Id,
EndCallCd;
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 29, 2013 at 11:18 am
It's easy to do in SQL2012, harder in 2008.
You didn't specify what determines a patient type, so I took that it is a "endcallcd", as you did partioned by this.
The following query should calculate the count of events, ignoring ones spaced less than 60 min apart:
;WITH calc_time_diff
AS
(
SELECT pmd_patient_Id,
EndCallCd,
CP.PrevCreatedAtDiffSec
FROM la_test_endcall as la
OUTER APPLY (SELECT ISNULL(DATEDIFF(SECOND,MAX(la1.CreatedAt),la.CreatedAt),999999999) PrevCreatedAtDiffSec
FROM la_test_endcall la1
WHERE la1.pmd_patient_id = la.pmd_patient_id
AND la1.endcallcd = la.endcallcd
AND la1.CreatedAt < la.CreatedAt) CP
)
SELECT pmd_patient_Id, EndCallCd, COUNT(*) YourCount
FROM calc_time_diff
WHERE PrevCreatedAtDiffSec > 3600 -- 60 min
GROUP BY pmd_patient_Id, EndCallCd
ORDER BY pmd_patient_id,endcallcd
The above may not be the best performing query, but you can try if it acceptable in your case.
Also, you need to be very careful in your definition of "duplicates". The above query will count record only if the previous one created 60 min before. Which mean if you have records created like that:
1. 18:00
2. 18:50
3. 19:10
it will count only 1 as #2 and #1 are 50 min diff and #3 and #2 is 20 min. In order to count in #3 you need to disregard record #2 completely.
The first way to do so coming into my mind would be "quirky update" method http://www.sqlservercentral.com/articles/T-SQL/68467/.
To represent the above just add the following two records into your sample:
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1002, 5133281,'2013-05-23 18:43:46.000')
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1002, 5133281,'2013-05-23 19:03:46.000')
You will see that count for the last patient didn't changed even that 4-th record timing is more than hour aftre the 2-nd one...
Surely it's possible by other ways, but I want to go home now 🙂
May 29, 2013 at 1:57 pm
SSCrazy, that is very close to what I need but the problem you brought up is definitely something I have to solve. I read through the "quirky update" article, but I confess it is far more advanced than I am. I'm going to keep trying. I need to compare 1 to 2, 1 to 3, 1 to 4, etc... then 2 to 3, 2 to 4, etc... I'm completely bamboozled.
May 29, 2013 at 3:40 pm
What is the result set you are looking for based n the sample data provided.
May 29, 2013 at 4:29 pm
Maybe this (be sure to run it in an empty database first as I drop the table you created):
CREATE TABLE [dbo].[la_test_endcall](
[pmd_patient_id] [bigint] NOT NULL,
[endcallcd] [int] NOT NULL,
[eventid] [bigint] NOT NULL,
[createdat] [datetime] NOT NULL
) ON [PRIMARY];
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128137, 1001, 9999995, '2013-04-16 12:02:14.000')
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128137, 1001, 9999996, '2013-04-16 17:02:14.000');
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128137, 1001, 9999997, '2013-04-16 17:12:14.000');
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1001, 5093634, '2013-04-16 17:17:14.000');
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1001, 5099268, '2013-04-26 13:48:53.000');
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1002, 5121175, '2013-05-16 16:43:51.000');
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1002, 5133281, '2013-05-23 17:53:46.000');
GO
with overlaps as (
SELECT DISTINCT o.pmd_patient_id, o.endcallcd, o.eventid, o.createdat
FROMdbo.la_test_endcall AS o
CROSS APPLY(
SELECTx.pmd_patient_id, x.endcallcd, x.eventid, x.createdat
FROMdbo.la_test_endcall AS x
WHEREx.pmd_patient_id = o.pmd_patient_id and x.endcallcd = o.endcallcd
AND x.eventid <> o.eventid
AND x.createdat <= dateadd(minute, 60, o.createdat)
and dateadd(minute, 60, x.createdat) >= o.createdat
) AS f
), baseoverlaps as (
select
pmd_patient_id,
endcallcd,
eventid,
createdat,
rn = row_number() over (partition by pmd_patient_id order by createdat asc)
from
overlaps
)
select
pmd_patient_id, endcallcd, count(*) as EventCount
from (
select
pmd_patient_id, endcallcd, eventid, createdat
from
dbo.la_test_endcall
except
select
pmd_patient_id, endcallcd, eventid, createdat
from
baseoverlaps
where
rn > 1) dt
group by
pmd_patient_id, endcallcd
order by
pmd_patient_id, endcallcd
;
go
drop table dbo.la_test_endcall;
go
May 29, 2013 at 6:29 pm
Hi, can you let us know your expected output as Lynn requested - is it this:
+------------------------------------------------------------+
¦[highlight="#808080"] pmd_patient_id [/highlight]¦[highlight="#808080"] endcallcd [/highlight]¦[highlight="#808080"] eventid [/highlight]¦[highlight="#808080"] createdat [/highlight]¦
+----------------+-----------+---------+---------------------¦
¦ 100128138 ¦ 1001 ¦ 5093634 ¦ Apr 16 2013 5:17PM ¦
[highlight="#E0E0E0"]¦ 100128138 ¦ 1001 ¦ 5099268 ¦ Apr 26 2013 1:48PM ¦[/highlight]
¦ 100128138 ¦ 1002 ¦ 5121175 ¦ May 16 2013 4:43PM ¦
[highlight="#E0E0E0"]¦ 100128138 ¦ 1002 ¦ 5133281 ¦ May 23 2013 5:53PM ¦[/highlight]
¦ 100128137 ¦ 1001 ¦ 9999995 ¦ Apr 16 2013 12:02PM ¦
[highlight="#E0E0E0"]¦ 100128137 ¦ 1001 ¦ 9999996 ¦ Apr 16 2013 5:02PM ¦[/highlight]
+------------------------------------------------------------+
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 30, 2013 at 6:25 am
All,
Thank you again for your responses. Lynn's code is very close, but Eugene had 2 additional insert rows to demonstrate the issue I'm worried about. Below is the full create/insert including the two rows:
CREATE TABLE [dbo].[la_test_endcall](
[pmd_patient_id] [bigint] NOT NULL,
[endcallcd] [int] NOT NULL,
[eventid] [bigint] NOT NULL,
[createdat] [datetime] NOT NULL
) ON [PRIMARY];
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128137, 1001, 9999995, '2013-04-16 12:02:14.000')
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128137, 1001, 9999996, '2013-04-16 17:02:14.000');
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128137, 1001, 9999997, '2013-04-16 17:12:14.000');
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1001, 5093634, '2013-04-16 17:17:14.000');
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1001, 5099268, '2013-04-26 13:48:53.000');
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1002, 5121175, '2013-05-16 16:43:51.000');
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1002, 5133281, '2013-05-23 17:53:46.000');
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1002, 5133281,'2013-05-23 18:43:46.000');
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1002, 5133281,'2013-05-23 19:03:46.000');
GO
Lynn's code returns
pmd_patient_idendcallcdEventCount
10012813710012
10012813810012
10012813810024
Eugene's code returns
pmd_patient_idendcallcdNumAttempts
10012813710012
10012813810012
10012813810022
I really think it should be
pmd_patient_idendcallcdNumAttempts
10012813710012
10012813810012
10012813810023
Here's the problem:
pmd_patient_idendcallcdeventidcreatedat
100128138100251211752013-05-16 16:43:51.000
100128138100251332812013-05-23 17:53:46.000 --easy one; over a wk apart
100128138100251332812013-05-23 18:43:46.000--only 50 min apart; shouldn't count
100128138100251332812013-05-23 19:03:46.000--70 min from the 17:53:46 entry, so it should count!
I think the logic should be to count the ones that are >60 min from "the last one that counted."
Does that make sense? Am I thinking about it wrong? I could use advice on my logic, as well.
Thanks again!
May 30, 2013 at 6:37 am
Rather than just returning a count and not knowing which rows are being selected, I will return the full details of each selected row and a count. Once you are happy with the results, you can remove the detailed select.
declare @bb bit;
select pmd_patient_id,endcallcd,eventid,createdat,0 as selected
into #results
from la_test_endcall;
while exists(Select 1 from #results where selected=0)
begin
select @bb=1;
while @@rowcount>0
with data as
(
select *
, datediff(minute,min(createdat) over(partition by pmd_patient_id, endcallcd),createdat) as elapsed
, sum(1) over(partition by pmd_patient_id, endcallcd) as cnt
from #results
where selected=0
)
update data
set selected = -1
where (elapsed>0 and elapsed<=60) ;
;with data as
(
select *
, datediff(minute,min(createdat) over(partition by pmd_patient_id, endcallcd),createdat) as elapsed
, sum(1) over(partition by pmd_patient_id, endcallcd) as cnt
from #results
where selected=0
)
update data
set selected=1
where elapsed=0
end
select pmd_patient_id,endcallcd,eventid,createdat
from #results
where selected=1
order by pmd_patient_id,endcallcd,eventid;
select pmd_patient_id,endcallcd,count(*) as [count]
from #results
where selected=1
group by pmd_patient_id,endcallcd
order by pmd_patient_id,endcallcd;
drop table #results;
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 30, 2013 at 6:47 am
MM-- I think that's it! I'm staring at your code and not understanding exactly why it's working, but that is definitely the result set I was hoping for. I'm going to keep studying it and maybe it will start to make sense to me. 😀
Thank you all tremendously for your help!
LA
May 30, 2013 at 6:55 am
lduvall (5/30/2013)
I think the logic should be to count the ones that are >60 min from "the last one that counted."Does that make sense? Am I thinking about it wrong? I could use advice on my logic, as well.
Thanks again!
I'm not sure what the logic should be because I don't have the business requirements. The issue I have is with counting from the "last one that counted" is that means the events aren't occurring at a greater than 60 minute interval, they are occurring more frequently but continuing for over 60 minutes. Do you see the difference?
Also, is there a maximum time interval that should be ignored. What if the second occurrence of an event for a patient is 2 years after the first occurrence, would this still count? In the code we are using that would still count.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 30, 2013 at 6:57 am
What is is doing is this:
1. Copy the data to a temp table (#results) and add a column called "selected" - preset to 0.
2. Loop while we have rows where selected=0
3. Loop while @@rowcount>0 (primed by the SELECT @bb=1 line, but after that @@rowcount indicates that the UPDATE has affected some rows)
4. Find all unselected rows (selected=0) and calculate the time difference in minutes for each row from the first row for that group (elapsed)
and update the temp table, setting selected=-1 where the elapsed time is <=60 minutes, thus removing these rows from the data for the next run.
5. Repeat Step 4 until no rows are updated - which means we have removed all "duplicate" entries
6. Update all "first rows" for each group as "selected" = 1 - we know we want these now but don't want to test the elapsed time against these again
7. Repeat from Step 3
8. Select all rows which have been "selected" (selected=1)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 30, 2013 at 8:34 am
Cursor/loop based solution given by Mister.Magoo is fine, but will suffer performance issues if you need to process large dataset at once.
As I have said before "quirky update" would do it without RBAR:
select distinct pmd_patient_id, endcallcd, createdat , cast(null as datetime) as prevdt,
cast(null as int) ttl_dif into #forqu
from la_test_endcall
create unique clustered index ix_#forqu on #forqu (pmd_patient_id,endcallcd,createdat)
declare @prevdt datetime
declare @patid int, @ecd int
declare @diff int
update f
set @prevdt = prevdt = case when @prevdt is null or @patid != pmd_patient_id or @ecd != endcallcd then null
else @prevdt
end
,@diff = ttl_dif = case when @patid is null or @patid != pmd_patient_id or @ecd != endcallcd then null
when @diff >= 3600 then datediff(second,@prevdt,createdat)
else isnull(@diff,0) + datediff(second,@prevdt,createdat)
end
,@prevdt = createdat
,@patid = pmd_patient_id
,@ecd = endcallcd
from #forqu as f with (tablockx)
option (maxdop 1)
select pmd_patient_id, endcallcd, count(*)
from #forqu
where ttl_dif is null or ttl_dif >=3600
group by pmd_patient_id, endcallcd
Explanation of how "quirky update" works you can find in Jeff Moden article (http://www.sqlservercentral.com/articles/T-SQL/68467/)...
You can actually remove prevdt column from #forqu table, I have left it there so you can see how the createdat is taken from previous row.
May 30, 2013 at 8:44 am
Tweaked mine so that it returns the same results as Eugene's. Having a different set of data with other possibilities helped. Again, run the following in an empty database first. There are two queries, on the returns just counts and another that returns the dataset used to generate the counts.
CREATE TABLE [dbo].[la_test_endcall](
[pmd_patient_id] [bigint] NOT NULL,
[endcallcd] [int] NOT NULL,
[eventid] [bigint] NOT NULL,
[createdat] [datetime] NOT NULL
) ON [PRIMARY];
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128137, 1001, 9999995, '2013-04-16 12:02:14.000')
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128137, 1001, 9999996, '2013-04-16 17:02:14.000');
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128137, 1001, 9999997, '2013-04-16 17:12:14.000');
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1001, 5093634, '2013-04-16 17:17:14.000');
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1001, 5099268, '2013-04-26 13:48:53.000');
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1002, 5121175, '2013-05-16 16:43:51.000');
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1002, 5133281, '2013-05-23 17:53:46.000');
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1002, 5133281,'2013-05-23 18:43:46.000');
INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1002, 5133281,'2013-05-23 19:03:46.000');
GO
with overlaps as (
SELECT DISTINCT o.pmd_patient_id, o.endcallcd, o.eventid, o.createdat
FROMdbo.la_test_endcall AS o
CROSS APPLY(
SELECTx.pmd_patient_id, x.endcallcd, x.eventid, x.createdat
FROMdbo.la_test_endcall AS x
WHEREx.pmd_patient_id = o.pmd_patient_id and x.endcallcd = o.endcallcd
AND x.createdat <> o.createdat
AND x.createdat <= dateadd(minute, 60, o.createdat)
and dateadd(minute, 60, x.createdat) >= o.createdat
) AS f
), baseoverlaps as (
select
pmd_patient_id,
endcallcd,
eventid,
createdat,
rn = row_number() over (partition by pmd_patient_id order by createdat asc)
from
overlaps
)
select
pmd_patient_id, endcallcd, count(*) as EventCount
from (
select
pmd_patient_id, endcallcd, eventid, createdat
from
dbo.la_test_endcall
except
select
pmd_patient_id, endcallcd, eventid, createdat
from
baseoverlaps
where
rn > 1) dt
group by
pmd_patient_id, endcallcd
order by
pmd_patient_id, endcallcd
;
go
with overlaps as (
SELECT DISTINCT o.pmd_patient_id, o.endcallcd, o.eventid, o.createdat
FROMdbo.la_test_endcall AS o
CROSS APPLY(
SELECTx.pmd_patient_id, x.endcallcd, x.eventid, x.createdat
FROMdbo.la_test_endcall AS x
WHEREx.pmd_patient_id = o.pmd_patient_id and x.endcallcd = o.endcallcd
AND x.createdat <> o.createdat
AND x.createdat <= dateadd(minute, 60, o.createdat)
and dateadd(minute, 60, x.createdat) >= o.createdat
) AS f
), baseoverlaps as (
select
pmd_patient_id,
endcallcd,
eventid,
createdat,
rn = row_number() over (partition by pmd_patient_id order by createdat asc)
from
overlaps
)
select
pmd_patient_id, endcallcd, eventid, createdat
from
dbo.la_test_endcall
except
select
pmd_patient_id, endcallcd, eventid, createdat
from
baseoverlaps
where
rn > 1
;
go
drop table dbo.la_test_endcall;
go
May 30, 2013 at 8:48 am
Scratch, something happened here need to work it again.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply