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:
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

Open in new window

moe57Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I don't know Netezza, but looking at the code:

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?
0
PortletPaulfreelancerCommented:
Netazza has an HOURS_BETWEEN() function

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)
0
moe57Author Commented:
I've requested that this question be deleted for the following reason:

this question is not answered and the link provided is not helpful.  
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PortletPaulfreelancerCommented:
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?
0
PortletPaulfreelancerCommented:
moe57

It is normal to ask for further help if something is offered that you don't find useful. I don't have access to Netezza so I cannot test anything I provide but in essence you need to substitute DATEDIFF() with HOURS_BETWEEN(). There is a slight difference in the parameters where HOURS_BETWEEN() only needs 2 whereas DATEDIFF() requires 3.

As far as I know the balance of the query should be OK once the TSQL specific syntax is removed.  In particular
WITH ... AS ( )
LAG()
ROW_NUMBER()
OVER()

should all be available in Netezza

UNTESTED!
WITH DATASOURCE ([ATIENT_ID], [ADMIT_DATE], [LOCATION], [DIFF_IN_HOURS])
AS (
      SELECT
            [ATIENT_ID]
          , [ADMIT_DATE]
          , [LOCATION]
          , HOURS_BETWEEN(
              LAG([ADMIT_DATE], 1, NULL) OVER (PARTITION BY [ATIENT_ID], [LOCATION] ORDER BY [ADMIT_DATE] ASC)
            , [ADMIT_DATE]
            )
      FROM SourceTable /* from the source table, whatever that is */
)
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

Open in new window

0
PortletPaulfreelancerCommented:
if WITH ... AS () does prove to be a problem then try a derived table (nested subquery) instead:
SELECT
      [ATIENT_ID]
    , [ADMIT_DATE]
    , [LOCATION]
    , ROW_NUMBER() OVER (PARTITION BY [ATIENT_ID], [LOCATION] ORDER BY [ADMIT_DATE] ASC)
FROM (
      SELECT
            [ATIENT_ID]
          , [ADMIT_DATE]
          , [LOCATION]
          , HOURS_BETWEEN(
              LAG([ADMIT_DATE], 1, NULL) OVER (PARTITION BY [ATIENT_ID], [LOCATION] ORDER BY [ADMIT_DATE] ASC)
            , [ADMIT_DATE]
            )
      FROM SourceTable /* from the source table, whatever that is */
     ) DATASOURCE
WHERE [DIFF_IN_HOURS] >= 48
OR [DIFF_IN_HOURS] IS NULL -- these are first records

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
Netezza also supports directly subtracting 2 dates.  It works the same way that Oracle does in that it returns a number of days.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.