CODEWITHSUNDEEP
sqlpostgresqloracle-databasejoin
user15181291
user15181291

Reputation:

Calculating sales on daily basis comparing the previous sales

How can I calculate sales on the basis of date comparing the previous, current, and upcoming dates?

order date  |   total qty
------------------------------
    02/01/2021  |   5   
    02/04/2021  |   10  
    02/06/2021  |   7
    02/08/2021  |   10
    02/10/2021  |   2

Upvotes: 0

Views: 156

Answers (1)

Caius Jard
Caius Jard

Reputation: 74660

Your bucket column could be given by:

CONCAT(
  DATE_PART('day', AGE('2021-02-01', orderdate))*7+1,
  '-',
  (DATE_PART('day', AGE('2021-02-01', orderdate))+1)*7,
  ' days'
)

Your cumu total by:

SUM(total) OVER(PARTITION BY DATE_PART('day', AGE('2021-02-01', orderdate)) ORDER BY orderdate)

A sum has an implied "rows unbounded preceding" if it has an order by

I presume you're starting your report somewhere (eg your front end does where orderdate > x so it can supply the min date for the functions too. If it doesn't then you might benefit from a cte that calls the min orderdate

Upvotes: 1

Related Questions