September 27, 2016 at 2:05 pm
I have a table (see attached) listing visitors IDs, listed more than once, and Arrival DateTime. I need to find those whose gap between arrivals is less than 72 hrs.
Is it possible to do it without a cursor loop?
Thanks
September 27, 2016 at 2:22 pm
Please read the second link in my signature (by Jeff Moden)[/url] to help us to help you.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
September 27, 2016 at 2:30 pm
Sorry, I thought that info I provided was straightforward.
September 27, 2016 at 2:36 pm
Well, you will find that while some people may be happy to open an Excel spreadsheet and figure things out, the best help will come if you provide readily consumable data and preferably show what you have tried so far...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
September 27, 2016 at 2:49 pm
I do not know what would be the better way to present a simple table with 7K+ records (Excel file). The table has 2 columns, ID and Arrival DateTime. Sorted by ID and then by Arrival DateTime. In the past I wrote a code with double cursors iterating over each ID (not for this table and not for this task). I thought that there may be a better way to do it.
Thanks
September 27, 2016 at 3:05 pm
This is the fragment of the table
CREATE TABLE [dbo].[Readmission_Table](
[ID] [float] NULL,
[ArrivalDateTime] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10000683, CAST(0x0000A66801716A50 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10000683, CAST(0x0000A66900B9AB40 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10000989, CAST(0x0000A64000B778C0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10000989, CAST(0x0000A65101735680 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10001033, CAST(0x0000A65200F54510 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10001033, CAST(0x0000A66200B91EA0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10001350, CAST(0x0000A64D01526100 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10001350, CAST(0x0000A66F007055D0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10002866, CAST(0x0000A63B01305240 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10002866, CAST(0x0000A63E014D6F60 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10003932, CAST(0x0000A65800C27540 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10003932, CAST(0x0000A66201422C90 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10004218, CAST(0x0000A665013357B0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10004218, CAST(0x0000A66A00EC34C0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10004220, CAST(0x0000A662006D5060 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10004220, CAST(0x0000A66E0072CEA0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10005862, CAST(0x0000A66000A06680 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10005862, CAST(0x0000A662009EC0A0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10006299, CAST(0x0000A64000CC5880 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10006299, CAST(0x0000A645014272E0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10007852, CAST(0x0000A64F00563E20 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10007852, CAST(0x0000A65600BE1040 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10008474, CAST(0x0000A65500163F50 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10008474, CAST(0x0000A66F00503340 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009346, CAST(0x0000A6470130DEE0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009346, CAST(0x0000A64E009A5BA0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009346, CAST(0x0000A651017BDA30 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009903, CAST(0x0000A64E0123AFE0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009903, CAST(0x0000A659015752A0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009903, CAST(0x0000A65A0008CA00 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10010401, CAST(0x0000A64E00549840 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10010401, CAST(0x0000A65600305700 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10011580, CAST(0x0000A63E015798F0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10011580, CAST(0x0000A6570126B550 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10011580, CAST(0x0000A65D0105BFD0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10012712, CAST(0x0000A64E00AB1CB0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10012712, CAST(0x0000A65D00041EB0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10012887, CAST(0x0000A64001586BE0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10012887, CAST(0x0000A66901236990 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10014218, CAST(0x0000A63A00C6DA40 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10014218, CAST(0x0000A64B00B80560 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10014918, CAST(0x0000A63D01731030 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10014918, CAST(0x0000A64000C9DFB0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10014918, CAST(0x0000A6490123F630 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10015000, CAST(0x0000A63D00CAF8F0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10015000, CAST(0x0000A65701388FA0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10015401, CAST(0x0000A665015BB7A0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10015401, CAST(0x0000A669015D1730 AS DateTime))
September 27, 2016 at 3:08 pm
valeryk2000 (9/27/2016)
I do not know what would be the better way to present a simple table with 7K+ records (Excel file). The table has 2 columns, ID and Arrival DateTime. Sorted by ID and then by Arrival DateTime. In the past I wrote a code with double cursors iterating over each ID (not for this table and not for this task). I thought that there may be a better way to do it.Thanks
please read this article.....https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
not sure why you think it is necessary to post 7K+ records to explain your problem....but heyho.
if you could simplify the sample data to demonstrate what you want to achieve by providing sample data that covers ALL scenarios and also to post your expected results based on your sample data...then I am sure you will be pleasantly surprised how quick you get a tried and tested answer.
as it stands at the moment....you have posted an excel file....so what are responders supposed to do with that...
1 they need to download it....very few will.
2 they need to then create a test table in SQL that represents your excel file...making best guesses on datatypes, nulls, constraints, indexes etc
3 they then need to import your excel file into table created above
and that is before they start to provide you with a solution....hmmm.....everyone here is a volunteer, do you see now why you were asked to help us help you?
anyways rant over......
you have posted in a SQL2008 forum .....so here is one method for you to try..it might not be the best...and assume that you only have two rows per ID.
SET DATEFORMAT mdy
GO
CREATE TABLE #mytable(
ID INT NOT NULL
,ArrivalDateTime DATETIME NOT NULL
);
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10000683,'8/20/16 22:25');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10000683,'8/21/16 11:16');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10000989,'7/11/16 11:08');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10000989,'7/28/16 22:32');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001033,'7/29/16 14:53');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001033,'8/14/16 11:14');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001350,'7/24/16 20:32');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001350,'8/27/16 6:49');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10002866,'7/6/16 18:28');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10002866,'7/9/16 17:14');
WITH pt AS (
SELECT Id,
MAX(CASE WHEN rn = 1 THEN at END )AS at1,
MAX(CASE WHEN rn =2 THEN at END) AS at2
FROM (
SELECT Id,
dbo.#mytable.ArrivalDateTime AS at,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ArrivalDateTime) rn
FROM #mytable) x
group by id)
SELECT Id,
at1,
at2,
DATEDIFF(hh, at1, at2) AS hrdiff
FROM pt
WHERE(DATEDIFF(hh, at1, at2) < 72);
DROP TABLE #mytable
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 27, 2016 at 3:09 pm
valeryk2000 (9/27/2016)
I do not know what would be the better way to present a simple table with 7K+ records (Excel file). The table has 2 columns, ID and Arrival DateTime. Sorted by ID and then by Arrival DateTime. In the past I wrote a code with double cursors iterating over each ID (not for this table and not for this task). I thought that there may be a better way to do it.Thanks
Now, based on the sample data, what should the results look like?
September 27, 2016 at 4:32 pm
Now each pair where the difference between two dates is less than 72 hrs will appear in a new table or result set
Thanks
September 27, 2016 at 4:36 pm
Thank you. I understand and, as you can see above, provided an sql version of the table. And thank you for your code - I'll test it tomorrow (now out of the network)
September 27, 2016 at 6:31 pm
Here are two solutions... The 1st will work in 2008 & 2008R2. The 2nd will work with 2012 and above...
Note: The full script, including the creation & population of test data is attached as well.
-- ======================================================================
-- ======================================================================
-- 2008 & 2008R2 version --
IF OBJECT_ID('tempdb..#temp', 'U') IS NOT NULL
DROP TABLE #temp;
SELECT
a.ID,
a.ArrivalDateTime,
rn = ROW_NUMBER() OVER (PARTITION BY a.ID ORDER BY a.ArrivalDateTime)
INTO #temp
FROM
#Appointments a;
SELECT
t1.ID,
PrevDate = t2.ArrivalDateTime,
t1.ArrivalDateTime,
TimeSincePrevDate = DATEDIFF(hh, t2.ArrivalDateTime, t1.ArrivalDateTime)
FROM
#temp t1
JOIN #temp t2
ON t1.ID = t2.ID
AND t1.rn = t2.rn + 1
WHERE
DATEDIFF(hh, t2.ArrivalDateTime, t1.ArrivalDateTime) < 72
ORDER BY
t1.id,
t1.ArrivalDateTime;
-- ======================================================================
-- ======================================================================
-- 2012 & up version --
WITH
cte_GetPrev AS (
SELECT
a.ID,
PrevDate = LAG(a.ArrivalDateTime, 1) OVER (PARTITION BY a.ID ORDER BY a.ArrivalDateTime),
a.ArrivalDateTime,
TimeSincePrevDate = DATEDIFF(hh, LAG(a.ArrivalDateTime, 1) OVER (PARTITION BY a.ID ORDER BY a.ArrivalDateTime), a.ArrivalDateTime)
FROM
#Appointments a
)
SELECT
gp.ID,
gp.PrevDate,
gp.ArrivalDateTime,
gp.TimeSincePrevDate
FROM
cte_GetPrev gp
WHERE
gp.TimeSincePrevDate < 72;
September 27, 2016 at 7:48 pm
Thank you. However the task may be more complicated: in some cases there is more than 2 IDs (check 10011580, 10014918, just for example, in my table posted @ 10:05:34)
September 27, 2016 at 8:07 pm
valeryk2000 (9/27/2016)
Thank you. However the task may be more complicated: in some cases there is more than 2 IDs (check 10011580, 10014918, just for example, in my table posted @ 10:05:34)
I just assumed that was just bad data and filtered it out by adding "WITH (IGNORE_DUP_KEY = ON)"...
Aside from violating the laws of physics (it's impossible to "arrive" twice at the exact same time), it doesn't complicate anything for either of the two solutions. just remove the PK constraint and rerun it... 10501108, 11320410 & 10042522 (neither 10011580 nor 10014918 had dupe dates in your sample data :-P) will show up having TimeSincePrevDate = 0.
September 28, 2016 at 3:18 am
valeryk2000 (9/27/2016)
This is the fragment of the table
CREATE TABLE [dbo].[Readmission_Table](
[ID] [float] NULL,
[ArrivalDateTime] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10000683, CAST(0x0000A66801716A50 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10000683, CAST(0x0000A66900B9AB40 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10000989, CAST(0x0000A64000B778C0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10000989, CAST(0x0000A65101735680 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10001033, CAST(0x0000A65200F54510 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10001033, CAST(0x0000A66200B91EA0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10001350, CAST(0x0000A64D01526100 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10001350, CAST(0x0000A66F007055D0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10002866, CAST(0x0000A63B01305240 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10002866, CAST(0x0000A63E014D6F60 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10003932, CAST(0x0000A65800C27540 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10003932, CAST(0x0000A66201422C90 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10004218, CAST(0x0000A665013357B0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10004218, CAST(0x0000A66A00EC34C0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10004220, CAST(0x0000A662006D5060 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10004220, CAST(0x0000A66E0072CEA0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10005862, CAST(0x0000A66000A06680 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10005862, CAST(0x0000A662009EC0A0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10006299, CAST(0x0000A64000CC5880 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10006299, CAST(0x0000A645014272E0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10007852, CAST(0x0000A64F00563E20 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10007852, CAST(0x0000A65600BE1040 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10008474, CAST(0x0000A65500163F50 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10008474, CAST(0x0000A66F00503340 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009346, CAST(0x0000A6470130DEE0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009346, CAST(0x0000A64E009A5BA0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009346, CAST(0x0000A651017BDA30 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009903, CAST(0x0000A64E0123AFE0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009903, CAST(0x0000A659015752A0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009903, CAST(0x0000A65A0008CA00 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10010401, CAST(0x0000A64E00549840 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10010401, CAST(0x0000A65600305700 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10011580, CAST(0x0000A63E015798F0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10011580, CAST(0x0000A6570126B550 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10011580, CAST(0x0000A65D0105BFD0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10012712, CAST(0x0000A64E00AB1CB0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10012712, CAST(0x0000A65D00041EB0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10012887, CAST(0x0000A64001586BE0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10012887, CAST(0x0000A66901236990 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10014218, CAST(0x0000A63A00C6DA40 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10014218, CAST(0x0000A64B00B80560 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10014918, CAST(0x0000A63D01731030 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10014918, CAST(0x0000A64000C9DFB0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10014918, CAST(0x0000A6490123F630 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10015000, CAST(0x0000A63D00CAF8F0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10015000, CAST(0x0000A65701388FA0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10015401, CAST(0x0000A665015BB7A0 AS DateTime))
INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10015401, CAST(0x0000A669015D1730 AS DateTime))
Since this is SQL 2008, you will need a triangular join to access the PREVIOUS/NEXT rows of data.
WITH cteWindow AS (
SELECT
m.ID, m.ArrivalDateTime
, rn = ROW_NUMBER() OVER (PARTITION BY m.ID ORDER BY m.ArrivalDateTime)
, EndOf72HrWindow = DATEADD(HOUR, 72, m.ArrivalDateTime)
FROM [dbo].[Readmission_Table] AS m
)
, cteIsInWindow AS (
SELECT curr.ID, curr.ArrivalDateTime
, IsInPrev72HrWindow = CASE WHEN curr.ArrivalDateTime < prv.EndOf72HrWindow THEN 1 ELSE 0 END
, IsInNext72HrWindow = CASE WHEN curr.EndOf72HrWindow > nxt.ArrivalDateTime THEN 1 ELSE 0 END
, PrevArrivalDateTime = prv.ArrivalDateTime
, NextArrivalDateTime = nxt.ArrivalDateTime
FROM cteWindow AS curr
LEFT JOIN cteWindow AS prv
ON curr.ID = prv.ID
AND curr.rn = prv.rn +1
LEFT JOIN cteWindow AS nxt
ON curr.ID = nxt.ID
AND curr.rn = nxt.rn -1
)
SELECT cte.ID, cte.ArrivalDateTime
FROM cteIsInWindow AS cte
WHERE IsInPrev72HrWindow = 1
OR IsInNext72HrWindow = 1
ORDER BY cte.ID, cte.ArrivalDateTime;
September 28, 2016 at 5:01 am
Oh, gosh, so much SQL...
So complicated code is not needed at all, if we think about the task.
We need to find which admissions have following up re-admissions of the same patient within 72 hours from the time of the admission.
OK.
Let's do it step by step.
1. We need to find out what is the 72 hors time span for each admission recordedin the table:
select A1.[ID], A1.[ArrivalDateTime], DATEADD(hh, 72, A1.[ArrivalDateTime]) TimeSpan
from [dbo].[Readmission_Table] A1
Done.
2. We need to find every other admissions for the same patient (same ID) with ArrivalDateTime within that time span.
I guess a simple INNER JOIN would do:
select A1.[ID], A1.[ArrivalDateTime], AN.ArrivalDateTime NextAdmissionDatetime
from [dbo].[Readmission_Table] A1
INNER JOIN [dbo].[Readmission_Table] AN ON A1.ID = AN.ID
and AN.ArrivalDateTime > A1.ArrivalDateTime
and AN.ArrivalDateTime <= DATEADD(hh, 72, A1.[ArrivalDateTime])
Order by 1,2,3
3. Hold on... We're done!
Or, probably, you want to see only 1st of readmissions, not all which fall within 72 hours.
In such case we need MIN of NextAdmissionDatetime:
select A1.[ID], A1.[ArrivalDateTime], MIN(AN.ArrivalDateTime) FirstReAdmissionWithin72hours
from [dbo].[Readmission_Table] A1
INNER JOIN [dbo].[Readmission_Table] AN ON A1.ID = AN.ID
and AN.ArrivalDateTime > A1.ArrivalDateTime and AN.ArrivalDateTime <= DATEADD(hh, 72, A1.[ArrivalDateTime])
group by A1.[ID], A1.[ArrivalDateTime]
order by 1,2
Easy, yeah?:hehe:
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 52 total)
You must be logged in to reply to this topic. Login to reply