CODEWITHSUNDEEP
sqloracle-database
Vicky
Vicky

Reputation: 681

How to count the number of records based on the date and cycle is from Tuesday to Tuesday

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.

Upvotes: 0

Views: 63

Answers (2)

Gnqz
Gnqz

Reputation: 3382

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, TO_DATE('12-04-22','DD-MM-YY') as end_date FROM dual UNION ALL
SELECT 12,'France','STG_1',TO_DATE('13-04-22','DD-MM-YY') FROM dual UNION ALL
SELECT 13,'France','STG_1',TO_DATE('14-04-22','DD-MM-YY') FROM dual
)
SELECT COUNT(*) 
  FROM rpt_tab
 WHERE end_date BETWEEN TRUNC(NEXT_DAY(end_date),'TUESDAY') - 7) AND TRUNC(NEXT_DAY(end_date),'TUESDAY'))
   AND end_date <= TRUNC(SYSDATE);

Upvotes: 0

MT0
MT0

Reputation: 167842

Rather than using language/territory specific functions like TO_CHAR or NEXT_DAY, you can do it independently of language and territory using TRUNC(SYSDATE - 1, 'IW') + 1 to shift the date back one day and then truncate it to the start of the ISO week (which is always a Monday) and then shift the day forward one day back to Tuesday:

SELECT COUNT(*) since_tuesday
FROM   rpt_tab
WHERE  end_date >= TRUNC(SYSDATE - 1, 'IW') + 1
AND    end_date <  TRUNC(SYSDATE) + 1
GROUP BY stages

Which, for the sample data outputs:

SINCE_TUESDAY
1

db<>fiddle here

Upvotes: 1

Related Questions