[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

QUERY SEARCHING CLOSEST DATE

Hi, I'm searching the best and simplest way to make the following query:

I've got some SkU with specific dates. you can find multiple row with same sku, but with different date.
I want the closest date from today (getdate), but not greater.

Sample.

ID      SKU      date
1        abc       01/01/2004
2        abc       01/01/2005
3        abc       01/01/2006
4        abc       01/01/2007

I want the row with ID 3, because the date is closest than date's row ID1 and ID 2.

ID 4's date is closest than ID 3's date but is greater than getdate so not good.

Thank you for your help.
0
bruno_boccara
Asked:
bruno_boccara
1 Solution
 
imran_fastCommented:
select  max(date)  date from yourtable where date <= getdate()
0
 
imran_fastCommented:

select A.* from yourtable A
inner join (
select  max(date)  [date] , sku  from yourtable  where date <= getdate() group by sku )B
on A.sku = B.sku and A.[date] = b.[date]
0
 
dduserCommented:
Select SKU,Min(DateDiff(day,YourDate,GetDate())) from YourTable where DateDiff(day,YourDate,GetDate()) >= 0
Group by SKU

Regards,

dduser
0
 
KelvinYCommented:
Hi bruno_boccara,

Try

SELECT * FROM SKUTable WHERE SKU = 'abc' AND [DATE] = (SELECT MAX(DATE) FROM SKUTable WHERE SKU = 'abc' AND [DATE] <= GETDATE())

Regards
  Kelvin
0
 
Gautham JanardhanCommented:
select top 1 * from dbo.TESTDATE where DT < getdate()
order by DT desc
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now