Solved

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

Posted on 2014-11-19
7
254 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
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 48

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 48

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 48

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 48

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 34

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now