Relatively have Count(For that Day), Count for last 10 days and Count of last 20 days for each Vehicle

user_12487542 0
Viewed 303

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.

window-functions count group-by oracle sql
2 Answers

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'))!

user_14859550

This looks like a good place to use the window range specification (along with aggregation): this clause accepts an interval that defines the window, which is what you seem to be looking for:

select
    vehicle_id,
    trunc(sales_date) sales_day,
    count(*) sales_count,
    sum(count(*)) over(
        order by trunc(sales_date)
        range between interval '10' day preceding and current row
    ) sales_count_last_10,
    sum(count(*)) over(
        order by trunc(sales_date)
        range between interval '20' day preceding and current row
    ) sales_count_last_20
from mytable
group by vehicle_id, trunc(sales_date)
order by sales_day
user_106767160
Related