How to exclude data based on the weeks in oracle-CodePudding

CREATE TABLE states_tab (
    id             NUMBER(10),
    states         VARCHAR2(50),
    action         VARCHAR2(50),
    schedule_time  DATE,
    CONSTRAINT pk_states_tab PRIMARY KEY ( id )
);

INSERT INTO states_tab VALUES(1,'Albania','Rejected','07-03-22');
INSERT INTO states_tab VALUES(2,'Albania','Approved','07-03-22');
INSERT INTO states_tab VALUES(3,'Albania','Rejected','28-02-22');
INSERT INTO states_tab VALUES(4,'Albania','Approved','21-02-22');
INSERT INTO states_tab VALUES(5,'Albania','Reviewed','14-02-22');
INSERT INTO states_tab VALUES(6,'Albania','Reviewed','14-02-22');
INSERT INTO states_tab VALUES(7,'Albania','Reviewed','07-02-22');

commit;

Hi Team, Above are some sample data for which I need to extract the data based on the dates. For example in my sample data, I have data from 7th Feb till 7th March. But I need only the past 4 weeks' data i.e till 14th Feb. So, I need to exclude the data whichever coming after the 4th week. Below is my attempt.

SELECT
    states,
    schedule_time,
    SUM(decode(action, 'Rejected', 1, 0))              reject_count,
    SUM(decode(action, 'Approved', 1, 0))              approve_count,
    SUM(decode(action, 'Reviewed', 1, 0))              review_count
FROM
    states_tab
GROUP BY
    states,
    schedule_time
ORDER BY schedule_time DESC ;

From the above query, I am getting all the records but I need to restrict the records which are beyond the 4th week from 7th Mar 2022.

Expected Output:

 --------- ---------------- --------------- ---------------- --------------- 
| States  | Schedule_TIME  | REJECT_COUNT  | APPROVE_COUNT  |  REVIEW_COUNT |
 --------- ---------------- --------------- ---------------- --------------- 
| Albania | 07-03-22       |             1 |              1 |             0 |
| Albania | 28-02-22       |             1 |              0 |             0 |
| Albania | 21-02-22       |             0 |              1 |             0 |
| Albania | 14-02-22       |             0 |              0 |             2 |
 --------- ---------------- --------------- ---------------- ---------------

Just I need to exclude the 4th week's record from my existing query. Rest output is as expected from my attempt but how will I be able to exclude the data after the 4th week?

Note: From the current date I need past 4-week data only no matter how many records are present I need just till 4th-week data. This I am not able to achieve

Tool used: Oracle SQL Developer(18c)

CodePudding user response：

Sorry to inform you, but - what you got actually is 4 weeks back. Result you expect is 3 weeks back.

SQL> select sysdate,
  2    trunc(sysdate) - (4 * 7) four_weeks,
  3    trunc(sysdate) - (3 * 7) three_Weeks
  4  from dual;

SYSDATE    FOUR_WEEKS THREE_WEEK
---------- ---------- ----------
07.03.2022 07.02.2022 14.02.2022

Anyway - whichever period you need, you'll get data if you filter it, and that's done with the WHERE clause (see line #8):

SQL> SELECT
  2      states,
  3      schedule_time,
  4      SUM(decode(action, 'Rejected', 1, 0))              reject_count,
  5      SUM(decode(action, 'Approved', 1, 0))              approve_count,
  6      SUM(decode(action, 'Reviewed', 1, 0))              review_count
  7  FROM    states_tab
  8  where schedule_time >= trunc(sysdate) - (3 * 7)
  9  GROUP BY
 10      states,
 11      schedule_time
 12  ORDER BY schedule_time DESC ;

STATES          SCHEDULE_T REJECT_COUNT APPROVE_COUNT REVIEW_COUNT
--------------- ---------- ------------ ------------- ------------
Albania         07.03.2022            1             1            0
Albania         28.02.2022            1             0            0
Albania         21.02.2022            0             1            0
Albania         14.02.2022            0             0            2

SQL>