Sysdate minus 2 different days

Question

I am running this query currently for one scenario but I have two scenarios: If SYSDATE = Monday, then run "SYSDATE - 2", otherwise run "SYSDATE - 1". I'm connecting to the database via an OLE connection from Excel so I'm not sure I can use a stored procedure. Is there a way to write the query to accomplish both scenarios? Thanks for all help.

SELECT 
   DISTINCT VERSION_NAME VERSION, MIN(RECONCILE_START_DT) DATES
FROM
   SDE.GDBM_RECONCILE_HISTORY
WHERE
    RECONCILE_RESULT = 'Conflicts'
AND
    RECONCILE_START_DT > SYSDATE -1
GROUP BY VERSION_NAME
ORDER BY 2 ASC NULLS LAST

Answer 1

You may use a CASE statement in your WHERE condition to subtract either 2 for Mondays or 1 for the rest of the week. TO_CHAR(DATE, 'D') delivers the day of week beginning with Sundays = 1. Therefore Mondays are 2.

Try this:

SELECT
    VERSION_NAME AS VERSION,
    MIN(RECONCILE_START_DT) AS DATES
FROM
   SDE.GDBM_RECONCILE_HISTORY
WHERE
    RECONCILE_RESULT = 'Conflicts'
AND
    RECONCILE_START_DT > SYSDATE -
        CASE TO_CHAR(SYSDATE, 'D')
            WHEN '2' THEN 2
            ELSE 1 END

GROUP BY VERSION_NAME
ORDER BY 2 ASC NULLS LAST

Also you don't need the DISTINCT keyword as you're already use GROUP BY.