SELECT R1.SUBMISSION_TIME,
COUNT (SM_ID),
COUNT ((case when SM_STATUS= 1 then 1 else 0 end))
FROM VAS.SMS_DATA_SMC R1
WHERE SUBMISSION_TIME >= TO_DATE ('16/12/2018 00:00:00', 'dd/mm/yyyy HH24:MI:SS')
AND SUBMISSION_TIME <= TO_DATE ('16/12/2018 23:59:59', 'dd/mm/yyyy HH24:MI:SS')
AND MT_MSC_ADDR IS NOT NULL
AND MT_MSC_ADDR NOT LIKE '569%'
AND MT_MSC_ADDR <> '0'
AND FINAL_TIME IS NOT NULL