Reputation: 195
Easy way to fetch 2 years of months data even if we miss some months
I need to fetch all 24 months displayed even if the months are missing with corresponding sales value as null. This is what i came up with. As u can see we are missing id and custname. I need the id and custname with just sales as null, as dipicted in the below screenshot
select CUSTNAME, reportdate, sales, mth
from ( select add_months(date '2017-01-01', level - 1) as mth
from dual
connect by level <= 24)mo
left outer join oracle_tbl dc on mo.mth = trunc(reportdate, 'MM')
order by mth
I have also tried the following and nothing just seem to work (i.e) giving nulls for custname and id which has missing dates
WITH mydates AS (
select LAST_DAY(add_months(date '2017-01-01', level - 1)) as mth, min_id,min_custname
from (
select min(id) as min_id, min(CUSTNAME) as min_custname
from my_oracle_tbl
)
connect by level <= 24)
select
nvl(t.id, a.min_id)id,
nvl(t.CUSTNAME,a.min_custname)CUSTNAME, a.mth, t.sales
from mydates a left join my_oracle_tbl t on a.mth= LAST_DAY(t.reporttdate)
where
t.id=12345;
select CUSTNAME, reportdate, sales, mth
from( SELECT CUSTNAME, reportdate, sales, mth FROM my_oracle_tbl
WHERE id=123 )
myTotals
right outer join
(select LAST_DAY(date '2017-01-01' + numtoyminterval(level-1,'month')) MonthName
from dual
connect by level <= 24) ALLMONTHS
on( myTotals.mm = allmonths.MonthName )
[![This is how i get.. missing id and custname][1]][1]
[![this is what i need][2]][2]
Upvotes: 0
Views: 82
Answers (2)
Reputation: 2252
Suppose we have the following tables: {1} DATES, containing the last day of all months that you need, and {2} SALES, containing sales for customers (not for every month).
create table dates ( month_end_date )
as
select add_months(date '2017-01-01', level - 1) - 1
from dual
connect by level <= 24 ;
create table sales (customerid, custname, sales, date_of_sale )
as
select *
from (
select
case when mod( level, 3 ) = 0 then 1 end as customerid
, case when mod( level, 3 ) = 0 then 'test' end
, case when mod( level, 3 ) = 0 then trunc( dbms_random.value() * 10000 ) end
, case when mod( level, 3 ) = 0 then add_months(date '2017-01-01', level - 1) end
from dual connect by level <= 24
)
where customerid is not null ;
SALES table
-- SALES colum: random values!
-- sales values recorded every third month
SQL> select * from sales ;
CUSTOMERID CUST SALES DATE_OF_S
---------- ---- ---------- ---------
1 test 5764 01-MAR-17
1 test 3937 01-JUN-17
1 test 9926 01-SEP-17
1 test 3045 01-DEC-17
1 test 598 01-MAR-18
1 test 325 01-JUN-18
1 test 2590 01-SEP-18
1 test 5803 01-DEC-18
8 rows selected.
DATES table
SQL> select * from dates ;
MONTH_END
---------
31-DEC-16
31-JAN-17
28-FEB-17
31-MAR-17
30-APR-17
...
31-JUL-18
31-AUG-18
30-SEP-18
31-OCT-18
30-NOV-18
24 rows selected.
The following query should give you something to work from ... The CROSS JOIN gives you all combinations of customerids + customernames and month_end_dates. The LEFT JOIN outputs all the NULL values you need. (Notice that we are calling LAST_DAY() in the join condition.) You could also use "SELECT ... CONNECT BY ..." for generating all MONTH_END_DATEs ( as you have done in your own query )
select CM.customerid, CM.custname, S.sales, CM.month_end_date
from (
select *
from (
( select unique customerid, custname from sales )
cross join
( select month_end_date from dates ) -- <- data could be "generated"
)
) CM left join sales S on CM.month_end_date = last_day( S.date_of_sale )
order by CM.month_end_date
;
Result
CUSTOMERID CUST SALES MONTH_END
---------- ---- ---------- ---------
1 test NULL 31-DEC-16
1 test NULL 31-JAN-17
1 test NULL 28-FEB-17
1 test 5764 31-MAR-17
1 test NULL 30-APR-17
1 test NULL 31-MAY-17
1 test 3937 30-JUN-17
1 test NULL 31-JUL-17
1 test NULL 31-AUG-17
1 test 9926 30-SEP-17
1 test NULL 31-OCT-17
1 test NULL 30-NOV-17
1 test 3045 31-DEC-17
1 test NULL 31-JAN-18
1 test NULL 28-FEB-18
1 test 598 31-MAR-18
1 test NULL 30-APR-18
1 test NULL 31-MAY-18
1 test 325 30-JUN-18
1 test NULL 31-JUL-18
1 test NULL 31-AUG-18
1 test 2590 30-SEP-18
1 test NULL 31-OCT-18
1 test NULL 30-NOV-18
-- caution: sale for 01-DEC-18 "chopped off"
24 rows selected.
EDIT (restrict the date range, filter by customer)
For dealing with more than one customer, and narrowing the date range (of the result set), add a condition to the LEFT JOIN's ON clause, and then some more conditions to the WHERE clause eg
SALES (test) table CTAS
-- New SALES table for testing
-- 5 customers, 1 sale every 5 months (per customer)
create table sales (customerid, custname, sales, date_of_sale )
as
select *
from (
select
mod( level, 5 ) + 1
, 'test' || to_char( mod( level, 5 ) + 1 )
, trunc( dbms_random.value() * 10000 )
, add_months(date '2017-01-01', level - 1)
from dual connect by level <= 24
)
;
SALES data
SQL> select * from sales;
CUSTOMERID CUSTNAME SALES DATE_OF_SALE
2 test2 5594 01-JAN-17
3 test3 6398 01-FEB-17
4 test4 2072 01-MAR-17
5 test5 4269 01-APR-17
1 test1 9435 01-MAY-17
2 test2 6984 01-JUN-17
3 test3 5735 01-JUL-17
4 test4 9549 01-AUG-17
5 test5 9686 01-SEP-17
1 test1 9193 01-OCT-17
2 test2 1702 01-NOV-17
3 test3 8277 01-DEC-17
4 test4 8235 01-JAN-18
5 test5 7596 01-FEB-18
1 test1 5507 01-MAR-18
2 test2 6267 01-APR-18
3 test3 5708 01-MAY-18
4 test4 755 01-JUN-18
5 test5 3966 01-JUL-18
1 test1 5167 01-AUG-18
2 test2 6819 01-SEP-18
3 test3 9268 01-OCT-18
4 test4 1844 01-NOV-18
5 test5 1085 01-DEC-18
24 rows selected.
Query
-- sales for customer (id) 3, between 30 Apr 2018 and 31 Dec 2018
select CM.customerid, CM.custname, S.sales, CM.month_end_date
from (
select *
from (
( select unique customerid, custname from sales )
cross join
( select month_end_date from dates )
)
) CM
left join sales S
on CM.month_end_date = last_day( S.date_of_sale )
and CM.customerid = S.customerid
where CM.customerid = 3
and CM.month_end_date > date '2018-04-30'
and CM.month_end_date < date '2018-12-31'
order by CM.month_end_date
;
-- result
CUSTOMERID CUSTNAME SALES MONTH_END_DATE
3 test3 5708 31-MAY-18
3 test3 NULL 30-JUN-18
3 test3 NULL 31-JUL-18
3 test3 NULL 31-AUG-18
3 test3 NULL 30-SEP-18
3 test3 9268 31-OCT-18
3 test3 NULL 30-NOV-18
Upvotes: 0
Reputation: 191235
You haven't said exactly what is wrong, but you are ordering by a string value, so you will get January 2017, then January 2018, then February 2017...
Keep dates as actual dates until you need to display them in a fixed format (or let your client do that). That includes for comparison.
select dc.CUSTNAME, dc.reportdate, dc.sales, mo.mth
from (
select add_months(date '2017-01-01', level - 1) as mth
from dual
connect by level <= 24
) mo
left outer join oracle_tbl dc on mo.mth = trunc(dc.reportdate, 'MM')
order by mo.mth
where trunc(reportdate, 'MM') truncates the report date to the first day of its month; or with a date range (which is more index-friendly):
select dc.CUSTNAME, dc.reportdate, dc.sales, mo.mth
from (
select add_months(date '2017-01-01', level - 1) as mth
from dual
connect by level <= 24
) mo
left outer join oracle_tbl dc
on dc.reportdate >= mo.mth
and dc.reportdate < add_months(mo.mth, 1)
order by mo.mth
Either way you'll get mth as a date, which you can then format however you want for display - if you actually want to display it at all.
Upvotes: 1
Related Questions
- Fill missing months
- Get the Data for Each Month (till that month) for 12 months
- Fill missing months in a SELECT query
- How to get value for the missing months
- How to include all months of a year in query
- How to add missing months in each year in oracle
- How to fill in missing month data in an Oracle query
- Oracle: Get data for only the past half year
- How do I return all data for the previous month in SQL?
- Return month and year in a Oracle database