January 4, 2010 at 4:10 am
I have a stored Procedure that calculates savings, i need these savings to be grouped by month and by week, i need the savings to be a running total, for example if my savings for January are 100 and savings for February are 200 i want my savings total to show 300. At the moment my SQL calculates the savings and groups by both month and week, i now just need to have a running total, what i mean by this is that i want my total to be accumulative, so that it will be increasing always. Would appreciate any help seen a lot online about running totals but cant seem to get it working 🙁
My current output is something like this:
Year Month Week Savings
2009 January 1 100
2009 February 5 200
ALTER PROCEDURE dbo.SP_SAVINGS_REPORT
AS
/* Create temp table */
CREATE TABLE #savingstemp
(
pc_profile_id int,
shutdown_Time datetime NULL,
hibernate_Time datetime NULL,
sleep_Time datetime NULL,
startup_Time datetime NULL,
status varchar(50),
subpolicy_name varchar(50),
building_name varchar(50),
floor_name varchar(50),
room_name varchar(50),
hours_off_day int,
day_hour_rate float,
hours_off_night int,
night_hour_rate float,
pc_kwh_rate float,
savings float
)
/** Insert Values into Temp Table from View, including Day/Night Rates*/
insert into #savingstemp (pc_profile_id, shutdown_Time, hibernate_Time, sleep_Time, startup_Time, status, subpolicy_name, building_name, floor_name, room_name, day_hour_rate, night_hour_rate, pc_kwh_rate)
SELECT PC_PROFILE_ID, SHUTDOWN_TIME, HIBERNATE_TIME, SLEEP_TIME, STARTUP_TIME, STATUS, SUB_POLICY_NAME, BUILDING_NAME, FLOOR_NAME, ROOM_NAME, DAY_RATE, NIGHT_RATE, PC_POWER_RATING
FROM VIEW_SAVINGS_REPORT
/** Get hours off between Shutdown/hibernate/sleep and Startup and insert them into Hours Off Day or Hours Off Night fields*/
UPDATE #savingstemp
SET hours_off_day = case when DATEPART(hh, startup_Time) >= 7 AND DATEPART(hh, shutdown_Time) <= 23
then DATEDIFF(HOUR, shutdown_Time, startup_Time) else hours_off_day end,
hours_off_night = case when DATEPART(hh, startup_Time) <= 7 AND DATEPART(hh, shutdown_Time) >= 23
then DATEDIFF(HOUR, shutdown_Time, startup_Time) else hours_off_night end
WHERE STATUS = 'CLOSED';
UPDATE #savingstemp
SET hours_off_day = case when DATEPART(hh, hibernate_Time) >= 7 AND DATEPART(hh, startup_Time) <= 23
then DATEDIFF(HOUR, hibernate_Time, startup_Time) else hours_off_day end,
hours_off_night = case when DATEPART(hh, hibernate_Time) <= 7 AND DATEPART(hh, startup_Time) >= 23
then DATEDIFF(HOUR, hibernate_Time, startup_Time) else hours_off_night end
WHERE STATUS = 'CLOSED';
UPDATE #savingstemp
SET hours_off_day = case when DATEPART(hh, sleep_Time) >= 7 AND DATEPART(hh, startup_Time) <= 23
then DATEDIFF(HOUR, sleep_Time, startup_Time) else hours_off_day end,
hours_off_night = case when DATEPART(hh, sleep_Time) <= 7 AND DATEPART(hh, startup_Time) >= 23
then DATEDIFF(HOUR, sleep_Time, startup_Time) else hours_off_night end
WHERE STATUS = 'CLOSED';
/** Calculate the Total Savings, multiple hours * KWH Rate * Rate for both Day/Night Hours off*/
UPDATE #savingstemp
SET savings = (isnull(hours_off_day, 0) * pc_kwh_rate * day_hour_rate) + (isnull(hours_off_night, 0) * pc_kwh_rate * night_hour_rate)
/** Cumlative Savings to date grouped by Year, Month*/
select datepart(yyyy,startup_Time) as 'Year',
max(datename(m,startup_Time)) as 'Month',
max(datename(wk,startup_Time)) as 'Week',
sum(savings) as 'total_savings'
from #savingstemp
group by datepart(yyyy,startup_Time),
datepart(mm,startup_Time)
order by datepart(yyyy,startup_Time),
datepart(mm,startup_Time);
RETURN
January 4, 2010 at 4:18 am
please give us some sample data to play with in a ready to use format. See the first link in my signature for how to do it to get the fastest help.
January 4, 2010 at 4:43 am
--===== Create Temp Table
CREATE TABLE #savingstemp
(
pc_profile_id int,
shutdown_Time datetime NULL,
startup_Time datetime NULL,
subpolicy_name varchar(50),
day_hour_rate float,
night_hour_rate float,
pc_kwh_rate float,
hours_off_day int,
hours_off_night int,
savings float
)
--===== Insert Values in to Temp Table
insert into #savingstemp
(pc_profile_id, shutdown_Time, startup_Time, subpolicy_name, day_hour_rate, night_hour_rate, pc_kwh_rate)
SELECT '2', '04/09/2009 11:02:08' , '04/09/2009 16:03:03', 'Exempt', '0.25', '0.14', '3.9' UNION ALL
SELECT '2','04/09/2009 11:00:17','04/09/2009 16:03:00', 'Exempt', '0.25', '0.14', '3.9' UNION ALL
SELECT '2', '04/09/2009 14:52:17','04/09/2009 16:02:57', 'Kane', '0.25', '0.14', '3.9' UNION ALL
SELECT '3', '04/09/2009 12:12:10','04/09/2009 16:12:50', 'Kane', '0.25', '0.14', '3.9' UNION ALL
SELECT '4', '04/09/2009 13:12:10', '04/09/2009 18:12:50', 'Kane', '0.25', '0.14', '3.9'
--=====Calculate hours on/off
UPDATE #savingstemp
SET hours_off_day = case when DATEPART(hh, startup_Time) >= 7 AND DATEPART(hh, shutdown_Time) <= 23
then DATEDIFF(HOUR, shutdown_Time, startup_Time) else hours_off_day end,
hours_off_night = case when DATEPART(hh, startup_Time) <= 7 AND DATEPART(hh, shutdown_Time) >= 23
then DATEDIFF(HOUR, shutdown_Time, startup_Time) else hours_off_night end
WHERE STATUS = 'CLOSED';
--=====Calculate the Total Savings
UPDATE #savingstemp
SET savings = (isnull(hours_off_day, 0) * pc_kwh_rate * day_hour_rate) + (isnull(hours_off_night, 0) * pc_kwh_rate * night_hour_rate)
--===== Cumulative Savings to date grouped by Year, Month
select datepart(yyyy,startup_Time) as 'Year',
max(datename(m,startup_Time)) as 'Month',
max(datename(wk,startup_Time)) as 'Week',
sum(savings) as 'total_savings'
from #savingstemp
group by datepart(yyyy,startup_Time),
datepart(mm,startup_Time)
order by datepart(yyyy,startup_Time),
datepart(mm,startup_Time);
Thanks 🙂
January 4, 2010 at 7:54 am
If you can add a column to your table to store the running total in AND create a CLUSTERED index on startup_Time, then you could use the "quirky update" method described in this[/url] article. As an alternative you could copy the preaggregated data into an indexed temp table and go from there.
If you don't want to use the quirky update, you could use the following code (disclaimer: since the code is a triangular join, it can lead to performance issues depending on the number of rows to process. To reduce this influence you could preaggregate the data by Year, Month and week and apply the concept to this intermediate table):
;with cte as
(
SELECT
datepart(yyyy,st.startup_Time) as 'Year',
(datename(m,st.startup_Time)) as 'Month',
(datename(wk,st.startup_Time)) as 'Week',
(
SELECT SUM(st2.savings)
FROM #savingstemp st2
WHERE
st2.startup_Time <= st.startup_Time
) AS AccountRunningTotal
FROM #savingstemp st
)
SELECT [Year], [Month],[week],max(AccountRunningTotal) AS total_savings
FROM cte
GROUP BY [Year], [Month],[week]
ORDER BY [Year], [Month],[week]
January 4, 2010 at 9:22 am
Thanks for the advice and code, The code you provided works great for me, thanks for that, really appreciate the help 🙂
January 4, 2010 at 9:35 am
You're very welcome and thank you for the feedback! 🙂
I still recommend reading the article I referred to. It's helpful to know how it works. Just in case... 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply