I think I have a simple request but just do not seem to find the answer... Also google is not my friend at the moment.. :)

I have a list (of integer) with Items

And I have 2 tables in the DB:

Tabel Count:

CountDate,
item

I am interested in the latest date the items are counted..

Tabel pick:
PickDate , 
Item

The Dates that Items are picked. I am interested in the amount of picks after the last time the item was counted, So;

Amount of times (picks) it is picked after the latest date from table count.

I can do: step 1:

“Select ITEM, max(countdate) as countdate from Count where Item = IN (1234,234,345)”

Step2 : For each itemnumber I need to do:

“SELECT COUNT (item) as amount from PICK where item = itemnumber AND Pickdate >  ResultCountDateStep1”

I can not seem to find 1 total query (for all itemnumbers) for this to get a datateable with:

Item    LastCountDate       AmountofPicks

CodePudding user response：

Here's a solution using a CTE, which would work for many versions of different dbms' such as MySQL, Sql Server and Postgres. You may need to slightly modify if you have a different rdbms or older version. You could convert this CTE into a subquery. Basically, you can use the CTE or subquery to get the max date and then join on item.

create table count_table (
  item integer, 
  countdate date
  );
  
insert into count_table values 
(1234, '2022-01-01'), 
(1234, '2022-04-15'), 
(234, '2022-05-10'), 
(234, '2022-08-10'), 
(234, '2022-09-10'), 
(345, '2022-10-15');

create table pick_table (
  item integer, 
  pickdate date
  );
  
insert into pick_table values 
(1234, '2022-03-15'), 
(1234, '2022-03-16'), 
(1234, '2022-05-19'), 
(1234, '2022-07-15'), 
(234, '2022-03-15'), 
(234, '2022-10-15'), 
(345, '2022-10-14'); 

with max_date as (
  select item, max(countdate) as max_dt
  from count_table
  group by item
  )
select p.item, d.max_dt as last_count_date, count(p.item) as amount_of_picks
  from pick_table p 
  join max_date d 
    on p.item = d.item 
 where p.pickdate > d.max_dt 
   --and p.item in (1234, 234, 345)
group by p.item, d.max_dt;

View on DB Fiddle

UPDATE

Or if you want to show ALL items, even those with a count of 0 (ie, 0 items with a pick date after last count date), then you could use use conditional aggregation:

with max_date as (
  select item, max(countdate) as max_dt
  from count_table
  group by item
  )
select p.item, d.max_dt as last_count_date, 
   sum(case when p.pickdate > d.max_dt then 1 else 0 end) as amount_of_picks
 from pick_table p 
 left join max_date d 
   on p.item = d.item 
--where p.item in (1234, 234, 345)
group by p.item, d.max_dt;