Search code examples
sqloracle-databasereporting

How to count sent messages after specific day in SQL(Oracle)?

I received a report recently about the count number of SMS messages that sent to the user number after the day of the un-subscription from a service ,I need to check this report is true or not by using the original table,below is my tables where table a is the report and table b is the original table.

I need to count the number of messages that sent after the UN-subscription date from the service and classify it by using STATUS_CODE Delivered and OTB.Delivered messages.

to make it understandable I put the expected result for these numbers as an example (9537603785678,9537123485905), each number have only one un-subscription date.

enter image description here

Solution

  • You will need to use group by as following:

    select b.A_NUMBER, b.DEST_NUMBER, b.status_code, 
       count(distinct b.rowid) as msgs_count_after_unsub_date
From b join a
On (a.a_number = b.a_number
And a.dest_number = b.dest_number
And b.date >= a.unsubscription_date)
Group by b.A_NUMBER, b.DEST_NUMBER, b.status_code;

    OR

    select b.A_NUMBER, b.DEST_NUMBER, b.status_code, 
       count(1) as msgs_count_after_unsub_date
From b where b.date
>= (select min(a.unsubscription_date)
From a where
a.a_number = b.a_number
And a.dest_number = b.dest_number)
Group by b.A_NUMBER, b.DEST_NUMBER, b.status_code;

    Cheers!!