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

Question

Post reply

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

Jeff Moden

SSC Guru

Points: 1004329
More actions
January 29, 2014 at 10:28 pm

#282902

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)
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 SSC-Forever Points: 47174 More actions · Answer 1

Nice job Jeff!

MM

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

David Burrows SSC Guru Points: 65142 More actions · Answer 2

mister.magoo (1/30/2014)
Nice job Jeff!

+1 🙂

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

danielfountain SSCarpal Tunnel Points: 4229 More actions · Answer 3

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

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

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)

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

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)

vitex2003 SSC Journeyman Points: 96 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

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)

Thomas Abraham SSChampion Points: 10761 More actions · Answer 8

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

vitex2003 SSC Journeyman Points: 96 More actions · Answer 9

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)

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

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.

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

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/

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

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/

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 13

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)

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

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)