CODEWITHSUNDEEP
sqlsql-server-2008
Shreyas Achar
Shreyas Achar

Reputation: 1435

Select query to display a row based on getting maximum date

I have this below query

 Select  distinct    
     a.Item_Name, b.Item_Rate, b.professional_Charge,
     c.discount, c.discount_Type,
     a.Dept_ID, c.Discount_Applicability, 
     c.Effective_Date, b.Effective_Date
 From 
     Item_M a
 left outer join 
     Rate_M b on a.Item_Code = b.Bill_Item and b.Rate_Cat_ID = 'GEN'
 left outer join 
     Discount_M c on a.Item_Code = c.Item_Code and c. Pay_Type_Id='CS'
 Where 
     a.Item_code = 'ABS002' 
     and c.Effective_Date <= GETDATE()
     and b.Effective_Date <= GETDATE()
 group by  
     a.Item_Name, b.Item_Rate, b.professional_Charge,
     c.discount, c.discount_Type,
     a.Dept_ID, c.Discount_Applicability, c.Effective_Date, b.Effective_Date
 order by 
     c.Effective_Date, b.Effective_Date desc

The output is as shown below:

 Item_Name  Item_Rate   professional_Charge discount    discount_Type   Dept_ID Discount_Applicability  Effective_Date  Effective_Date
 ----------------------------------------------------------------------------------------------------------------------------------------------------------
 ABSESS I & D 75          NULL                 0           P             CBN     I                   2014-04-06 12:34:36.530    2014-04-09 15:15:56.367
 ABSESS I & D 440         NULL                 0           P             CBN    I                    2014-04-06 12:34:36.530    2014-04-07 15:15:56.367
 ABSESS I & D 75          NULL                 0           P             CBN    I                    2014-04-09 16:36:05.790    2014-04-09 15:15:56.367
 ABSESS I & D 440         NULL                 0           P             CBN    I                    2014-04-09 16:36:05.790    2014-04-07 15:15:56.367

Now the expected output what I need is to get one row where both effective date is maximum, i.e in this above output I need to display the 3rd row in the output. Any help appreciated.

Upvotes: 0

Views: 50

Answers (1)

cha
cha

Reputation: 10411

As you are working with SQL Server 2008 it is time for you to learn window functions. The one that I would use in your situation is a ROW_NUMBER() (although RANK can be used as well). You should also learn how to use CTE (common table expressions). They are real life savers in the modern SQL. If you apply this knowledge to your problem, the query will look as simple as this one:

; with b AS(
SELECT Item_Rate, Bill_Item, 
professional_Charge, 
Effective_Date, 
ROW_NUMBER() OVER (PARTITION BY Bill_Item ORDER BY Effective_Date DESC) rn
FROM Rate_M WHERE Rate_Cat_ID = 'GEN' and Effective_Date <= GETDATE()),
c AS 
(SELECT discount, Item_Code,
discount_Type,
Discount_Applicability, 
Effective_Date,
ROW_NUMBER() OVER (PARTITION BY Item_Code ORDER BY Effective_Date DESC) rn
FROM Discount_M WHERE Pay_Type_Id='CS' and Effective_Date <= GETDATE())
 Select  
     a.Item_Name, b.Item_Rate, b.professional_Charge,
     c.discount, c.discount_Type,
     a.Dept_ID, c.Discount_Applicability, 
     c.Effective_Date, b.Effective_Date
 From 
     Item_M a
 left outer join 
     b on a.Item_Code = b.Bill_Item and b.rn = 1
 left outer join 
     c on a.Item_Code = c.Item_Code and c.rn = 1
 Where 
     a.Item_code = 'ABS002' 
 order by 
     c.Effective_Date, b.Effective_Date desc

Upvotes: 1

Related Questions