I am using MS Sql Server and my system contains power usage data in one table as timestamp and values for KW(two columns). This data is collected at 1 hour interval. At the end of the month I need to calculate on peak and off peak usage. The definition of on peak and off peak will depend on the contract with the utility company. So for example utility 'x' could have
- Monday to Friday - 7:00 am to 9:00 pm is on peak and 9:00 pm to 7:00 am is off peak
- Saturday - 9:00 am to 3:00 pm is on peak and 3:00 pm to 9:00 am is off peak
- Sunday - All day is off peak
- Stat Holiday - All day is off peak
Utility 'y' would have a different schedule for on peak and off peak times I am having trouble modelling the week of the day and corresponding time in a manner that would make it easier to select data for on peak and off peak from my usage table. I am pretty sure I would need a lookup table for Stat Holidays. My parent table would be the utility table.
Option #1 : A flat table with many columns - MondayOnPeakStart, MondayOnPeakEnd, MondayOffPeakStart, MondayOffPeakEnd, TuesdayOnPeakStart .....
The select from the usage table would be pretty ugly.
Option # 2 : A normalized table structure. This is where I am stuck and cannot decide how to structure the hierarchy.
For each utility company, I have two types - onPeak and offPeak and for each of these types I have a schedule which contains days of week and start and end times. I will get multiple rows for on peak and I am not sure how will I use it in the usage table select clause.
Option # 3: Something I haven't thought of.