0

Looking assistance in PL/SQL query.

Business Case:
Extract previous 2 days of data from DATE column excluding weekends.

select * from holddbo.pos where eff_date = '15-NOV-2022'
  1. on 15-11-2022, I'm looking data for 14-11 and 11-11 [exclude weekend]
  2. on 14-11-2022, I'm looking data for 11-11 and 10-11 [exclude weekend]
  3. on 11-11-2022, I'm looking data for 10-11 and 09-11 [exclude weekend]
RAHUL SONI
  • 1
  • 2
  • Does this answer your question? [Determine if Oracle date is on a weekend?](https://stackoverflow.com/questions/3450965/determine-if-oracle-date-is-on-a-weekend) – Bartosz Olchowik Nov 14 '22 at 07:22
  • Thanks @BartoszOlchowik, but not. This answer to first part, like weekend, but not about how to extract 2 days of data from given date excluding weekends. Example: if given date is Nov 14th, then need previous 2 days of data exclude weekends. Data of Thursday and Friday – RAHUL SONI Nov 14 '22 at 09:12

2 Answers2

0

Maybe this could answer your question:

WITH 
    tbl (A_DATE, A_VALUE) AS 
    (
        Select TRUNC(SYSDATE) - 15 + LEVEL , 300 + LEVEL 
        From DUAL 
        Connect By LEVEL <= 14
    ),
  grid AS
      (
          SELECT 
              A_DATE, A_VALUE,
              To_Char(A_DATE, 'd') "DAY_NUM_OF_WEEK",
              To_Char(A_DATE, 'DY') "DAY_OF_WEEK",
              CASE 
                  WHEN To_Char(A_DATE, 'DY') IN('MON', 'TUE') THEN A_DATE - 4
              ELSE
                A_DATE - 2
              END "DAY_1",
              CASE 
                  WHEN To_Char(A_DATE, 'DY') = 'MON' THEN A_DATE - 3
              ELSE
                A_DATE - 1
              END "DAY_2"
          FROM
              tbl
          ORDER BY
              A_DATE
      )
Select
    *
From
    grid
Where 
    A_DATE = (Select DAY_1 From grid Where A_DATE = :Effective_Date)
  OR
    A_DATE = (Select DAY_2 From grid Where A_DATE = :Effective_Date)
    
    
/*  
Result for :Effective_Date = '07-NOV-22' (MON)
A_DATE       A_VALUE DAY_OF_WEEK
--------- ---------- -----------
03-NOV-22        304 THU         
04-NOV-22        305 FRI        


Result for :Effective_Date = '08-NOV-22' (TUE)
A_DATE       A_VALUE DAY_OF_WEEK
--------- ---------- -----------
04-NOV-22        305 FRI         
07-NOV-22        308 MON       


Result for :Effective_Date = '09-NOV-22' (WED)
A_DATE       A_VALUE DAY_OF_WEEK
--------- ---------- -----------
07-NOV-22        308 MON         
08-NOV-22        309 TUE        
*/

WITH clause (tbl) just generates some sample data to work with.
Here a CTE named grid is created to attach DAY_1 and DAY_2 dates according to your rules from the question. This way you have new columns to filter your data on within the main SQL.
NOTE: I didn't handle :Effective_Day on Sunday in CASE expression couse you said that weekends are excluded - you can do it if needed. Saturday will work fine anyway.
Regards...

ADDITION
after the comment...

SELECT 
              A_DATE,
              To_Char(A_DATE, 'd') "DAY_NUM_OF_WEEK",
              To_Char(A_DATE, 'DY') "DAY_OF_WEEK",
              CASE 
                  WHEN To_Char(A_DATE, 'DY') IN('MON', 'TUE') THEN A_DATE - 4
              ELSE
                A_DATE - 2
              END "DAY_1",
              CASE 
                  WHEN To_Char(A_DATE, 'DY') = 'MON' THEN A_DATE - 3
              ELSE
                A_DATE - 1
              END "DAY_2"
          FROM
              dual
          ORDER BY
              A_DATE

If you just want to get the days from given date use the above query putting your date instead of A_DATE. It will give the results as:

--  For A_DATE = '15-NOV-22'
--  A_DATE    DAY_NUM_OF_WEEK DAY_OF_WEEK DAY_1     DAY_2   
--  --------- --------------- ----------- --------- ---------
--  15-NOV-22 2               TUE         11-NOV-22 14-NOV-22
--  
--  For A_DATE = '10-NOV-22'
--  A_DATE    DAY_NUM_OF_WEEK DAY_OF_WEEK DAY_1     DAY_2   
--  --------- --------------- ----------- --------- ---------
--  10-NOV-22 4               THU         08-NOV-22 09-NOV-22

With your db

SELECT 
    CASE 
        WHEN To_Char(Your_Date_Column, 'DY') IN('MON', 'TUE') THEN Your_Date_Column - 4
    ELSE
      Your_Date_Column - 2
    END "DAY_1",
    CASE 
        WHEN To_Char(Your_Date_Column, 'DY') = 'MON' THEN Your_Date_Column - 3
    ELSE
      Your_Date_Column - 1
    END "DAY_2"
FROM
    Your_Table
ORDER BY
    Your_Date_Column

... Or you can join your table with selection from dual to get the days
... Or select days from dual in your selection list and/or where clause
... any way it suits you
Regards...

If your updated question means what it looks like - here is your updated solution:

select 
    * 
from 
    holddbo.pos 
Where
  eff_date = CASE WHEN To_Char(A_DATE, 'DY') IN('MON', 'TUE') THEN A_DATE - 4 ELSE A_DATE - 2 END  --DAY_1
  OR
  eff_date = CASE WHEN To_Char(A_DATE, 'DY') = 'MON' THEN A_DATE - 3 ELSE A_DATE - 1 END;   --DAY_2

just put your date ( '15-NOV-2022' ) in place of A_DATE

d r
  • 3,848
  • 2
  • 4
  • 15
  • hello - thanks but not helpful. Here you're fabricating the date, I've date column in table. I basically needed - if I put the date of Nov 14th in where clause, then I need data of Nov 10th and Nov 11th. Somehow I'm not able to figure this out. Help appreciated. – RAHUL SONI Nov 15 '22 at 07:39
  • @RAHULSONI Just posted the addition. You can get desired days if you add to your selection list just DAY_1 And DAY_2 from SQL above passing your table date column instead of A_DATE..... – d r Nov 15 '22 at 08:48
  • Hello - Thanks again, but not looking into this... I've amended my original question - hope that helps – RAHUL SONI Nov 15 '22 at 09:25
  • @RAHULSONI - there is your code with WHERE clause seleeccting rows as in the question... At the and of the answer... – d r Nov 15 '22 at 09:46
0

Thanks @d r I've made couple of addition, so I don't have to change dates at multiple places

WITH cteData AS (SELECT TO_DATE('20220107', 'YYYYMMDD') AS BUSINESS_DATE FROM DUAL)
select * from holddbo.pos p 
CROSS JOIN 
cteData d
Where
  p.effective_date = CASE WHEN To_Char(BUSINESS_DATE, 'DY') IN('MON', 'TUE') THEN BUSINESS_DATE - 4 ELSE BUSINESS_DATE - 2 END  --DAY_1
  OR
  p.effective_date = CASE WHEN To_Char(BUSINESS_DATE, 'DY') = 'MON' THEN BUSINESS_DATE - 3 ELSE BUSINESS_DATE - 1 END   --DAY_2
RAHUL SONI
  • 1
  • 2