Skip to content
Advertisement

SQL Daily Outstanding Sales, Rolling Aggregate?

With the below test data I am trying to show how many sales have not been actioned by a colleague, an outstanding sale is any record that is still showing as “New”, I have been using the below to identify those.

The trouble I have is that I find it easy to show overall how many are still outstanding to date but I can’t figure out how to show how many were outstanding say 3 days ago. The idea is that this will show a trend when charted daily which will highlight if there has been an increase/decrease of outstanding sales by the end of each day. Ideally the output would be along the lines of the below

DataSet

Advertisement

Answer

You can use conditional aggregation and a cumulative sum:

This assumes that each saleId has at most one “new” and one “complete” record — which makes sense and is true in your sample data.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement