CODEWITHSUNDEEP
sqlsql-servert-sql
Thomas Pollack
Thomas Pollack

Reputation: 59

SELECT records based on criteria

I'm looking to find a way to select an ID (PersonID) based on criteria. Should this personID not meet the criteria, I need to select another one. To make it visual I have two tables: 1.

ID     Company  LatestOrder
50593   TEST    1
10955   TEST    2

2.

  ID    Criteria_A  Criteria_B   
 50593    1          0 
 10955    0          0

I need to find a way to SELECT ID from ``table1 withlatestOrder = 1 Check whether this person meets criteria from table 2 (value 1 for any criteria A or B), then if Yes, select ID from table1 with latestOrder = 2.

And check the criteria as long as table1.lastorder > 0.

Does it makes sense? I've been bogged with CASE's and IIF's, CTA's but I just cannot seem to find a way to work. I'd love someone to shed some light, in which function direction should I go.

Big thanks for any help guys! This is the 3rd version :(

HERE is where I got so far:

WITH LastOrdered AS  
(
            SELECT 
                OrderBillToID AS PersonID
            ,   OrderDate
            ,   OrderBillToCompanyID AS CompanyID
            ,   OrderBillToCompany
            ,   ROW_NUMBER() OVER(PARTITION BY ODE.OrderBilltoCompanyID ORDER BY ODE.OrderDate DESC) AS LatestOrder
            FROM dbo.OrderDetails ODE
            INNER JOIN dbo.Persons PER ON ODE.OrderBillToID = PER.ID AND PER.Segment = 'B' AND ODE.OrderBillToCompanyID IS NOT NULL

)

Second try 

SELECT
 PER.ID
 ,  PER.Company
 ,  LatestOrder
 ,  CASE WHEN PER.ExcludeAll = 0 AND (PER.Age > 17 OR PER.Age = 0) AND PER.DateOfDeath = '1900-01-01' AND PER.TLC = 'NA' AND PER.BulkAskExclude = 0 AND PER.EmailExclude = 0 THEN LastOrder
 ....here is when I realised that this case won't work.....
FROM dbo.Persons PER
  INNER JOIN LastOrdered LO ON LO.PersonID = PER.ID

EXPECTED output would be: 

ID     Company  
10955   TEST

......

Upvotes: 0

Views: 147

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Basically, you want the last person who ordered but not does not satisfy any of the criteria in Table2:

SELECT TOP 1
    id, Company
FROM Table1
WHERE
    id NOT IN(
        SELECT id
        FROM Table2
        WHERE 
            Criteria_A = 1
            OR Criteria_B = 1
    )
ORDER BY LatestOrder DESC

Upvotes: 2

Related Questions