none
Date - Netezza ---SQL Query RRS feed

  • Question

  • Check if order ID is in_business or out of business  -->Logic is nvl(Actual end_date, End date)> Todays date -->then in_business ELSE Out of business.

    For IN_Business orders

    metric-1 -->1 If start date - todays date(Now()) is <= 90 then '3M'      -->O/P : Count ( orderno) 
    metric-2 -->2 If start date - todays date is > 90 &<=365 then '3-12 M'--> O/P : Count ( orderno) 
    Metric-3-->3 If start date - todays date is > 365 then '>12M' -->O/P : Count ( orderno) 
    Metric-4-->4 if nvl(Actual end_date, End date) - todays date <= 90 days THEN "Leaving soon" -O/P : Count ( orderno) 

    Out of business orders

    Metric 5 -->5 Check if the nvl(Actual End_date, End date) is > 12 months from today  - O/P : count (orderno)

    Metric 6 -->6 Check if the nvl(Actual End_date, End date) is < 12 months from today - O/p: count (orderno)

    please provide the case logic for the above 6 metrics

    Tuesday, October 13, 2020 10:45 PM

Answers

  • Hi Fareed37,
    To avoid misunderstanding, could you explain the following questions in detail?
     >>-->O/P : Count ( orderno) 
    what does this statement mean? I can't find "orderno" in your table.
    >> If start date - todays date(Now()) is <= 90
    Do you want this statement to express that the number of days from start date to the present time is less than 90 days?
    If so, please refer to this thread to get days in Netezza.
    I made a test in sql server and "NVL" is not supported in sql server, so I use ISNULL to replace NVL which is equivalent.
    Meanwhile, I use GETDATE()  in sql server which is equivalent to Now() and DATEDIFF() to get days between two date.
    Please note the above mentioned and I can't do a test in Netezza.
    Here is a my test sql statement you can refer to. (In test, My time now is "10/14/2020")

    select b.Order_Id,b.Start_date,b.[Actual end_date],b.[End date],c.u as DerivedColumn, d.v as ReferenceComments from Business b
    inner join 
    (
      select Order_Id,Start_date,[Actual end_date],[End date],
       Case 
            --Check if order ID is in_business or out of business
            when isnull([Actual end_date], [End date] )>GETDATE() then 'in_business'
            when isnull([Actual end_date],0) =0 and isnull([End date],0)=0 then ' this one We have to ignore'
           else 'Out of business'
    		End as u
      from Business
    )c
    on b.Order_Id=c.Order_Id
    inner join 
    (
      select Order_Id,Start_date,[Actual end_date],[End date],
       Case  
            --For IN_Business orders
            when isnull([Actual end_date], [End date] )>GETDATE() and DATEDIFF(day,Start_date,GETDATE())<= 90 then '3M'
            when isnull([Actual end_date], [End date] )>GETDATE() and DATEDIFF(day,Start_date,GETDATE()) > 90 and DATEDIFF(day,Start_date,GETDATE()) <=365 then '3-12M'
            when isnull([Actual end_date], [End date] )>GETDATE() and DATEDIFF(day,Start_date,GETDATE()) >365 then '>12M'
    		when isnull([Actual end_date], [End date] )>GETDATE() and DATEDIFF(day,GETDATE(),isnull([Actual end_date], [End date] ))<= 90 then 'Leaving soon'
    		when isnull([Actual end_date],0) =0 and isnull([End date],0)=0 then 'ignore'
    		--Out of business orders
    		when isnull([Actual end_date], [End date] )<=GETDATE() and DATEDIFF(month,GETDATE(),isnull([Actual end_date], [End date] ))<= 12 then 'satisfies...'
    		when isnull([Actual end_date], [End date] )<=GETDATE()and  DATEDIFF(month,GETDATE(),isnull([Actual end_date], [End date] ))<= 12 then 'satisfies...'
    		End as v
      from Business
    )d
    on b.Order_Id=d.Order_Id

    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, October 14, 2020 6:29 AM
  • Thank you!!

    Answer to your question :

    To avoid misunderstanding, could you explain the following questions in detail?
     >>-->O/P : Count ( orderno)  
    what does this statement mean? I can't find "orderno" in your table.

    ANS : Sorry its Order_Id
    >> If start date - todays date(Now()) is <= 90
    Do you want this statement to express that the number of days from start date to the present time is less than 90 days?

    yes you are correct.

    I was just testing with this below piece of case statement. the logic looks good but DATEDIFF( day,start_date,getdate())<=90 when I run in netezza it says "Error: Attribute day not found"

    What function would be the best for DATEDIFF in Netezza please.

    CASE when isnull([Actual end_date], [End date] )>GETDATE() and DATEDIFF(day,Start_date,GETDATE())<= 90 then '3M'

    • Marked as answer by Fareed37 Thursday, October 15, 2020 5:39 AM
    Wednesday, October 14, 2020 8:31 AM
  • Hi Fareed37,
    You can try to use days_between() function.
    The days_between() function has the following syntax:

    int = days_between(timestamp t1, timestamp t2);

    The t1 value specifies the beginning time stamp.
    The t2 value specifies the ending time stamp.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Fareed37 Thursday, October 15, 2020 5:39 AM
    Wednesday, October 14, 2020 8:47 AM
  • Hi Daniel

    Your CASE statements logic and using days between and Months between thoughts are marvellous. I could able to sort out all my metrics. thanks a ton!!!

    marking as closed.

    Thanks

    Fareed

    • Marked as answer by Fareed37 Thursday, October 15, 2020 5:39 AM
    Thursday, October 15, 2020 5:38 AM

All replies

  • Hi Fareed37,
    To avoid misunderstanding, could you explain the following questions in detail?
     >>-->O/P : Count ( orderno) 
    what does this statement mean? I can't find "orderno" in your table.
    >> If start date - todays date(Now()) is <= 90
    Do you want this statement to express that the number of days from start date to the present time is less than 90 days?
    If so, please refer to this thread to get days in Netezza.
    I made a test in sql server and "NVL" is not supported in sql server, so I use ISNULL to replace NVL which is equivalent.
    Meanwhile, I use GETDATE()  in sql server which is equivalent to Now() and DATEDIFF() to get days between two date.
    Please note the above mentioned and I can't do a test in Netezza.
    Here is a my test sql statement you can refer to. (In test, My time now is "10/14/2020")

    select b.Order_Id,b.Start_date,b.[Actual end_date],b.[End date],c.u as DerivedColumn, d.v as ReferenceComments from Business b
    inner join 
    (
      select Order_Id,Start_date,[Actual end_date],[End date],
       Case 
            --Check if order ID is in_business or out of business
            when isnull([Actual end_date], [End date] )>GETDATE() then 'in_business'
            when isnull([Actual end_date],0) =0 and isnull([End date],0)=0 then ' this one We have to ignore'
           else 'Out of business'
    		End as u
      from Business
    )c
    on b.Order_Id=c.Order_Id
    inner join 
    (
      select Order_Id,Start_date,[Actual end_date],[End date],
       Case  
            --For IN_Business orders
            when isnull([Actual end_date], [End date] )>GETDATE() and DATEDIFF(day,Start_date,GETDATE())<= 90 then '3M'
            when isnull([Actual end_date], [End date] )>GETDATE() and DATEDIFF(day,Start_date,GETDATE()) > 90 and DATEDIFF(day,Start_date,GETDATE()) <=365 then '3-12M'
            when isnull([Actual end_date], [End date] )>GETDATE() and DATEDIFF(day,Start_date,GETDATE()) >365 then '>12M'
    		when isnull([Actual end_date], [End date] )>GETDATE() and DATEDIFF(day,GETDATE(),isnull([Actual end_date], [End date] ))<= 90 then 'Leaving soon'
    		when isnull([Actual end_date],0) =0 and isnull([End date],0)=0 then 'ignore'
    		--Out of business orders
    		when isnull([Actual end_date], [End date] )<=GETDATE() and DATEDIFF(month,GETDATE(),isnull([Actual end_date], [End date] ))<= 12 then 'satisfies...'
    		when isnull([Actual end_date], [End date] )<=GETDATE()and  DATEDIFF(month,GETDATE(),isnull([Actual end_date], [End date] ))<= 12 then 'satisfies...'
    		End as v
      from Business
    )d
    on b.Order_Id=d.Order_Id

    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, October 14, 2020 6:29 AM
  • Thank you!!

    Answer to your question :

    To avoid misunderstanding, could you explain the following questions in detail?
     >>-->O/P : Count ( orderno)  
    what does this statement mean? I can't find "orderno" in your table.

    ANS : Sorry its Order_Id
    >> If start date - todays date(Now()) is <= 90
    Do you want this statement to express that the number of days from start date to the present time is less than 90 days?

    yes you are correct.

    I was just testing with this below piece of case statement. the logic looks good but DATEDIFF( day,start_date,getdate())<=90 when I run in netezza it says "Error: Attribute day not found"

    What function would be the best for DATEDIFF in Netezza please.

    CASE when isnull([Actual end_date], [End date] )>GETDATE() and DATEDIFF(day,Start_date,GETDATE())<= 90 then '3M'

    • Marked as answer by Fareed37 Thursday, October 15, 2020 5:39 AM
    Wednesday, October 14, 2020 8:31 AM
  • Hi Fareed37,
    You can try to use days_between() function.
    The days_between() function has the following syntax:

    int = days_between(timestamp t1, timestamp t2);

    The t1 value specifies the beginning time stamp.
    The t2 value specifies the ending time stamp.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Fareed37 Thursday, October 15, 2020 5:39 AM
    Wednesday, October 14, 2020 8:47 AM
  • Hi Daniel

    Your CASE statements logic and using days between and Months between thoughts are marvellous. I could able to sort out all my metrics. thanks a ton!!!

    marking as closed.

    Thanks

    Fareed

    • Marked as answer by Fareed37 Thursday, October 15, 2020 5:39 AM
    Thursday, October 15, 2020 5:38 AM