16 Jun 2011
Ontario Energy Board TOU Pricing in SQL
If, for whatever reason, you need to know when a particular time-stamp in your database occurs during the on-peak, mid-peak, or off-peak times for time of use pricing in Ontario, you can use the following SQL.
These rules are accurate as of the date of this post.
Let me walk you through the nested CASE statements:
- Is it a weekend? if so: off-peak, else: step 2
- Is it summer? (May 1 to October 31) if so: step 3, else: step 4.
- Figure out what time it is and apply the proper pricing.
- Figure out what time it is and apply the proper pricing for winter rates.
This logic does not take into account holidays. Official OEB holidays can be found at their site. If anyone has a suggestion on how I can incorporate the holidays as well, please let me know.
Blake said:
> If anyone has a suggestion on how I can incorporate
> the holidays as well, please let me know.
Use a Calendar Table (scroll down a wee bit):
http://books.google.com/books?id=Bx_32AT-u0YC&pg=PA346
RDL
June 18th, 2011 at 12:37 pmpermalink
Use a lookup calendar table as a lookup. In fact my preference is to use a lookup table instead of the case statements. Why?
Because it can be changed easily and is its own documentation. And you do not need very many rows – potentially 365 if you used a day for a year, but if you had start and end dates you would need certainly less than ten/twenty to contain your rules (currently in CASE form) and holidays.
Mathew Frank
June 19th, 2011 at 8:23 pmpermalink