Using Days Of The Week For Aggregates In Db2

I currently have a query that reads from a table which is written to daily, so I have totals for every day. What I'm trying to do is modify this query so that I can use days of the

Solution 1:

You can use dayofweek function. SQL gets all records starting from Sunday including current date. Second column "DailyBrownNumbers" uses case statement to restrict totals to current date records. Third column "WeeklyTotal" has totals for all records from Sunday.

SELECT employee,
sum(case when category = 'Brown' and  date_of_report >=  current date 
       then daily_total 
       else 0 end) as DailyBrownNumbers,
sum(case when category = 'Brown' 
       then daily_total 
       else 0 end) as WeeklyTotal,
sum(case when category = 'Brown' 
       then weekly_quota 
    else 0 end) as WeeklyBrownNumbers,
CURRENT_DATE as DATE_OF_REPORT
from dailyRecords
where date_of_report >= ( current date - ( dayofweek(current date) - 1 ) days )
group by employee