SQL Server 2008 Running Total

Question

I'm aware this has been asked but I'm completely baffled.

Trying to run a running total by day using SQL Server 2008. Have looked at solutions elsewhere but would am still completely perplexed.

The below code shows Daily sales but I cannot make a running total fit. Have looked at the similar solutions here but no luck. Have looked at partition by, order by, CTE etc but I'm just not there yet with SQL.

Would appreciate help, my code is below. I know this only returns the total grouped by day...

SELECT
    dim_invoice_date.invoice_date AS 'Invoice Date',
    round(SUM(invoice_amount_corp),2) AS 'Sales'
FROM 
    fact_om_bud_invoice 
JOIN
    dim_invoice_date ON fact_om_bud_invoice.dim_invoice_date_key = dim_invoice_date.dim_invoice_date_key 
WHERE   
    dim_invoice_date.current_cal_month IN ('Current') 
    AND fact_om_bud_invoice.budget_code IN ('BUDGET') 
GROUP BY 
    dim_invoice_date.invoice_date
HAVING 
    ROUND(SUM(invoice_amount_corp), 2) <> 0
ORDER BY
    'Invoice Date'

This returns the output:

Invoice Date     Sales 
-----------------------
4/10/2016       24,132 
5/10/2016       15,849 
6/10/2016       24,481 
7/10/2016       10,243 
10/10/2016      42,398 
11/10/2016      24,187

Required format is something like:

Invoice Date     Sales   Running Sales
-------------------------------------------
04/10/2016      24,132   24,132
05/10/2016      15,849   39,981 
06/10/2016      24,481   64,462
07/10/2016      10,243   74,705
10/10/2016      42,398   117,103
11/10/2016      24,187   141,290

dim_invoice_date is a numeric field, it's looking up a separate date table to display as date time.

I am not able to understand what you actually want are Invoice_amount_corp is numeric data type or you want the total of each day if there is no invoice also ?? — Rohit Gupta, Nov 04 '16 at 06:28
If there's no value on that day the having clause is to remove it. — Boothy_79, Nov 04 '16 at 06:30
i am not sure if it is possible to have summary to another column, what i believe is that you can do the summary by using "ROLL UP" which add a new row as a summary. — bogzy, Nov 04 '16 at 06:52
You are probably looking at solutions [here on SO](https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server)? Wrap the query you have in a CTE, or populate in a temporary table so you can refer to the information by table name, then apply the solution you prefer to the CTE or temporary table. Does that get you any further? Let us know... — TT., Nov 04 '16 at 07:16

score 0 · Answer 1 · answered Nov 04 '16 at 07:15

For example, can use WITH common_table_expression

WITH cte AS
     (
       SELECT 
         ROW_NUMBER() OVER(ORDER BY h.[Date]) RowN,
         h.[Date],
         SUM(s.Quantity) q
       FROM   
         Sales s
           JOIN Headers h
             ON s.ID_Headers = h.ID
       WHERE  
         h.[Date] > '2016.10.31'
       GROUP BY
         h.[Date]
     )

SELECT 
  c.[Date],
  c.q,
  SUM(c1.q)
FROM   
  cte c
    JOIN cte c1
      ON c1.RowN <= c.RowN
GROUP BY
  C.[Date],
  c.q
ORDER BY
  c.[Date]

SQL Server 2008 Running Total

1 Answers1