Solved

How to find the difference between dates within the same column using Netezza

Posted on 2014-11-19
7
397 Views
Last Modified: 2014-11-23
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
Comment
Question by:moe57
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40454810
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40454962
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
 

Author Comment

by:moe57
ID: 40456503
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 49

Expert Comment

by:PortletPaul
ID: 40456504
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
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 40456516
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40456518
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
 
LVL 35

Expert Comment

by:johnsone
ID: 40457428
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question