TRICKY SQL QUERY

Question

TRICKY SQL QUERY

martin.edward

SSCommitted

Points: 1645
More actions
March 3, 2010 at 6:42 am

#216211

Dear Experts ,
I would like to do the following :-
get the date out of a certain column in a table
select docdate from tablename
Check if the docdate is greater than todays date, if so display it
end.
I have tried using a while loop but it fails. It just picks one date only instead of all the dates in the table. This does not work
WHILE (SELECT docdate FROM tablename) <> ''
BEGIN
SELECT @BATCH=docdate FROM tablename
END
Kind Regards,

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

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

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

martin.edward (3/3/2010)
Dear Experts ,
I would like to do the following :-
get the date out of a certain column in a table
select docdate from tablename
Check if the docdate is greater than todays date, if so display it
end.
I have tried using a while loop but it fails. It just picks one date only instead of all the dates in the table. This does not work
WHILE (SELECT docdate FROM tablename) <> ''
BEGIN
SELECT @BATCH=docdate FROM tablename
END
Kind Regards,

Try this:

select

docdate

from

tablename

where

docdate > getdate() -- includes time info

-- docdate > dateadd(dd, datediff(dd, 0, getdaate()), 0) -- drops time portion from consideration, use one or the other.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Hi Martin

Please don't take this the wrong way - we all have to learn somewhere. The query you posted is about as simple as it can get. Buy yourself this book and save yourself a lot of time. It's about ten quid in pounds sterling. Don't be put off by the name, it's one of a series of excellent startup guides, well written and researched.

Good luck!

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Garadin One Orange Chip Points: 29613 More actions · Answer 3

I haven't read the one Chris mentioned, but I would recommend these:

http://www.amazon.co.uk/Microsoft-Server-T-SQL-Fundamentals-PRO-Developer/dp/0735626014/ref=pd_sim_b_7

http://www.amazon.co.uk/Inside-Microsoft-SQL-Server-2008/dp/0735626030/ref=pd_sim_b_3

http://www.amazon.co.uk/Inside-Microsoft-Server-2008-Pro-Developer/dp/0735626022/ref=pd_sim_b_3

That said, all of the books we just linked target SQL Server 2008. Although the fundamentals books will have good info applicable to all versions, many of the functions that people take for granted nowadays were not available in SQL 2000.

Windowed Functions, CTE's, Cross Apply, FOR XML Path... (the list goes on)... 2008 folks are spoiled.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

martin.edward SSCommitted Points: 1645 More actions · Answer 4

Thanks,

Am not sure which book you are suggesting : Is it understanding and using APPLY 1 ? Could you suggest where I could purchase it from ?

Garadin One Orange Chip Points: 29613 More actions · Answer 5

Martin,

Chris was suggesting this book:

http://www.amazon.co.uk/Microsoft-SQL-Server-2008-Dummies/dp/0470224657 You can pick it up online. You can also get all of the books I listed on Amazon.

The Understanding and using APPLY (I) link is part of Chris' signature and not directly related to your question.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Garadin (6/14/2010)
Martin,
Chris was suggesting this book:
http://www.amazon.co.uk/Microsoft-SQL-Server-2008-Dummies/dp/0470224657 You can pick it up online. You can also get all of the books I listed on Amazon.
The Understanding and using APPLY (I) link is part of Chris' signature and not directly related to your question.

Many thanks Seth, I missed this one yesterday.

Must admit I recommended the SQL for Dummies book without reading all of it, but it looks pretty good. The Excel, Statistics and Word equivalents have helped my better half no end.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Garadin One Orange Chip Points: 29613 More actions · Answer 7

Anytime sir =)

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]