Lag and Lead Function - Populate Column with Values
-
Good Morning,
I have a table that contains precipitation accumulation values, but I need to populate another column with only the increment values.
As it stands, the accumulation values is a running total, and in order for me to sum up the accumulation for a day and/or month, I need to substract the second row from the first and so on until all the records
have been processed.For example:
Accumulation Increment
161.9
162.3 0.4
164.5 2.2
169.8 5.3I need to subtract the row #2 accumulation value from row #1 and populate the increment field with a value. So, in the example above:
162.3 - 161.9 = 0.4
And so on, until all the rows are processed.Can someone please help me understand these functions and how I would construct a sql statement that would accomplish this?
Walter
-
MSDN gives pretty good detail and examples on how to use these functions:
LEAD (Transact-SQL)
LAG (Transact-SQL)They effectively work in the same way, apart from LAG looks backwards, and LEAD looks forwards in the dataset. You need to use them with the OVER clause, which is explained in both articles, but this does give you the option to PARTITION as well.
For example, something like this might give you the previous temperate value in a given city:
LAG(Temperature) OVER (PARTITION BY CityName ORDER BY ReadingDate ASC)
Note both functions also have 2 optional parameters, offset and default. Offset being how many rows ahead/behind you want to read, and default being a value to return if a scalar value cannot be found at the offset location (you've tried to obtain a row beyond the dataset).Hope that helps.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Thom,
I'm close, but yet still not getting the values correctly.select date, value, lead(value,1) over (order by date) from [GIS_EDMS].[sde].[Precip_Acc]
The value in the column to the right should be .1, meaning 161.8 - 161.7 = .1
Could you please give me a hand with this?
Walter
-
suggest
lead(value,1) over (order by date) from [GIS_EDMS].[sde].[Precip_Acc] - value as result________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
Gents,
I can't seem to get this sql statement to work:select date, value, lead(value,1) over (order by date) from [GIS_EDMS].[sde].[Precip_Acc] - value as result
It's giving me an error "Incorrect syntax near '-'
Walter
- walter.dziuba - Friday, February 24, 2017 11:37 AM
please post the whole query,,,,,,you have no "from" statement in what you have posted
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day - walter.dziuba - Friday, February 24, 2017 11:37 AM
select date, value, lead(value,1) over (order by date) - value as result
from [GIS_EDMS].[sde].[Precip_Acc]It helps if you format your code instead of putting it all on the same line.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Drew,
Appreciate your return comment. Your suggestion worked like a charm.Thanks to all the gentlemen who've helped me above.
thx..Walter
-
Morning,
Now that I have a successful sql statement, thanks to the help of others, I now would like to update a column in a table using that same line.select date, value, lead(value,1) over (order by date) - value as result
from [GIS_EDMS].[sde].[Precip_Acc]Now with the update statement:
update [GIS_EDMS].[dbo].[DAILY2]
set Increment = (select date, value, lead(value,1) over (order by date) - value as Increment from [GIS_EDMS].[dbo].[DAILY2])
The above line causes an error: "Only one expression can be specified in the select list when the subquery is not introduced with exists"Walter
- walter.dziuba - Saturday, February 25, 2017 9:15 AM
if you look at your set statement then the error message is self explanatory...(select date,value, increment)...how is SQL going to choose from multiple values?
Moving on....you cannot use a windowing function directly in an update statement... you can solve this using a CTE (go google MS SQL Common Table Expressions)
CTE's can be updated directly......here is some example code...I have also rearranged the "result" column...which is what I "think" you originally asked for.
CREATE TABLE #yourtable(
thedate DATETIME NOT NULL
,thevalue INT NOT NULL
,increment INT
);
INSERT INTO #yourtable(thedate,thevalue,increment) VALUES
('2005-01-01',10,NULL)
,('2005-01-02',12,NULL)
,('2005-01-03',14,NULL)
,('2005-01-04',14,NULL)
,('2005-01-05',18,NULL)
,('2005-01-06',19,NULL);-- see select results
SELECT thedate,
thevalue,
increment,
thevalue - lag(thevalue, 1) OVER(ORDER BY thedate) AS result
FROM #yourtable;-- you need to update using a CTE
WITH cte as (
SELECT
increment,
thevalue - lag(thevalue, 1) OVER(ORDER BY thedate) AS result
FROM #yourtable
)UPDATE cte
SET increment = resultSELECT * FROM #yourtable
DROP TABLE #yourtable
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
Thanks J. Livingston...
I'll give your suggestion a try.
Walter
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply