CODEWITHSUNDEEP
sqlsql-server
Jacob Cook
Jacob Cook

Reputation: 41

SQL Server 2008 R2 - How to filter a group of records with conditional logic?

I have the following dataset:

enter image description here

Each sales order line has an item which can be found in various location areas in our warehouse (UPPER, GROUND, FLOOR). What I want is a way to evaluate each sales order line and then pick one location, based on a condition.

The condition would say, if SO line contains a location with FLOOR, pick only that location, else check if it contains GROUND, then pick that, or if it contains neither ground or floor then return UPPER.

I don't want to see multiple location areas for each SO line. What's all the ways this can be done? I'd imagine some form of using a case statement with a HAVING clause?

Upvotes: 1

Views: 39

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146499

Select coalesce(f.SO, g.SO, u.SO) SO,
    coalesce(f.Line, g.Line, u.Line) Line,
    coalesce(f.item_code, g.item_code, u.item_code) item_code,
    coalesce(f.item_description, g.item_description, u.item_description) item_description,
    coalesce(f.SO_Qty, g.SO_Qty, u.SO_Qty) SO_Qty,
    coalesce(f.branch_Number, g.branch_Number, u.branch_Number) branch_Number,
    coalesce(f.location_area, g.location_area, u.location_area) location_area

from myTable f
   full join myTable g 
      on f.location_area='floor'
         and g.SO = f.So 
         and g.location_area='ground'
   full join myTable u 
      on u.SO = f.So 
         and u.location_area='upper'

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

This can be done using the row_number function by ordering the location areas based on the conditions.

select * 
from (select t.*
      ,row_number() over(partition by so# 
                         order by case when location_area='Floor' then 1
                                       when location_area='GROUND' then 2
                                  else 3 end) rn
from tablename t 
) x 
where rn = 1

Upvotes: 2

Related Questions