3 Replies Latest reply on May 27, 2019 6:20 AM by Gaz in Oz

    query count getting mismatch

    Ricky007

      Hi experts,

       

       

      I have 2 table

       

       

      1.registration --- stores all registered customer

      2.deregistration --stores all deregistered customer,data will be deleted from registration table and moved to deregistration table

       

      now,i am unable to match registered and deregistered customer on my below case

       

       

      assume

      1. <=25-05-19 so far registered

       

       

      select count(1) from registration

      where reg_date<=to_timestamp('25-05-19 23:59:59.999999999','DD-MM-YY HH24:MI:SSXFF'); ==> Total count 600

       

       

       

       

      2.registered on 26-05-19

       

       

      select count(1) from registration

      where reg_date between to_timestamp('26-05-19 00:00:00.999999999','DD-MM-YY HH24:MI:SSXFF') and

      to_timestamp('26-05-19 23:59:59.999999999','DD-MM-YY HH24:MI:SSXFF') ==> Total count 100

       

       

      3.deregistered on 26-05-19

       

       

      select count(1) from deregistration

      where dereg_date between to_timestamp('26-05-19 00:00:00.999999999','DD-MM-YY HH24:MI:SSXFF') and

      to_timestamp('26-05-19 23:59:59.999999999','DD-MM-YY HH24:MI:SSXFF') ==> Total count 50

       

       

       

       

      (total registered till 25-05-19 + total registered on 26-05-19) - total deregistered on

       

       

      (600+100)-50= 650

       

       

      i am getting 650 as registered ,but i should be 700 by deducting 50 deregistered user

       

      i want to match count of  deregistered and registered and total count should match in registration table.

       

      please help experts