CODEWITHSUNDEEP
sqlsql-serverselectif-statement
user2511772
user2511772

Reputation: 19

Return one value if true, return another value if not true

I have the following example table:

ID    Type    Price    Code    Date
 1      1       .99    Null    6/1
 2      2      1.99    Null    5/1
 3      1       .99    1234    4/1
 4      3      1.99    Null    5/1
 5      2      3.99    Null    6/1
 6      1      1.30    1234    5/1
 7      1      1.64    5673    6/10

I need to select the following: Type, Price - for all types based upon the following rules:

  1. Where a code matches the request, take the most recent record.
  2. If all codes for a Type are Null, take the most recent record.

So, the result set for a request with a Code of '1234' should be:

ID:
4 (This is the most recent record for type 3)
5 (This is the most recent record for type 2)
6 (This is the most recent record for type 1 having a code = '1234')

I have created the following query:

Select distinct 
    ID, Type, Price, Code, Date 
from 
    tblPRODUCT 
where 
    Code = '1234' OR Date IN (Select MAX(Date) from tblPRODUCT Group By Type)

But this does not give me the correct results. Thoughts?

Upvotes: 1

Views: 198

Answers (3)

Joachim Isaksson
Joachim Isaksson

Reputation: 181097

Belated and similar to a deleted answer, but just to show a slightly simpler approach than the accepted answer;

WITH cte AS (
  SELECT *, 
  ROW_NUMBER() OVER(PARTITION BY Type ORDER BY Code DESC, Date DESC) rn
  FROM tblPRODUCT WHERE Code='1234' OR Code IS NULL
)
SELECT ID, Type, Price, Code, Date
FROM cte WHERE rn=1;

An SQLfiddle for testing.

Upvotes: 1

cosmos
cosmos

Reputation: 2303

Select 
    ID, Type, Price, Code, Date 
from 
    tblPRODUCT tbpr
where 
    (Code = '1234' AND Date IN (Select MAX(Date) from tblPRODUCT where type= tbpr.type   
and code = '1234'))
OR Date IN (Select MAX(Date) from tblPRODUCT where type= tbpr.type 
         and not exists(select code from tblPRODUCT where type= tbpr.type  and code is 
not null) )

How does this work :

The first part of Ored condition will select the row if the code matches. Please note that it select the row with max date if there are multiple rows to match the code for the same id.

The second Ored condition will select the row with max date if all the codes are null.

I tested it, it works PERFECT with your sampled data and will work for any combination of data you try.

SQLFIDDLE:

http://www.sqlfiddle.com/#!3/19b03/18

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271231

This seems rather complicated after a moment of thought. You don't have records with all the types on a given code, so you have to generate them somehow. This suggests the use of a driver table.

The precedence rules then make this a bit complicated:

select coalesce(p.id, driver.id) as id,
       coalesce(p.[type], driver.[type]) as [type],
       coalesce(p.price, driver.price) as price,
       coalesce(p.code, driver.code) as code,
       coalesce(p.[date], driver.[date]) as [date]
from (select p.*
      from (select p.*, row_number() over (partition by [type]
                                           order by (case when [type] is NULL then 1 else 0 end) desc,
                                                    [date]
                                          ) as seqnum
            from tblProduct p
           ) p
       where seqnum = 1
     ) driver left outer join
     tblProduct p
     on p.code = XXX and
        p.type = driver.[type] and
        driver.type is NULL

A similar approach is to use union all to get the values:

select ID, Type, Price, Code, Date
from tblProduct p
where date = (select max(date) from tblProduct p2 where p2.code = p.code and p2.type = p.type) and
      code = XXX
union all
select id, type, price, code, Date
from tblProduct p
where code is null and
      date = (select max(date) from tblProduct p2 where p2.code = p.code and p2.type is null) and
      type not in (select type from tblProduct p2 where p2.code = p.code)

I realize the SQL code looks very different. But the similar idea is that you have to fetch the values for the NULL codes separately from the codes you are looking for. The first uses a driver table and then does the logic on the select. The second does a union all, separating the two sets into two different troups.

Upvotes: 0

Related Questions