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 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply