August 23, 2009 at 1:59 am
Hi,
Apologies in advance if this seems a bit basic, but I am new to SQL, and cant seem to get my head round something.
I have a really basic table with 3 fields:
ID
Value
Time_Stamp
The values are from a meter, and the values logged are the actual meter reading at the time of logging.
So I get a list of what the meter readings are at the logged time.
What I really need is the consumption of the meter, rather than the actual reading.
So I need to subtract the previous value from the current one.
I doesn't need to be real time. Even to run a report that creates a new table would do.
I hope this makes sense :hehe:
Thanks in Advance
Andy
August 23, 2009 at 7:01 am
Welcome to SSC Andy;
the key to solving this kind of issue, is that you have to join the table against a copy of itself.
the join has to get the ID of your table, and use an extra condition to get the dates in the second copy to be greater than the original table;
Note how I included the CREATE TABLE as well as sample data? If you can do that in future posts, everyone here can help you much better, with perfect, tested solutions, without you having to take code and extrapolate an example to your situation;
for example my example guessed on the datatypes for your columns...probably correct, but not 100% sure.
here's an example for you:
--my sample table
CREATE TABLE MyMeter(
ID int,
Value int,
Time_Stamp datetime )
--my sample data
insert into MyMeter
SELECT 1,1500,getdate() -1 UNION ALL
SELECT 2, 500,getdate() -1 UNION ALL
SELECT 3,1777,getdate() -1 UNION ALL
SELECT 4,720, getdate() -1 UNION ALL
SELECT 1,3700,getdate() UNION ALL
SELECT 2,5670,getdate() UNION ALL
SELECT 3,6295,getdate()
--just an example so you can see the join against a copy of itself
select *
FROM MyMeter
LEFT OUTER JOIN MyMeter AS MySelfJoinedTable --note the alias for the copy of the table?
ON MyMeter.ID = MySelfJoinedTable.ID
AND MyMeter.Time_Stamp < MySelfJoinedTable.Time_Stamp
--the actual calculation:
SELECT MyMeter.ID,
MySelfJoinedTable.Value - MyMeter.Value as MeterUsage,
MyMeter.Time_Stamp As StartDate,
MySelfJoinedTable.Time_Stamp As EndDate
FROM MyMeter
LEFT OUTER JOIN MyMeter AS MySelfJoinedTable
ON MyMeter.ID = MySelfJoinedTable.ID
AND MyMeter.Time_Stamp < MySelfJoinedTable.Time_Stamp
WHERE MySelfJoinedTable.ID IS NOT NULL --ignoring anything without a matching end date
Lowell
August 23, 2009 at 8:05 am
Lowell,
First, Thanks for the welcome, and also the very helpful example.
Being a Newbie at SQL, this is something I need to work at to understand.
I appreciate the time you have taken to respond to this. (you were right with the datatypes :-))
I'll go over it and digest it, and hopefully get my head round it.
Many thanks again
Andy
August 23, 2009 at 8:39 am
My Pleasure Andy;
In my first example, I "assumed" that the ID repeated, like hoses, so you had to get last reading and current reading for each ID/House;
reading your request a different way, the solution is very similar, but assumes you need the totals for each consecutive ID;
here's an example of that as well:
CREATE TABLE MyMeter(
ID int identity(1,1) PRIMARY KEY,
Value int,
Time_Stamp datetime )
insert into MyMeter
SELECT 1500,getdate() -1 UNION ALL
SELECT 1602,getdate() -1 UNION ALL
SELECT 1777,getdate() -1 UNION ALL
SELECT 1842,getdate() -1 UNION ALL
SELECT 1922,getdate() UNION ALL
SELECT 2147,getdate() UNION ALL
SELECT 3381,getdate()
select *
FROM MyMeter
LEFT OUTER JOIN MyMeter AS MySelfJoinedTable
ON MyMeter.ID = MySelfJoinedTable.ID -1
--the actual calculation:
SELECT MyMeter.ID,
MySelfJoinedTable.Value - MyMeter.Value as MeterUsage,
@RunningTotal=@RunningTotal + MyMeter.Value As RunningTotal
MyMeter.Time_Stamp As StartDate,
MySelfJoinedTable.Time_Stamp As EndDate
FROM MyMeter
LEFT OUTER JOIN MyMeter AS MySelfJoinedTable
ON MyMeter.ID = MySelfJoinedTable.ID -1
WHERE MySelfJoinedTable.ID IS NOT NULL
/*
--RESULTS
IDMeterUsageStartDateEndDate
11022009-08-22 10:22:41.2332009-08-22 10:22:41.233
21752009-08-22 10:22:41.2332009-08-22 10:22:41.233
3652009-08-22 10:22:41.2332009-08-22 10:22:41.233
4802009-08-22 10:22:41.2332009-08-23 10:22:41.233
52252009-08-23 10:22:41.2332009-08-23 10:22:41.233
612342009-08-23 10:22:41.2332009-08-23 10:22:41.233
*/
Lowell
August 23, 2009 at 9:11 am
The ID is actually an Autonumber
Many thanks again - It's appreciated
August 24, 2009 at 8:39 am
andymackk (8/23/2009)
The ID is actually an Autonumber
Do you mean it's an IDENTITY column? I.e each row in the table has a unique ID value.
If so you'll need a different approach.
Please post the CREATE TABLE statement, some example data and expected results so we can help you better.
August 24, 2009 at 11:54 am
Ok 🙂 here goes with a better explanation (thanks for the patience)
When I first asked - I had a table that had only 2 fields:
A value Field (Int) and a Timestamp Field (Datetime)
I did say I had an ID field - but I actually hadn't created it (I thought I would be easier to do any calculations with one)
I thought it was an easy task to just add a "UniqueIdentifier" ID field to my existing table, but i had loads of issues with it today. :blush: and couldn't do it cos it just added Null Values.....
So Really - I have this 2 field table that contains data like :
1589695 12/08/2009 14:29
1589753 12/08/2009 14:59
1589811 12/08/2009 15:29
1589888 12/08/2009 15:58
1589991 12/08/2009 16:29
1590092 12/08/2009 16:59
1590162 12/08/2009 17:29
1590220 12/08/2009 17:59
1590274 12/08/2009 18:28
1590336 12/08/2009 18:59
1590423 12/08/2009 19:29
1590511 12/08/2009 19:59
1590598 12/08/2009 20:29
Column 1 are meter readings taken at the Timestamp.
I'm trying to get a list of actual usage, , so i need to subtract 1 record from the previous reading.
I.e:
0 12/08/2009 14:29
58 12/08/2009 14:59
5812/08/2009 15:29
7712/08/2009 15:58
10312/08/2009 16:29
10112/08/2009 16:59
7012/08/2009 17:29
5812/08/2009 17:59
5412/08/2009 18:28
6212/08/2009 18:59
8712/08/2009 19:29
8812/08/2009 19:59
8712/08/2009 20:29
(sorry about the formatting - it changes when i post it )
I hope this makes better sense
Thanks for your help...
Andy
August 24, 2009 at 1:29 pm
May not be the most elegant way to solve, but it works.
CREATE TABLE #MyMeter(
ID int identity(1,1) PRIMARY KEY,
Value int,
Time_Stamp datetime )
insert into #MyMeter
SELECT 1589695, '12/08/2009 14:29' UNION ALL
SELECT 1589753, '12/08/2009 14:59' UNION ALL
SELECT 1589811, '12/08/2009 15:29' UNION ALL
SELECT 1589888, '12/08/2009 15:58' UNION ALL
SELECT 1589991, '12/08/2009 16:29'UNION ALL
SELECT 1590092, '12/08/2009 16:59' UNION ALL
SELECT 1590162, '12/08/2009 17:29'
Select ts1.ID,ts1.[Value],ts1.[Time_Stamp]
,ts1.[Value] - (
Select [Value] from #MyMeter
Where [ID] = (
Select top 1 ts2.[ID] from #MyMeter ts2
where ts2.[Time_Stamp] < ts1.[Time_Stamp]
order by ts2.[Time_Stamp] desc
)
) as [Stamp_Diff]
From #MyMeter ts1
drop table #MyMeter
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
August 24, 2009 at 1:41 pm
Or you could use the attached code (couldn't post it directly, again).
August 24, 2009 at 2:10 pm
Thanks a lot folks...
I need to work through this so i can understand it.
It's good of you all to spend the time to help.
I'll let you know
Cheers
Andy
August 24, 2009 at 2:46 pm
It appears to me that Lynn's code assumes the data is entered in time_stamp order and the ID for the previous Time_Stamp is one less than the current record.
Where as my lookup assumes no order to the data an looks for the time_stamp record that was previous to the current time_stamp.
If the op is using an autonumber for the ID then may be sequential where as the the time_stamp MAY not be sequential. I could have assued the data was in time_stamp order by sorting the outside query by adding an Order by like:
Select ts1.ID,ts1.[Value],ts1.[Time_Stamp]
,ts1.[Value] - (
Select [Value] from #MyMeter
Where [ID] = (
Select top 1 ts2.[ID] from #MyMeter ts2
where ts2.[Time_Stamp] < ts1.[Time_Stamp]
order by ts2.[Time_Stamp] desc
)
) as [Stamp_Diff]
From #MyMeter ts1
Order by ts1.[Time_Stamp]
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
August 24, 2009 at 2:49 pm
DougGifford (8/24/2009)
It appears to me that Lynn's code assumes the data is entered in time_stamp order and the ID for the previous Time_Stamp is one less than the current record.Where as my lookup assumes no order to the data an looks for the time_stamp record that was previous to the current time_stamp.
If the op is using an autonumber for the ID then may be sequential where as the the time_stamp MAY not be sequential. I could have assued the data was in time_stamp order by sorting the outside query by adding an Order by like:
Select ts1.ID,ts1.[Value],ts1.[Time_Stamp]
,ts1.[Value] - (
Select [Value] from #MyMeter
Where [ID] = (
Select top 1 ts2.[ID] from #MyMeter ts2
where ts2.[Time_Stamp] < ts1.[Time_Stamp]
order by ts2.[Time_Stamp] desc
)
) as [Stamp_Diff]
From #MyMeter ts1
Order by ts1.[Time_Stamp]
Fix that I will. Back I will be in a few minutes.
August 24, 2009 at 2:53 pm
Here is an updated set of code.
August 24, 2009 at 3:08 pm
Like Lynn's code I do.
I am using SQL Server 2000 and solved the problem without the "over" statement and Join.
If I had more time, I may be able come up with a Join.
Is there a way with a Join and without the "over"?
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
August 24, 2009 at 3:15 pm
You could use a modified version of Jeff Moden's "running total" solution to carry the value around. In high cardinality sets - it should outpace the correlated subquery rather dramatically:
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url]
edit: sorry - initally posted the wrong article link. this one's the right one!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply