Running total in SQL 2005 - 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 in SQL 2005

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-07-24 : 02:41:42

Dear All,
I am using the below query to get the table details, i need to do the
running total for the field "Efficiency"
here is teh query.

SELECT ReportingMonth.Month, ReportingMonth.PlantID, ISNULL(Efficiency.Eff, 0) AS Efficiency,baselineEfficiency
FROM
(SELECT TOP (100) PERCENT ReportingMonth_1.Month, Plant.PlantID,plant.baselineEfficiency
FROM Plant CROSS JOIN
ReportingMonth AS ReportingMonth_1
WHERE (ReportingMonth_1.Month BETWEEN DATEADD(mm, - 1, GETDATE()) AND DATEADD(mm, 83, GETDATE()))
ORDER BY ReportingMonth_1.Month, Plant.PlantID,plant.baselineEfficiency) AS ReportingMonth LEFT OUTER JOIN
(SELECT TOP (100) PERCENT PlantID, DATEADD(dd, - DAY(P90Unit1Date) + 1, P90Unit1Date) AS Month, SUM(P90Fraction * P90Efficiency / 100)
AS Eff
FROM FanOut AS F
GROUP BY PlantID, DATEADD(dd, - DAY(P90Unit1Date) + 1, P90Unit1Date)
ORDER BY PlantID, Month) AS Efficiency ON ReportingMonth.PlantID = Efficiency.PlantID AND ReportingMonth.Month = Efficiency.Month

If somebody helps really help full for me..
Thanks in advance

Here is the data format

Month PlantID Efficiency running total baselineEfficiency
00:00.0 FFO 0.01 0.01 10
00:00.0 KLM1 0.235000003 0.24 10
00:00.0 KLM2 0.260000004 0.5 10
00:00.0 KLM3 0.033 0.83 10
00:00.0 KLM4 0 0.83 10

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-24 : 03:01:55

[code]WITH CTE
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY ReportingMonth.Month) as sqnumbs
ReportingMonth.Month,
ReportingMonth.PlantID,
ISNULL(Efficiency.Eff, 0) AS Efficiency,
baselineEfficiency
FROM (
SELECT TOP (100) PERCENT ReportingMonth_1.Month, Plant.PlantID,plant.baselineEfficiency
FROM Plant CROSS JOIN
ReportingMonth AS ReportingMonth_1
WHERE (ReportingMonth_1.Month BETWEEN DATEADD(mm, - 1, GETDATE()) AND DATEADD(mm, 83, GETDATE()))
ORDER BY ReportingMonth_1.Month, Plant.PlantID,plant.baselineEfficiency) AS ReportingMonth LEFT OUTER JOIN
(
SELECT TOP (100) PERCENT PlantID,
DATEADD(dd, - DAY(P90Unit1Date) + 1, P90Unit1Date) AS Month,
SUM(P90Fraction * P90Efficiency / 100) AS Eff
FROM FanOut AS F
GROUP BY PlantID, DATEADD(dd, - DAY(P90Unit1Date) + 1, P90Unit1Date)
ORDER BY PlantID, Month) AS Efficiency ON ReportingMonth.PlantID = Efficiency.PlantID AND ReportingMonth.Month = Efficiency.Month
)
SELECT ReportingMonth.Month,
ReportingMonth.PlantID,
ISNULL(Efficiency.Eff, 0) AS Efficiency,
baselineEfficiency,
SELECT SUM(efficiency) FROM CTE two WHERE two.sqnumbs<=one.sqnumbs
FROM CTE one
[/code]

Hope can help...but advise to wait pros with confirmation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources