Records Nearest to GETDATE()

Last Post 26 Mar 2008 01:48 AM by SQLUSA. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
daverooke
New Member
New Member

--
19 Mar 2008 12:21 AM
i have a history table with multiple entries and i need to find only the records that are nearest to todays date, eg

id product_id lastupdated_date price
1 1 17/01/2008 .28
2 1 16/03/2008 .29
3 2 18/03/2008 1.10
4 2 19/03/2008 1.20



i need to return one occurence for each of the products, but the one that is closest to todays date?

thanks in advance....
SQLUSA
New Member
New Member

--
19 Mar 2008 01:20 AM

You have to GROUP BY on ProductID
and SELECT ProductID, MAX (LastUpdated)



Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
BI Workshop NYC SSAS, SSIS, SSRS - April 21-24: http://www.sqlusa.com/


daverooke
New Member
New Member

--
21 Mar 2008 06:38 AM
Thanks for both the replies...

Once i read my question again i realised it was wrong..

what it should be is:

I have a product table and a product price history table.

the history table holds the price and the datetime.

I need to present the user with a list of all the products, but show the price of the product "nearest" to a datetime then specify in the search criteria...

Sorry about that, but would be glad if you can help here....
SwePeso
New Member
New Member

--
26 Mar 2008 12:23 AM
Seems bloated to me.
quote:

Originally posted by: Pro Pete

Select p.ProductId, p.Description, pph.price, @SuppliedDate as [As at], pph.LastUpdate_date as [Since]
from Product p
join (
select pph1.product_id, pph1.lastupdate_date, pph1.price
from ProductPriceHistory as pph1
join (select ProductId, MAX(lastupdate_date) as lastupdate_date from ProductPriceHistory where lastupdate_date <= @SuppliedDate group by ProductId) pph2
on pph2.ProductId = pph1.ProductId and pph2.lastupdate_date = pph1.lastupdate_date
) as pph
on pph.ProductId = p.ProductId

Try this

-- Prepare sample data
DECLARE @Sample TABLE (ID INT, ProductID INT, LastUpdated DATETIME, Price MONEY)

SET DATEFORMAT DMY

INSERT @Sample
SELECT 1, 1, '17/01/2008', 0.28 UNION ALL
SELECT 2, 1, '16/03/2008', 0.29 UNION ALL
SELECT 3, 2, '18/03/2008', 1.10 UNION ALL
SELECT 4, 2, '19/03/2008', 1.20

-- Initialize search parameter
DECLARE @WantedDate DATETIME

SET @WantedDate = '20/02/2008'

-- Show the expected result
SELECT ID,
ProductID,
LastUpdated,
Price
FROM (
SELECT ID,
ProductID,
LastUpdated,
Price,
RANK() OVER (PARTITION BY ProductID ORDER BY ABS(DATEDIFF(DAY, LastUpdated, @WantedDate))) AS RecID
FROM @Sample
) AS d
WHERE RecID = 1
SQLUSA
New Member
New Member

--
26 Mar 2008 01:48 AM
Peter,

Absolutely marvelous use of the ABS(absolute) mathematical function. I thought it only useful in nuclear weapon design!

Kalman Toth, Business Intelligence Architect
SQL 2005 Business Intelligence Workshop NYC APR 21-24: http://www.sqlusa.com
SwePeso
New Member
New Member

--
26 Mar 2008 01:56 AM
quote:

Originally posted by: SQLUSA
Absolutely marvelous use of the ABS(absolute) mathematical function. I thought it only useful in nuclear weapon design!


Thanks. This question almost is at same level, isn't it?
You are not authorized to post a reply.

Acceptable Use Policy