Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Remove repeated column header Expand / Collapse
Author
Message
Posted Tuesday, June 9, 2009 1:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 9:06 AM
Points: 12, Visits: 125
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
Post #731840
Posted Tuesday, June 9, 2009 2:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:09 PM
Points: 13,872, Visits: 9,597
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
Post #731850
Posted Tuesday, June 9, 2009 2:21 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
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/
Post #731858
Posted Tuesday, June 9, 2009 2:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 9:06 AM
Points: 12, Visits: 125
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
Post #731874
Posted Tuesday, June 9, 2009 3:01 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:09 PM
Points: 13,872, Visits: 9,597
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
Post #731887
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse