Home » SQL & PL/SQL » SQL & PL/SQL » Fiscal Year data counts (11.2)
| Fiscal Year data counts [message #645605] |
Wed, 09 December 2015 10:17  |
 |
lott42_gmail
Messages: 146
Registered: June 2010
|
Senior Member |
|
|
I need to count the number of transactions based upon calendar data but display the data within our defined fiscal year BY MONTH calendar.
We use a Fiscal Year which starts at "07/01/YYYY" and ends "06/30/YYYY".
So if I have 4 Calendar transactions like below
05/24/2014
05/25/2014
10/24/2014
11/24/2014
I want to count the data based upon each Year and Month
CALENDER_YEARMO CALENDER_YEARMO_CNT
201405 2
201411 1
201410 1
and display these results in a generated set of Fiscal Year and Months. Because this set of data falls between two given Fiscal Year
7/1/2013 - 6/30/2014
7/1/2014 - 6/30/2015
I want to generate 2 years worth of Fiscal data and then display the calendar counts where appropriate.
So for the data above, I'd like to see results like this
FISCAL_YEAR FISCAL_YEARMO_DATES CALENDER_YEAR_CNT
2014 201307 0
2014 201308 0
2014 201309 0
2014 201310 0
2014 201311 0
2014 201312 0
2014 201401 0
2014 201402 0
2014 201403 0
2014 201404 0
2014 201405 2
2014 201406 0
2015 201407 0
2015 201408 0
2015 201409 0
2015 201410 1
2015 201411 1
2015 201412 0
2015 201501 0
2015 201502 0
2015 201503 0
2015 201504 0
2015 201505 0
2015 201506 0
My code
WITH data AS
(SELECT to_date('05/24/2014', 'mm/dd/yyyy') testdate
FROM dual
UNION
SELECT to_date('05/25/2014', 'mm/dd/yyyy') testdate
FROM dual
UNION
SELECT to_date('10/24/2014', 'mm/dd/yyyy') testdate
FROM dual
UNION
SELECT to_date('11/24/2014', 'mm/dd/yyyy') testdate
FROM dual),
test_data AS
(SELECT add_months(trunc(add_months(testdate, 6), 'YEAR'), -6) fiscal_year_start,
add_months(trunc(add_months(testdate, 6), 'YEAR'), 6) - 1 fiscal_year_end,
to_char(testdate, 'YYYYMM') calender_yearmo
FROM data),
fiscal_year_min_max_date AS
(SELECT MIN(fiscal_year_start) fiscal_year_start_min,
MAX(fiscal_year_end) fiscal_year_end_max
FROM test_data),
fiscal_year_min_max_dates AS
(SELECT fiscal_yearmo_dates,
to_char(trunc(add_months(to_date(fiscal_yearmo_dates, 'yyyymm'), 6),
'yyyy'),
'yyyy') fiscal_year
FROM ((SELECT DISTINCT to_char(fiscal_year_start_min + LEVEL - 1,
'YYYYMM') fiscal_yearmo_dates
FROM fiscal_year_min_max_date
CONNECT BY LEVEL <=
fiscal_year_end_max - fiscal_year_start_min + 1))),
calender_data_cnts AS
(SELECT calender_yearmo, COUNT(*) calender_yearmo_cnt
FROM test_data
GROUP BY calender_yearmo)
SELECT fiscal_year_min_max_dates.fiscal_year,
fiscal_year_min_max_dates.fiscal_yearmo_dates,
nvl(calender_data_cnts.calender_yearmo_cnt, 0) calender_year_cnt
FROM calender_data_cnts, fiscal_year_min_max_dates
WHERE fiscal_year_min_max_dates.fiscal_yearmo_dates =
calender_data_cnts.calender_yearmo(+)
ORDER BY 1, 2
seems to work but is there a "cleaner" way to write this?? I feel like I'm performing too many steps to accomplish this when it might be able to be done one 2 or 3 steps.
Thanks
|
|
|
|
| Re: Fiscal Year data counts [message #645611 is a reply to message #645605] |
Wed, 09 December 2015 11:23  |
 |
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> WITH data AS
2 (SELECT to_date('05/24/2014', 'mm/dd/yyyy') testdate
3 FROM dual
4 UNION
5 SELECT to_date('05/25/2014', 'mm/dd/yyyy') testdate
6 FROM dual
7 UNION
8 SELECT to_date('10/24/2014', 'mm/dd/yyyy') testdate
9 FROM dual
10 UNION
11 SELECT to_date('11/24/2014', 'mm/dd/yyyy') testdate
12 FROM dual),
13 FY as (
14 select min(trunc(add_months(testdate,6),'YEAR')) minFY,
15 max(trunc(add_months(testdate,6),'YEAR')) maxFY
16 from data
17 )
18 select extract(year from add_months(mnth,6)) fiscal_year,
19 to_number(to_char(mnth,'YYYYMM')) FISCAL_YEARMO,
20 count(testdate) CALENDER_YEAR_CNT
21 from data
22 right outer join
23 ( select add_months(minFY,-6+level-1) mnth
24 from FY
25 connect by level <= months_between(maxFY,minFY)+12 )
26 on trunc(testdate,'MONTH') = mnth
27 group by mnth
28 order by mnth
29 /
FISCAL_YEAR FISCAL_YEARMO CALENDER_YEAR_CNT
----------- ------------- -----------------
2014 201307 0
2014 201308 0
2014 201309 0
2014 201310 0
2014 201311 0
2014 201312 0
2014 201401 0
2014 201402 0
2014 201403 0
2014 201404 0
2014 201405 2
2014 201406 0
2015 201407 0
2015 201408 0
2015 201409 0
2015 201410 1
2015 201411 1
2015 201412 0
2015 201501 0
2015 201502 0
2015 201503 0
2015 201504 0
2015 201505 0
2015 201506 0
24 rows selected.
[Updated on: Wed, 09 December 2015 11:24] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Fri Jul 24 20:45:50 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
