value near a certain date

Question

1.00/5 (1 vote)

See more:

Hi
I need a query that gives me the value of a column on a certain date..
If that does not exist in the DB it needs to take the closest possible date ..

currently I have this

SQL

select A, min(B)
from Table1

where gebeurtenis between begindatum and einddatum

group by A

thanks in advance!

Posted 20-Aug-13 23:55pm

Sam Van den Bossche

Updated 21-Aug-13 0:01am

coded007

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2013-08-21T00:01:00

Solution 1

"Closest possible date" is a slightly nebulous concept here: is it closer if it's before, or after the desired date?

Probably, you are going to want to do something like:

SQL

SELECT TOP 1 * FROM myTable WHERE gebeurtenis >= '2013-01-01' ORDER BY gebeurtenis DESC

Or

SQL

SELECT TOP 1 * FROM myTable WHERE gebeurtenis <= '2013-01-01' ORDER BY gebeurtenis ASC

But there is no good mechanism for doing both at once.

Posted 21-Aug-13 0:01am

OriginalGriff

Comments

Sam Van den Bossche 21-Aug-13 6:04am

right, its the next date not the date before the given date

OriginalGriff 21-Aug-13 6:08am

So you want the second one.
"Return the first date on or after the specified date"

Aarti Meswania · Answer 2 · 2013-08-21T00:08:00

check below example replace underlined portion with tablename

SQL

declare @begindatum datetime
set @begindatum = '2013-07-09'
select top 1 A, ABS(DateDiff(d,gebeurtenis,@begindatum)) nearbydays,gebeurtenis
from
(
SELECT 'a' as A, '2013-07-15' gebeurtenis
union all
SELECT 'a' as A, '2013-07-08' gebeurtenis
union all
SELECT 'a' as A, '2013-07-10' gebeurtenis
) as table1
order by  ABS(DateDiff(d,gebeurtenis,@begindatum))

Happy Coding!
:)

Yesudasan Moses · Answer 3 · 2013-08-21T00:07:00

Solution 2

create an sql function "CurentorNearestDate" and pass a code like this

SQL

select A, min(B)
from Table1

where gebeurtenis = CurentorNearestDate(gebeurtenis) 

group by A

Posted 21-Aug-13 0:07am

Yesudasan Moses

v2

Comments

Mas11 21-Aug-13 6:11am

Nice Try !