April 21, 2016 at 3:52 am
Hi Guys
I have the following script when give me the max record on each date:
DECLARE @StartDate AS DATETIME
SET @StartDate = CONVERT(DATETIME,'15/04/2016',103)
SELECT amr.MeterId
,amr.MeterDescription
,amr.MeterCode
,CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(HOUR,2,amr.ReadingOn),105),105)
,MAX(amr.NewCumulativeReading) AS NewReading
FROM dbo.vw_MeterReading AS amr
WHERE AssetId = 3080
AND ( DATEADD(HOUR,2,amr.ReadingOn) >= @StartDate - 1 )
AND ( DATEADD(HOUR,2,amr.ReadingOn) <= @StartDate + 1 )
AND amr.MeterCode LIKE '%_LHPP'
GROUP BY amr.MeterId
,amr.MeterDescription
,amr.MeterCode
,CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(HOUR,2,amr.ReadingOn),105),105)
,amr.AssetId
the results are as follows:
MeterIdMeterDescription MeterCode DateRead NewReading
1350Left Hand Power Pack RHMDS6776_LHPP2016-04-14 00:00:00.0002566.8
1350Left Hand Power Pack RHMDS6776_LHPP2016-04-15 00:00:00.0002570.9
I need to amend the code so that it gives me the difference between (2570.9 - 2566.8) = 4.1
and the results should be :
MeterIdMeterDescriptionMeterCodeNewReading
1350Left Hand Power Pack RHMDS6776_LHPP4.1
April 21, 2016 at 4:25 am
Nomvula (4/21/2016)
Hi GuysI have the following script when give me the max record on each date:
DECLARE @StartDate AS DATETIME
SET @StartDate = CONVERT(DATETIME,'15/04/2016',103)
SELECT amr.MeterId
,amr.MeterDescription
,amr.MeterCode
,CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(HOUR,2,amr.ReadingOn),105),105)
,MAX(amr.NewCumulativeReading) AS NewReading
FROM dbo.vw_MeterReading AS amr
WHERE AssetId = 3080
AND ( DATEADD(HOUR,2,amr.ReadingOn) >= @StartDate - 1 )
AND ( DATEADD(HOUR,2,amr.ReadingOn) <= @StartDate + 1 )
AND amr.MeterCode LIKE '%_LHPP'
GROUP BY amr.MeterId
,amr.MeterDescription
,amr.MeterCode
,CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(HOUR,2,amr.ReadingOn),105),105)
,amr.AssetId
the results are as follows:
MeterIdMeterDescription MeterCode DateRead NewReading
1350Left Hand Power Pack RHMDS6776_LHPP2016-04-14 00:00:00.0002566.8
1350Left Hand Power Pack RHMDS6776_LHPP2016-04-15 00:00:00.0002570.9
I need to amend the code so that it gives me the difference between (2570.9 - 2566.8) = 4.1
and the results should be :
MeterIdMeterDescriptionMeterCodeNewReading
1350Left Hand Power Pack RHMDS6776_LHPP4.1
Could you post some sample data please? It will make it much easier to give you a solution.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
April 21, 2016 at 6:33 am
Here's the sample data, thanks
DECLARE @MeterReading TABLE
(
MeterId int,MeterDescription varchar(100), ReadingOn datetime, NewReading float
)
INSERT INTO @MeterReading ( MeterId ,MeterDescription ,ReadingOn ,NewReading) VALUES ( 1350,'Left Hand Power Pack RHM','2016-04-14 11:30:00.000',2562.9)
INSERT INTO @MeterReading ( MeterId ,MeterDescription ,ReadingOn ,NewReading) VALUES ( 1350,'Left Hand Power Pack RHM','2016-04-14 23:30:00.000',2566.8)
INSERT INTO @MeterReading ( MeterId ,MeterDescription ,ReadingOn ,NewReading) VALUES ( 1350,'Left Hand Power Pack RHM','2016-04-15 11:30:00.000',2570.9)
INSERT INTO @MeterReading ( MeterId ,MeterDescription ,ReadingOn ,NewReading) VALUES ( 1350,'Left Hand Power Pack RHM','2016-04-15 23:30:00.000',2570.9)
April 21, 2016 at 7:07 am
DECLARE @MeterReading TABLE
(
MeterId int,MeterDescription varchar(100), ReadingOn datetime, NewReading float
)
INSERT INTO @MeterReading ( MeterId ,MeterDescription ,ReadingOn ,NewReading) VALUES ( 1350,'Left Hand Power Pack RHM','2016-04-14 11:30:00.000',2562.9)
INSERT INTO @MeterReading ( MeterId ,MeterDescription ,ReadingOn ,NewReading) VALUES ( 1350,'Left Hand Power Pack RHM','2016-04-14 23:30:00.000',2566.8)
INSERT INTO @MeterReading ( MeterId ,MeterDescription ,ReadingOn ,NewReading) VALUES ( 1350,'Left Hand Power Pack RHM','2016-04-15 11:30:00.000',2570.9)
INSERT INTO @MeterReading ( MeterId ,MeterDescription ,ReadingOn ,NewReading) VALUES ( 1350,'Left Hand Power Pack RHM','2016-04-15 23:30:00.000',2570.9);
WITH cte AS
(
SELECT
MeterId
,MeterDescription
,ReadingOn
,NewReading
,RowNO = ROW_NUMBER() OVER (PARTITION BY MeterID ORDER BY readingon)
FROM @MeterReading
)
SELECT
c1.MeterId
,c1.MeterDescription
,c1.ReadingOn
,c1.NewReading
,CAST(c1.NewReading - c2.NewReading AS decimal(5,1))
FROM cte c1
LEFT JOIN cte c2 ON c2.RowNO = c1.RowNO - 1
Does this do what you want? Your sample data doesn't match the query you've posted so I've taken a guess or two.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
April 21, 2016 at 7:34 am
You could use the LAG() Windows Function which retrieves the value of the previous row. LEAD() retrieves the value on the next row. Something like;
DECLARE @StartDate AS DATETIME
SET @StartDate = CONVERT(DATETIME,'15/04/2016',103)
WITH CTE AS (
SELECT amr.MeterId
,amr.MeterDescription
,amr.MeterCode
,CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(HOUR,2,amr.ReadingOn),105),105) AS DateRead
,MAX(amr.NewCumulativeReading) AS NewReading
FROM dbo.vw_MeterReading AS amr
WHERE AssetId = 3080
AND ( DATEADD(HOUR,2,amr.ReadingOn) >= @StartDate - 1 )
AND ( DATEADD(HOUR,2,amr.ReadingOn) <= @StartDate + 1 )
AND amr.MeterCode LIKE '%_LHPP'
GROUP BY amr.MeterId
,amr.MeterDescription
,amr.MeterCode
,CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(HOUR,2,amr.ReadingOn),105),105)
,amr.AssetId
)
SELECT MeterId, MeterDescription, MeterCode, DateRead,
NewReading - LAG(NewReading, 1) OVER (PARTITION BY MeterId ORDER BY DateRead DESC) AS NewReading
FROM CTE
ORDER BY MeterId, DateRead DESC
April 21, 2016 at 7:52 am
NJ Smith (4/21/2016)
You could use the LAG() Windows Function which retrieves the value of the previous row. LEAD() retrieves the value on the next row. Something like;
DECLARE @StartDate AS DATETIME
SET @StartDate = CONVERT(DATETIME,'15/04/2016',103)
WITH CTE AS (
SELECT amr.MeterId
,amr.MeterDescription
,amr.MeterCode
,CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(HOUR,2,amr.ReadingOn),105),105) AS DateRead
,MAX(amr.NewCumulativeReading) AS NewReading
FROM dbo.vw_MeterReading AS amr
WHERE AssetId = 3080
AND ( DATEADD(HOUR,2,amr.ReadingOn) >= @StartDate - 1 )
AND ( DATEADD(HOUR,2,amr.ReadingOn) <= @StartDate + 1 )
AND amr.MeterCode LIKE '%_LHPP'
GROUP BY amr.MeterId
,amr.MeterDescription
,amr.MeterCode
,CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(HOUR,2,amr.ReadingOn),105),105)
,amr.AssetId
)
SELECT MeterId, MeterDescription, MeterCode, DateRead,
NewReading - LAG(NewReading, 1) OVER (PARTITION BY MeterId ORDER BY DateRead DESC) AS NewReading
FROM CTE
ORDER BY MeterId, DateRead DESC
Bear in mind that LEAD and LAG are 2012 functions. They won't be available if the OP is on 2008.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
April 21, 2016 at 9:13 am
As someone pointed out, LAG/LEAD are not available on 2008. So you need to use ROW_NUMBER() OVER () to get a sequence based on some valid ordering that ensures you see the proper "before" and "current" record based on partitioning/ordering of other output set columns. Then you can join that set to itself and compare each row to the row with a rownumber 1 less than current. This is a fairly common data processing need, so I am sure you can find some examples of it online if it doesn't make sense with my description. Hopefully ROW_NUMBER() isn't new to you - it is a VERY powerful tool in your TSQL tool bag!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 22, 2016 at 12:46 am
thank you so much guys it really does help with what I need to achieve.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply