ORACLE Query to check if one field has changed to another specific-CodePudding

We recently noticed that some workes have the maximum rate (13) when they barely have any bought items, so we need a query that checks if any worker rate changes to the maximum so we can check if its legit.

So I have these two tables:

Table1-> Workers (DNI,RATE,TICKET,PRICE,...)

Table2-> Sells (DNI,NAME,DATE)

edit: DATE and RATE are both Strings, Date follows this structure: YYYYMMDD

Sells table have a special discount depending on the rate of the client.

I want to check if any of those wokers rate changes from something that is not 13 --> to 13

The only way this can happen if is the worker buys a lot, or if someone changes it manually so they get a better discount (NOT ALLOWED).

So, I want to check if yesterday any worker had a 6 rate and today its 13.

SELECT RATE,DNI FROM SELLS WHERE RATE='13' AND DNI IN 
(
SELECT DNI FROM WORKERS
)
AND DATE = to_char(sysdate-1, 'yyyymmdd')

Is there any way to UNION those so the matched remain?

SELECT RATE,DNI FROM SELLS WHERE RATE <> '13' AND DNI IN 
(
SELECT DNI FROM WORKERS
)
AND DATE to_char(sysdate, 'yyyymmdd')

Or is there any better way?

CodePudding user response：

You can join both queries to compare. In this case I would think that an full outer join is more appropriate. For example:

select
  coalesce(t.dni, y.dni) as dni,
  t.rate as today_rate,
  y.rate as yesterday_rate
from (
  SELECT RATE, DNI FROM SELLS 
  WHERE RATE='13' 
    AND DNI IN (SELECT DNI FROM WORKERS)
    AND DATE = to_char(sysdate-1, 'yyyymmdd')
) y
full join (
  SELECT RATE, DNI FROM SELLS 
  WHERE RATE <> '13' 
    AND DNI IN (SELECT DNI FROM WORKERS)
    AND DATE to_char(sysdate, 'yyyymmdd')
) t on t.dni = y.dni

Also, the following query can compare all dates, not just today and yesterday:

select *
from (
  select
    dni, date, rate,
    lag(date) over(partition by dni order by date) as prev_date,
    lag(rate) over(partition by dni order by date) as prev_rate
  from workers
) x
where prev_rate <> '13' and rate = '13'