Select from where problem

Question

Post reply

Select from where problem

lemonsqueezy101

SSC Eights!

Points: 915
More actions
June 12, 2009 at 7:13 am

#131896

Hi Guys,
I'm stuck with a query that I could do with some help on if anybody doesn't mind:
It goes something like this:
SELECT Col1, Col1, Col3
Where Col1 = x
from this result set I then need to filter out any rows where Col2 (DateTime) value is more than 24hours before todays date, which I've done with:
DATEADD (HOUR, -24, GETDATE())
However I only want to do this on rows where col3 = x
Any ideas?
Thanks in advance
Mark

Viewing 8 posts - 1 through 7 (of 7 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Select Col1, Col2, Col3

FROM SomeTable

WHERE Col3 = x

AND Coln < DATEADD (HOUR, -24, GETDATE())

Or have I misunderstood your requirement? If so, please post some sample data and expected results.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lemonsqueezy101 SSC Eights! Points: 915 More actions · Answer 2

Hi Gail,

Thanks for replying. I'll get some sample data together, my explanation probably isn't great!

Thanks

M

stopitallready SSC Eights! Points: 800 More actions · Answer 3

Is this what you were looking for?

SELECT Col1, Col2, Col3

FROM Table1

WHERE (Col1 = x AND Col2 = DATEADD (HOUR, -24, GETDATE()) AND Col3 = x) OR

(Col1 = x AND Col3 x)

This should return 24 hour old records when Col3 = x and return all records when Col3 doesn't equal x.

Hope this help,

Eli

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

lemonsqueezy101 (6/12/2009)
Hi Gail,
Thanks for replying. I'll get some sample data together, my explanation probably isn't great!
Thanks
M

Just checking to see if your problem has been resolved or if you have that sample data ans expected results put together yet.

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 5

GilaMonster (6/12/2009)
Select Col1, Col2, Col3
FROM SomeTable
WHERE Col3 = x
AND Coln < DATEADD (HOUR, -24, GETDATE())

[font="Verdana"]I'm wondering whether it's more like this? (So it only filters where Col3 = X... when it doesn't, then it doesn't filter).

Select Col1, Col2, Col3

FROM SomeTable

WHERE Col3 x

OR Col2 >= DATEADD (HOUR, -24, GETDATE())

So the Col3 expression says "if Col3 is not X, then don't bother filtering" and the Col2 expression says "okay, so I guess Col3 does equal X, so filter out anything that is more than 24 hours old."

[/font]

lemonsqueezy101 SSC Eights! Points: 915 More actions · Answer 6

Hi Guys,

Thanks for the replys. The requirements have changed so I've left this behind now. I never did get to the bottom of it though.

Thanks to those that contributed.

Mark

Paul White SSC Guru Points: 150467 More actions · Answer 7

Hey Bruce,

if Col3 is not X, then don't bother filtering

That assumes that the condition on Col3 is evaluated first and that the condition on Col2 shouldn't be evaluated if the condition on Col3 evaluates true! 🙂

You've been working in a procedural programming language recently, eh?

😀

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi