Skip to Main Content
  • Questions

  • Display all dates data of a given period name

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Amar.

Asked: March 29, 2017 - 9:46 am UTC

Last updated: March 29, 2017 - 1:23 pm UTC

Version: 10.1.0

Viewed 1000+ times

You Asked

Hi Tom,

I need to pass a gl period_name eg.:-('JAN-17') to the query and get sum of debit and credit hit to each and every account.

But here a problem is that I also need to display the output of any date which is not having the data.

If any date is not contain any data then sum of debit and credit should come zero.

e.g;-

I have made a query


SELECT GCC.SEGMENT5 ACCOUNT
,GJL.EFFECTIVE_DATE
,SUM(GJL.ACCOUNTED_DR) DEBIT
,SUM(GJL.ACCOUNTED_CR) CREDIT
FROM GL.GL_JE_HEADERS GJH
,GL.GL_JE_LINES GJL
,GL.GL_CODE_COMBINATIONS GCC
WHERE GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJH.PERIOD_NAME='JAN-17'
AND GJH.STATUS='P'
GROUP BY GCC.SEGMENT5
,GJL.EFFECTIVE_DATE
ORDER BY GJL.EFFECTIVE_DATE , GCC.SEGMENT5;


But, this will fetch data only for the effective dates available. If a date does not have balance then it must display sum as zero.

Please help me !

and Chris said...

So you have a query that only returns a few dates from your period, but you need to show all of them?

In that case you need a data source containing all the dates you need to see. So your process

- Create a table with all the dates in your period
- Left join your query to this on date
- Nvl/coalesce the sum to display zero when your query returns no rows

In the example below the sales table only includes rows for some days in January. So I've generated a table of dates on the fly. Then left joined to it. You could replace this with a real dates table if you want (useful if you need to exclude non-working or other days for whatever reason):

select time_id, sum(nvl(amount_sold, 0)) from SH.SALES s
where  trunc(time_id, 'mm') = date'1998-01-01' 
group  by time_id
order  by time_id;

TIME_ID               SUM(NVL(AMOUNT_SOLD,0))  
02-JAN-1998 00:00:00  798                      
03-JAN-1998 00:00:00  1,264                    
05-JAN-1998 00:00:00  306                      
06-JAN-1998 00:00:00  378                      
09-JAN-1998 00:00:00  5,100                    
10-JAN-1998 00:00:00  1,580                    
12-JAN-1998 00:00:00  1,092                    
14-JAN-1998 00:00:00  1,746                    
16-JAN-1998 00:00:00  308                      
20-JAN-1998 00:00:00  742                      
23-JAN-1998 00:00:00  312                      
28-JAN-1998 00:00:00  4,680                    
29-JAN-1998 00:00:00  546  

with dates as (
  select date'1997-12-31'+level dt from dual
  connect by level <= 31
) 
select dt, sum(nvl(amount_sold, 0)) from dates d
left   join SH.SALES s
on     d.dt = s.time_id
and    trunc(time_id, 'mm') = date'1998-01-01' 
group  by dt
order  by dt;

DT                    SUM(NVL(AMOUNT_SOLD,0))  
01-JAN-1998 00:00:00  0                        
02-JAN-1998 00:00:00  798                      
03-JAN-1998 00:00:00  1,264                    
04-JAN-1998 00:00:00  0                        
05-JAN-1998 00:00:00  306                      
06-JAN-1998 00:00:00  378                      
07-JAN-1998 00:00:00  0                        
08-JAN-1998 00:00:00  0                        
09-JAN-1998 00:00:00  5,100                    
10-JAN-1998 00:00:00  1,580                    
11-JAN-1998 00:00:00  0                        
12-JAN-1998 00:00:00  1,092                    
13-JAN-1998 00:00:00  0                        
14-JAN-1998 00:00:00  1,746                    
15-JAN-1998 00:00:00  0                        
16-JAN-1998 00:00:00  308                      
17-JAN-1998 00:00:00  0                        
18-JAN-1998 00:00:00  0                        
19-JAN-1998 00:00:00  0                        
20-JAN-1998 00:00:00  742                      
21-JAN-1998 00:00:00  0                        
22-JAN-1998 00:00:00  0                        
23-JAN-1998 00:00:00  312                      
24-JAN-1998 00:00:00  0                        
25-JAN-1998 00:00:00  0                        
26-JAN-1998 00:00:00  0                        
27-JAN-1998 00:00:00  0                        
28-JAN-1998 00:00:00  4,680                    
29-JAN-1998 00:00:00  546                      
30-JAN-1998 00:00:00  0                        
31-JAN-1998 00:00:00  0

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

with MODEL clause

Rajeshwaran, March 29, 2017 - 12:12 pm UTC 

demo@ORA11G> select trunc(created,'mm') dt,count(*) cnt
  2  from t
  3  group by trunc(created,'mm')
  4  order by dt ;

DT                 CNT
----------- ----------
01-JAN-2017         69
01-FEB-2017         11
01-MAR-2017          5

demo@ORA11G> select *
  2  from (
  3  select trunc(created,'mm') dt,count(*) cnt
  4  from t
  5  group by trunc(created,'mm')
  6      )
  7  model
  8    dimension by (dt)
  9    measures( cnt  )
 10    rules(
 11     cnt[for dt from to_date('01-jan-2017','dd-mon-yyyy') to
 12                    to_date('31-dec-2017','dd-mon-yyyy')
 13                             increment numtoyminterval(1,'month')]
 14          = nvl( cnt[cv()],0 ) )
 15  order by dt
 16  /

DT                 CNT
----------- ----------
01-JAN-2017         69
01-FEB-2017         11
01-MAR-2017          5
01-APR-2017          0
01-MAY-2017          0
01-JUN-2017          0
01-JUL-2017          0
01-AUG-2017          0
01-SEP-2017          0
01-OCT-2017          0
01-NOV-2017          0
01-DEC-2017          0

12 rows selected.

demo@ORA11G>

Chris Saxon
March 29, 2017 - 1:23 pm UTC

Just because you can use the model clause, doesn't mean you should! ;)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.