Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Lead - 2 Expand / Collapse
Author
Message
Posted Monday, November 12, 2012 8:39 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 7:14 AM
Points: 8,739, Visits: 9,286
mbova407 (11/12/2012)
Thanks for the question

Will someone please explain example C in english
CREATE TABLE T (a int, b int, c int); 
GO
INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5);

SELECT b, c,
LEAD(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) OVER (ORDER BY a) AS i
FROM T;

I am very confused over the first parameter 2*c


It's very easy: 2*c is just what it says - the value in column c multiplied by 2. Which row supplies the value of c that is to be multiplied by 2? The row that is b*(select min(b)) rows ahead of the current row (the value of b used for that is of course the value in the current row; if 2*c calculated by those rules is NULL, the value -c/2.0 (where this time c is taken from the current row) is to be (implicitly converted to an integer and) used instead of that NULL.

More generally, the three arguments of LEAD are treated as follows:
The second and third arguments are expressions where any unqualified row names (or row names using the source table & schama names as qualifers) that are not bound in an aggregate refer to the current row. The first argument is an expression where those unbound column names refer to the row that leads the current row by the number which the second argument evaluates to. The third argument doesn't need to be evaluated unless evaluating the second argument and then the first argument leads to NULL for the first argument, but there's no guarantee that it won't be evaluated even when its value isn't needed. The other two arguments always have to be evaluated, and the first argument can't be evaluated until the second argument has been evaluated, because until then one doesn't know which row the free column names in it refer to.


Tom
Post #1383931
Posted Tuesday, November 13, 2012 7:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 3:27 PM
Points: 1,393, Visits: 479
L' Eomot Inversé (11/12/2012)
mbova407 (11/12/2012)
Thanks for the question

Will someone please explain example C in english
CREATE TABLE T (a int, b int, c int); 
GO
INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5);

SELECT b, c,
LEAD(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) OVER (ORDER BY a) AS i
FROM T;

I am very confused over the first parameter 2*c


It's very easy: 2*c is just what it says - the value in column c multiplied by 2. Which row supplies the value of c that is to be multiplied by 2? The row that is b*(select min(b)) rows ahead of the current row (the value of b used for that is of course the value in the current row; if 2*c calculated by those rules is NULL, the value -c/2.0 (where this time c is taken from the current row) is to be (implicitly converted to an integer and) used instead of that NULL.

More generally, the three arguments of LEAD are treated as follows:
The second and third arguments are expressions where any unqualified row names (or row names using the source table & schama names as qualifers) that are not bound in an aggregate refer to the current row. The first argument is an expression where those unbound column names refer to the row that leads the current row by the number which the second argument evaluates to. The third argument doesn't need to be evaluated unless evaluating the second argument and then the first argument leads to NULL for the first argument, but there's no guarantee that it won't be evaluated even when its value isn't needed. The other two arguments always have to be evaluated, and the first argument can't be evaluated until the second argument has been evaluated, because until then one doesn't know which row the free column names in it refer to.



Thanks for the effort but I am still missing it.

So for the first row C=-3 b=1 (and min b in the table is 1)

so -3*2=-6 the offset is 1*1
So Lead(-6,1,-3/2) = 8?
----------------------------------

Actually I just got it its not Lead(c*2,b*min(b),-c/2) but the value to be returned based on the specified offset

But it is Lead(C value of the row + the offset (not the current row), the calculated offset of the current row value of B, default to the current row value of C if the first parameter is null)
Post #1384111
Posted Tuesday, November 13, 2012 7:57 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 3:27 PM
Points: 1,393, Visits: 479
The first argument is an expression where those unbound column names refer to the row that leads the current row by the number which the second argument evaluates to.



That is what you said
Post #1384113
Posted Tuesday, November 13, 2012 2:35 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:51 PM
Points: 21,644, Visits: 15,317
Another solid question Ron.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1384308
Posted Friday, November 23, 2012 8:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 4:10 PM
Points: 287, Visits: 303
Cool question, this makes me want to get my hands on a SQL 2012 server :)
Post #1388190
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse