Retrieve results from the first day of each quarter (and not earlier)

How do I make the below query retrieve results from the first day of each quarter (and not earlier)

In other words, results for quarter 1 should include records from Jan 1 and later, results for quarter 2 should include records from April 1 and later, and so on

select year, quarter, uploaddate, wagecount, 
       row_number()
         OVER (partition by year, quarter
               ORDER BY quarter, uploaddate) as Day_Number 
from 
(
select year, quarter, uploaddate, sum(wagecount) as wagecount from SAMPLE_DATA
group by year, quarter, uploaddate
)
order by year, quarter, uploaddate

The query will accomplish what is logically (but not functionally correctly) represented here

select year, quarter, uploaddate, wagecount, 
       row_number()
         OVER (partition by year, quarter
               ORDER BY quarter, uploaddate) as Day_Number 
from 
(
select year, quarter, uploaddate, sum(wagecount) as wagecount from SAMPLE_DATA
where uploaddate >= 
    CASE 
        WHEN Quarter = 1 THEN "TRUNC(uploaddate, 'Q1')"
        WHEN Quarter = 2 THEN "TRUNC(uploaddate, 'Q2')"
        WHEN Quarter = 3 THEN "TRUNC(uploaddate, 'Q3')"
        WHEN Quarter = 4 THEN "TRUNC(uploaddate, 'Q4')"
    END
group by year, quarter, uploaddate
)
order by year, quarter, uploaddate;

Sample data is as follows:

CREATE TABLE SAMPLE_DATA (
    YEAR VARCHAR2(4) NULL,
    QUARTER NUMBER(1,0) NULL,
    UPLOADDATE DATE NULL,
    WAGECOUNT NUMBER(10,0) NULL
);

Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2019','1',to_date('27-MAR-19','DD-MON-RR'),5);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2019','1',to_date('28-MAR-19','DD-MON-RR'),8493);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2019','1',to_date('29-MAR-19','DD-MON-RR'),15070);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2019','1',to_date('30-MAR-19','DD-MON-RR'),1244);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2020','1',to_date('03-JAN-20','DD-MON-RR'),0);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2020','1',to_date('05-JAN-20','DD-MON-RR'),2);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2020','1',to_date('06-JAN-20','DD-MON-RR'),3);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2020','1',to_date('07-JAN-20','DD-MON-RR'),6);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2021','2',to_date('21-APR-21','DD-MON-RR'),59);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2021','2',to_date('22-APR-21','DD-MON-RR'),10);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2021','2',to_date('23-APR-21','DD-MON-RR'),16);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2021','2',to_date('24-APR-21','DD-MON-RR'),1);


Read more here: https://stackoverflow.com/questions/67387721/retrieve-results-from-the-first-day-of-each-quarter-and-not-earlier

Content Attribution

This content was originally published by cmomah at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: