CODEWITHSUNDEEP
oracle-database
Erik
Erik

Reputation: 17

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

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?

Edit of what tried:

Real query:

select
  coalesce(t.dni, y.dni) as dni,
  t.Tarifa as today_rate,
  y.Tarifa as yesterday_rate
from (
  SELECT Tarifa, DNI FROM pws_ventas_materiales,pws_ventas_cabecera
  WHERE Tarifa='13' 
    AND DNI IN (SELECT DNI FROM trabajadores_sirgo)
    AND pws_ventas_materiales.fecha = to_char(sysdate-1, 'yyyymmdd')
    and pws_ventas_materiales.ticket = pws_ventas_cabecera.ticket
    and pws_ventas_cabecera.fecha = pws_ventas_materiales.fecha
) y
full join (
  SELECT Tarifa, DNI FROM pws_ventas_materiales ,pws_ventas_cabecera
  WHERE Tarifa <> '13' and Tarifa is not null
    AND DNI IN (SELECT DNI FROM trabajadores_sirgo)
    AND pws_ventas_materiales.fecha = to_char(sysdate, 'yyyymmdd')
    and pws_ventas_materiales.ticket = pws_ventas_cabecera.ticket
    and pws_ventas_cabecera.fecha = pws_ventas_materiales.fecha
) t on t.dni = y.dni
;

Table Updates:

pws_ventas_materiales=Sells

trabajadores_sirgo=Workers

pws_ventas_cabecera --> There was a mistake on the Sells table, that table does not contain a DNI column, this table does.

Sorry for that mistake

Also, rate=Tarifa and date=fecha + I added a pws_ventas_materiales.ticket = pws_ventas_cabecera.ticket to make sure that we are talking about the same sell between those two tables. enter image description here

Upvotes: 0

Views: 59

Answers (1)

The Impaler
The Impaler

Reputation: 48865

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
-- WHERE today_rate is not null -- extra filtering here

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'

Upvotes: 2

Related Questions