0

I have a query that needs to return data for all days between a given date range. The query needs to return a value of 0 for a date even if there is no data.

Example:

DATE    Empid   AMOUNT
20-Jun  10  200
20-Jun  11  300
24-Jun  5   150
25-Jun  17  300
25-Jun  18  200

Using this query: 

SELECT DATE, SUM(AMOUNT) 
From Table A
Where Date between (:FromDate) and (:ToDate)
Group by Date

**Input dates are**: 20-Jun-14 and 25-Jun-14

Result:

DATE    AMOUNT
20-Jun  500
24-Jun 150
25-Jun 500

What I'm really after: 

DATE    AMOUNT
20-Jun  500
21-Jun  0
22-Jun  0
23-Jun  0
24-Jun 150
25-Jun 500

Is there a way possible to achieve this?

Thanks!

rka257
  • 75
  • 2
  • 6
  • possible duplicate of [Need Oracle SQL to split up date/time range by day](http://stackoverflow.com/questions/17035176/need-oracle-sql-to-split-up-date-time-range-by-day) – DirkNM Nov 20 '14 at 12:48

2 Answers2

3

You need to first create a list of all dates that you are interested in, and then do a left join on that summing up the numbers:

with all_dates (the_date) as (
   select (select min(some_date) from a) + level - 1 as the_date
   from dual
   connect by (select min(some_date) from a) + level - 1 <= (select max(some_date) from a)
)
select ad.the_date, coalesce(sum(amount),0) as amount
from all_dates ad
  left join a on a.some_date = ad.the_date
group by ad.the_date
order by ad.the_date;

Note that I used some_date as the column name because date is a reserved word and should not be used as an identifier.

0

You can create an intermediate Table Using inside your request. Do this through a join with a list of dates, containing all the dates you absolutely want a response for, and use it instead of table A

Borbag
  • 597
  • 4
  • 21