Forum Stats

  • 3,827,255 Users
  • 2,260,751 Discussions
  • 7,897,192 Comments

Discussions

SQL PROBLEM: checking shipments from yesterday, today and tomorrow with exceptions on Monday and Fri

Bjornlaat
Bjornlaat Member Posts: 20 Green Ribbon

is it possible to create a data model in BI publisher (OTM) that looks at the shipments of: 

  • yesterdays date
  • today date
  • tomorrow date

With the exception that on Friday instead of looking at tomorrow we look at next Monday and on Monday instead of looking at yesterday we look at last Friday.


The code that we have so far to look at the shipments for yesterday, today and tomorrow is, but we would like to put in the exception. Can anyone help us with this? thanks in advance for the help!!!

SELECT 

S.SHIPMENT_GID SHIPMENT

FROM

SHIPMENT S,

SHIPMENT_INVOLVED_PARTY INV

WHERE

S.PERSPECTIVE = 'B'

AND

S.SHIPMENT_GID = INV.SHIPMENT_GID 

AND 

INV.INVOLVED_PARTY_QUAL_GID = 'BILL-TO' 

AND

INV.INVOLVED_PARTY_CONTACT_GID = 'RSK.50144'

AND

(TRUNC(sysdate)+ 1 = TRUNC(S.START_TIME) OR TRUNC(sysdate) = TRUNC(S.START_TIME) OR TRUNC (sysdate) -1 = TRUNC(S.START_TIME)) 

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,482 Red Diamond

    Oops,

    It should be CASE TRUNC(T.TODAY) - TRUNC(TODAY,'IW'), not CASE TRUNC(T.TODAY,'IW') - TRUNC(TODAY):

    SELECT  S.SHIPMENT_GID SHIPMENT
      FROM  SHIPMENT S,
            SHIPMENT_INVOLVED_PARTY INV
      WHERE S.PERSPECTIVE = 'B'
        AND S.SHIPMENT_GID = INV.SHIPMENT_GID 
        AND INV.INVOLVED_PARTY_QUAL_GID = 'BILL-TO' 
        AND INV.INVOLVED_PARTY_CONTACT_GID = 'RSK.50144'
        AND (
                TRUNC(S.START_TIME) = TRUNC(SYSDATE) -- today
             OR
                TRUNC(S.START_TIME) = CASE TRUNC(SYSDATE) - TRUNC(SYSDATE,'IW')
                                        WHEN 0 THEN TRUNC(SYSDATE) - 3 -- Friday, if today is Monday
                                        ELSE TRUNC(SYSDATE) - 1 -- yesterday
                                      END
             OR
                TRUNC(S.START_TIME) = CASE TRUNC(SYSDATE) - TRUNC(SYSDATE,'IW')
                                        WHEN 4 THEN TRUNC(SYSDATE) + 3 -- Monday, if today is Friday
                                        ELSE TRUNC(SYSDATE) + 1 -- tomorrow
                                      END
            )
    /
    


    SY.

    Bjornlaat

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,482 Red Diamond
    SELECT  S.SHIPMENT_GID SHIPMENT
      FROM  SHIPMENT S,
            SHIPMENT_INVOLVED_PARTY INV
      WHERE S.PERSPECTIVE = 'B'
        AND S.SHIPMENT_GID = INV.SHIPMENT_GID 
        AND INV.INVOLVED_PARTY_QUAL_GID = 'BILL-TO' 
        AND INV.INVOLVED_PARTY_CONTACT_GID = 'RSK.50144'
        AND (
                TRUNC(S.START_TIME) = TRUNC(SYSDATE) -- today
             OR
                TRUNC(S.START_TIME) = CASE TRUNC(SYSDATE,'IW') - TRUNC(SYSDATE)
                                        WHEN 0 THEN TRUNC(SYSDATE) - 3 -- Friday, if today is Monday
                                        ELSE TRUNC(SYSDATE) - 1 -- yesterday
                                      END
             OR
                TRUNC(S.START_TIME) = CASE TRUNC(SYSDATE,'IW') - TRUNC(SYSDATE)
                                        WHEN 4 THEN TRUNC(SYSDATE) + 3 -- Monday, if today is Friday
                                        ELSE TRUNC(SYSDATE) + 1 -- tomorrow
                                      END
            )
    /
    
    

    SY.

  • Bjornlaat
    Bjornlaat Member Posts: 20 Green Ribbon

    The code obtained unfortunately does not work, the S.START_TIME is a timestamp is it perhaps because of that that the code does not work? 

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,070 Red Diamond

    Hi, @User_63WSS

    Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

    Since you didn't give any sample data, Solomon couldn't test his idea. Perhaps instead of TRUNC(SYSDATE,'IW') - TRUNC(SYSDATE) he meant TRUNC (SYSDATE) - TRUNC( SYSDATE, 'IW') . If that doesn't solve the problem, then point out exactly where his solution is wrong with the sample data you post.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,070 Red Diamond

    Ho, @User_63WSS

    What if today is Saturday or Sunday? What if today is Monday or Friday, and there were shipments made on the nearest Saturday or Sunday? If things like this are possible in you application, then include examples when you post the sample data and desired results.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,482 Red Diamond

    Oops,

    It should be CASE TRUNC(T.TODAY) - TRUNC(TODAY,'IW'), not CASE TRUNC(T.TODAY,'IW') - TRUNC(TODAY):

    SELECT  S.SHIPMENT_GID SHIPMENT
      FROM  SHIPMENT S,
            SHIPMENT_INVOLVED_PARTY INV
      WHERE S.PERSPECTIVE = 'B'
        AND S.SHIPMENT_GID = INV.SHIPMENT_GID 
        AND INV.INVOLVED_PARTY_QUAL_GID = 'BILL-TO' 
        AND INV.INVOLVED_PARTY_CONTACT_GID = 'RSK.50144'
        AND (
                TRUNC(S.START_TIME) = TRUNC(SYSDATE) -- today
             OR
                TRUNC(S.START_TIME) = CASE TRUNC(SYSDATE) - TRUNC(SYSDATE,'IW')
                                        WHEN 0 THEN TRUNC(SYSDATE) - 3 -- Friday, if today is Monday
                                        ELSE TRUNC(SYSDATE) - 1 -- yesterday
                                      END
             OR
                TRUNC(S.START_TIME) = CASE TRUNC(SYSDATE) - TRUNC(SYSDATE,'IW')
                                        WHEN 4 THEN TRUNC(SYSDATE) + 3 -- Monday, if today is Friday
                                        ELSE TRUNC(SYSDATE) + 1 -- tomorrow
                                      END
            )
    /
    


    SY.

    Bjornlaat
  • Bjornlaat
    Bjornlaat Member Posts: 20 Green Ribbon

    @Solomon Yakobson Thanks so much for the help, this was exactly what we were looking for!