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.
select saleID, count(*) group by saleID having count(*)=1
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
Date VolumeOutstanding 2020-01-01 0 2020-01-02 1 2020-01-03 3 2020-01-04 2
DataSet
SaleID Date Outcome 1 2020-01-01 New 1 2020-01-01 Complete 2 2020-01-01 New 2 2020-01-02 Complete 3 2020-01-03 New 4 2020-01-03 New 5 2020-01-03 New 5 2020-01-04 Complete
Advertisement
Answer
You can use conditional aggregation and a cumulative sum:
select date, sum(sum(case when outcome = 'New' then 1 when outcome 'Complete' then -1 else 0 end) ) over (order by date) as VolumeOutstanding from t group by date order by date;
This assumes that each saleId
has at most one “new” and one “complete” record — which makes sense and is true in your sample data.