Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 OnPeak Calculation

Author  Topic 

shivdsnair
Starting Member

3 Posts

Posted - 2014-11-20 : 08:56:35
Could someone help me with a SQL query to calculate the total on-peak and off-values for a month as well as the Max/highest on-peak/offPeak hourly value for that month.

On a daily basis i store the hourly values of the meter in a SQL table.

On-Peak

Summer: Apr-Oct hours(7-22) on weekdays (M-F)
Winter: Nov-Mar hours(8-23) on weekdays (M-F)

off-Peak

Summer: Apr-Oct hours(0-6,23,24); Weekends (Saturday & sunday) ; all public holidays during those months as to be considered as off peak

Winter: Nov-Mar hours(0-7,24);Weekends (Saturday & sunday); all public holidays during those months as to be considered as off peak

Here is the DB Table Structure:

Column Name & Data Types

HourId - Uniqueidentifier

CustomerName - nvarchar(50)

Readingdate - datetime

IntegratedHour - TinyInt

Load - decimal(18,4)

Generation - Decimal (18,4)

LastModified - Datetime

ModifiedBy - nvarchar(50)


Thank you for looking at this query.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-20 : 09:43:36
First off I might some other tables:

1. a Dates table that has a row for every day of the year. It would include a column IsOffPeak that I would set to 1 for days that are completely OffPeak, including public holidays and weekends. This table would need updating once a year. It would also include a reference to a cross-reference table.
2. the OffPeakHours table would have a row for every OffPeakPeriod, with columns for the start time and end time of the period. This table would only need updating when the periods change.
3. a DayPeakXref table that contains ids for each day and ids for the OffPeakHours rows that apply to that day.

Armed with these new tables, computing on- and off-peak values becomes a straightforward join on the four tables.

Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-20 : 09:43:37
First off I might some other tables:

1. a Dates table that has a row for every day of the year. It would include a column IsOffPeak that I would set to 1 for days that are completely OffPeak, including public holidays and weekends. This table would need updating once a year. It would also include a reference to a cross-reference table.
2. the OffPeakHours table would have a row for every OffPeakPeriod, with columns for the start time and end time of the period. This table would only need updating when the periods change.
3. a DayPeakXref table that contains ids for each day and ids for the OffPeakHours rows that apply to that day.

Armed with these new tables, computing on- and off-peak values becomes a straightforward join on the four tables.

Go to Top of Page

shivdsnair
Starting Member

3 Posts

Posted - 2014-11-25 : 09:03:24
gbritton,
Thank you for your response, and sorry for replying it late.
I have only one table structure on the DB which i mentioned in query. Not sure whether i could add or request the client to add new tables to calculate. Let me try, since i am not a DB guy i have to make a request to add these. COuld you clear me 1 more time, to my understanding below i have to create 3 tables (Dates,
OffPeakHours, DayPeakXref). if you dont mind could you show the table structure for all these so that i can ask the team to create the same structure on the DB.

quote:
Originally posted by gbritton

First off I might some other tables:

1. a Dates table that has a row for every day of the year. It would include a column IsOffPeak that I would set to 1 for days that are completely OffPeak, including public holidays and weekends. This table would need updating once a year. It would also include a reference to a cross-reference table.
2. the OffPeakHours table would have a row for every OffPeakPeriod, with columns for the start time and end time of the period. This table would only need updating when the periods change.
3. a DayPeakXref table that contains ids for each day and ids for the OffPeakHours rows that apply to that day.

Armed with these new tables, computing on- and off-peak values becomes a straightforward join on the four tables.



Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-25 : 09:21:59
Good article on a Dates table here:

http://www.codeproject.com/Articles/647950/Create-and-Populate-Date-Dimension-for-Data-Wareho

I'd add a column IsWeekday as a bit, 1 ==> Weekday, 0 ==> Weekend and a derived column:


IsOffPeak as CASE WHEN IsHoliday = 1 Or IsWeekend = 1 THEN 1 ELSE 0 END


The OffPeakHours table might look like this:


CREATE TABLE OffPeakHours(OffPeakHoursId int identity, OffPeakStartTime Time, OffPeakEndTime Time)


The DayPeakXref table might look like this:


CREATE TABLE DayPeakXref(DateKey int, OffPeakHoursId int)


Of course you have to populate these tables with data that makes sense for your business.
Go to Top of Page
   

- Advertisement -