Finding “Active” Rows for the Previous Month (SQL Spackle)

  • Comments posted to this topic are about the item Finding “Active” Rows for the Previous Month (SQL Spackle)

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Nice job Jeff!

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • mister.magoo (1/30/2014)


    Nice job Jeff!

    +1 🙂

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Excellent article. A problem that i have never come across but may do. The train of thought on the problem was insightful.

  • Thanks guys. I appreciate it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • danielfountain (1/30/2014)


    Excellent article. A problem that i have never come across but may do. The train of thought on the problem was insightful.

    That, good sir, is why I didn't simply submit this as a script. Understanding why this works may lead someone to doing something even better.

    Thank you for the feedback.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Everything is right, but there are and other ways how to make your code bullet proof.

  • vitex2003 (1/30/2014)


    Everything is right, but there are and other ways how to make your code bullet proof.

    Thanks for the insight. Would you post some code for said bullet proofing? Thanks.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • When I went to review this spackle, I found the current rating was "Good". Can't agree. Jeff has, as usual, taken a problem of some import, broken it down into easily digestible steps, provided excellent explanations, and written pretty code to show how to solve it.

    Can I get an "Excellent" people?

    Thanks Jeff. I want to be you someday when I grow up. If I decide it's worth growing up. 😉

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • SELECT RangeType,StartDate,EndDate,Comment

    FROM dbo.TestTable

    WHERE convert(char(10),EndDate,102) >= cast('01 Oct 2013' as datetime)

    AND convert(char(10),StartDate,102) <= cast('01 Nov 2013' as datetime)

  • Nice article Jeff, a nice brain-teaser, thank you.

    I got this far, via a rather round-about route...

    select * from testtable

    where datediff(month,startdate,'oct 2013') >= 0

    and datediff(month,'oct 2013',enddate) >= 0

    This deals with the time problem without having to give it a thought.

    Thanks very much for the 'oct 2013' insight, not needing a date. That's very useful. I had used '1 oct 2013', which still works of course.

    Jerry.

  • Thomas Abraham (1/30/2014)


    When I went to review this spackle, I found the current rating was "Good". Can't agree. Jeff has, as usual, taken a problem of some import, broken it down into easily digestible steps, provided excellent explanations, and written pretty code to show how to solve it.

    Can I get an "Excellent" people?

    Thanks Jeff. I want to be you someday when I grow up. If I decide it's worth growing up. 😉

    I agree. I was surprised to see the rating of this so low. I wonder if some people are rating it based on the complexity of the topic instead of the quality of the writing.

    It is a topic that most middle to advanced sql people would be able to solve with little to no thought. But as Jeff has said, this was something he remembered encountering when he was green (hard to imagine Jeff as being green) and struggled with.

    This is an excellent spackle about a topic that many many newbies really struggle with. And once again Jeff's ability to step outside of his own knowledge and into the mind of somebody who will find this useful is quite impressive.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • vitex2003 (1/30/2014)


    SELECT RangeType,StartDate,EndDate,Comment

    FROM dbo.TestTable

    WHERE convert(char(10),EndDate,102) >= cast('01 Oct 2013' as datetime)

    AND convert(char(10),StartDate,102) <= cast('01 Nov 2013' as datetime)

    Are you sure about that? You are now evaluating a string on one side to a date on the other. Do you know what really happens there? It will promote your string to a datetime. In other words you are explicitly casting a datetime to a string and then the sql engine will implicitly convert it right back to a datetime. This is good because if the engine compared them as strings your logic would not work correctly.

    Take a look at the values from your convert.

    SELECT RangeType,StartDate,EndDate,Comment

    ,convert(char(10),EndDate,102) as ConvertEndDate, cast('01 Oct 2013' as datetime)

    , convert(char(10),StartDate,102) as ConvertStartDate, cast('01 Nov 2013' as datetime)

    FROM dbo.TestTable

    WHERE convert(char(10),EndDate,102) >= cast('01 Oct 2013' as datetime)

    AND convert(char(10),StartDate,102) <= cast('01 Nov 2013' as datetime)

    And adding the functions around EndDate and StartDate in your where clause has rendered this query nonsargable to boot.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Jerry Kelk (1/30/2014)


    This deals with the time problem without having to give it a thought.

    Thanks for the feedback and the code, Jerry, but you've just exemplified one of the reasons why I wrote this article. You have to give even these simple things some thought.

    Your method uses formulas on the date columns in the WHERE clause. That makes the query "non-SARGable" and won't ever be able to use an index properly. Sure, it'll use an index but it will never be better than a full index scan because it has to express the formula over every value in the index before it can be evaluated for return.

    With certain exceptions that would require an article of their own, you must avoid encapsulating table columns as criteria in either the WHERE or ON clause of a join in order to ensure future scalability and performance. And you must think of these things every time you write code or it will eventually bite you or your customers when either of you can least afford it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • vitex2003 (1/30/2014)


    SELECT RangeType,StartDate,EndDate,Comment

    FROM dbo.TestTable

    WHERE convert(char(10),EndDate,102) >= cast('01 Oct 2013' as datetime)

    AND convert(char(10),StartDate,102) <= cast('01 Nov 2013' as datetime)

    You're code has the same performance and scalability problems as Jerry's code does. Please see my reply to him just above this post.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Viewing 15 posts - 1 through 15 (of 42 total)

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