CODEWITHSUNDEEP
sqlsql-server
Piraba
Piraba

Reputation: 7004

How to write query for this situation to get the max date?

I have issues in query:

 priceDeatil 
 ProductCode  BusinessUnit  price   DateFrom     DateTo
  10001         ORB          12.00   12-08-2011   31-09-2015
  10001         ORB          21.00   01.08-2011   15-11-2011
  10002         ORB          31.00   01.04-2011   15-08-2012
  10003         ORB          42.00   01.05-2011   15-08-2012

My query is:

 SELECT     BusinessUnit, ProductCode,  DateFrom, DateTo, Price
 FROM         WMPriceDetail
 WHERE     (DateFrom < 'Sep 01 2011') AND (DateTo > 'Sep 01 2011' OR DateTo = '')
 ORDER BY ProductCode

This return 

   priceDeatil 
 ProductCode  BusinessUnit  price   DateFrom     DateTo
  10001         ORB          12.00   12-08-2011   31-09-2015
  10001         ORB          21.00   01.08-2011   15-11-2011
  10002         ORB          31.00   01.04-2011   15-08-2012
  10003         ORB          42.00   01.05-2011   15-08-2012

But Here productCode 10001 return two records; That time I want to get the Max Date i.e 12-08-2011 .

So desired result should be like this: 

   priceDeatil 
 ProductCode  BusinessUnit  price   DateFrom     DateTo
  10001         ORB          12.00   12-08-2011   31-09-2015
  10002         ORB          31.00   01.04-2011   15-08-2012
  10003         ORB          42.00   01.05-2011   15-08-2012

How to write query for this situation?

Upvotes: 4

Views: 94

Answers (2)

user868322
user868322

Reputation: 5

Use this query:

select BusinessUnit, ProductCode,  DateFrom, DateTo, Price from WMPriceDetail 
where DateFrom  in (select MAX(datefrom) from WMPriceDetail group by ProductCode)

Upvotes: 0

rkg
rkg

Reputation: 5719

 SELECT     BusinessUnit, ProductCode,  DateFrom, DateTo, Price FROM
 (SELECT    BusinessUnit, ProductCode,  DateFrom, DateTo, Price, 
 rank() over (PARTITION BY ProductCode ORDER BY DateFrom DESC) rank_num
 FROM  WMPriceDetail
 WHERE (DateFrom < 'Sep 01 2011') AND (DateTo > 'Sep 01 2011' OR DateTo = '') t 
 WHERE rank_num=1
 ORDER BY ProductCode

Upvotes: 3

Related Questions