headder headder - 5 months ago 18
SQL Question

oraclesql - Search for missing salesimport data

I have a two tables (one with data about 'salesimports' and next with a data about shops).

This is an example data:

shop city importid importtype importtime committime endtime
S410 KURSK 1107597374 PSCN 2016-06-17 2016-06-17 2016-06-17
S004 KIEV 1107595750 PSCN 2016-06-17 2016-06-17 2016-06-17
S230 WARSAW 1107595594 PSCN 2016-06-17 2016-06-17 2016-06-17
S115 LVIV 1107595507 PSCN 2016-06-17 2016-06-17 2016-06-17
S220 PRAGUE 1107595458 PSCN 2016-06-17 2016-06-17 2016-06-17
S179 MINSK 1107595328 PSCN 2016-06-17 2016-06-17 2016-06-17
S247 HOMEL 1107595264 PSCN 2016-06-17 2016-06-17 2016-06-17
S202 ROSTOV 1107595114 PSCN 2016-06-17 2016-06-17 2016-06-17
S132 BERLIN 1107595112 PSCN 2016-06-17 2016-06-17 2016-06-17


The select I use to take data from both tables:

select f.shid shop, f.ctid city, i.limportref importid, i.simporttype importtype,
centrala_user.borlanddatetostr(i.itp) importtime,
centrala_user.borlanddatetostr(i.gtp) committtime,
centrala_user.borlanddatetostr(i.edp) endtime
from oracla.imports i
join oracla.firm f on i.shid = f.shid
where f.byact = 0
and f.bytype = 0
and i.simporttype not in ('PSXN')
order by i.itp desc;


My plan is to get a select, that gives me an info about shops that had no imports during last days, or whole 'salesdata' from a shop is missing in the salesimports table.

I tried few solutions with 'and not in' and 'and not exists' but it didn't give me a result I wanted.

Any ideas?

Answer

You did not show, what you have tried. Did you try something like this:

select f.*, 
      (select max(centrala_user.borlanddatetostr(i.itp)) from oracla.imports i where i.simporttype not in ('PSXN') and i.shid=f.shid) as last_import_date
  from oracla.firm f 
 where f.byact = 0 
   and f.bytype = 0
   and f.shid not in  (
      select distinct i.shid
        from oracla.imports i
       where i.simporttype not in ('PSXN')
         and centrala_user.borlanddatetostr(i.itp) > trunc(add_months(sysdate,-1))
   )   

Because is not clear, what LAST DAYS means, I have used one week ( trunc(sysdate -7 ) )