# PL/SQL query to calculate Quarterly, QTD (Quarterly to Date) sale total [Answered]RSS

## 6 replies

Last post May 04, 2015 09:24 AM by avt2k7

Member

150 Points

361 Posts

## Hello,

I have a view that store the following data:

yearMonth | saleAmount

2015-04 | 3000

2015-03 | 7000

2015-02 | 10000

2015-01 | 4000

2014-12 | 300

2014-11 | 1300

2014-10 | 30

2014-09 | 600

2014-08 | 3000

2014-07 | 90

2014-06| 80

2014-05| 70

2014-04 | 25

2014-03 | 200

2014-02 | 100

2014-01 | 50

2013-12 | 100

2013-10 | 260

2013-01 | 3500

etc...

I need a query that calculate quarterly (Q1, Q2, Q3, Q4) based on each year data and Quarter to Date ( a time interval that captures all relevant sale activity that occurred between the beginning of the current quarter and the time in which the data was gathered). Your time is much appreciated on helping the solution. Thanks.

Contributor

3242 Points

1224 Posts

### Re: PL/SQL query to calculate Quarterly, QTD (Quarterly to Date) sale total

Yes

Oracle analytics can do that too!!

Contributor

3242 Points

1224 Posts

### Re: PL/SQL query to calculate Quarterly, QTD (Quarterly to Date) sale total

First extract year and quarter from the text value for date

Second calculate the sum by quarter

Third create the cumulative sum by quarter within each year

```YEAR Q  SUM_SALES CUM_SUM_SALES_BY_YEAR
---- - ---------- ---------------------
2013 1       3500                  3500
2013 4        360                  3860
2014 1        350                   350
2014 2        175                   525
2014 3       3690                  4215
2014 4       1630                  5845
2015 1      21000                 21000
2015 2       3000                 24000

SELECT
b.YEAR,
b.QUARTER,
b.SUM_SALES,
SUM(SUM(b.SUM_SALES)) OVER (PARTITION BY b.YEAR ORDER BY b.YEAR, b.QUARTER ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUM_SUM_SALES_BY_YEAR
FROM
(
SELECT DISTINCT
a.YEAR,
a.QUARTER,
SUM(a.SALEAMOUNT) OVER (PARTITION BY a.YEAR, a.QUARTER ORDER BY a.YEAR, a.QUARTER) AS SUM_SALES
FROM
(
SELECT
TO_CHAR(TO_DATE(yearMonth,'YYYY-MM'),'YYYY') AS YEAR,
TO_CHAR(TO_DATE(yearMonth,'YYYY-MM'),'Q') AS QUARTER,
SALEAMOUNT
FROM
VW_SALES
) a
) b
GROUP BY b.YEAR, b.QUARTER, b.SUM_SALES
ORDER BY b.YEAR, b.QUARTER
/

```

Member

150 Points

361 Posts

### Re: PL/SQL query to calculate Quarterly, QTD (Quarterly to Date) sale total

Lannie,

Thank you for your quick query to calculate Quarterly by year in SUM_SALES column; another CUM_SUM_SALES_BY_YEAR seem to be calculated the Sum from start year up to date year cumulatively.  In fact, I need to calculate the QTD query only. For example, this current month is May belong to Q2 ( May, June, July), so my understanding of QTD is a period starting at the beginning of the current quarter and ending at the current date. In fact, you already calculate the current  2015 Q2 in SUM_SALE column as following:

`2015 2       3000                 24000`

From current system date, we can extract year, quarter.  Can you help me further query to populate the available yearMonth data for calculate QTD based on year, month, quarter extraction? Thanks.

Contributor

3242 Points

1224 Posts

### Re: PL/SQL query to calculate Quarterly, QTD (Quarterly to Date) sale total

so what you need is

monthly with cumulative sum by quarter

not quarter with cumulative sum by year?

Contributor

3242 Points

1224 Posts

### Re: PL/SQL query to calculate Quarterly, QTD (Quarterly to Date) sale total

Cumulative sales by month for each year and quarter

```YEAR Q MO  SUM_SALES CUM_SUM_SALES_BY_YEAR_QTR
---- - -- ---------- -------------------------
2013 1 01       3500                      3500
2013 4 10        260                       260
2013 4 12        360                       620
2014 1 01         50                        50
2014 1 02        150                       200
2014 1 03        350                       550
2014 2 04         25                        25
2014 2 05         95                       120
2014 2 06        175                       295
2014 3 07         90                        90
2014 3 08       3090                      3180
2014 3 09       3690                      6870
2014 4 10         30                        30
2014 4 11       1330                      1360
2014 4 12       1630                      2990
2015 1 01       4000                      4000
2015 1 02      14000                     18000
2015 1 03      21000                     39000
2015 2 04       3000                      3000

SET PAGESIZE 50000;

SELECT
b.YEAR,
b.QUARTER,
b.MONTH,
b.SUM_SALES,
SUM(SUM(b.SUM_SALES)) OVER (PARTITION BY b.YEAR, b.QUARTER  ORDER BY b.YEAR, b.QUARTER, b.MONTH ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUM_SUM_SALES_BY_YEAR_QTR
FROM
(
SELECT DISTINCT
a.YEAR,
a.QUARTER,
a.MONTH,
SUM(a.SALEAMOUNT) OVER (PARTITION BY a.YEAR, a.QUARTER ORDER BY a.YEAR, a.QUARTER, a.MONTH) AS SUM_SALES
FROM
(
SELECT
TO_CHAR(TO_DATE(yearMonth,'YYYY-MM'),'YYYY') AS YEAR,
TO_CHAR(TO_DATE(yearMonth,'YYYY-MM'),'Q')    AS QUARTER,
TO_CHAR(TO_DATE(yearMonth,'YYYY-MM'),'MM')    AS MONTH,
SALEAMOUNT
FROM
VW_SALES
) a
) b
GROUP BY b.YEAR, b.QUARTER, b.MONTH, b.SUM_SALES
ORDER BY b.YEAR, b.QUARTER, b.MONTH
/
```

Member

150 Points

361 Posts