4 Replies Latest reply on Jul 26, 2018 9:05 AM by Saket Shelar

    Counting users who did not get verified

    Saket Shelar

      Here's my case:


      I have the following table structure :


      Created AtIdMessageNumberSms Message Code IdType
      7/25/2018 1:32:13 AM1xxxxxxx2Msg19xxxxxxxx695SmsReceived
      7/25/2018 12:46:18 AM2xxxxxxx3Msg19xxxxxxxx195SmsReceived
      7/25/2018 1:35:25 AM3xxxxxxx4Msg19xxxxxxxx995SmsReceived
      7/25/2018 11:11:42 AM4xxxxxxx5Msg19xxxxxxxx295SmsReceived
      7/25/2018 8:04:26 AM5xxxxxxx6Msg19xxxxxxxx395SmsReceived
      7/25/2018 5:23:38 AM6xxxxxxx7Msg19xxxxxxx4195SmsReceived
      7/25/2018 2:49:31 AM7xxxxxxx8Msg19xxxxxxxx495SmsReceived
      7/25/2018 1:33:14 AM8xxxxxxx9Your phone number has been verified9xxxxxxxx695SmsSent
      7/25/2018 12:48:05 AM9xxxxxxx1Your phone number has been verified9xxxxxxxx195SmsSent
      7/25/2018 1:36:48 AM4xxxxxxx2Your phone number has been verified9xxxxxxxx995SmsSent
      7/25/2018 11:15:02 AM5xxxxxxx3Your phone number has been verified9xxxxxxxx295SmsSent
      7/25/2018 8:05:36 AM6xxxxxxx4Your phone number has been verified9xxxxxxxx395SmsSent
      7/25/2018 5:25:39 AM7xxxxxxx5Your phone number has been verified9xxxxxxx4195SmsSent

      I have a process where my system sends out a message to the user to verify his number.
      The user then, if sends the message in reply, they get a message back with a successful verification status.
      I'm looking to count the records where the user sent the message to the system but did not get the successful verification status message.
      NOTE: Type column statuses are wrt system and not user.
      So SmsSent means sent by the system
      Any help is appreciated.



      Thanks in Advance!