CREATE TABLE rpt_tab (
    e_id      NUMBER(10),
    region    VARCHAR2(20),
    stages    VARCHAR2(20),
    end_date  DATE
);

INSERT INTO rpt_tab VALUES(11,'Mumbai','STG_1','12-04-22');
INSERT INTO rpt_tab VALUES(12,'France','STG_1','13-04-22');
INSERT INTO rpt_tab VALUES(13,'France','STG_1','14-04-22');

COMMIT;

I need to write a logic that will give me the data after finding the last Tuesday from the end_date column. I need a report with the logic as mentioned below:

1. If today is 12th April i.e SYSDATE and day is Tuesday then the report should give me only today's date data only.
2. If suppose the date is 13th April i.e tommorows date then the report should give me data count from 12th April(Tuesday) and 13th April. Basically, the cycle will be from Tueday to Tuesday. If the date is 19th April (Tuesday) then the data count should be from 12th - 19th(Tuesday).
3. If the date is 19th April then again the cycle will be continued as mention in point no. 1 that will check if the sysdate is Tuesday and sysdate then it will give me the count.

My attempt:

WITH a AS(
SELECT COUNT(*) since_tuesday FROM rpt_tab
WHERE --end_date need to fetch the data count based on the end_date column and check Tuesday week day.
GROUP BY stages
)
SELECT since_tuesday FROM a;

Expected Output if date is 12th April (Tuesday):

 -------------- 
| since_tuesday |
 -------------- 
|            1 |
 -------------- 

Expected Output if date is 13th April:

 -------------- 
| since_tuesday |
 -------------- 
|            2 |
 -------------- 

Expected Output if date is 14th April:

 -------------- 
| since_tuesday |
 -------------- 
|            3 |
 -------------- 

Need to check if sysdate is tuesday, then from there need to populate the count.

CodePudding user response：

You can use the NEXT_DAY() function for this:

WITH rpt_tab AS
(                            
SELECT 11 as e_id,'Mumbai' as region,'STG_1' as stages, '12-04-22' as end_date FROM dual UNION ALL
SELECT 12,'France','STG_1','13-04-22' FROM dual UNION ALL
SELECT 13,'France','STG_1','14-04-22' FROM dual
)
SELECT COUNT(*) 
  FROM rpt_tab
 WHERE TO_DATE(end_date,'DD-MM-YY') BETWEEN TRUNC(NEXT_DAY(TO_DATE(end_date,'DD-MM-YY'),'TUESDAY') - 7) AND TRUNC(NEXT_DAY(TO_DATE(end_date,'DD-MM-YY'),'TUESDAY'))
   AND TO_DATE(end_date,'DD-MM-YY') <= TRUNC(SYSDATE);

CodePudding user response：

If I understood u right u are looking for the last Tuesday in table and subtracting sysdate with that day, so here it is:

    select floor((sysdate-end_date)) 1 Day_num
    from rpt_tab
    where trim(to_char(end_date,'Day'))='Tuesday'
    and end_date<sysdate and sysdate-end_date<7

Todays result:

DAY_NUM
-------
1

Tomorrow result:

 select (to_date('13-04-2022','dd-mm-yyyy')-end_date) 1 Day_num
 from rpt_tab
 where trim(to_char(end_date,'Day'))='Tuesday'
 and end_date<to_date('13-04-2022','dd-mm-yyyy') 
 and to_date('13-04-2022','dd-mm-yyyy')-end_date<6

 DAY_NUM
 -------
 2

And so on...