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

[RosieGp]

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

Table1:

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:

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

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:

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.

 

[DaveInIowa]

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

 

[gmmastros]

try this:

;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:

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