INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

select the row with max number of days btw the start and close date

select the row with max number of days btw the start and close date

(OP)
i have table with column that have duplicates with different start and end date.

Table1:

CODE

Product       startDate     endDate      Desc
A0987         05/01/2017    05/05/2017   Traiging
A0987         05/01/2017    05/05/2017   Traiging
A1234         05/02/2017    05/03/2017   Driver Mag
A1234         05/05/2017    05/11/2017   Driver Mag
A1234         05/04/2017    05/15/2017   Driver Mag
A1234         05/01/2017    05/15/2017   Driver Mag 

Table2:

CODE

ProductDesc
A0198
A0987
A1234 
i need to query on first 3 cols and retrieve the row that has maximum number of days. something like the following

CODE

SELECT ProductDesc, endDate, startDate
FROM Table2
INNER JOIN Table1 ON Product = ProductDesc
WHERE (endDate Is Not Null) AND (startDate Is Not Null) AND max(DateDiff(Day, startDate, endDate)) 

Result to look like:

CODE

Product       startDate     endDate      Desc
A0987         05/01/2017    05/05/2017   Traiging
A1234         05/01/2017    05/15/2017   Driver Mag 

How can I do that? Your help is greatly appreciated. thanks.

RE: select the row with max number of days btw the start and close date

CODE

SELECT t2.ProductDesc, t1.startdate, t1.endDate, t1.[Desc]
FROM Table2 t2
CROSS
APPLY (SELECT TOP 1 *
FROM Table1
WHERE Product = t2.ProductDesc
ORDER BY DATEDIFF(DAY, startDate, endDate) DESC) t1 

RE: select the row with max number of days btw the start and close date

try this:

CODE

;With Data As
(
Select  *,
Row_Number() Over (Partition By Product order By DateDiff(Day, startDate, endDate) DESC) As RowId
From    YourTableNameHere
)
Select	*
From    Data
Where	RowId = 1 

full example:

CODE

declare @temp Table(Product varchar(20), startDate DateTime, endDate DateTime, Description varchar(20))

Insert Into @Temp Values('A0987','05/01/2017','05/05/2017','Traiging')
Insert Into @Temp Values('A0987','05/01/2017','05/05/2017','Traiging')
Insert Into @Temp Values('A1234','05/02/2017','05/03/2017','Driver Mag')
Insert Into @Temp Values('A1234','05/05/2017','05/11/2017','Driver Mag')
Insert Into @Temp Values('A1234','05/04/2017','05/15/2017','Driver Mag')
Insert Into @Temp Values('A1234','05/01/2017','05/15/2017','Driver Mag')

;With Data As
(
Select *,
Row_Number() Over (Partition By Product order By DateDiff(Day, startDate, endDate) DESC) As RowId
From   @Temp
)
Select  *
From    Data
Where   RowId = 1 

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!