SQLTeam.com | Weblogs | Forums

Show the minimum date


#1

Hi,

declare @TableVar table ( NewPK int identity(1,1)
, ItemID Int NOT NULL
,Descp varchar(30)
 )

 Insert Into @TableVar values ( 1831,'door')
  Insert Into @TableVar values ( 1832,'fan')
 
  
declare @TableVar2 table ( NewPK2 int identity(1,1)
, ItemID Int NOT NULL
,ExpDate DateTime )
 Insert Into @TableVar2 values ( 1831,'2015-Jun-17')
  Insert Into @TableVar2 values ( 1831,'2015-Dec-30')

The first table contain the item related information and second table contains
its available expected date. Is it possible to display the item information
using the minimum value of ExpDate like this.

ItemID  Description  ExpectedDate
1831	  door	    2015-06-17 
1832	  fan	        NULL

#2

SELECT T1.ItemID,
T1.Descp,
T2.ExpDate
FROM @TableVar T1
LEFT OUTER JOIN(
SELECT ItemID,
MIN( ExpDate ) ExpDate
FROM @TableVar2
GROUP BY ItemID ) T2 ON T1.ItemID = T2.ItemID;


#3

SELECT T1.ItemID,
T1.Descp,
MIN( ExpDate ) ExpDate
FROM @TableVar T1
left outer JOIN @TableVar2 T2
ON T1.ItemID = T2.ItemID
GROUP BY T1.ItemID , T1.Descp