Running total on 2 columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Running total on 2 columns

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-08-05 : 04:09:46

Dear All,

I need to do the running total on 2 columns.
The table stucture is
MONTH PlantID Efficiency baselineEfficiency

I am using this query to do the running total
SELECT CONVERT(VARCHAR,MONTH,106) AS MONTH,PlantID,Efficiency,baselineEfficiency,todisplayin1page,EFFICIENCY+10+COALESCE((SELECT SUM(EFFICIENCY) FROM
#TEMP1 B WHERE B.MONTH < A.MONTH and PlantID = B.PLANTID),0) AS RUNNINGTOTAL

But i need to do it on Plantid column as well..there are 6 plantid will be there & month field will be there.I need to do runnin gtotal based on 2 columns..
Pls help me

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-05 : 04:16:55

Something like this? The reason I just guess is that I have no idea of which column you want to do your second running total.

SELECT		CONVERT(VARCHAR, a.[Month], 106) AS [Month],
		a.PlantID,
		a.Efficiency,
		a.baselineEfficiency,
		a.toDisplayInOnePage,
		a.EFFICIENCY + 10 + COALESCE(f.EFFICIENCY ,0) AS RUNNINGTOTAL,
		a.baselineEfficiency + 10 + COALESCE(f.baselineEfficiency ,0) AS RUNNINGTOTAL2
FROM		#Temp1 AS a
CROSS APPLY	(
			SELECT	SUM(b.Efficiency) AS Efficiency,
				SUM(b.baselineEfficiency) AS baselineEfficiency
			FROM	#Temp1 AS b
			WHERE	b.[Month] < a.[Month]
				AND b.PlantID = a.PlantID
		) AS f

N 56°04'39.26"
E 12°55'05.63"

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-08-05 : 04:55:37

Its not wrking properly.
Please help

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-08-05 : 05:04:02

Hi Peso,

Sorry for misunderstanding with your answer.
Its working properly..

Thanks a lot Peso..Its great help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources