Sean Lange (11/27/2012)
leesider (11/27/2012)
OK, as I said in my last post I need to extend it a little further to include a running total. Cadavre is correct, I don't need a cursor. I need to execute an inner select from another table that also contains a startup_Time. I want to compare this startup_Time with the startup_Time in the outer select and as it goes from one week to the next, select from the column KWH_savings and add it up for each week in the inner select.What I have below does it except it is too slow:
;with cte as
(
SELECT [YEAR] = YEAR(st.startup_Time),
DATEPART(ww, st.startup_Time) as WeekNumber,
(
SELECT SUM(st2.KWH_savings)
FROM #KWHtemp st2
WHERE
st2.startup_Time <= st.startup_Time
) AS AccountRunningTotal
FROM #KWHtemp st
)
SELECT *
FROM cte
It is the inner select that is slowing it down, I don't know if I can do it a different way that is not so slow.
This is where knowing the entire problem up front would be very helpful. You don't need to do an "inner query" here as you call it. I think what you really want here is a running total. What you have done here is what is known as a triangular join and as you have discovered the performance is hideous. Take a look at this article by Jeff Moden. http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]
He explains how the running total works and how to make it fast.
If you need help with the actual code you need to post ddl, sample data and desired output based on the article in my signature.
Yeah I know; I wasn't the one that wrote it though. I decided on the solution below. The procedure was slow because everytime it ran it was entering the same data everytime. It only needs to calculate the "newest" week. For example this week is the 48th week of the year. It runs this week and calculates all 48 weeks of the year. Next week it calculates all 48 again and the 49th week. This is unnecessary so I created a pemanent table (TBL_SP_ACCUMLATIVE_WEEK_KWH_ALL_II) and entered zero into the column for "week".
I select everything from the temporary table KWHtemp that has a "week" value greater than the maximum "week" value in the table TBL_SP_ACCUMLATIVE_WEEK_KWH_ALL_II.
The first time it runs it will take a while because the maximum "week" value in the table TBL_SP_ACCUMLATIVE_WEEK_KWH_ALL_II is zero.
The next time it runs it will only add one new week because the maximum week value in TBL_SP_ACCUMLATIVE_WEEK_KWH_ALL_II will be 48.
;with cte as
(
SELECT
datepart(yyyy,st.startup_Time) as 'Year',
datepart(week,st.startup_Time) as 'Week_Num',
datename(wk,st.startup_Time) as 'Week',
(
SELECT SUM(st2.KWH_savings)
FROM #KWHtemp st2
WHERE
st2.startup_Time <= st.startup_Time
) AS AccountRunningTotal
FROM #KWHtemp st where datepart(week,st.startup_Time)>(select MAX (week) from TBL_SP_ACCUMLATIVE_WEEK_KWH_ALL_II)
)
insert into dbo.TBL_SP_ACCUMLATIVE_WEEK_KWH_ALL_II(Year,week,total_savings)
SELECT [Year], [week],max(AccountRunningTotal) AS total_savings
FROM cte
GROUP BY [Year], [week], [Week_Num]
ORDER BY [Year], [Week_Num]
delete from dbo.TBL_SP_ACCUMLATIVE_WEEK_KWH_ALL_II where week=0
SELECT *
FROM dbo.TBL_SP_ACCUMLATIVE_WEEK_KWH_ALL_II
GROUP BY [Year], [week], total_savings
ORDER BY [Year], [week],total_savings