Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

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

0
moe57
Asked:
moe57
2 Solutions
 
Phillip BurtonCommented:
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
 
PortletPaulCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now