June 9, 2009 at 1:59 pm
Hello,
I have the need to create an energy consumption report. The store procedure below retrieves this data for me but I am having a problem with it because the output returned is seperated by column headers wich is repeated for every hour of the 24 hour period. So in other words I get the column titles repeated 24 times and of course when I use this for my report the report doesn't like that...
any help in getting this same result without the repeated columnn headers is deeply appreciated.
here is my code so far:
Use DbName
go
--FIRST CREATE A FUNCTION:
--This is a function that will round a date time to the nearest hour
CREATE Function [dbo].[RoundTime] (@Time datetime, @RoundTo float)
Returns Datetime as
BEGIN
DECLARE @RoundTime smalldatetime
DECLARE @Multiplier float
Set @Multiplier = 24.0/@RoundTo
SET @RoundTime = ROUND(Cast(Cast(Convert(Varchar, @Time, 121) AS datetime) as float) * @Multiplier, 0)/@Multiplier
RETURN @Roundtime
END
-- SECOND CREATE STORE PROCEDURE:
-- =============================================================
-- Author:
-- Create date: 9 June 2009
-- Description:This procedure will pull the consumption data for each
--hour for Cognos Report (Hourly Demand)
-- =============================================================
ALTER PROCEDURE usp_EnergyDemandByHour
@reportDate smalldatetime
@daydate smalldatetime
AS
--This procedure requires the creation/use of: Function [RoundTime] that needs to be created.
--Declare variables
DECLARE @hour int
BEGIN
SET NOCOUNT ON
SET @reportDate = '2009-01-10 00:00:00.000' --hard coded date for now
SET @daydate = (SELECT min(dbo.roundtime(TimeStampUTC, 1)) FROM Ion_Data_Test..DataLogStamp
WHERE DATEADD(Day, DATEDIFF(Day, 0, TimeStampUTC), 0) = @reportdate)
SET @hour = DatePart(hh, @daydate)
WHILE @Hour < 24
BEGIN
SELECT sr.SiteName , b.BldgName AS BuildingName, mm.CommodityType, DATEADD(hh,1,@daydate) AS DaySelected, CONVERT(VARCHAR(10), DATEADD(hh,1,@daydate),8) AS HOUR
, MAX(dl.value) AS MaxValue, MIN(dl.value) AS MinValue, (MAX(dl.value) - MIN(dl.value))AS Consumption
FROM Ion_Data_Test..DataLogStamp dls
INNER JOIN Ion_Data_Test..DataLog dl
ON dls.ID = dl.DataLogStampID
INNER JOIN ION_Data_Test..Source s
ON s.ID = dls.SourceID
INNER JOIN TEE..MeterMapper mm
ON mm.Meter_Name = s.Name
INNER JOIN TEE..TEGInstance ti
ON mm.TEGInstanceID = ti.ID
INNER JOIN TEE..SiteRef sr
ON sr.ID = ti.SiteRefID
INNER JOIN TEE..Building b
on b.ID = mm.Bldg_ID
WHERE dls.SourceID = 8
AND dl.QuantityID = '10005' --KWH
AND mm.CommodityType = 'ELECTRICITY'
AND dls.TimeStampUTC IN(@daydate, DATEADD(hh,1,@daydate))
AND DATEADD(Day, DATEDIFF(DAY, 0, dls.TimeStampUTC), 0) = @reportdate
GROUP BY sr.SiteName, b.BldgName, mm.commodityType
SET @daydate = DATEADD(hh,1,@daydate)
SET @hour = @hour + 1
END
END
GO
--TIA
Abdel Ougnou
June 9, 2009 at 2:13 pm
Why not simplify the whole thing by doing something like this:
SET @reportDate = '2009-01-10 00:00:00.000' --hard coded date for now
SELECT
sr.SiteName,
b.BldgName AS BuildingName,
mm.CommodityType,
@reportDate AS DaySelected,
DATEPART(hour, dls.TimeStampUTC) AS [HOUR],
MAX(dl.value) AS MaxValue,
MIN(dl.value) AS MinValue,
(MAX(dl.value) - MIN(dl.value))AS Consumption
FROM
Ion_Data_Test..DataLogStamp dls
INNER JOIN Ion_Data_Test..DataLog dl
ON dls.ID = dl.DataLogStampID
INNER JOIN ION_Data_Test..Source s
ON s.ID = dls.SourceID
INNER JOIN TEE..MeterMapper mm
ON mm.Meter_Name = s.Name
INNER JOIN TEE..TEGInstance ti
ON mm.TEGInstanceID = ti.ID
INNER JOIN TEE..SiteRef sr
ON sr.ID = ti.SiteRefID
INNER JOIN TEE..Building b
ON b.ID = mm.Bldg_ID
WHERE
dls.SourceID = 8
AND dl.QuantityID = '10005' --KWH
AND mm.CommodityType = 'ELECTRICITY'
AND dls.TimeStampUTC >= @reportDate
AND dls.TimeStampUTC < @reportDate + 1
GROUP BY
sr.SiteName,
b.BldgName,
mm.commodityType,
DATEPART(hour, dls.TimeStampUTC);
Also, there's a more efficient way to round to the nearest hour:
declare @TimeVariable datetime;
select @TimeVariable = '6/9/09 4:11 PM';
select dateadd(hour, datediff(hour, 0, @TimeVariable), 0);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2009 at 2:21 pm
I assume the way you phrase it that your data is passing through some other format such as CSV or excel before coming into SQL for your report.
If that is the case, have you considered writing a script to strip out those column headers in something like python/perl/vb.net before you import it into sql?
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
June 9, 2009 at 2:50 pm
timothyawiseman>: no I am not passing it from a CSV. I am actually pulling it from my DB and when I run my SP it returns the data that way...
GSquared>: thank you, that is defently a different way to write it that I didn't think about. However, your query returns every dls.TimeStampUTC
For example my data looks something like this:
2008-01-01 05:00:00.000
2008-01-01 05:15:00.000
2008-01-01 05:30:00.000
2008-01-01 05:45:00.000
2008-01-01 06:00:00.000
2008-01-01 06:15:00.000
2008-01-01 06:30:00.000
2008-01-01 06:45:00.000
2008-01-01 07:00:00.000
....
as you can see above the time is in 15 mn increments, but I am only interested in calculating energy consumed each hour. For example from 5:00 - 6:00, 6:00 to 7:00, 7:00 to 8:00 etc. etc... for a 24 hour period.
thanks again,
Abdel Ougnou
June 9, 2009 at 3:01 pm
Did you modify the query at all?
Having Datepart(hour) in the Group By should make it just return the hours. I just tested that, and it worked:
create table #T (
ID int identity primary key,
TimeStampUTC datetime);
insert into #T (TimeStampUTC)
select '2008-01-01 05:00:00.000' union all
select '2008-01-01 05:15:00.000' union all
select '2008-01-01 05:30:00.000' union all
select '2008-01-01 05:45:00.000' union all
select '2008-01-01 06:00:00.000' union all
select '2008-01-01 06:15:00.000' union all
select '2008-01-01 06:30:00.000' union all
select '2008-01-01 06:45:00.000' union all
select '2008-01-01 07:00:00.000';
select datepart(hour, TimeStampUTC)
from #T
group by datepart(hour, TimeStampUTC);
I don't have your tables, so I can't test much beyond that, but this gives exactly what it's supposed to, and it should work the same way with your data.
If you didn't modify the query, I need table definitions (create scripts) and sample data (insert scripts), so I can test it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply