Post reply

The Christmas CROSS APPLY

  • September 27, 2016 at 10:26 pm

    #100939

    Comments posted to this topic are about the item The Christmas CROSS APPLY

  • September 28, 2016 at 12:24 am

    #1903545

    Hi Steve, thank you for this question.

    Manie Verster
    Developer
    Johannesburg
    South Africa

    I am happy because I choose to be happy.
    I just love my job!!!

  • September 28, 2016 at 12:51 am

    #1903547

    thanks for the nice question steve.

  • September 28, 2016 at 1:32 am

    This was removed by the editor as SPAM

  • September 28, 2016 at 9:09 am

    #1903687

    Steve, in your explanation, did you mean to say "This is offset by -1" instead of "by 1"? Or maybe by "offset" you meant the successive values of the ROWNUMBER() generated values in the CTE and not the N-1 argument of DATEADD?

    I also was looking for the gotcha!

    Rich

  • September 28, 2016 at 9:26 am

    #1903694

    Rich Mechaber (9/28/2016)

    Steve, in your explanation, did you mean to say "This is offset by -1" instead of "by 1"? Or maybe by "offset" you meant the successive values of the ROWNUMBER() generated values in the CTE and not the N-1 argument of DATEADD?

    I also was looking for the gotcha!

    Rich

    Fair enough. The offset is by 1, but in the negative direction, but I can see why you might confuse that.

    Changed.

  • September 28, 2016 at 9:43 am

    #1903697

    Yay. I got one right.

    Personally I'd simplify this one like this:

    SELECT

    DATEADD(YEAR, N, CONVERT(DATE, v.HolidayDate)) AS HolidayDate,

    HolidayName

    FROM (VALUES ('12/25/2016', 'Christmas Day')) AS v (HolidayDate, HolidayName)

    CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) vTally(n);

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • September 28, 2016 at 10:00 am

    #1903706

    Yay, really witty. Thank you, Steve!

  • September 29, 2016 at 6:37 am

    #1903916

    Nice question, thanks.

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

  • September 29, 2016 at 8:22 am

    #1903968

    Wow, that's some crazy SQL! Thanks for the cool question, though. 🙂

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • November 1, 2016 at 3:29 pm

    #1910015

    I would never have forgiven myself if I missed this one.

    I'm just glad the date wasn't Thanksgiving.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 11 posts - 1 through 10 (of 10 total)

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