INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Jobs

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

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
twitter
"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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close