moe57
asked on
How to find the difference between dates within the same column using Netezza
I am trying to solve the following challenge:
1) If a patient visits the ER within 48 hours, I want to flag that as 1.
2) If the same patient visits the ER again after 48 hours, I want to flag that as 2.
3) Each subsequent visit must be flagged as 3, 4, 5 etcetera after the first 48 hours.
the sql code below works fine but we are now using netezza database and the code below does not work in netezza so i would like to modify it so it can work in netezza. I believe the main thing that does not work here is the datediff function here. here is my sql code:
1) If a patient visits the ER within 48 hours, I want to flag that as 1.
2) If the same patient visits the ER again after 48 hours, I want to flag that as 2.
3) Each subsequent visit must be flagged as 3, 4, 5 etcetera after the first 48 hours.
the sql code below works fine but we are now using netezza database and the code below does not work in netezza so i would like to modify it so it can work in netezza. I believe the main thing that does not work here is the datediff function here. here is my sql code:
SET NOCOUNT ON
GO
DECLARE @DataSource TABLE
(
[ATIENT_ID] TINYINT
,[ADMIT_DATE] DATE
,[LOCATION] VARCHAR(3)
)
INSERT INTO @DataSource ([ATIENT_ID], [ADMIT_DATE], [LOCATION])
VALUES (33, '1-10-2014', 'ER')
,(33, '1-11-2014', 'ER')
,(33, '1-15-2014', 'ER')
,(33, '1-17-2014', 'ER')
,(45, '2-15-2014', 'OBS')
,(45, '2-16-2014', 'OBS')
,(45, '2-20-2014', 'OBS')
,(45, '2-25-2014', 'OBS')
,(45, '2-27-2014', 'OBS')
;WITH DataSource ([ATIENT_ID], [ADMIT_DATE], [LOCATION], [DIFF_IN_HOURS]) AS
(
SELECT [ATIENT_ID]
,[ADMIT_DATE]
,[LOCATION]
,DATEDIFF(
HOUR
,LAG([ADMIT_DATE], 1, NULL) OVER (PARTITION BY [ATIENT_ID], [LOCATION] ORDER BY [ADMIT_DATE] ASC)
,[ADMIT_DATE]
)
FROM @DataSource
)
SELECT [ATIENT_ID]
,[ADMIT_DATE]
,[LOCATION]
,ROW_NUMBER() OVER (PARTITION BY [ATIENT_ID], [LOCATION] ORDER BY [ADMIT_DATE] ASC)
FROM DataSource
WHERE [DIFF_IN_HOURS] >= 48
OR [DIFF_IN_HOURS] IS NULL -- these are first records
SET NOCOUNT OFF
GO
Netazza has an HOURS_BETWEEN() function
and it does support ROW_NUMBER() OVER() as well
(but could not locate the documentation link start here
https://www-304.ibm.com/support/knowledgecenter/SSULQD_7.1.0/com.ibm.nz.dbu.doc/c_dbuser_ntz_sql_analytic_funcs.html)
select hours_between('1996-02-27 06:12:33' , '1996-03-01 07:12:33');see: https://www-304.ibm.com/support/knowledgecenter/SSULQD_7.1.0/com.ibm.nz.sqltk.doc/r_sqlext_hours_between.html
and it does support ROW_NUMBER() OVER() as well
(but could not locate the documentation link start here
https://www-304.ibm.com/support/knowledgecenter/SSULQD_7.1.0/com.ibm.nz.dbu.doc/c_dbuser_ntz_sql_analytic_funcs.html)
ASKER
I've requested that this question be deleted for the following reason:
this question is not answered and the link provided is not helpful.
this question is not answered and the link provided is not helpful.
The questions asks "How to find the difference between dates within the same column using Netezza"
The detail of the question shows that the difference wanted is in hours.
The function in Netezza to produce the difference between 2 timestamps is HOURS_BETWEEN()
The question is answered. How is that not useful?
The detail of the question shows that the difference wanted is in hours.
The function in Netezza to produce the difference between 2 timestamps is HOURS_BETWEEN()
The question is answered. How is that not useful?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Netezza also supports directly subtracting 2 dates. It works the same way that Oracle does in that it returns a number of days.
1. It may be the DataDiff, but
2. It may be the Lag function.
Do you know if Netezza has the lag function? If it has the DataDiff function, do you know whether it has the same syntax as SQL Server?