techzone12
Technical User
I have a SQL View called "KWH_Daily", and it’s driven by a 15 minutes table called "KW_15min"
The 15-min table has data in 15-min intervals, i.e.:
[DateTime] KW
9/13/2005 11:00 am 300
9/13/2005 11:15 am 304
9/13/2005 11:30 am 298
The Daily View (KWH_Daily) is constructed by using the query:
SELECT
CONVERT(DATETIME, DATEDIFF(dd, 0, DateTime) AS DateTime,
SUM(KW)/ 4 AS KWH,
MAX(KW) AS KW_Peak
FROM Site_KW_15min
WHERE (CONVERT(varchar, DateTime, 101) <> CONVERT(varchar,
GETDATE(), 101))
GROUP BY CONVERT(DATETIME, DATEDIFF(dd, 0, DateTime), 1)
I need to modify the above query to add two additional columns to the view, namely “KW_On_Peak” and “KW_Off_Peak”. The first one is the Maximum KW for the day time only, i.e. form 8 am to 8 pm. The second one is the Maximum KW for the rest of the day.
How do I do this?
The 15-min table has data in 15-min intervals, i.e.:
[DateTime] KW
9/13/2005 11:00 am 300
9/13/2005 11:15 am 304
9/13/2005 11:30 am 298
The Daily View (KWH_Daily) is constructed by using the query:
SELECT
CONVERT(DATETIME, DATEDIFF(dd, 0, DateTime) AS DateTime,
SUM(KW)/ 4 AS KWH,
MAX(KW) AS KW_Peak
FROM Site_KW_15min
WHERE (CONVERT(varchar, DateTime, 101) <> CONVERT(varchar,
GETDATE(), 101))
GROUP BY CONVERT(DATETIME, DATEDIFF(dd, 0, DateTime), 1)
I need to modify the above query to add two additional columns to the view, namely “KW_On_Peak” and “KW_Off_Peak”. The first one is the Maximum KW for the day time only, i.e. form 8 am to 8 pm. The second one is the Maximum KW for the rest of the day.
How do I do this?