June 19, 2012 at 2:19 pm
Hi All,
I've got myself a nice little project for someone who doesn't have to do much DB work.
I'm am logging around 40 Electricity Meters kWh reading every half hour into a readings table.
The database , at the moment has two tables, one to hold details about the meter the other for the readings.
The readings table looks like this:
ID MeterID dtDateTimeReading
1 1 2012-06-19 10:00:00.000 123
2 2 2012-06-19 10:00:00.000 457
3 1 2012-06-19 10:30:00.000 123
4 2 2012-06-19 10:30:00.000 457
5 1 2012-06-19 11:00:00.000 123
6 2 2012-06-19 11:00:00.000 457
...
...
First question is this a good design? Should I create a seperate table for the date and time?
Second Question: How could I query the table to give me a view like:
dtDateTimeMeter1Reading Meter2Reading
2012-06-19 10:00:00.000123457
2012-06-19 10:30:00.000123457
2012-06-19 11:00:00.000123457
...
...
Thanks in advance
June 19, 2012 at 3:02 pm
From your description it sounds like a solid design.
For your query question, you said you had around 60 meters? So you want a column for every meter? That isn't going to be very legible by a human but it is certainly in the realm of feasible. Depending on how you plan on viewing this it might be better suited in the front end to do this type of formatting and layout. If you need to do it on the sql side you are looking at what is called a cross tab. Given that the number of meters is highly likely to change from time to time you probably need to look at a dynamic cross tab. Check out the links to cross tabs and the dynamic counterpart in my signature. These are not terribly easy to implement because they are breaking database normalization.
Read those articles and post back if you need some help. You might also take a look at the first link in my signature about best practices when posting questions if you need some detailed help coding.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2012 at 3:03 pm
Hello Marshy100,
Welcome to SSC. I see that you are fairly new to the site. For getting best results for your question, please read the following article on the etiquettes.
You have explained your problem pretty well. The only thing missing is the way you wll have to format your question. That article will lead you step-by-step.
Now, parking the design question aside (as im not a good designer), here is the view you were looking for. This method is called Cross-Tabs (search this site for "Cross-Tab Jeff Moden" to learn more on the idea behind the method)
Lets make your sample data readily consumable
DECLARE @ReadingTable TABLE
(
iD INT
,MeterID SMALLINT
,dtDateTime DATETIME
,Reading INT
)
;
INSERT INTO @ReadingTable (iD , MeterID, dtDateTime, Reading)
SELECT 1,1,'2012-06-19 10:00:00.000',123
UNION ALL SELECT 2,2,'2012-06-19 10:00:00.000',457
UNION ALL SELECT 3,1,'2012-06-19 10:30:00.000',123
UNION ALL SELECT 4,2,'2012-06-19 10:30:00.000',457
UNION ALL SELECT 5,1,'2012-06-19 11:00:00.000',123
UNION ALL SELECT 6,2,'2012-06-19 11:00:00.000',457
Lets work the query which will give the required output
SELECT dtDateTime = RT.dtDateTime
,Meter1Reading = MAX (CASE WHEN RT.MeterID = 1 THEN RT.Reading
ELSE 0
END)
,Meter2Reading = MAX (CASE WHEN RT.MeterID = 2 THEN RT.Reading
ELSE 0
END)
FROM @ReadingTable RT
GROUP BY RT.dtDateTime
ORDER BY RT.dtDateTime
Have a look at how i set-up your sample data so that anyone else could use that 🙂
June 19, 2012 at 3:44 pm
Thank you both for taking the time to reply to my post!
That is just what I needed. I've printed the articles for a bit of bedtime reading ;o)
June 19, 2012 at 7:15 pm
I'd say the design is probably OK for what you're doing.
I'm concerned about the data getting stored for the reading time. Your sample data is nice and clean, that is to say always 00.000 as the ss.mmm part of the time stamp. In real life, this often fails to come true unless you take steps that you ensure that it does.
Like whatever process INSERTs the reading, needs to truncate to the 10s of minutes in the timestamp.
I also share the concern that, with 60 meters, that's a lot of columns to display in the crosstab query. And if you add meters, you'll always be changing that code, unless you use a dynamic cross tab.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 20, 2012 at 1:27 am
Hi,
Welcome To SSC!!!!
You table design seem good. No problems with it.
Regarding the Query you require for the result set, as Sean said it would be best to do it at the Front end.
But, if you still want to do it using TSQL depending upon your requirement, then you can use any of the following ways of doing it:
--Creating Table
Create Table Ex
(ID int,
MeterID int,
dtDateTime DateTime,
Reading int )
--Inserting Sample Data
Insert Into Ex
Select 1, 1, '2012-06-19 10:00:00.000', 123
Union ALL
Select 2, 2, '2012-06-19 10:00:00.000', 457
Union ALL
Select 3, 1, '2012-06-19 10:30:00.000', 123
Union ALL
Select 4, 2, '2012-06-19 10:30:00.000', 457
Union ALL
Select 5, 1, '2012-06-19 11:00:00.000', 123
Union ALL
Select 6, 2, '2012-06-19 11:00:00.000', 457
--Query Using Case(Static)
Select dtDateTime,
Max(Case When MeterID = 1 Then Reading Else '' End) As Meter1Reading,
Max(Case When MeterID = 2 Then Reading Else '' End) As Meter2Reading
From Ex
Group By dtDateTime
--Static Pivot
Select dtDateTime, MAX(Meter1Reading) As Meter1Reading, MAX(Meter2Reading) As Meter2Reading From
(Select dtDateTime, [1] As Meter1Reading, [2] As Meter2Reading From Ex
Pivot
(Max(Reading) For MeterId IN ([1], [2]) ) As Pvt ) As a
Group By dtDateTime
--Dynamic Pivot
Declare @cols Varchar(max), @sql Varchar(max), @cols1 Varchar(max)
Declare @temp Table(Cols Varchar(30) )
Insert Into @temp
Select Distinct ('Meter' + Cast(MeterID As Varchar(30)) + 'Reading') As MeterReading From Ex
Select @cols = Coalesce(@cols + ', ', '') +QUOTENAME(Cols) From @temp
Select @cols1 = Coalesce(@cols1 + ') , Max(', '') + QUOTENAME(Cols) From @temp
Set @cols1 = 'Max(' + @cols1 + ')'
Set @sql = 'Select dtDateTime, '+@cols1+' From
(Select dtDateTime, '+@cols+' From
(Select *, (''Meter'' + Cast(MeterID As Varchar(30)) + ''Reading'') As MeterReading From Ex) As a
Pivot
(Max(Reading) For MeterReading IN ('+@cols+') ) As Pvt ) As b
Group By dtDateTime'
Execute (@sql)
Since, you know that you would be dealing with Readings for 40 Meters then you should avoid the Dynamic Pivot and should be using the Static Pivot or the Query using Case.
Hope that helped.
June 20, 2012 at 7:00 am
dwain.c (6/19/2012)
I'd say the design is probably OK for what you're doing.I'm concerned about the data getting stored for the reading time. Your sample data is nice and clean, that is to say always 00.000 as the ss.mmm part of the time stamp. In real life, this often fails to come true unless you take steps that you ensure that it does.
Like whatever process INSERTs the reading, needs to truncate to the 10s of minutes in the timestamp.
I also shared the concern that, with 60 meters, that's a lot of columns to display in the crosstab query. And if you add meters, you'll always be changing that code, unless you use a dynamic cross tab.
I had that concern regarding the ss.mmmm so what I've done is round the datetime to the nearest half hour when the readings are inserted into the table.
A bit more info on why I want the meters in columns; all the meters are split up into groups e.g. Process Plant Meters, Workshop Meters, Office Meters. The maximum number of meters I'd want to display in columns is around 5. The reason I want to do this is so that I can display the data as a stacked bar chart, that way I can give the boss a graphical representation of the biggest energy user in his area.
June 20, 2012 at 7:15 am
Marshy,
Today is your lucky day! Well maybe tomorrow as it is night time here now.
I have just the query for you but I need a description of something.
I'm assuming you'll want bars that represent time periods.
1. How many time periods to report across the chart (i.e., how many bars)?
2. How long is each individual time period (e.g., one day, two days, etc.)?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 20, 2012 at 9:17 am
dwain.c (6/20/2012)
Marshy,Today is your lucky day! Well maybe tomorrow as it is night time here now.
I have just the query for you but I need a description of something.
I'm assuming you'll want bars that represent time periods.
1. How many time periods to report across the chart (i.e., how many bars)?
2. How long is each individual time period (e.g., one day, two days, etc.)?
Cool!
I want to have one chart the displays consumption throughout the day, so this will have 48 bars one for each half hour.
Another will display daily consumption, the number of bars would be dependent on the date range, this would be no more than a month.
The next step after I've sorted out the meter readings would be to query another table that holds production data. The idea behind this is that I can show the production managers what they've got for the power consumed, this will help highlight how efficiently they're running the plant. I'd also like to do a cost analysis as the cost per kWh varies throughout the day, between 4:30pm and 7pm it's more than double what it is during the night. But I'll leave all that for another post ;o)
Thanks for the help.
June 20, 2012 at 7:33 pm
I originally designed this SetUpIntervals CTE to be able to SUM (or COUNT, etc.) values across a continuous date range. However it is easily adapted to the case where your meter readings are exactly at 30 minute intervals.
First, we'll set up some test data for 18-Jun (my birthday).
DECLARE @ReadingTable TABLE (iD INT IDENTITY, MeterID SMALLINT, dtDateTime DATETIME, Reading INT)
DECLARE @MStartKWH FLOAT = 250
,@MEndKWH FLOAT = 450
,@KWHRange FLOAT
,@NoIntervals INT = 48
,@DStartValue DATETIME = '2012-06-18'
,@DRange FLOAT = 1 -- Number of days to chart
;WITH Tally (n) AS (
SELECT TOP 50 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns a)
INSERT INTO @ReadingTable
SELECT MeterID = n1
,dtDateTime = DATEADD(minute, 30 * (n2 - 1), @DStartValue)
,Reading = RAND(CHECKSUM(NEWID())) * (@MEndKWH - @MStartKWH) + @MStartKWH
FROM (SELECT n FROM Tally WHERE n BETWEEN 1 AND 5) t1(n1)
CROSS APPLY (SELECT n FROM Tally WHERE n BETWEEN 1 AND 48) t2(n2)
Now we'll combine SetUpIntervals with the Meter pivoting algorithm (crosstab method) and do some minor futzing around with the last part that combines the two.
;WITH SetUpIntervals AS (
-- Anchor: Initial, placeholder interval for totals
SELECT ID=0
,StartInterval = CAST(NULL AS DATETIME)
,EndInterval = CAST(NULL AS DATETIME)
,Interval = CAST('Total' AS VARCHAR(40))
UNION ALL
-- Anchor: Start interval taken from @DStartValue
SELECT ID=1
,StartInterval = @DStartValue
,EndInterval = @DStartValue + @DRange/@NoIntervals
,Interval=CAST(CONVERT(VARCHAR(17), @DStartValue, 113) AS VARCHAR(40)) -- + ' - ' +
-- This was used to construct a range of days
--CONVERT(VARCHAR(17), @DStartValue + @DRange/@NoIntervals, 113) AS VARCHAR(40))
UNION ALL
-- Recursive: Next interval starts at last EndInterval
SELECT ID + 1
,EndInterval
,EndInterval + @DRange/@NoIntervals
,CAST(CONVERT(VARCHAR(17), EndInterval, 113) AS VARCHAR(40)) -- + ' - ' +
-- This was used to construct a range of days
--CONVERT(VARCHAR(17), EndInterval + @DRange/@NoIntervals, 113) AS VARCHAR(40))
FROM SetUpIntervals
WHERE ID > 0 AND ID < @NoIntervals),
CrossTabMeters AS (
SELECT dtDateTime = dtDateTime
,Meter1Reading = MAX (CASE WHEN MeterID = 1 THEN Reading ELSE 0 END)
,Meter2Reading = MAX (CASE WHEN MeterID = 2 THEN Reading ELSE 0 END)
,Meter3Reading = MAX (CASE WHEN MeterID = 3 THEN Reading ELSE 0 END)
,Meter4Reading = MAX (CASE WHEN MeterID = 4 THEN Reading ELSE 0 END)
,Meter5Reading = MAX (CASE WHEN MeterID = 5 THEN Reading ELSE 0 END)
FROM @ReadingTable
GROUP BY dtDateTime
)
--SELECT * FROM SetupIntervals
SELECT c.Interval, Meter1Reading, Meter2Reading, Meter3Reading, Meter4Reading, Meter5Reading
FROM SetupIntervals c
LEFT JOIN (
-- Summarize Meter Readings by Intervals
-- Note this was originally designed to sum up values over a continuous date range
-- so the GROUP BY isn't really necessary but nice to allow capture of the total
SELECT ID, Meter1Reading=SUM(Meter1Reading), Meter2Reading=SUM(Meter2Reading)
,Meter3Reading=SUM(Meter3Reading), Meter4Reading=SUM(Meter4Reading)
,Meter5Reading=SUM(Meter5Reading)
FROM CrossTabMeters
-- CROSS APPLY to put dtDateTime into Intervals
CROSS APPLY (
SELECT ID
FROM SetUpIntervals
WHERE dtDateTime >= StartInterval AND dtDateTime < EndInterval) a
-- Use ROLLUP to get a total row in the grouped results set
GROUP BY ID WITH ROLLUP) b
ON ISNULL(b.ID, 0) = c.ID
ORDER BY c.ID
Copy results, paste into Excel and then all that's left is the charting itself!
Let me know if your boss is impressed.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 23, 2012 at 5:29 am
dwain.c (6/20/2012)
I originally designed this SetUpIntervals CTE to be able to SUM (or COUNT, etc.) values across a continuous date range. However it is easily adapted to the case where your meter readings are exactly at 30 minute intervals.First, we'll set up some test data for 18-Jun (my birthday).
Copy results, paste into Excel and then all that's left is the charting itself!
Let me know if your boss is impressed.
Thanks for taking the time to do this, truly impressed. Learnt so much over the pass couple of days.
Happy Birthday for the 18th!
June 23, 2012 at 3:34 pm
Marshy100 (6/23/2012)
Thanks for taking the time to do this, truly impressed. Learnt so much over the pass couple of days.
There's just one more thing to learn... and I don't mean to sound harsh but I'm trying to save you some real trouble in the future that you aren't even aware of, yet.
I had that concern regarding the ss.mmmm so what I've done is round the datetime to the nearest half hour when the readings are inserted into the table.
If what you mean is that you've done this to the only place where meter readings are stored in their original form, then what you've done is you've tampered with original data. It might not happen right now but someday in the future it's going to be important to someone to know when the meter reading was actually taken. Save your rounding for the display of data. Leave the original data as it is. NEVER change original data.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply