Nsryjdtyk

I have 3 tables

• transaction


• product


• date

Transaction table has columns

• 
  date_id, prod_id,spend_amt, qty, transaction_fid .

Product table has

• prod_id, desc, category

and date table has

• 
  date_id, week_id .

I need to flag products active if they are sold in last 4 weeks else non-active
How to go about this.

Use an EXISTS clause to check whether a transaction in a date range exists for a product.

select

  p.*

  case when exists

  (

    select *

    from transaction t

    join date d on d.date_id = t.date_id

    where t.prod_id = p.prod_id

    and d.week_id between 201815 and 201818

  ) then 1 else 0 end as is_active

from product p;

It is strange not to see any date in your tables. In your request you are asking for the last four weeks, which is not possible without knowing a transaction's date of course. In your request comments, however, you say you want to look at the range week_id between 201815 and 201818, so I used this.

Finally I got the way to do this.

SELECT pr.prod_id, (case when count(distinct tr.transaction_fid)>=1 THEN 'Y' ELSE 'N' end) as active_flag

FROM transaction tr

INNER JOIN product pr ON pr.prod_id = tr.prod_id 

INNER JOIN

(

    SELECT date_id, week_id

    FROM date

    WHERE week_id BETWEEN 201815 AND 201818

) d ON d.date_id = tr.date_id

If you left join to the product_id's that have a transaction in the last 4 weeks then you could use a CASE for the flag.

Example:

SELECT p.*

, (CASE WHEN wk4.prod_id IS NOT NULL THEN 'Y' ELSE 'N' END) AS isActive

FROM Product p

LEFT JOIN (

  SELECT t.prod_id

  FROM Transaction t

  JOIN "date" d ON d.date_id = t.date_id

  WHERE d.week_id >= to_number(to_char(current_date-(7*4), 'YYYYWW'))

  GROUP BY t.prod_id

) wk4 ON wk4.prod_id = p.prod_id