Customer sales running totals 📈

Tip

Solution to the following problem:

• customer-sales-running-totals.md

Result Set

The result set should look like:

BalanceDate	CustomerID	RunningTotal
2014-06-01	11091	1243.5234
2014-06-01	11176	1222.8820
2014-06-01	11287	1115.2109
2014-06-02	11091	1243.5234
2014-06-02	11176	1222.8820
2014-06-02	11287	1115.2109
2014-06-03	11091	1243.5234
2014-06-03	11176	1222.8820
2014-06-03	11287	1115.2109
2014-06-04	11091	1243.5234
2014-06-04	11176	1222.8820
2014-06-04	11287	1115.2109
2014-06-05	11091	1243.5234
2014-06-05	11176	1222.8820
2014-06-05	11287	1158.1733
2014-06-06	11091	1243.5234
2014-06-06	11176	1222.8820
2014-06-06	11287	1158.1733
2014-06-07	11091	1282.1874
2014-06-07	11176	1222.8820
2014-06-07	11287	1158.1733
2014-06-08	11091	1282.1874
2014-06-08	11176	1222.8820
2014-06-08	11287	1158.1733
2014-06-09	11091	1282.1874
2014-06-09	11176	1222.8820
2014-06-09	11287	1158.1733
2014-06-10	11091	1314.2103
2014-06-10	11176	1222.8820
2014-06-10	11287	1158.1733
2014-06-11	11091	1314.2103
2014-06-11	11176	1222.8820
2014-06-11	11287	1158.1733
2014-06-12	11091	1314.2103
2014-06-12	11176	1222.8820
2014-06-12	11287	1158.1733
2014-06-13	11091	1314.2103
2014-06-13	11176	1222.8820
2014-06-13	11287	1158.1733
2014-06-14	11091	1314.2103
2014-06-14	11176	1228.3960
2014-06-14	11287	1191.3012
2014-06-15	11091	1314.2103
2014-06-15	11176	1388.0354
2014-06-15	11287	1191.3012
2014-06-16	11091	1314.2103
2014-06-16	11176	1388.0354
2014-06-16	11287	1191.3012
2014-06-17	11091	1314.2103
2014-06-17	11176	1430.9978
2014-06-17	11287	1191.3012
2014-06-18	11091	1314.2103
2014-06-18	11176	1430.9978
2014-06-18	11287	1191.3012
2014-06-19	11091	1314.2103
2014-06-19	11176	1430.9978
2014-06-19	11287	1191.3012
2014-06-20	11091	1314.2103
2014-06-20	11176	1430.9978
2014-06-20	11287	1191.3012
2014-06-21	11091	1314.2103
2014-06-21	11176	1430.9978
2014-06-21	11287	1191.3012
2014-06-22	11091	1314.2103
2014-06-22	11176	1430.9978
2014-06-22	11287	1191.3012
2014-06-23	11091	1314.2103
2014-06-23	11176	1430.9978
2014-06-23	11287	1191.3012
2014-06-24	11091	1314.2103
2014-06-24	11176	1430.9978
2014-06-24	11287	1191.3012
2014-06-25	11091	1314.2103
2014-06-25	11176	1430.9978
2014-06-25	11287	1191.3012
2014-06-26	11091	1314.2103
2014-06-26	11176	1430.9978
2014-06-26	11287	1191.3012
2014-06-27	11091	1314.2103
2014-06-27	11176	1430.9978
2014-06-27	11287	1191.3012
2014-06-28	11091	1314.2103
2014-06-28	11176	1430.9978
2014-06-28	11287	1191.3012
2014-06-29	11091	1314.2103
2014-06-29	11176	1458.6118
2014-06-29	11287	1191.3012
2014-06-30	11091	1314.2103
2014-06-30	11176	1458.6118
2014-06-30	11287	1289.6131

Expand for the DDL

solution(BalanceDate, CustomerID, RunningTotal) as (
    values
        ('2014-06-01', 11091, 1243.5234),
        ('2014-06-01', 11176, 1222.8820),
        ('2014-06-01', 11287, 1115.2109),
        ('2014-06-02', 11091, 1243.5234),
        ('2014-06-02', 11176, 1222.8820),
        ('2014-06-02', 11287, 1115.2109),
        ('2014-06-03', 11091, 1243.5234),
        ('2014-06-03', 11176, 1222.8820),
        ('2014-06-03', 11287, 1115.2109),
        ('2014-06-04', 11091, 1243.5234),
        ('2014-06-04', 11176, 1222.8820),
        ('2014-06-04', 11287, 1115.2109),
        ('2014-06-05', 11091, 1243.5234),
        ('2014-06-05', 11176, 1222.8820),
        ('2014-06-05', 11287, 1158.1733),
        ('2014-06-06', 11091, 1243.5234),
        ('2014-06-06', 11176, 1222.8820),
        ('2014-06-06', 11287, 1158.1733),
        ('2014-06-07', 11091, 1282.1874),
        ('2014-06-07', 11176, 1222.8820),
        ('2014-06-07', 11287, 1158.1733),
        ('2014-06-08', 11091, 1282.1874),
        ('2014-06-08', 11176, 1222.8820),
        ('2014-06-08', 11287, 1158.1733),
        ('2014-06-09', 11091, 1282.1874),
        ('2014-06-09', 11176, 1222.8820),
        ('2014-06-09', 11287, 1158.1733),
        ('2014-06-10', 11091, 1314.2103),
        ('2014-06-10', 11176, 1222.8820),
        ('2014-06-10', 11287, 1158.1733),
        ('2014-06-11', 11091, 1314.2103),
        ('2014-06-11', 11176, 1222.8820),
        ('2014-06-11', 11287, 1158.1733),
        ('2014-06-12', 11091, 1314.2103),
        ('2014-06-12', 11176, 1222.8820),
        ('2014-06-12', 11287, 1158.1733),
        ('2014-06-13', 11091, 1314.2103),
        ('2014-06-13', 11176, 1222.8820),
        ('2014-06-13', 11287, 1158.1733),
        ('2014-06-14', 11091, 1314.2103),
        ('2014-06-14', 11176, 1228.3960),
        ('2014-06-14', 11287, 1191.3012),
        ('2014-06-15', 11091, 1314.2103),
        ('2014-06-15', 11176, 1388.0354),
        ('2014-06-15', 11287, 1191.3012),
        ('2014-06-16', 11091, 1314.2103),
        ('2014-06-16', 11176, 1388.0354),
        ('2014-06-16', 11287, 1191.3012),
        ('2014-06-17', 11091, 1314.2103),
        ('2014-06-17', 11176, 1430.9978),
        ('2014-06-17', 11287, 1191.3012),
        ('2014-06-18', 11091, 1314.2103),
        ('2014-06-18', 11176, 1430.9978),
        ('2014-06-18', 11287, 1191.3012),
        ('2014-06-19', 11091, 1314.2103),
        ('2014-06-19', 11176, 1430.9978),
        ('2014-06-19', 11287, 1191.3012),
        ('2014-06-20', 11091, 1314.2103),
        ('2014-06-20', 11176, 1430.9978),
        ('2014-06-20', 11287, 1191.3012),
        ('2014-06-21', 11091, 1314.2103),
        ('2014-06-21', 11176, 1430.9978),
        ('2014-06-21', 11287, 1191.3012),
        ('2014-06-22', 11091, 1314.2103),
        ('2014-06-22', 11176, 1430.9978),
        ('2014-06-22', 11287, 1191.3012),
        ('2014-06-23', 11091, 1314.2103),
        ('2014-06-23', 11176, 1430.9978),
        ('2014-06-23', 11287, 1191.3012),
        ('2014-06-24', 11091, 1314.2103),
        ('2014-06-24', 11176, 1430.9978),
        ('2014-06-24', 11287, 1191.3012),
        ('2014-06-25', 11091, 1314.2103),
        ('2014-06-25', 11176, 1430.9978),
        ('2014-06-25', 11287, 1191.3012),
        ('2014-06-26', 11091, 1314.2103),
        ('2014-06-26', 11176, 1430.9978),
        ('2014-06-26', 11287, 1191.3012),
        ('2014-06-27', 11091, 1314.2103),
        ('2014-06-27', 11176, 1430.9978),
        ('2014-06-27', 11287, 1191.3012),
        ('2014-06-28', 11091, 1314.2103),
        ('2014-06-28', 11176, 1430.9978),
        ('2014-06-28', 11287, 1191.3012),
        ('2014-06-29', 11091, 1314.2103),
        ('2014-06-29', 11176, 1458.6118),
        ('2014-06-29', 11287, 1191.3012),
        ('2014-06-30', 11091, 1314.2103),
        ('2014-06-30', 11176, 1458.6118),
        ('2014-06-30', 11287, 1289.6131)
)

Solution

The solution for SQL Server is provided below.

SQL Server

with

Dates as (
        select cast('2014-06-01' as date) as BalanceDate
    union all
        select dateadd(day, 1, BalanceDate)
        from Dates
        where BalanceDate < '2014-06-30'
),

CustomerSalesUnioned as (
        select
            CustomerID,
            '2014-05-31' as OrderDate,
            sum(TotalDue) as TotalDue
        from Sales.SalesOrderHeader
        where 1=1
            and CustomerID in (11176, 11091, 11287)
            and OrderDate < '2014-06-01'
        group by CustomerID
    union all
        select
            CustomerID,
            OrderDate,
            sum(TotalDue) as TotalDue
        from Sales.SalesOrderHeader
        where 1=1
            and CustomerID in (11176, 11091, 11287)
            and OrderDate between '2014-06-01' and '2014-06-30'
        group by
            CustomerID,
            OrderDate
),

CustomerSales as (
    select
        CustomerID,
        OrderDate,
        -1 + lead(OrderDate, 1, '2014-07-01') over CustomerByOrderDate as NextOrderDate,
        sum(TotalDue) over CustomerByOrderDate as RunningTotal
    from CustomerSalesUnioned
    window CustomerByOrderDate as (
        partition by CustomerID
        order by OrderDate
    )
)

select
    Dates.BalanceDate,
    CustomerSales.CustomerID,
    CustomerSales.RunningTotal
from Dates
    left join CustomerSales
        on Dates.BalanceDate between CustomerSales.OrderDate and CustomerSales.NextOrderDate
order by
    Dates.BalanceDate,
    CustomerSales.CustomerID