Using Oracle to fetch last ten days of sales, even if day has none

Question

I am trying to select sales data from the last 10 days, and include the date with a 0 where there were no sales.

To do so, I am experimenting with using dual and connect by level, but I do not know why the dates do not match, and query is failing.

Here is the basic working query:

SELECT 
  current_date - (LEVEL - 1)
  ,(LEVEL - 1)  as result
FROM Dual CONNECT BY Level <= 10
order by result desc

And the results:

Current_date-(LEVEL-1) RESULT
13-MAR-15   9
14-MAR-15   8
15-MAR-15   7
16-MAR-15   6
17-MAR-15   5
18-MAR-15   4
19-MAR-15   3
20-MAR-15   2
21-MAR-15   1
22-MAR-15   0

So I thought this would work out great, Now that I had the dates that I wanted, I could try to add a subquery to fetch the sales (I thought about a join but was not clear about how to join with the level in the query):

SELECT 
  current_date - (LEVEL - 1)
  ,(select count(1) from sales where for_date=(current_date - (LEVEL - 1))) as sale_volume
  ,(LEVEL - 1)  as result
FROM Dual CONNECT BY Level <= 10
order by result desc

However, it always retuns 0, even on days that I know have sales:

Current_date-(LEVEL-1) SALE_VOLUME RESULT
13-MAR-15   0   9
14-MAR-15   0   8
15-MAR-15   0   7
16-MAR-15   0   6
17-MAR-15   0   5
18-MAR-15   0   4
19-MAR-15   0   3
20-MAR-15   0   2
21-MAR-15   0   1
22-MAR-15   0   0

Aheho · Accepted Answer · 2015-03-22T14:41:54.833

1

SELECT 
    count(sales.for_Date) 
FROM 
   (SELECT 
       Trunc(current_date) - (LEVEL - 1) as d
   FROM 
       Dual CONNECT BY Level <= 10) as DateRangeTbl
LEFT JOIN
    sales 
ON
    Sales.for_Date = DateRangeTbl.d
GROUP BY
    DateRangeTbl.d

edited Mar 22 '15 at 14:41

answered Mar 22 '15 at 13:59

Aheho

12,622
13
54
83

For some reason, it did not like me naming the subquery "as DateRangeTbl" but was happy with me removing the as however it still returns all 0s, and that makes me think thre is something wrong with the dates matching. – nycynik Mar 22 '15 at 14:10
Does the for_date column contain Times as well? – Aheho Mar 22 '15 at 14:10
It is a "date" column in oracle, and if i query one, it shows "2015-03-15 00:00:00" – nycynik Mar 22 '15 at 14:18
The Current_date function returns a time component. – Aheho Mar 22 '15 at 14:23
1

Use trunc(current_date) to strip the time out of it. – Aheho Mar 22 '15 at 14:42

score 0 · Answer 2 · answered Mar 23 '15 at 16:14

One addition to the solution given by Aheho - You will also need to strip out the time component from Sales.for_Date column otherwise the equality condition in ON clause will not work. This can be achieved by either trunc() or converting both the dates to string using to_char(your-date,date-format) in the ON clause. I got it to work on my database with both the methods. Date format can be for example 'DD-MON-YYYY'.

Using Oracle to fetch last ten days of sales, even if day has none

2 Answers2