0

I need to create a report which splits the values from table A into a row-by-date format. So for example If I create a sub-set of dates for the past week and sales in the past week, I need to output the 'REPORT OUTPUT' format. Just struggling to think of a way to 'loop' through the dates in date_range and say find sales in sales_in_range and do a count, then output the results.

WITH
    date_range (date_val)
    as
    (select sysdate-7 + level 
     from dual 
     connect by level <= ( sysdate - (sysdate-7))
    ),

    sales_in_range (itemcode,sale_date,value)
    as
    (select itemcode,sale_date,value 
     from sale_table 
     where sale_date between (sysdate-7) and (sysdate)
    );

-- REPORT OUTPUT SQL

;

Sub queries provide the below information.

Date Range:

select * from date_range
date_val
12-NOV-21
13-NOV-21
14-NOV-21
15-NOV-21
16-NOV-21
17-NOV-21
18-NOV-21

Sales in date range:

select * from sales_in_range;
itemcode    sale_date   value
00001       12-NOV-21   0
00002       13-NOV-21   0
00003       15-NOV-21   0
00004       15-NOV-21   0
00005       16-NOV-21   0
00006       16-NOV-21   0
00007       16-NOV-21   0
00008       18-NOV-21   0
00009       18-NOV-21   0
00010       18-NOV-21   0

REPORT OUTPUT:

sale_date   sale_count
12-NOV-21   1
13-NOV-21   1
14-NOV-21   0
15-NOV-21   2
16-NOV-21   3
17-NOV-21   0
18-NOV-21   3

Thanks in advance

K

1

I'm not really sure what the purpose of the query you've written so far is supposed to be, but any reason you can't just simply use the GROUP BY clause with the COUNT() aggregate function on your sale_table like so?

SELECT sale_date, COUNT(*) AS sale_count
FROM sale_table
GROUP BY sale_date
ORDER BY sale_date

If you need to filter on just a certain date range, then you can add a WHERE clause also (assuming sale_date is of a date-based data type):

SELECT sale_date, COUNT(*) AS sale_count
FROM sale_table
WHERE sale_date >= TO_DATE('SomeStartDate', 'SomeDateFormat')
    AND sale_date < TO_DATE('SomeEndDate', 'SomeDateFormat')
GROUP BY sale_date
ORDER BY sale_date

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.