using date-diff function in PostgreSQL

Question

I was using the query below in sql server and it worked fine but now we started using Netezza and the query stopped working. I am suspecting that datediff function does not work in NZ. I am new in Netezza - please help. Here is the complete query

SET NOCOUNT ON
GO

    DECLARE @DataSource TABLE
    (
         [PATIENT_ID] TINYINT
        ,[ADMIT_DATE] DATE
        ,[LOCATION] VARCHAR(3)
    )

    INSERT INTO @DataSource ([PATIENT_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 TempDataSource ([PATIENT_ID], [ADMIT_DATE], [LOCATION], [Rank])  AS
(
    SELECT [PATIENT_ID]
          ,[ADMIT_DATE]
          ,[LOCATION]
          ,ROW_NUMBER() OVER (PARTITION BY [PATIENT_ID], [LOCATION] ORDER BY [ADMIT_DATE] ASC)
    FROM @DataSource    
)

,DataSource ([PATIENT_ID], [ADMIT_DATE], [LOCATION], [DIFF_IN_HOURS]) AS
(
SELECT DS1.[PATIENT_ID]
      ,DS1.[ADMIT_DATE]
      ,DS1.[LOCATION]
      ,DATEDIFF(HOUR, DS2.[ADMIT_DATE], DS1.[ADMIT_DATE])
FROM TempDataSource DS1
LEFT JOIN TempDataSource DS2
    ON DS1.[Rank] - 1 = DS2.[Rank]
    AND DS1.[PATIENT_ID] = DS2.[PATIENT_ID]
    AND DS1.[LOCATION] = DS2.[LOCATION]
)
SELECT [PATIENT_ID]
      ,[ADMIT_DATE]
      ,[LOCATION]
      ,ROW_NUMBER() OVER (PARTITION BY [PATIENT_ID], [LOCATION] ORDER BY [ADMIT_DATE] ASC)
FROM DataSource
WHERE [DIFF_IN_HOURS] >= 48

Answer 1

This is how I would convert the SQL Server code to work in Netezza

    create temp table tmp_DataSource
    (
         PATIENT_ID smallint
        ,ADMIT_DATE timestamp
        ,LOCATION VARCHAR(3)
    );

    INSERT INTO tmp_DataSource(PATIENT_ID, ADMIT_DATE, LOCATION) VALUES (33, '1-10-2014 00:00:00', 'ER');
    INSERT INTO tmp_DataSource(PATIENT_ID, ADMIT_DATE, LOCATION) VALUES (33, '1-11-2014 00:01:00', 'ER');
    INSERT INTO tmp_DataSource(PATIENT_ID, ADMIT_DATE, LOCATION) VALUES (33, '1-15-2014', 'ER');
    INSERT INTO tmp_DataSource(PATIENT_ID, ADMIT_DATE, LOCATION) VALUES (33, '1-17-2014', 'ER');
    INSERT INTO tmp_DataSource(PATIENT_ID, ADMIT_DATE, LOCATION) VALUES (45, '2-15-2014', 'OBS');
    INSERT INTO tmp_DataSource(PATIENT_ID, ADMIT_DATE, LOCATION) VALUES (45, '2-16-2014', 'OBS');
    INSERT INTO tmp_DataSource(PATIENT_ID, ADMIT_DATE, LOCATION) VALUES (45, '2-20-2014', 'OBS');
    INSERT INTO tmp_DataSource(PATIENT_ID, ADMIT_DATE, LOCATION) VALUES (45, '2-25-2014', 'OBS');
    INSERT INTO tmp_DataSource(PATIENT_ID, ADMIT_DATE, LOCATION) VALUES (45, '2-27-2014', 'OBS');

create temp table TempDataSource as
    SELECT PATIENT_ID
          ,ADMIT_DATE
          ,LOCATION
          ,ROW_NUMBER() OVER (PARTITION BY PATIENT_ID, LOCATION ORDER BY ADMIT_DATE ASC) as rank
    FROM tmp_DataSource;

create temp table DataSource as
SELECT DS1.PATIENT_ID
      ,DS1.ADMIT_DATE
      ,DS1.LOCATION
      ,extract(epoch  from (DS1.ADMIT_DATE - DS2.ADMIT_DATE))/3600.0 as DIFF_IN_HOURS

FROM TempDataSource DS1
LEFT JOIN TempDataSource DS2
    ON DS1.Rank - 1 = DS2.Rank
    AND DS1.PATIENT_ID = DS2.PATIENT_ID
    AND DS1.LOCATION = DS2.LOCATION;

SELECT PATIENT_ID
      ,ADMIT_DATE
      ,LOCATION
      ,ROW_NUMBER() OVER (PARTITION BY PATIENT_ID, LOCATION ORDER BY ADMIT_DATE ASC)
FROM DataSource
WHERE DIFF_IN_HOURS >= 48;