Im using SQL stored procedure that calculates the total savings, which is just one value, this works fine. I want to create a cumulative chart which will show my savings to date. Im using a line graph in MS Chart controls, to do this I need to display my cumulative savings
per month or week.
Not sure how to do this, i already have a field called "startup_Time" which is a 'date time' field and records the date and time "04/11/2009 14:52:17" for example. I have then calculated my total savings per record, i will however eventually have a lot of
records and multiple records per day etc. Im really just not sure how to group by/filter my overall savings per month or by week and to accumulative those savings. My stored procedure is below, Would appreciate any help on how to do this, thanks
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)
/**Getting total Savings to Date*/
Select SUM(savings) as savings_total
From #savingstemp
RETURN
Im using SQL stored procedure that calculates the total savings, which is just one value, this works fine. I want to create a cumulative chart which will show my savings to date. Im using a line graph in MS Chart controls, to do this I need to display my cumulative savings
per month or week.
Not sure how to do this, i already have a field called "startup_Time" which is a 'date time' field and records the date and time "04/11/2009 14:52:17" for example. I have then calculated my total savings per record, i will however eventually have a lot of
records and multiple records per day etc. Im really just not sure how to group by/filter my overall savings per month or by week and to accumulative those savings. My stored procedure is below, Would appreciate any help on how to do this, thanks
You'll just need to include year, month and week in the select, and then group by it:
Select SUM(savings) as savings_total ,datepart(year,startup_time), datepart(month,startup_time), datepart(week,startup_time)
From #savingstemp
GROUP BY datepart(year,startup_time), datepart(month,startup_time), datepart(week,startup_time)
ORDER BY datepart(year,startup_time), datepart(month,startup_time), datepart(week,startup_time)
If this answered your question, be sure to mark it as the answer. Then you give credit to people who help you, and others will know that the question is already answered.
My SQL Server blog (swedish): http://www.underlandet.com/SqlServer
Thanks for the post appreciate it, that site is great very helpful :) i have it grouping by month now but i just have 1 last thing to do. I need my chart to be cumulative as the chart is "savings to Date" so i want to show the savings continuously increasing
as each month passes, Im not too sure how to do this :( Thanks for your help, i found that site very helpful, this is the code Im using now, works great :)
select datepart(yyyy,startup_Time) as 'Year',
max(datename(m,startup_Time)) as 'Month',
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);
i have it grouping by month now but i just have 1 last thing to do. I need my chart to be cumulative as the chart is "savings to Date" so i want to show the savings continuously increasing as each month passes, Im not too sure how to do this :(
I think you would like to calculate running totals, right?
nt86
Member
19 Points
42 Posts
Grouping Stored Procedure Results by Month/Week
Dec 18, 2009 01:48 PM|LINK
Im using SQL stored procedure that calculates the total savings, which is just one value, this works fine. I want to create a cumulative chart which will show my savings to date. Im using a line graph in MS Chart controls, to do this I need to display my cumulative savings per month or week.
Not sure how to do this, i already have a field called "startup_Time" which is a 'date time' field and records the date and time "04/11/2009 14:52:17" for example. I have then calculated my total savings per record, i will however eventually have a lot of records and multiple records per day etc. Im really just not sure how to group by/filter my overall savings per month or by week and to accumulative those savings. My stored procedure is below, Would appreciate any help on how to do this, thanks
Jian Kang - ...
All-Star
33132 Points
2465 Posts
Re: Grouping Stored Procedure Results by Month/Week
Dec 22, 2009 07:08 AM|LINK
Hi nt86,
Please refer to the following two links:
How to group sales by month with SQL Server
http://www.sqlhacks.com/pmwiki.php/Dates/GroupByMonth
How to group by week with MSSQL
http://www.sqlhacks.com/index.php/Dates/Group-By-Week
Microsoft Online Community Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
BlogPortalis...
Contributor
2451 Points
345 Posts
Re: Grouping Stored Procedure Results by Month/Week
Dec 22, 2009 09:24 AM|LINK
You'll just need to include year, month and week in the select, and then group by it:
Select SUM(savings) as savings_total ,datepart(year,startup_time), datepart(month,startup_time), datepart(week,startup_time) From #savingstemp GROUP BY datepart(year,startup_time), datepart(month,startup_time), datepart(week,startup_time) ORDER BY datepart(year,startup_time), datepart(month,startup_time), datepart(week,startup_time)My SQL Server blog (swedish): http://www.underlandet.com/SqlServer
nt86
Member
19 Points
42 Posts
Re: Grouping Stored Procedure Results by Month/Week
Dec 22, 2009 11:02 AM|LINK
Jian Kang,
Thanks for the post appreciate it, that site is great very helpful :) i have it grouping by month now but i just have 1 last thing to do. I need my chart to be cumulative as the chart is "savings to Date" so i want to show the savings continuously increasing as each month passes, Im not too sure how to do this :( Thanks for your help, i found that site very helpful, this is the code Im using now, works great :)
select datepart(yyyy,startup_Time) as 'Year', max(datename(m,startup_Time)) as 'Month', 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);Jian Kang - ...
All-Star
33132 Points
2465 Posts
Re: Grouping Stored Procedure Results by Month/Week
Dec 23, 2009 01:48 AM|LINK
I think you would like to calculate running totals, right?
Please refer to the following links:
http://www.sqlteam.com/article/calculating-running-totals
http://www.sqlmag.com/Articles/ArticleID/102251/102251.html?Ad=1
Microsoft Online Community Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help.