CODEWITHSUNDEEP
sqloracle11g
sivaranjani manoharan
sivaranjani manoharan

Reputation: 1

How to calculate sum of a metric for rest of the month, quarter and year daily in Oracle

I have a table with data starting from Jan 1, 2021 to current date.

I am calculating the MTD, QTD, YTD values against each date using these queries

MTD- "sum(METRIC) over ( partition by segment_grp, trunc(activity_date,'MM') order by activity_date range between activity_date-trunc(activity_date,'MM') preceding and 0 following) MTD_METRIC"

QTD- "sum(METRIC) over ( partition by segment_grp, trunc(activity_date,'Q') order by activity_date range between activity_date-trunc(activity_date,'Q') preceding and 0 following) QTD_METRIC"

YTD- "sum(METRIC) over ( partition by segment_grp, trunc(activity_date,'YYYY') order by activity_date range between activity_date-trunc(activity_date,'YYYY') preceding and 0 following) YTD_METRIC"

I also want to calculate the rest of the month, rest of the quarter, rest of the year metric as well. How do I modify the above queries to do that?

Sample data for Nov 2021: CREATE TABLE TEMP_SUMMARY ( ACTIVITY_DATE DATE, SEGMENT_GRP VARCHAR2(140 CHAR), MONTH VARCHAR2(6 CHAR), YEAR VARCHAR2(4 CHAR), QUARTER CHAR(2 CHAR), METRIC NUMBER, MTD_METRIC NUMBER, QTD_METRIC NUMBER, YTD_METRIC NUMBER, ROM_METRIC NUMBER, ROQ_METRIC NUMBER, ROY_METRIC NUMBER )

insert into TEMP_SUMMARY (ACTIVITY_DATE,SEGMENT_GRP,MONTH,YEAR,QUARTER,METRIC,MTD_METRIC,QTD_METRIC,YTD_METRIC) values (to_date('01/11/2021','dd/mm/yyyy'), 'ENT', '202111', '2021', 'Q4', 149, 149, 5219, 56396)

insert into TEMP_SUMMARY (ACTIVITY_DATE,SEGMENT_GRP,MONTH,YEAR,QUARTER,METRIC,MTD_METRIC,QTD_METRIC,YTD_METRIC) values (to_date('02/11/2021','dd/mm/yyyy'), 'ENT', '202111', '2021', 'Q4', 382, 531, 5601, 56778)

Nov 1-
Rest of the month: sum(Nov 2 to 30 Metric)
Rest of the quarter: sum(Nov 2 to 30 Metric, Dec Meric) Rest of the year: sum(Nov 2 to 30 Metric, Dec Meric)

Nov 2- Rest of the month: sum(Nov 3 to 30 Metric)
Rest of the quarter: sum(Nov 3 to 30 Metric, Dec Metric)
Rest of the year: sum(Nov 3 to 30 Metric, Dec Metric)

Thanks!

Upvotes: 0

Views: 293

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

It looks like you just want a simple analytic function

select sum( daily_count ) over (partition by month order by dt) mtd,
       sum( daily_count ) over (partition by quarter order by dt) qtd,
       sum( daily_count ) over (partition by year order by dt) ytd
  from your_table

Upvotes: 0

Related Questions