Post reply

Lead - 2

  • November 10, 2012 at 12:39 pm

    #270135

    Comments posted to this topic are about the item Lead - 2

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • November 11, 2012 at 10:35 pm

    #1558004

    Thanks for the question Ron.

    After messing-up with LEAD part-1 the other day, this question was easy to solve 🙂

    ~ Lokesh Vij

    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • November 12, 2012 at 12:20 am

    #1558017

    Great question Ron, thanks!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • November 12, 2012 at 1:01 am

    #1558038

    This is a PERFECT qotd!!!

    You can't miss it!

    :-D:-D:-D

  • November 12, 2012 at 2:23 am

    This was removed by the editor as SPAM

  • November 12, 2012 at 2:46 am

    #1558065

    Good to see a question covering some of the new 2012 features. Thanks.

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • November 12, 2012 at 6:05 am

    #1558133

    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

  • November 12, 2012 at 10:14 am

    #1558230

    I reviewed John Arnott's fine explanation in the previous question and was able to figure it out from that.

    Nice question.

  • November 12, 2012 at 11:49 am

    #1558265

    Thanks for the 2012 question! These functions are similar to ones that QlikView has in their query language for referencing the previous or next row in a set. Very handy. Too bad I cannot take advantage of these since none of my production systems are on 2012 yet. :crying:

  • November 12, 2012 at 8:21 pm

    #1558368

    Nice question, and easy.

    But I suspect a sales manager who noticed that goals were being set like that would not be happy - fancy giving the salesman who made the best sales last month the target of achieving just last months worst performance this month!

    Tom

  • November 12, 2012 at 8:39 pm

    #1558369

    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

  • November 13, 2012 at 7:56 am

    #1558507

    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)

  • November 13, 2012 at 7:57 am

    #1558509

    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

  • November 13, 2012 at 2:35 pm

    #1558663

    Another solid question Ron.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • November 23, 2012 at 8:56 am

    #1561885

    Cool question, this makes me want to get my hands on a SQL 2012 server 🙂

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply