CASE statement when criterias are in different rows and columns

user_4934150 0
Viewed 71

DB Fiddle

CREATE TABLE sales (
    campaign VARCHAR(255),
    event_type VARCHAR(255),
    event_date VARCHAR(255),
    quantity VARCHAR(255)
);

INSERT INTO sales
(campaign, event_type, event_date, quantity)
VALUES 
("C001", "buy", "2019-05-08", "500"),
("C001", "sale", "2019-04-20", "400"),

("C002", "buy", "2019-06-07", "800"),
("C002", "sale", "2019-06-15", "900"),

("C003", "buy", "2020-02-23", "700"),
("C003", "sale", "2020-03-17", "300"),

("C004", "buy", "2020-04-05", "200"),
("C004", "sale", "2020-03-17", "600");

The table displays the event_types (buy, sell) and the corresponing quantities per campaign.

Now, I want to run a query which checks the following:

a) event_date for event_type buy > event_date for event_type sell
b) quantity for event_type buy > quantity for event_type sell

The result should look like this:

campaign     Check_Date     Check_Quantity
C001            Error            OK
C002            OK              Error
C004            OK              Error

I think I need to go with the CASE statement somehow like this:

SELECT
campaign, 
(CASE WHEN event_date of buy > event_date of sale THEN "Error" ELSE "OK") AS Check_Date,
(CASE WHEN quantity of buy > quantity of sale THEN  "Error" ELSE "OK") AS Check_Quantity
quantity
FROM sales;
pivot group-by date mysql sql
4 Answers

You want to compare values across rows, so this suggests aggregation. You can then implement the logic with case expressions:

select
    campaign, 
    case when 
        max(case when event_type = 'buy' then event_date end) 
            > max(case when event_type = 'sale' then event_date end)
            then 'OK'
            else 'Error'
    end check_date,
    case when 
        max(case when event_type = 'buy' then quantity end) 
            > max(case when event_type = 'sale' then quantity end)
            then 'OK'
            else 'Error'
    end check_date
from sales
group by campaign;

In your DB Fiddle, this produces:

campaign | check_date | check_date
:------- | :--------- | :---------
C001     | OK         | OK        
C002     | Error      | Error     
C003     | Error      | OK        
C004     | OK         | Error
user_106767160

You can use sub-query & do aggregation : 

select t.campaign, 
       (case when buy_dt > sale_dt then 'OK' else 'Error' end) as Check_date,
       (case when buy_qty > sale_qty then 'OK' else 'Error' end) as Check_qty
from (select s.campaign, 
             max(case when s.event_type = 'buy' then s.event_date end) as buy_dt,
             max(case when s.event_type = 'sale' then s.event_date end) as sale_dt,
             max(case when s.event_type = 'buy' then s.quantity end) as buy_qty,
             max(case when s.event_type = 'sale' then s.quantity end) as sale_qty
      from sales s
      group by s.campaign 
     ) t;
user_55294450

Split the table in 2 queries, then join and use CASE expressions:

select
  b.campaign,
  case when b.event_date > s.event_date then 'Error' else 'OK' end Check_Date,
  case when b.quantity > s.quantity then 'Error' else 'OK' end Check_Quantity
from (select * from sales where event_type = 'buy') b
inner join (select * from sales where event_type = 'sale') s
on s.campaign = b.campaign

See the demo.
Results:

> campaign | Check_Date | Check_Quantity
> :------- | :--------- | :-------------
> C001     | Error      | Error         
> C002     | OK         | OK            
> C003     | OK         | Error         
> C004     | Error      | OK
user_104988280 
SELECT t1.campaign, 
       CASE WHEN t1.event_date > t2.event_date
            THEN 'OK'
            ELSE 'Error'
            END Check_Date,
       CASE WHEN t1.quantity > t2.quantity
            THEN 'OK'
            ELSE 'Error'
            END Check_Quantity
FROM sales t1
JOIN sales t2 USING ( campaign )
WHERE t1.event_type = 'buy'
  AND t2.event_type = 'sale';

fiddle

For the result to be meaningful you MUST add unique index by (campaign, event_type) and restrict values list safe for event_type field (make it ENUM?).

Also you have not specified what must be returned for a campaign when only one record for it exists - my query will not return such campaign at all.

user_101387340
Related