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
Beverly PenneyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rushShahCommented:
can you post what the output you want..
I am confused about whether you want daily total or daily total per product..
0
ThomasianCommented:
You just need to change

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

To

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rajkumar GsSoftware 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

Open in new window

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

ThomasianCommented:
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

Open in new window

0
Rajkumar GsSoftware 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
ThomasianCommented:
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

Open in new window

0
Rajkumar GsSoftware 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
Rajkumar GsSoftware 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

Open in new window

0
Rajkumar GsSoftware 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
ThomasianCommented:
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
Rajkumar GsSoftware 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

Open in new window

0
ThomasianCommented:
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
Rajkumar GsSoftware 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

Open in new window

0
Rajkumar GsSoftware 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.