MAX date code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

MAX date code

Author

Topic

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-07-06 : 15:43:08

HI,

I'm trying to bring through the most recent date from a field.

A sample of what I am doing is:

CustomerID, DateNotified, DateOfSale, ProductSold

For several reasons, there are several "DateNotified"'s attached to each customer ID. I want to bring through the most recent date used only in my results.

I've tried :

"SELECT CustomerID, DateNotified, DateofSale, ProductSold

FROM dbo.Tbl_Identity INNER JOIN
dbo.Tbl_Sales ON dbo.Tbl_Identity.CustomerID = dbo.Tbl_Sales.CustomerID

Where DateNotified in (select MAX [DateNotified] from Tbl_Identity).

Not sure why it won't work. Studio says something about the MAX.

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-06 : 16:16:37

[code]WITH cteSource
AS (
SELECT i.CustomerID,
i.DateNotified,
s.DateOfSale,
s.ProductSold,
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY DateNotified DESC) AS rn
FROM dbo.Tbl_Identity AS i
INNER JOIN dbo.Tbl_Sales AS s ON s.CustomerID = i.CustomerID
)
SELECT CustomerID,
DateNotified,
DateOfSale,
ProductSold
FROM cteSource
WHERE rn = 1;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-06 : 16:17:20

[code]SELECT TOP(1) i.CustomerID,
i.DateNotified,
s.DateOfSale,
s.ProductSold
FROM dbo.Tbl_Identity AS i
INNER JOIN dbo.Tbl_Sales AS s ON s.CustomerID = i.CustomerID
ORDER BY i.DateNotified DESC;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources