SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Remove repeated column header


Remove repeated column header

Author
Message
-A
-A
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23401 Visits: 9730
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
timothyawiseman
timothyawiseman
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1214 Visits: 920
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/
-A
-A
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23401 Visits: 9730
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search