SMS statistics for internal use
For internal use it would be interesting to display (for authenticated user) statistics about sending sms. It may serve also for accounting department to check if invoice from the supplier is correct. For this it is important to keep Europe/Prague time zone so it match the monthly statistics provided by supplier.
The goal is to display counts of sent SMSs (daily, weekly, monthly, yearly) for 4 categories (Czech mobile, Czech fix line, Slovak number and Other-foreginers).
SQL into FRED for particular day could be as follows:
SELECT
CASE
WHEN REPLACE(sa.phone_number,'.','') ~ '^\+?420[67].*' THEN '1-CZ'
WHEN REPLACE(sa.phone_number,'.','') ~ '^\+?420[01234589].*' THEN '2-CZ-FIX'
WHEN REPLACE(sa.phone_number,'.','') ~ '^\+?421.*' THEN '3-SK'
ELSE '4-OTHER-FOREIGN'
END AS type,
COUNT(*)
FROM
message_archive ma
JOIN sms_archive sa ON (sa.id=ma.id)
WHERE
DATE_TRUNC('day',moddate::timestamptz AT TIME ZONE 'Europe/Prague') = '2021-09-01'
AND status_id = 5
GROUP BY type;