I have a table like below (tablename: sales)

From that table I want to make a summary sales for 5 days period for each salesman. So the summary table that I want is look like this

I have created following query (PSQL) but the results were not same as I want. Assume today is 2022-08-05

with dateseries as 
(select generate_series(current_date-'4 days'::interval,
   current_date::date,
   '1 day'::interval)::date as periode)
select d.periode,coalesce(sum(s.sales),0) as total_sales,s.salesman from dateseries d
left outer join sales s
on d.periode=s.sales_datetime::date
group by d.periode, s.salesman order by d.periode

results:

Any advices would be so great. Thank you

CodePudding user response：

Step by step first aggregate the daily sales per salesperson (aggregated_sales CTE), create a list of days to report (days CTE), create a list of salesmen (salesmen CTE) and then query the sales for each day/salesman pair.

with aggregated_sales as 
(
 select sales_datetime::date sales_date, sum(sales) sales, salesman 
 from sales group by sales_datetime::date, salesman
),
days(sales_date) as 
(
 select d::date 
 from generate_series('2022-08-01', '2022-08-08', interval '1 day') d
),
salesmen (salesman) as 
(
 select distinct salesman from sales
)
select sales_date, coalesce(sales, 0) sales, salesman 
from (select * from days cross join salesmen) fl 
left outer join aggregated_sales ags using (sales_date, salesman);

The query may be shorter if CTEs are inlined yet I think that clarity and readability are more important than mere size.

In order to "make a summary sales for 5 days period for each salesman" replace generate_series('2022-08-01', '2022-08-08', interval '1 day') with generate_series(current_date - 4, current_date, interval '1 day').

CodePudding user response：

the results were not same as I want. Assume today is 2022-08-05

Please note that '2022-08-05'::date - '5 days'::interval will give you 2022-07-31, and not 2022-08-01 as you assume. Because of that, I think you meant it to be current_date - '4 days'::interval.

With that out of the way, here is one possible query:

with sales_by_date as (
    select
        salesman,
        sales_datetime::date,
        sum(sales) total_sales
    from sales
    where 
    -- assuming you need to have totals for salesmen that had sales in specified period only
        sales_datetime::date between current_date-'4 days'::interval and current_date 
    group by
        salesman,
        sales_datetime::date),
dateseries as (
    select
        distinct salesman,
        generate_series(current_date-'4 days'::interval, current_date, '1 day'::interval)::date as periode
    from sales_by_date)
select
    d.periode,
    coalesce(s.total_sales, 0) total_sales,
    d.salesman
from dateseries d
left join sales_by_date s 
 on d.periode = s.sales_datetime
and d.salesman = s.salesman
order by d.periode, d.salesman;

But you still have to figure out some requirements for this problem. E.g. what if for the specified period there are no sales at all in the sales table?