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

Question

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

Viewing 15 posts - 16 through 30 (of 41 total)

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

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 1

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. :blush: 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.

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)

Jerry Kelk SSC Enthusiast Points: 127 More actions · Answer 2

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

Jerry_Boutot Old Hand Points: 394 More actions · Answer 3

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, MTA
Jerry Boutot Official

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 4

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.

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)

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 5

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.

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)

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 6

Jerry_Boutot (1/30/2014)
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.

Excellent idea on the "advanced article" on such things, Jerry. This small "spackle" article actually sets the stage not only for what you suggest, but also for how to manage things like how to handle Type 2 SCDs (Slowly Changing Dimensions) for "Point-in-Time" statuses. Many such articles have already been written on the subject (like this simple "Spackle" article has), but it might serve as a reminder or a different point of view.

Thank you very much for the feedback. 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)

Jerry Kelk SSC Enthusiast Points: 127 More actions · Answer 7

I think it depends on the purpose of the request.

If my 'customer' wants a one-time answer to such a question, on less than a huge table, I think my solution was entirely valid. These are the sorts of challenge I get in my world. You are of course perfectly correct though, so thanks for that additional insight. Note that I have neither mega-data nor transaction-load to be able to detect a difference, even if I did write two solutions and compare them. I do, however, like interesting puzzles and will look forward to another. I probably won't post an answer though.

Hand slapped; cookie jar back on the shelf.

Have a nice day Jeff & everyone.

J

Thomas Abraham SSChampion Points: 10761 More actions · Answer 8

Jeff Moden (1/30/2014)

Thank you for the kind words, Thomas. :blush: 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. 😛

See, I have plenty of time. I'm only 52! As I like to say, you may not any longer want to fly a kite. But, if you can't remember why you wanted to when you were younger, you might as well be worm food.

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

Thomas Abraham SSChampion Points: 10761 More actions · Answer 9

Jeff Moden (1/30/2014)
Thank you for the kind words, Thomas. :blush: 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. 😛

See, I have plenty of time. I'm only 52! As I like to say, you may not any longer want to fly a kite. But, if you can't remember why you wanted to when you were younger, you might as well be worm food.

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

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 10

Jerry Kelk (1/30/2014)
I think it depends on the purpose of the request.
If my 'customer' wants a one-time answer to such a question, on less than a huge table, I think my solution was entirely valid.

I'n not trying to chastise anyone. I'm trying to educate and maybe save them some embarrassment in front of their bosses or customers. 😉

--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)

Marcia J SSCertifiable Points: 5649 More actions · Answer 11

I agree with the comments about this being excellent. I work with a lot of effective-dated information and have been using similar logic for over 20 years (in COBOL for a good bit of that), so it comes almost without thinking to me now. However, when I first started, it was not that easy to get my mind around it.

WayneS SSC Guru Points: 95409 More actions · Answer 12

Jeff Moden (1/30/2014)
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.

+ infinity Jeff.

Additionally, someone else may be doing the work "when it counts", and they just might copy/re-use the poorer quality code that you've written because it does do the job. Then they will be struggling to figure out why the code has performance issues.

Let's also consider the simplicity of the code.

Jeff's final result:

SELECT RangeType,StartDate,EndDate,Comment

FROM dbo.TestTable

WHERE EndDate >= '01 Oct 2013'

AND StartDate < '01 Nov 2013'

Your recommendation:

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)

or

select * from testtable

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

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

Which code is simpler? Which is SARGable? Which will be faster on a large data set?

They are all one and the same, so (as is often the case), the simpler, more correct code is the better code to use. Why try to also remember these formulas when all you need to learn is "End-Date >= StartingDate and Start-Date < EndingDate + 1"?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

staudacher SSC Veteran Points: 297 More actions · Answer 13

Nice Example to find out that Date can have Time included

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 14

marcia.j.wilson (1/30/2014)
I agree with the comments about this being excellent. I work with a lot of effective-dated information and have been using similar logic for over 20 years (in COBOL for a good bit of that), so it comes almost without thinking to me now. However, when I first started, it was not that easy to get my mind around it.

Thanks, Marcia. Hopefully, this article will make it a little easier on those just getting started.

--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)

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 15

staudacher (1/30/2014)
Nice Example to find out that Date can have Time included

That's one of the things that many people miss or, in the face of a column that's supposed "guaranteed" to never have a time in it, simply ignore. Ironically, I just helped someone at work today on that later item. They never looked at the data because they were told the column would/could never have a time in it. If you write the code to always accomodate having a time, it's never a problem and you don't need to have two different sets of code. The same code will solve the problem with or without time.

Thanks for the observation and 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)