I am trying to see the sales of the vehicle for the day and create two more columns which tells me the count of sales for the last 10 days and count of sales for the last 20 days. There may be multiple sales for same day and same vehicle. My goal is to take the Distinct Vehicle and Date and see their sales count.
The N days count should be relative to the Date present that row.
My Data Looks like:
Vehicle_ID Sales_Date
X500 01/03/2020 02:00:00 PM
X500 01/11/2020 05:00:00 PM
X500 01/25/2020 06:00:00 PM
X500 01/25/2020 01:00:00 PM
X500 02/13/2020 06:00:00 PM
X500 02/21/2020 02:00:00 PM
My target table should look like
Vehicle_ID Sales_Date Sales_Count Sales_Count_last_10 Sales_Count_last_20
X500 01/03/2020 1 1 1
X500 01/11/2020 1 2 2
X500 01/25/2020 2 2 3
X500 02/13/2020 1 1 4
X500 02/21/2020 1 2 2
I tried the following Query:
SELECT TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY') Sales_Date1, Vehicle_ID,
COUNT(*) OVER (PARTITION BY Vehicle_ID, TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY')) SALES_COUNTS,
SUM(CASE WHEN TO_DATE(Sales_Date) BETWEEN TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY') - 10 AND TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY')
THEN 1 ELSE 0 END) OVER (PARTITION BY Vehicle_ID, TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY'))
Sales_Count_last_10,
SUM(CASE WHEN TO_DATE(Sales_Date) BETWEEN TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY') - 20 AND TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY')
THEN 1 ELSE 0 END) OVER (PARTITION BY Vehicle_ID, TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY'))
Sales_Count_last_10
FROM TABLE1
It just created the same values for Sales_Count, Sales_Count_last_10 and Sales_Count_last_20. It is N-day columns were just duplicate of Sales_Count
I also tried the following query.
SELECT TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY') Sales_Date1, Vehicle_ID, COUNT(*) SALES_COUNTS,
SUM(CASE WHEN TO_DATE(Sales_Date) BETWEEN TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY') - 10 AND TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY')
THEN 1 ELSE 0 END) Sales_Count_last_10,
SUM(CASE WHEN TO_DATE(Sales_Date) BETWEEN TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY') - 20 AND TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY')
THEN 1 ELSE 0 END) Sales_Count_last_20
FROM TABLE1
GROUP BY Vehicle_ID, TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY')
It showed the same result like the previous query. Duplicating the Sales_Count for the N-days column.
Please help me with this issue.
You can use the window clause of sum to specify how many days back you want to look.
With range between N preceding and current row, it includes all values from the current row - N to the current
Which gives:
with rws as (
select 'X500' vehicle_id, to_date ( '01/03/2020 02:00:00 PM', 'mm/dd/yyyy hh:mi:ss pm' ) sales_date from dual union all
select 'Y300' vehicle_id, to_date ( '01/11/2020 05:00:00 PM', 'mm/dd/yyyy hh:mi:ss pm' ) sales_date from dual union all
select 'Q240' vehicle_id, to_date ( '01/25/2020 06:00:00 PM', 'mm/dd/yyyy hh:mi:ss pm' ) sales_date from dual union all
select 'Q240' vehicle_id, to_date ( '01/25/2020 01:00:00 PM', 'mm/dd/yyyy hh:mi:ss pm' ) sales_date from dual union all
select 'F310' vehicle_id, to_date ( '02/13/2020 06:00:00 PM', 'mm/dd/yyyy hh:mi:ss pm' ) sales_date from dual union all
select 'E990' vehicle_id, to_date ( '02/21/2020 02:00:00 PM', 'mm/dd/yyyy hh:mi:ss pm' ) sales_date from dual
)
select distinct trunc ( sales_date ), vehicle_id,
count(*) over (
partition by vehicle_id, trunc(sales_date)
) sales_counts,
count (*) over (
order by trunc ( sales_date )
range between 10 preceding and current row
) last_10,
count (*) over (
order by trunc ( sales_date )
range between 20 preceding and current row
) last_20
from rws
order by 1, 2
TRUNC(SALES_DATE) VEHICLE_ID SALES_COUNTS LAST_10 LAST_20
03-JAN-2020 00:00:00 X500 1 1 1
11-JAN-2020 00:00:00 Y300 1 2 2
25-JAN-2020 00:00:00 Q240 2 2 3
13-FEB-2020 00:00:00 F310 1 1 3
21-FEB-2020 00:00:00 E990 1 2 2
PS if you want to remove the time component from a date in Oracle Databse, just trunc it. Much easier than to_date(to_char(..., 'fmt'))!