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 ««12345»»»

Finding “Active” Rows for the Previous Month (SQL Spackle) Expand / Collapse
Author
Message
Posted Thursday, January 30, 2014 7:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 30, 2014 4:50 AM
Points: 3, Visits: 17
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.
Post #1536351
Posted Thursday, January 30, 2014 7:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1536356
Posted Thursday, January 30, 2014 7:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1536360
Posted Thursday, January 30, 2014 8:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:51 PM
Points: 35,606, Visits: 32,190
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1536366
Posted Thursday, January 30, 2014 8:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:51 PM
Points: 35,606, Visits: 32,190
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1536369
Posted Thursday, January 30, 2014 8:27 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:51 PM
Points: 35,606, Visits: 32,190
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.


Thank you for the kind words, Thomas. If I ever need a couple of PR guys for something really important, you'll definitely be one of them.

Heh... as for "growing up", it's seriously over rated. I'm 61 and I still enjoy being a "kid". MUCH more fun that way.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1536372
Posted Thursday, January 30, 2014 8:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 30, 2014 4:50 AM
Points: 3, Visits: 17
Fair comment Jeff. Point taken.

Fortunately, I don't have huge data or paying customers, so on a horses-for-courses basis, the method suits me well enough.

Food for thought though, thanks.
Jerry
Post #1536374
Posted Thursday, January 30, 2014 8:33 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 3, 2014 7:35 AM
Points: 8, Visits: 133
Nice article, well written. As a developer that has had to do a lot of work around date and time functionality, I can appreciate this article. You are correct that this is really the most efficient way to get active rows based on start/end dates. It would be really great if you could do an advanced version and show people how to deal with reverse dates (for databases that don't have proper constraints and allow end dates to be incorrectly added with values prior to the start date - you know we don't always have control over every database design) and more importantly, NULL values. Assuming that in some cases, depending on the business rules and purpose of the data, an end date hasn't been determined yet and may be NULL. I prefer to use a COALESCE for this, forcing the NULL end dates to be the day following the variable end date of the range to be tested. But there are a number of other ways to do this.

Jerry Boutot, MCAD MCP
Owner, AppDataWorks, LLC
AppDataWorks, LLC
Jerry Boutot Music
Post #1536377
Posted Thursday, January 30, 2014 8:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:51 PM
Points: 35,606, Visits: 32,190
Sean Lange (1/30/2014)
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.


Heh... Your observation about complexity and ratings is spot on. It's always a danger when you write something so simple about such a simple problem even though (as demonstrated by some of the code posts on this very thread) some of the "middle to advanced" SQL people get it wrong.

Of course and due to the coded responses on this thread, I'm thinking that a lot of people looked at the code and said "Well, hell... I can do it easier than that" without understanding what they've actually done in terms of performance and scalability. I probably should have mentioned something to the effect in the article but then I'd have also had to explain and demonstrate what "SARGability" is and that would have been too long for a simple "spackle" article.

On the bright side (or, maybe, the extremely dark side), some of those coded responses have demonstrated the need for yet another article on the web about SARGability. I'll have to write that one at a newbie level, as well.

Thank you very much for the thoughtful kudo, ol' friend. You'd be one of the other PR guys that I'd seek out.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1536381
Posted Thursday, January 30, 2014 8:47 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:51 PM
Points: 35,606, Visits: 32,190
Jerry Kelk (1/30/2014)
Fair comment Jeff. Point taken.

Fortunately, I don't have huge data or paying customers, so on a horses-for-courses basis, the method suits me well enough.

Food for thought though, thanks.
Jerry


That's one of the things that I always take a stand on, though, Jerry. It's like practicing the piano... unless you're practicing to be a piano wielding comedian, you don't practice hitting the wrong notes just because no one is listening. Justification of bad code based on table size isn't something that anyone should ever do. Always practice doing it right so that when it actually does count, it's second nature and then truly requires "no thought" to do it quickly and the right way.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1536389
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse