-1

I really need your help with the following query. I have the following query:

SELECT 
Country,
CRM_OBJ_ID as "Service_Order_ID",
"CRM_NUMINT" as "Service_Order_Line",
nullif(CRM_CRD_AT,'') as "Service_Order_Creation_Date",
--nullif(ZDAT_PO,'') as "Part_Order_Release_Date",

NULLIF(ERDAT,'') as "Item_Creation_Date",
NULLIF("SHPFR_DATE",'') as "Shipped_Date",
NULLIF("ACT_DATE",'') as "Delivered_Date",

DATEDIFF(day,iif(max("ACT_DATE") over (PARTITION BY CRM_OBJ_ID)='',
                    IIF(max("SHPFR_DATE") over(PARTITION BY CRM_OBJ_ID)='',
                        IIF(max(ERDAT) over(PARTITION BY CRM_OBJ_ID) is null,max("CRM_CRD_AT") over(PARTITION BY CRM_OBJ_ID),max(ERDAT) over(PARTITION BY CRM_OBJ_ID)),                             
                    max("SHPFR_DATE") over(PARTITION BY CRM_OBJ_ID)),
                max("ACT_DATE") over (PARTITION BY CRM_OBJ_ID)),
        GETDATE()) as dif

FROM ZOH_SVS43_IDL

LEFT JOIN ZO_SDA05_IDL ON ZOH_SVS43_IDL.CRM_OBJ_ID=ZO_SDA05_IDL.VBELN and ZOH_SVS43_IDL.CRM_NUMINT=ZO_SDA05_IDL.POSNR

where CRM_OBJ_ID in ('7008097791','7007987492','7008002892','7008097849')

Group by Country,CRM_OBJ_ID,CRM_NUMINT,CRM_CRD_AT,ZDAT_OCMP,ERDAT,SHPFR_DATE,ACT_DATE,CSM_CLDA,ZDAT_PO

Order by Country,CRM_OBJ_ID,CRM_NUMINT

and the result is

Country service_order   Line    Create_Date Item_Creation_Date  Shipped_Date    Delivered_Date  Today() Dif
CL  7007987492  10  5/2/2017    NULL    NULL    NULL    7/8/2017    65
CL  7007987492  20  5/2/2017    5/2/2017    5/3/2017    5/5/2017    7/8/2017    65
MX  7008002892  0   5/11/2017   NULL    NULL    NULL    7/8/2017    41
MX  7008002892  20  5/11/2017   5/29/2017   NULL    NULL    7/8/2017    41
MX  7008097791  10  7/4/2017    7/4/2017    NULL    NULL    7/8/2017    4
MX  7008097791  20  7/4/2017    7/4/2017    7/5/2017    NULL    7/8/2017    4
MX  7008097849  10  7/4/2017    NULL    NULL    NULL    7/8/2017    NULL

What I´m trying to do is calculate the difference btw dates , for the last status - today.

The logic is, if the Delivered is null, look for Shipped, if its null, look for, Item creation date, if this field is null, look for creation date.

The correct result must be

Country service_order   Line    Create_Date Item_Creation_Date  Shipped_Date    Delivered_Date  Today() Dif
CL  7007987492  10  5/2/2017    NULL    NULL    NULL    7/8/2017    65
CL  7007987492  20  5/2/2017    5/2/2017    5/3/2017    5/5/2017    7/8/2017    65
MX  7008002892  0   5/11/2017   NULL    NULL    NULL    7/8/2017    41
MX  7008002892  20  5/11/2017   5/29/2017   NULL    NULL    7/8/2017    41
MX  7008097791  10  7/4/2017    7/4/2017    NULL    NULL    7/8/2017    4
MX  7008097791  20  7/4/2017    7/4/2017    7/5/2017    NULL    7/8/2017    4
MX  7008097849  10  7/4/2017    NULL    NULL    NULL    7/8/2017    4

Any guide or answer will be really appreciate, thank you in advanced.

Roger Bernal
  • 3
  • 3
  • Give your sample data and expected output in text format – Utsav Jul 09 '17 at 02:15
  • Hi @Utsav for your quickly response, this is my sample data and actual result – Roger Bernal Jul 09 '17 at 03:06
  • As you could see the error is on line '7008097849' on column "dif", it´s NULL and must be "4" Thanks and waiting for your comments – Roger Bernal Jul 09 '17 at 03:08
  • 1
    please don't post your code as a comment. It is not readable. update your post with your code by editing it. – OLIVER.KOO Jul 09 '17 at 03:18
  • @Utsav,@O.KOO, done, please let me know if it´s ok – Roger Bernal Jul 09 '17 at 07:07
  • Clearly that is not ok -- that is html -- how do you expect anyone to read that? Post as plain text -- indent four spaces to get a monospaced font and look at the bloody thing to see if it is readable. What you posted here is like you are trolling the people that are trying to help you. – Hogan Jul 09 '17 at 17:32
  • @Hogan, I´m so sorry It's not my intention to offend anyone , I hope the information is now understandable thank you – Roger Bernal Jul 09 '17 at 19:12

1 Answers1

0

According to your stated logic on order of precedence for date column usage to get the date diff, you can use the COALESCE function to get the 1st non-null value.

Using your "correct" result set, I inserted those values into a temp table as quick way to show how the COALESCE function would be used with DATEDIFF. Here's the example script:

IF OBJECT_ID('tempdb..#Orders') IS NOT NULL
   BEGIN
      DROP TABLE #ORders;
   END;

CREATE TABLE #Orders
   (
      Country            VARCHAR(2)
    , service_order      BIGINT
    , Line               INT
    , Create_Date        DATE
    , Item_Creation_Date DATE
    , Shipped_Date       DATE
    , Delivered_Date     DATE
    , Today              DATE
   );

INSERT INTO #Orders (
                       Country
                     , service_order
                     , Line
                     , Create_Date
                     , Item_Creation_Date
                     , Shipped_Date
                     , Delivered_Date
                     , Today
                    )
VALUES ('CL', 7007987492, 10, '5/2/2017', NULL, NULL, NULL, '7/8/2017')
     , ('CL', 7007987492, 20, '5/2/2017', '5/2/2017', '5/3/2017', '5/5/2017', '7/8/2017')
     , ('MX', 7008002892, 0, '5/11/2017', NULL, NULL, NULL, '7/8/2017')
     , ('MX', 7008002892, 20, '5/11/2017', '5/29/2017', NULL, NULL, '7/8/2017')
     , ('MX', 7008097791, 10, '7/4/2017', '7/4/2017', NULL, NULL, '7/8/2017')
     , ('MX', 7008097791, 20, '7/4/2017', '7/4/2017', '7/5/2017', NULL, '7/8/2017')
     , ('MX', 7008097849, 10, '7/4/2017', NULL, NULL, NULL, '7/8/2017');

SELECT O.Country
     , O.service_order
     , O.Line
     , O.Create_Date
     , O.Item_Creation_Date
     , O.Shipped_Date
     , O.Delivered_Date
     , O.Today
     , FirsNonNullDate = COALESCE(O.Delivered_Date, O.Shipped_Date, O.Item_Creation_Date, O.Create_Date)
     , DateDiffInDays  = DATEDIFF(DAY, COALESCE(O.Delivered_Date, O.Shipped_Date, O.Item_Creation_Date, O.Create_Date), Today)
FROM   #Orders AS O;

This results into what is shown in the screen shot below. Hope this helps you.

enter image description here

fausto.io
  • 106
  • 5