SQL conditional query with DateTime 2

[techzone12]

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?

 

[amrita418]

Try this query and let us know your findings:

SELECT    CONVERT(DATETIME, DATEDIFF(dd, 0, Datevalue)) AS Datevalue,  
          SUM(KW)/ 4 AS KWH,
          MAX(KW) AS KW_Peak,
          MAX(CASE WHEN DATEPART(Hour, DateValue) between 8 and 20 THEN kw ELSE 0 END) 'Max KW',
          MAX(CASE WHEN DATEPART(Hour, DateValue) not between 8 and 20 THEN kw ELSE 0 END) 'Max KW RODay'
FROM      Site_KW_15min
WHERE     CONVERT(VARCHAR, Datevalue, 101) != CONVERT(varchar, GETDATE(), 101)
GROUP BY  CONVERT(DATETIME, DATEDIFF(dd, 0, Datevalue))

I would strongly recommend not to use keywords as column names, assuming you posted just sample data, I took the liberty of renaming the column name to DateValue instead.

Regards,
AA

 

[vongrunt]

Looks OK overall... I guess "from 8am to 8pm" means "08:00 - 19:59:59.99..." in which case BETWEEN 8 and 19 is more appropriate.

And WHERE clause (from original query) would probably make query slow...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[techzone12]

Great!!. It works just fine.
I changed it to "between 8 and 19", as vongrunt has suggested. This is because "between" is inclusive or "closed ended".

Thanks a lot!

 

[amrita418]

Good catch vongrunt! You have a great pair of eyes.