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

Bjornlaat

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)) 

Solomon Yakobson

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.

Solomon Yakobson

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

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

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

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

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

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