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

First information you should give, which dbms is this??? – Eric Apr 22 '19 at 16:04 — Eric, Apr 22 '19 at 16:04

score 1 · Answer 1 · answered Apr 22 '19 at 15:41

Just wondering if better if you had a date table driving this and join your transaction table to it then you should be able to use Sum Partition By for each previous 12 months and count where Transaction <> 0... what if you join below to the table.

The other guys on this site will probably know for sure if this would work.

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

I think this is the way to go. Create your dates first... then use the dates you created to query your data based on those dates. — Code Novice, Apr 22 '19 at 18:32

score 0 · Answer 2 · edited Apr 22 '19 at 19:17

Going with the suggestion by Roger Clerkwell, I would first create a Dates table using a With Block (CTE) to then use in the remainder of your query. This would allow you to query the data based on the dates being pulled back from your dates table.

Since I work out of an Oracle Database, my solution shows how to create a dates table that produces a list of month start dates for the date range entered into the Query. It can also produce month END dates, however, I realize for this question the Month End days are not needed. I've written a countless number of reports where I have used this. With some small tweaks, this code can also produce distinct days if you are needing to query results day by day as well as all sorts of other helpful hacks once you fully understand the CONNECT BY LEVEL.

SELECT TRUNC(ADD_MONTHS('01-JUL-18', LEVEL-1), 'MM') START_DATE,
       LAST_DAY(ADD_MONTHS('01-JUL-18', LEVEL-1)) END_DATE
FROM DUAL CONNECT BY LEVEL <= CEIL(MONTHS_BETWEEN('30-JUN-19', '01-JUL-18'))
;

The code will produce results that look like the below screenshot.

SQL Server, however, does not need to utilize a dual table. See THIS Stackoverflow question if you have questions about the dual table.

score 0 · Answer 3 · answered Apr 23 '19 at 12:33

WITH CTE AS (
   SELECT 2006 AS Year
  UNION ALL
  SELECT Year + 1 FROM CTE  WHERE Year < 2019
 ),CTE2 AS (
 SELECT 1 AS Month
 UNION ALL
 SELECT Month + 1 FROM CTE2  WHERE Month < 12
 )
 select dateadd(mm,datediff(mm,-1,dateadd(year,-1,convert(varchar,year)+'- '+convert(varchar,Month)+'-'+'1')),-1)start_Date,
dateadd(mm,datediff(mm,-1,convert(varchar,year)+'-'+convert(varchar,Month)+'-  '+'1'),-1)End_Date
  from CTE 
cross join CTE2
order by 1
OPTION (MAXRECURSION 0)

score 0 · Answer 4 · answered Apr 23 '19 at 16:31

Thank you Roger, Stuphan and Dr. Stark for your recommendations. Not being as familiar with cross joins, I continued down another road yesterday and found an alternative solution. But, after looking at the date tables you've shown examples on how to create, if I were to re-do this today, I might take this route. For the record, this is how I resolved this: (basically used the same monthly totals data-set twice, which allowed for the first data-set to provide the dates, and the second data-set to provide the rolling yearly average)

WITH monthly_totals AS (SELECT
sum(t1.[Transaction_totals]) AS sum_of_sales, t5.date, product_id, t1.store_id

FROM TRANSACTIONS t1

INNER JOIN BATCH t2 ON t1.batch_id = t2.batch_id

INNER JOIN vw_dimDate t5 ON t4.month_id = t5.month_id AND t4.year = t5.year AND t5.dayNumber = 1

GROUP BY t5.date, product_id, t1.store_id ), yearly_totals as ( SELECT t1.date, t1.store_id, t1.product_id, sum(t2.sum_of_sales) AS sum_of_sales, count(distinct t2.date) as month_count

FROM monthly_totals t1

INNER JOIN monthly_totals t2 ON t2.date > DATEADD(Year, -1, CONVERT(DATE, t1.date)) AND t2.date <= CONVERT(DATE, t1.date) AND t1.store_id = t2.store_id AND t1.product_id = t2.product_id

GROUP BY t1.date, t1.store_id, t1.product_id

) SELECT date, store_id, product_id, sum_of_sales / month_count * 12 as sum_of_sales, month_count

ORDER BY t1.date, store_id, product_id

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

4 Answers4