0

Problem : I have a table which has three columns such as item id ,transaction_date,Amount_sold.

I want to have a list of item with Amount sold which are sold today but if the same item was sold yesterday then it should not come in my list.

4
  • Do you want the sum of amounts per item or the individual rows?
    – dnoeth
    Commented Aug 21, 2016 at 21:08
  • Is transaction_date has time on it or just date?
    – vercelli
    Commented Aug 21, 2016 at 22:37
  • To answer to query it would be Sum of amount per item and transaction_date is date but still oracle takes it a datatime. Commented Aug 23, 2016 at 4:46
  • @SujitKumar I updated the answer
    – vercelli
    Commented Aug 23, 2016 at 7:23

1 Answer 1

1

Not exists will work here

 select item_id, amount_sold 
       from table t
   where transaction_date = trunc(sysdate) 
     and not exists (select 1
                       from table t1
                      where t1.item_id = t.item_id
                          and t1.transaction_date = trunc(sysdate)-1;

EDIT based on OP's latest observations :

select item_id, SUM(amount_sold) as amount
       from table t
   where trunc(transaction_date) = trunc(sysdate) 
     and not exists (select 1
                       from table t1
                      where t1.item_id = t.item_id
                          and trunc(t1.transaction_date) = trunc(sysdate)-1
 group by item_id;
4
  • Shouldn't it be trunc(sysdate) -1 in not exists ?
    – a1ex07
    Commented Aug 21, 2016 at 22:13
  • one more note - I think you need to compare trunc(transaction_date) , not transasction_date itself with trunc(sysdate) and trunc(sysdate) -1 . Or rewrite condition in both WHERE a bit : for instance, ... WHERE transaction_date >= trunc(sysdate) and transaction_date < trunc(sysdate) +1
    – a1ex07
    Commented Aug 21, 2016 at 22:29
  • @a1ex07 We don't know if transaction_date has time or just date. I'll ask the OP. I don't want to truncate the column right away if is indexed. Thanks again
    – vercelli
    Commented Aug 21, 2016 at 22:37
  • @SujitKumar please post some sample data.
    – vercelli
    Commented Aug 22, 2016 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.