SELECT dept+itemcode as Itemcode,max(LRD) LRD FROM [bjreport].[dbo].DailySales201412 where storename in ('081') and salesdate = convert(varchar(10),getdate()-1,1) and dept+itemcode in ('10031387','10311005','10330005','10400980','10420102','10421011') group by dept+itemcode
FROM [bjreport].[dbo].DailySales201412
where storename in ('081')
and salesdate = convert(varchar(10),getdate()-1,1)
and dept+itemcode in ('10031387','10311005','10330005','10400980','10420102','10421011')
group by dept+itemcode
max(LRD) as LRD
thanks very much