i have table with column that have duplicates with different start and end date.
Table1:
Table2:
i need to query on first 3 cols and retrieve the row that has maximum number of days. something like the following
Result to look like:
How can I do that? Your help is greatly appreciated. thanks.
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
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))
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.