Need a date between what MIN and MAX returns

Question

Post reply

Need a date between what MIN and MAX returns

ninel

Ten Centuries

Points: 1027
More actions
May 14, 2010 at 11:20 pm

#218916

I posted this in another forum(SQL 7.2) and realized it was in the wrong location...
I have a table that stores dates of records that need to be reprocessed. Normally I would use MIN function to pick up the earliest date I need processed. Lately i've been finding at least one date is out of the norm. So instead of picking up the min I'd like to pick up the next in line.
For example, the first date is 20090104 (which min would give me), then 20100403 then 20100404,20100405 but i want to pick up the next one.
This is a night time process and I won't know which dates are in the table so I can't hardcode. I'm only checking if the 1st date returned by the min is greater than 30 days from current day. Is there any way of doing this?
Thanks,
Ninel

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

see http://msdn.microsoft.com/en-us/library/ms186734.aspx

and http://www.sqlservercentral.com/articles/T-SQL/69717/

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

vijay.s SSCrazy Points: 2161 More actions · Answer 2

WHERE MIN(GETDATE()) = GETDATE()--So its a next day date from your minimum date

I Hope this what you wanted! If you want to a batter solution then try to post your problem with some sample data in form of temp table or table variable

Regards

Vijay

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 3

vijay.s (5/15/2010)
WHERE MIN(GETDATE()) = GETDATE()--So its a next day date from your minimum date
I Hope this what you wanted! If you want to a batter solution then try to post your problem with some sample data in form of temp table or table variable
Regards
Vijay

I think something is missing in that code, Vijay. The code you posted is like saying WHERE 1 = 1. Is it just a typo or am I missing something?

--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: 1004292 More actions · Answer 4

ninel (5/14/2010)
Normally I would use MIN function to pick up the earliest date I need processed. Lately i've been finding at least one date is out of the norm.

What would you do if two or twenty dates were "out of the norm"? It sounds like there's a more fundamental and urgent problem that needs to be fixed rather than trying to come up with a work around. What causes the dates to be "out of the norm" and how do you know they're "out of the norm"?

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

Do you want to select the next date after the MIN, that query will do it:

declare @dt table (dt datetime)

insert @dt select '20090101'

union select '20090210'

union select '20090312'

union select '20090415'

union select '20100510'

select MIN(dt.dt)

from @dt dt

left join (select min(dt) mindt from @dt) md

on md.mindt = dt.dt

where md.mindt is null

As you can see it will return not the 1 Jan 2009, but the next min date of 10 Feb 2009

Is it what you want?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Peter Bellis SSC Rookie Points: 40 More actions · Answer 6

Firstly I'd echo Jeff's concern ... you're attempting to work around the problem.

However, putting that aside ... what's wrong with:

Declare @MDate VarChar(10) -- I'm assuming its a varchar ....

Set @MDate = (Select Min(DDate) From TTable)

Select Min(DDate)

From TTable

Where DDate > @MDate

I'm definitely not a guru, so might have overlooked some simple trap ... I depend on the gurus to correct me :hehe:

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

DECLARE @dt TABLE (dt DATETIME NOT NULL PRIMARY KEY);

INSERT @dt

SELECT '20090101' UNION ALL

SELECT '20090210' UNION ALL

SELECT '20090312' UNION ALL

SELECT '20090415' UNION ALL

SELECT '20100510';

SELECT TOP (1) MIN2.dt

FROM (

SELECT TOP (2) dt,

rn = ROW_NUMBER() OVER (ORDER BY DT.dt ASC)

FROM @dt DT

ORDER BY DT.dt ASC

) MIN2

WHERE rn = 2;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi