March 23, 2010 at 5:56 am
Hey,
The following is a stored procedure I'm using to calculate savings and to group these savings in regards to the policy there in. It works as it should what I now need to do is have the ability to show the policy savings per week, I need to do this as I want to compare week on week. I'm filtering the current report by the last 7 days or week, but what I really need to able to do is to filter it by the previous week if thats possible. Theres a Startup Time which I'm using for date filtering its a field in my database which records a time when each record is complete.
A way which I was thinking of doing it was to have one stored procedure which filter through last 7 days, i.e. last week and an other stored procedure which filtered for the previous week, the graph below hopefully explains what I'm trying to do 🙂
Would appreciate any advice on whats the best way to this.
Thanks
|
|
| Looking to get savings between Day7 - Day14
| <------------------>
|______________________________
Day 1 Day7 Day 14
ALTER PROCEDURE [dbo].[SP_WEEK_POLICY]
AS
--=====Create temp table
CREATE TABLE #savingstemp
(
audit_id int,
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),
hours_off_day int,
hours_off_night int,
day_hour_rate float,
night_hour_rate float,
pc_kwh_rate float,
savings float
)
--===== Insert Values in to Temp Table
insert into #savingstemp
(audit_id, pc_profile_id, shutdown_Time, hibernate_Time, sleep_Time, startup_Time, status, subpolicy_name, day_hour_rate, night_hour_rate, pc_kwh_rate)
SELECT '1', '2', '01/09/2010 17:02:08', NULL, NULL, '01/10/2010 09:03:03', 'CLOSED', 'Exempt', '0.25', '0.14', '3.9' UNION ALL
SELECT '2', '3', '01/10/2010 18:02:08', NULL, NULL, '01/11/2010 10:03:03', 'CLOSED', 'Manufacturing', '0.25', '0.14', '3.9' UNION ALL
SELECT '3', '4', '01/11/2010 18:02:08', NULL, NULL, '01/12/2010 09:03:03', 'CLOSED', 'ORB', '0.25', '0.14', '3.9' UNION ALL
SELECT '4', '5', '02/09/2010 17:00:17', NULL, NULL,'02/10/2010 10:03:00', 'CLOSED', 'Manufacturing', '0.25', '0.14', '3.9' UNION ALL
SELECT '5', '6', '02/10/2010 18:02:08', NULL, NULL, '02/11/2010 08:03:03', 'CLOSED', 'ORB', '0.25', '0.14', '3.9' UNION ALL
SELECT '6', '7', '02/11/2010 18:02:08', NULL, NULL, '02/12/2010 09:03:03', 'CLOSED', 'Exempt', '0.25', '0.14', '3.9' UNION ALL
SELECT '7', '8', '02/12/2010 17:02:08', NULL, NULL, '02/13/2010 09:03:03', 'CLOSED', 'Kane', '0.25', '0.14', '3.9' UNION ALL
SELECT '8', '9', '02/20/2010 16:02:08', NULL, NULL, '02/21/2010 08:03:03', 'CLOSED', 'Exempt', '0.25', '0.14', '3.9' UNION ALL
SELECT '9', '10', '02/21/2010 17:02:08', NULL, NULL, '02/22/2010 11:03:03', 'CLOSED', 'Kane', '0.25', '0.14', '3.9' UNION ALL
SELECT '10', '11', '03/15/2010 17:52:17', NULL, NULL, '03/16/2010 11:02:57', 'CLOSED', 'Kane', '0.25', '0.14', '3.9' UNION ALL
SELECT '11', '12', '03/16/2010 18:12:10', NULL, NULL, '03/17/2010 10:12:50', 'CLOSED', 'ORB', '0.25', '0.14', '3.9' UNION ALL
SELECT '12', '13', '03/17/2010 19:12:10', NULL, NULL, '03/18/2010 09:12:50', 'CLOSED', 'ELEC', '0.25', '0.14', '3.9'
--===== 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 = isnull(savings.hours_off_day, 0) ,
hours_off_night = isnull(savings.hours_off_night, 0),
savings = (isnull(savings.hours_off_day, 0) * pc_kwh_rate * day_hour_rate) + (isnull(savings.hours_off_night, 0) * pc_kwh_rate * night_hour_rate)
FROM #savingstemp inner join (
select audit_id, sum(datediff(MINUTE, day_start, day_end))/ 60.0 as hours_off_day, sum(datediff(MINUTE, night_start, night_end))/ 60.0 as hours_off_night
from (
select audit_id, pc_profile_id, status, subpolicy_name, day_hour_rate, night_hour_rate, pc_kwh_rate,
shutdown_Time, hibernate_Time, sleep_Time, startup_Time, start1, finish1, start2, finish2, offset,
-- Either return null, or the start of the day period or the shutdown_time. The null value is to know when to stop counting
case
when startup_Time < case when COALESCE( shutdown_Time, hibernate_Time, sleep_Time) < start1 then start1 else case when COALESCE( shutdown_Time, hibernate_Time, sleep_Time) < finish1 then COALESCE( shutdown_Time, hibernate_Time, sleep_Time) else finish1 end
-- Provides a stoping condition, when the field day_start begins to be NULL
-- means that the day and night intervals don't fall in the shutdown/start period
end then null else
case when COALESCE( shutdown_Time, hibernate_Time, sleep_Time) < start1 then start1 else case when COALESCE( shutdown_Time, hibernate_Time, sleep_Time) < finish1 then COALESCE( shutdown_Time, hibernate_Time, sleep_Time) else finish1 end end
end as day_start,
-- Either returns the finish of the day rate or the startup_time
case when startup_Time < finish1 then startup_Time else finish1 end as day_end,
-- Either returns the start of the night rate or the startup_time
case when startup_Time > start2 then start2 else startup_Time end as night_start,
-- Either return the finish of the night rate or the startup_time
case when startup_Time < finish2 then startup_Time else finish2 end as night_end
from (
-- For each record in the table provides the shutdown, hibernate, sleep and start time
select audit_id, pc_profile_id, shutdown_Time, hibernate_Time, sleep_Time, startup_Time, status, subpolicy_name, day_hour_rate, night_hour_rate, pc_kwh_rate,
-- Start time for the day period
convert(datetime, left(convert(varchar, dateadd(d, offset, COALESCE( shutdown_Time, hibernate_Time, sleep_Time) ), 120), 11) + '07:00:00', 120) start1,
-- Finish time for the day period
convert(datetime, left(convert(varchar, dateadd(d, offset, COALESCE( shutdown_Time, hibernate_Time, sleep_Time) ), 120), 11) + '22:59:00', 120) finish1,
-- Start time for the night period
convert(datetime, left(convert(varchar, dateadd(d, offset, COALESCE( shutdown_Time, hibernate_Time, sleep_Time) ), 120), 11) + '23:00:00', 120) start2,
-- Finish time for the night period
convert(datetime, left(convert(varchar, dateadd(d, offset + 1, COALESCE( shutdown_Time, hibernate_Time, sleep_Time) ), 120), 11) + '06:59:00', 120) finish2, offset
from #savingstemp cross join (
-- Tally Table
select top 90 row_number() over(order by id) -1 as offset
from syscolumns
) numbers
where status = 'CLOSED'
) periods
)a
-- Stopping condition and calculations
where day_start is not null
group by audit_id
) Savings on #savingstemp.audit_id= Savings.audit_id
--===== Group Savings per Week
select datepart(yyyy,startup_Time) as 'year',
subpolicy_name as 'subpolicy',
max(datename(wk,startup_Time)) as 'week',
sum(savings) as 'weekly_savings'
from #savingstemp
WHERE startup_Time >=dateadd(day,datediff(day,0,GetDate())- 7,0)
group by datepart(yyyy,startup_Time), subpolicy_name,
datepart(wk,startup_Time)
order by datepart(yyyy,startup_Time), subpolicy_name,
datepart(wk,startup_Time);
RETURN
March 23, 2010 at 3:31 pm
Not sure I understand the problem. Are you looking for savings between Day-14 and Day-7?
select datepart(yyyy,startup_Time) as 'year',
subpolicy_name as 'subpolicy',
max(datename(wk,startup_Time)) as 'week',
sum(savings) as 'weekly_savings'
from #savingstemp
WHERE startup_Time between dateadd(day,datediff(day,0,GetDate())- 14,0) and dateadd(day,datediff(day,0,GetDate())- 7,0)
group by datepart(yyyy,startup_Time), subpolicy_name,
datepart(wk,startup_Time)
order by datepart(yyyy,startup_Time), subpolicy_name,
datepart(wk,startup_Time);
March 24, 2010 at 9:09 am
Thanks for the post, that SQL works very well just what I needed thanks. what I need to do is compare savings week on week. I'm not sure whats the best method of doing this. My problem is I'm using MS Chart Controls to display the data which have limitations, basically if i have a bar chart all i can do is assign a column from the result set to the X Axis and a column from the result set in the stored procedure to the Y axis.
Is it possible to get the current week when using "getDate" for example 01/08/2010 this is the 2nd day of the 2nd week of the year, is it possible to get the savings for the whole 1st week?
Thanks for the help, sorry if I'm not clear on what I'm trying to achieve, I think the way which you showed me solves my problem in a way and its a good basis to work off, thanks again.
March 24, 2010 at 9:46 am
You are very welcome.
For your question see this website, which has some of the best info on SQL date functions.
http://www.sql-server-helper.com/functions/index.aspx
More specifically, what you would like to do is related to this:
http://www.sql-server-helper.com/functions/get-first-day-of-week.aspx
and this:
http://www.sql-server-helper.com/functions/get-date-only.aspx
So try this
select CAST(CONVERT(VARCHAR(10),
DATEADD(DD, (-5) - DATEPART(DW, GETDATE()),
GETDATE()), 111) AS DATETIME) --first day of last week
select CAST(CONVERT(VARCHAR(10),
DATEADD(DD, 0 - DATEPART(DW, GETDATE()), GETDATE()), 111) AS DATETIME)
--saturday of last week, so include all of Friday, up to midnight,
--0 is added for readbility,
--it converts the result to negative
You would use this in "BETWEEN" like my prior post.
Please let me know if you have any questions.
Ray
March 24, 2010 at 10:07 am
Ray,
Thanks that pretty much does exactly what I hoped, thanks for the links as well could be very helpful 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply