sql - Conditional Running Total SQL statement: For every distinct month in transaction record-set, get the last twelve months sum of column

Question

There is a transactional table that contains dated data from 2006 - 2019, of which there are zero to many quantity records for each month (sale amount). The requirement is a SQL statement that, for every month, get me the sum of the last twelve months sales and the number of distinct months that contain values.

Below is what SQL so far (it's a little muddy because the date is held in a separate table and the date has to come from the batch)

WITH monthly_totals AS 
(SELECT  
    sum(t1.[Transaction_totals]) AS sum_of_sales, 
    CASE 
        WHEN t2.month_id % 100 < 10 AND t2.month_id/100 < 10 THEN CONCAT('200', t2.month_id % 100, '-0', t2.month_id / 100, '-01')
        WHEN t2.month_id % 100 < 10 AND t2.month_id/100 >= 10 THEN CONCAT('200', t2.month_id % 100, '-', t2.month_id / 100, '-01')
        WHEN t2.month_id % 100 >= 10 AND t2.month_id/100 < 10 THEN CONCAT('20', t2.month_id % 100, '-0',t2. month_id / 100, '-01')
        ELSE CONCAT('20', t2.month_id % 100, '-', t2.month_id / 100, '-01')
    END as date, 
    t2.month_id
FROM 
    TRANSACTION t1
INNER JOIN
    BATCH  t2 ON t1.batch_id = t2.batch_id
GROUP BY 
    CASE 
        WHEN t2.month_id % 100 < 10 AND t2.month_id/100 < 10 THEN CONCAT('200', t2.month_id % 100, '-0', t2.month_id / 100, '-01')
        WHEN t2.month_id % 100 < 10 AND t2.month_id/100 >= 10 THEN CONCAT('200', t2.month_id % 100, '-', t2.month_id / 100, '-01')
        WHEN t2.month_id % 100 >= 10 AND t2.month_id/100 < 10 THEN CONCAT('20', t2.month_id % 100, '-0',t2. month_id / 100, '-01')
        ELSE CONCAT('20', t2.month_id % 100, '-', t2.month_id / 100, '-01')
    END, 
    t2.month_id
) 
SELECT 
    sum(sum_of_sales) AS sum_of_sales, 
    count(distinct month_id) as month_count, 
    date
FROM 
    monthly_totals
WHERE 
date IN (select distinct month_id
        from 
            vw_dimDate as d
        where 
            date >= (
                select 
                    distinct(date)
                from 
                    dimDate
                where 
                    month_id = month_id
                    and dayNumber = 1) - 365
        and 
            date <= (
                select 
                    distinct(date)
                from 
                    dimDate
                where 
                    month_id = month_id
                    and dayNumber = 1
            )
        )
GROUP BY 
    date

But this brings up the following error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

For a table spanning 13 years, the end product should be something like this:

2006-01-01: $2,382,823 [sum of sales from 2005-01-01 - 2006-01-01], 1 [distinct month count of transactions]

2006-02-01 $4,382,823 [sum of sales from 2005-02-01 - 2006-02-01], 2 [distinct month count of transactions]

2006-03-01 $4,382,823 [sum of sales from 2005-03-01 - 2006-03-01], 3 [distinct month count of transactions]

... 2010-01-01: $23,323,204 [sum of sales from 2009-01-01 - 2010-01-01], 12 [distinct month count of transactions]

2011-01-01: $12,938,823 [sum of sales from 2009-02-01 - 2010-02-01], 12 [distinct month count of transactions]

etc... for every month in the table

标签： sqlsql-servertsqlgrouping

Answer 1

只是想知道如果你有一个日期表来驱动这个并将你的事务表加入它是否更好，那么你应该能够在过去的每 12 个月中使用 Sum Partition By 并计算 Transaction <> 0 的位置......如果你在下面加入到桌子。

该站点上的其他人可能会确定这是否可行。

with years as (
     select * from 
     (values(2006),(2007),(2008),(2009),(2010),(2011),(2012),(2013),(2014),(2015),(2016),(2017),(2018),(2019)
     ) as t (Year_id))
,months as (
     select * from 
     (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
     ) as t (month_id))
select Year_id,month_id,0 as [Transaction_totals]
from years
cross join months
order by 1,2