SQL subquery with aggregate value

Sorry for the vague title, I’m a little lost here. I have two simple aggregate SQL queries, but I struggle to combine them in a functional way (likely through a subquery) in IBM DB2. Main goal is to run COUNT function in INVOICES table – but with the starting date based on a subquery MAX aggregate result.

select count(a.invno), a.item from INVOICES a
where a.invdate > 20200101
group by a.item

But as noted, I don’t want a.invdate to be fixed (20200101). I’d need to run MAX function in SERVICE table and use its result as the paramenter for a.invdate. Is there a reasonable way to do this?

select max(b.servdate), b.item from SERVICE b
where b.servtype = 1
group by b.item

So, looking to count invoices for each Item since the last service date (matching it’s own parameters). Obviously, a.item = b.item here.

Answer

If I followed you correctly, you can join, or use a correlated subquery:

select count(i.invno), i.item 
from invoices i
where i.invdate > (
    select max(s.servdate) 
    from services s 
    where s.servtype = 1 and s.item = i.item
)
group by i.item

This should be an efficient approach, provided that you have an index on services(item, servtype).

Alternatively, here is the join approach:

select count(i.invno), i.item 
from invoices i
inner join (
    select item, max(servdate) servdate
    from services
    where servtype = 1
    group by item
) s on s.item = i.item and s.invdate > s.servdate
group by i.item