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.
| 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 peakWinter: Nov-Mar hours(0-7,24);Weekends (Saturday & sunday); all public holidays during those months as to be considered as off peakHere is the DB Table Structure:Column Name & Data TypesHourId - UniqueidentifierCustomerName - nvarchar(50)Readingdate - datetimeIntegratedHour - TinyIntLoad - decimal(18,4)Generation - Decimal (18,4)LastModified - DatetimeModifiedBy - 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. |
 |
|
|
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. |
|
|
|
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.
|
|
|
|
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-WarehoI'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. |
|
|
|
|
|
|
|
|
OnPeak Calculation