CODEWITHSUNDEEP
sqlcasepartitioningwhere-in
labrynth
labrynth

Reputation: 155

Use CASE statements with PARTITION in sql

Given the following table_a: 

 id  | status | reg_date | so_date
 ----+------------------------------
  1  |   abc  | 15-11-01 | 15-11-02 
  1  |   def  | 15-11-01 | 15-11-04
  1  |   abc  | 15-11-01 | 15-11-06
  2  |   abc  | 15-11-01 | 15-11-03
  2  |   abc  | 15-11-01 | 15-11-04
  2  |   abc  | 15-11-01 | 15-11-06
  2  |   abc  | 15-11-01 | 15-11-08
  3  |   xyz  | 15-11-01 | 15-11-03
  3  |   def  | 15-11-01 | 15-11-08
  3  |   def  | 15-11-01 | 15-11-09

The following is required: For every group of id, the minimum difference between so_date and reg_date, if the status is "abc". This yields the following table:

 id  | min_date  
 ----+----------
  1  |    1   
  2  |    2   
  3  |   Null

The following code would suffice:

select 
    id,
    distinct datediff('day', reg_date, min(so_date) over (partition by id)) as min_date
from table_a  
where status ilike '%abc%'

However, due to constraints of other "selections", the use of the filter where status ilike '%abc%' is not feasible at the base table. I tried using 

 select 
        id,
        case when status is like '%abc%' then (distinct datediff('day', reg_date, min(so_date) over (partition by id))) end as min_date
    from table_a

But this did not work. Any insights/suggestions will be appreciated. Thanks

Upvotes: 0

Views: 882

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

Based on your sample should be

select 
    id
  , min (datediff( so_date, reg_date )) as min_date
from table_a 
where status ='abc'
group by id

if you don't want where bat case then 

select 
   case status when 'abc' then  id end
  , case status when 'abc' then min (datediff( so_date, reg_date ))  end as min_date
from table_a 
group by id

Upvotes: 1

Related Questions