Luis Lara Luis Lara - 1 month ago 6
SQL Question

SELECT MAX DATE FROM MULTIPLE DATES

this is my case:

I have 2 tables

SKU
&
Disco_Dates
.

In both tables there is a similar field.

SKU.[MFG_PART_NUMBER] = Disco_Dates.[MATERIAL]


I need to know the max DATE_UPDATE available for each MFG_PART_NUMBER

DISCO_DATE TABLE

+-------------+-----------------------+-----------------------+
| MATERIAL |DISCO_DATE | DATE_UPDATE |
+-------------+-----------------------+-----------------------+
|T6C25AW#ABC |NULL |2016-09-14 11:15:03.587|
|T6C25AW#ABC |2016-10-28 00:00:00.000|2016-09-21 13:45:03.591|
|T6C25AW#ABC |2016-10-31 00:00:00.000|2016-09-30 12:38:08.990|
+-------------+-----------------------+-----------------------+

SKU TABLE

+---------------+
|MFG_PART_NUMBER|
+---------------+
|T6C25AW#ABC |
|G2F32UC#ABC |
+---------------+


SQL QUERY

SELECT
t1.[MFG_PART_NUMBER]
,t2.[DISCO_DATE]
,MAX (t2.[DATE_UPDATE]) as DATE_UPDATE

FROM Test.dbo.SKU t1

LEFT JOIN Test.dbo.Tbl_Disco_Dates t2
ON [MFG_PART_NUMBER] = [MATERIAL]

WHERE t1.[MFG_PART_NUMBER] = 'T6C25AW#ABC'

group by
t1.[MFG_PART_NUMBER]
,t2.[DISCO_DATE]


My current result is giving me all 3 rows.

This is what I need.

+----------------+-----------------------+-----------------------+
|MFG_PART_NUMBER |DISCO_DATE | DATE_UPDATE |
+----------------+-----------------------+-----------------------+
|T6C25AW#ABC |2016-10-31 00:00:00.000|2016-09-30 12:38:08.990|
+----------------+-----------------------+-----------------------+


Thanks in advance.

Answer

Try this:

SELECT
 t1.[MFG_PART_NUMBER]
,t2.[DISCO_DATE]
,t2.[DATE_UPDATE] as DATE_UPDATE

FROM Test.dbo.SKU t1

LEFT JOIN Test.dbo.Tbl_Disco_Dates t2
ON [MFG_PART_NUMBER] = [MATERIAL]

WHERE t1.[MFG_PART_NUMBER] = 'T6C25AW#ABC'
      AND t2.[DATE_UPDATE] = (SELECT MAX(t2.[DATE_UPDATE] FROM Test.dbo.Tbl_Disco_Dates t2)

group by 
  t1.[MFG_PART_NUMBER]
 ,t2.[DISCO_DATE]
 ,t2.[DATE_UPDATE]