Running Total in SQL

I am trying to calculate a running total in SQL.  I need a report that shows

Date    Product     Daily Total      MTD Total
1/1/10    ABC           5                    5
1/2/10    ABC           3                    8
1/3/10    ABC           7                   15
1/1/10    XYZ           3                    3
1/2/10    XYZ           4                    7
1/3/10    XYZ           1                   8 ........etc ......

I have come up with this code so far .... but it keeps giving me a grand total for each day instead of a day to day total ...

SELECT     CALL_DATE, SERVICE_CODE, sum(REPORTING_TRAFFIC_CHARGES) as Daily,
(SELECT     SUM(REPORTING_TRAFFIC_CHARGES) AS MTD
FROM          HUB_RATED_TRAFFIC_SUMM AS B
WHERE      (CALL_DATE >= CALL_DATE)) AS 'Running Total'
FROM         HUB_RATED_TRAFFIC_SUMM AS A
WHERE     (CALL_DATE >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)) AND
(CALL_DATE < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
GROUP BY SERVICE_CODE, CALL_DATE
order by SERVICE_CODE, CALL_DATE
Who is Participating?

Commented:
You just need to change

WHERE      (CALL_DATE >= CALL_DATE)) AS 'Running Total'

To

WHERE      (A.CALL_DATE >= CALL_DATE)) AS 'Running Total'
0

Commented:
can you post what the output you want..
I am confused about whether you want daily total or daily total per product..
0

Software EngineerCommented:
Try this

Raj
``````SELECT	CALL_DATE AS [Date],
SERVICE_CODE AS Product,
sum(REPORTING_TRAFFIC_CHARGES) as [Daily Total],
(SELECT SUM(REPORTING_TRAFFIC_CHARGES)
FROM HUB_RATED_TRAFFIC_SUMM
WHERE SERVICE_CODE = A.SERVICE_CODE
AND CALL_DATE <= A.CALL_DATE) AS [MTD Total]
FROM HUB_RATED_TRAFFIC_SUMM A
GROUP BY SERVICE_CODE, CALL_DATE
order by SERVICE_CODE, CALL_DATE
``````
0

Commented:
I forgot to add a WHERE condition into the subquery.

Here's the modified query.
``````SELECT CALL_DATE
, SERVICE_CODE
, SUM(REPORTING_TRAFFIC_CHARGES) as Daily
,(SELECT SUM(REPORTING_TRAFFIC_CHARGES) AS MTD
FROM HUB_RATED_TRAFFIC_SUMM AS B
WHERE A.CALL_DATE >= CALL_DATE
AND CALL_DATE >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)
) AS 'Running Total'
FROM HUB_RATED_TRAFFIC_SUMM AS A
WHERE (CALL_DATE >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)) AND
(CALL_DATE < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
GROUP BY SERVICE_CODE, CALL_DATE
ORDER BY SERVICE_CODE, CALL_DATE
``````
0

Software EngineerCommented:
Hi Rhonda,

Corrections I made in my query are

1. You missed one more WHERE condition in the sub-query for SERVICE_CODE
2. CALL_DATE >= CALL_DATE
Changed to
CALL_DATE <= CALL_DATE
3. No need of the WHERE condition
...WHERE Â  Â  (CALL_DATE >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)) AND
Â  Â  Â  Â  Â  (CALL_DATE < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))

Raj
0

Commented:
Raj,

1. I missed that.
2. The problem is because the field was not prefixed to identify the table instance used and not the comparer operator.
3. Why? Condition limits the result to the dates from the previous month. Removing it will return the results for the whole table..

I modified my query to fix the issue as pointed out by Raj in 1.
``````SELECT CALL_DATE
, SERVICE_CODE
, SUM(REPORTING_TRAFFIC_CHARGES) as Daily
,(SELECT SUM(REPORTING_TRAFFIC_CHARGES) AS MTD
FROM HUB_RATED_TRAFFIC_SUMM AS B
WHERE A.SERVICE_CODE=SERVICE_CODE
AND A.CALL_DATE >= CALL_DATE
AND CALL_DATE >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)
) AS 'Running Total'
FROM HUB_RATED_TRAFFIC_SUMM AS A
WHERE (CALL_DATE >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)) AND
(CALL_DATE < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
GROUP BY SERVICE_CODE, CALL_DATE
ORDER BY SERVICE_CODE, CALL_DATE
``````
0

Software EngineerCommented:
Hi Thomasian,

2. (... CALL_DATE >= CALL_DATE) ...)
I mentioned so, because I assumed the first 'CALL_DATE' as the column of the table in the sub-query. I used so.

>> Condition limits the result to the dates from the previous month

You may be right. I didn't noticed this requirement as it is not mentioned in the question.

Raj
0

Software EngineerCommented:
Included the missed WHERE condition from the original question

Raj
``````SELECT	CALL_DATE AS [Date],
SERVICE_CODE AS Product,
sum(REPORTING_TRAFFIC_CHARGES) as [Daily Total] ,
(SELECT SUM(REPORTING_TRAFFIC_CHARGES)
FROM #HUB_RATED_TRAFFIC_SUMM
WHERE SERVICE_CODE = A.SERVICE_CODE
AND CALL_DATE <= A.CALL_DATE) AS [MTD Total]
FROM #HUB_RATED_TRAFFIC_SUMM A
WHERE     CALL_DATE >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) AND
CALL_DATE < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
GROUP BY SERVICE_CODE, CALL_DATE
order by SERVICE_CODE, CALL_DATE
``````
0

Software EngineerCommented:
The WHERE condition that fetch ONLY those records of last month was missed in my first post. ( http:#a30362912 )

My first post will fetch all the entries without any date restriction ( http:#a30371140 ).

Thanks Thomasian to point out that.

Raj
0

Commented:
Raj,

You will also need to include the start date in the subquery, otherwise it will get the running total starting from the earliest date in the table.
0

Software EngineerCommented:
Thomasian,

>> ... need to include the start date in the subquery, otherwise it will get the running total starting from the earliest date in the table

If I am right, there is no need to include an additional WHERE condition in this sub-query, as the sub-query & main query are using SAME tables.
The WHERE condition of main query table will apply to sub-query table as they are both the same tables, provided there is a link (WHERE condition of sub-query) from sub-query records to main query records is enough.

I first doubt whether I am wrong, when you commented about his. So I create a script and make sure about this. Please the see the attached script.

Raj
``````CREATE TABLE #HUB_RATED_TRAFFIC_SUMM
(
CALL_DATE	DATETIME,
SERVICE_CODE	VARCHAR(20),
REPORTING_TRAFFIC_CHARGES	INT
)

-- RECORDS FROM FEBRUARY, MARCH & APRIL
-- WE NEED TO GET RECORDS FROM MARCH ONLY
SET DATEFORMAT mdy
INSERT INTO #HUB_RATED_TRAFFIC_SUMM
SELECT '2/25/2010',    'ABC',           1
UNION all
SELECT '2/25/2010',    'ABC',           2
UNION all
SELECT '3/1/2010',    'ABC',           1
UNION all
SELECT '3/1/2010',    'ABC',           3
UNION all
SELECT '3/15/2010',    'ABC',           6
UNION all
SELECT '3/15/2010',    'ABC',           3
UNION all
SELECT '4/10/2010',    'ABC',           6
UNION all
SELECT '4/10/2010',    'ABC',           6

-- CURRENT RECORDS
SELECT * FROM #HUB_RATED_TRAFFIC_SUMM

-- FILTER RECORDS.
-- (SUB-QUERY HAVE NO ADDITONAL WHERE CONDITION THAN LINK TO MAIN QUERY TABLE)
-- THIS QUERY NEED TO FETCH RECORDS FROM LAST MONTH ONLY (MARCH, 2010)
SELECT	CALL_DATE AS [Date],
SERVICE_CODE AS Product,
sum(REPORTING_TRAFFIC_CHARGES) as [Daily Total] ,
(SELECT SUM(REPORTING_TRAFFIC_CHARGES)
FROM #HUB_RATED_TRAFFIC_SUMM
WHERE SERVICE_CODE = A.SERVICE_CODE
AND CALL_DATE <= A.CALL_DATE) AS [MTD Total]
FROM #HUB_RATED_TRAFFIC_SUMM A
WHERE     CALL_DATE >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) AND
CALL_DATE < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
GROUP BY SERVICE_CODE, CALL_DATE
order by SERVICE_CODE, CALL_DATE

DROP TABLE #HUB_RATED_TRAFFIC_SUMM
``````
0

Commented:
This is the result of your query:

Date      Product                        Daily Total      MTD Total
2010-03-01 00:00:00.000      ABC      4            7
2010-03-15 00:00:00.000      ABC      9            16

Shouldn't it be:

Date      Product                        Daily Total      MTD Total
2010-03-01 00:00:00.000      ABC      4            4
2010-03-15 00:00:00.000      ABC      9            13

which can be done by adding "AND CALL_DATE >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)" in the subquery?
0

Software EngineerCommented:
Thomasian,

You are right. :)

My thoughts first confused me. I realized where I went wrong.

Here is the correct query

Raj

``````-- THIS QUERY NEED TO FETCH RECORDS FROM LAST MONTH ONLY (MARCH, 2010)
SELECT	CALL_DATE AS [Date],
SERVICE_CODE AS Product,
sum(REPORTING_TRAFFIC_CHARGES) as [Daily Total] ,
(SELECT SUM(REPORTING_TRAFFIC_CHARGES)
FROM HUB_RATED_TRAFFIC_SUMM
WHERE SERVICE_CODE = A.SERVICE_CODE
AND CALL_DATE <= A.CALL_DATE
AND CALL_DATE >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)) AS [MTD Total]
FROM HUB_RATED_TRAFFIC_SUMM A
WHERE     CALL_DATE >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) AND
CALL_DATE < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
GROUP BY SERVICE_CODE, CALL_DATE
order by SERVICE_CODE, CALL_DATE
``````
0

Software EngineerCommented:
So Thomasian...

We both corrected each other's errors and now the queries we posted finally is assumed to be the right one :)

Thanks
Raj
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.