SMS Messaging

-- How many users are using SMS?

SELECT COUNT( distinct T.userId )
FROM (SELECT
        smf_sendingUserId AS userId
    FROM
        [dbo].[SMSMessageFact] WITH( NOLOCK )
    WHERE
        smf_messageDirection = 'OUTBOUND' AND smf_sendingUserId is not NULL
    UNION
    SELECT
        smf_receivingUserId  AS userId
    FROM
        [dbo].[SMSMessageFact] WITH( NOLOCK )
    WHERE
        smf_messageDirection = 'INBOUND' AND smf_receivingUserId is not NULL) AS T

-- List of users that are using SMS

SELECT distinct T.userId
FROM (SELECT
        smf_sendingUserId AS userId
    FROM
        [dbo].[SMSMessageFact] WITH( NOLOCK )
    WHERE
        smf_messageDirection = 'OUTBOUND' AND smf_sendingUserId is not NULL
    UNION
    SELECT
        smf_receivingUserId  AS userId
    FROM
        [dbo].[SMSMessageFact] WITH( NOLOCK )
    WHERE
        smf_messageDirection = 'INBOUND' AND smf_receivingUserId is not NULL) AS T
ORDER BY userId

-- Usage report
-- date sent, direction, SMS number, SMS UserId, other party

SELECT
	CONVERT(varchar, smf_originationTimestamp, 120) AS [Date Sent], smf_messageDirection AS [Direction],
	snd_number AS [SMS Number],
	CASE WHEN smf_messageDirection = 'INBOUND' THEN smf_receivingUserId ELSE smf_sendingUserId END AS [SMS UserId],
	CASE WHEN smf_messageDirection = 'INBOUND' THEN smf_fromContactNumber ELSE smf_toContactNumber END AS [Other Party]
FROM
	[dbo].[SMSMessageFact] LEFT OUTER JOIN [dbo].[SMSNumberDimension] WITH( NOLOCK ) ON CASE WHEN smf_messageDirection = 'INBOUND' THEN smf_toNumberFk ELSE smf_fromNumberFk END = snd_id
WHERE
	smf_originationDateLocal >= 20210101 AND smf_originationDateLocal < 20220101
ORDER BY smf_originationTimestamp;

-- How many unique contacts (external phone numbers) have been texted?
SELECT COUNT(DISTINCT contact) AS [Contact Count]
    FROM (SELECT
        smf_toNumber AS contact
    FROM
        [dbo].[SMSMessageFact] WITH( NOLOCK )
    WHERE
        smf_messageDirection = 'OUTBOUND' AND smf_sendingUserId is not NULL
    UNION
    SELECT
        smf_fromNumber AS contact
    FROM
        [dbo].[SMSMessageFact] WITH( NOLOCK )
    WHERE
        smf_messageDirection = 'INBOUND' AND smf_receivingUserId is not NULL) AS T;

-- List of unique contacts that have been texted
SELECT DISTINCT contact AS [Contacts]
    FROM (SELECT
        smf_toNumber AS contact
    FROM
        [dbo].[SMSMessageFact] WITH( NOLOCK )
    WHERE
        smf_messageDirection = 'OUTBOUND' AND smf_sendingUserId is not NULL
    UNION
    SELECT
        smf_fromNumber AS contact
    FROM
        [dbo].[SMSMessageFact] WITH( NOLOCK )
    WHERE
        smf_messageDirection = 'INBOUND' AND smf_receivingUserId is not NULL) AS T
ORDER BY contact;

-- Stop request counts by SMS number
SELECT
    ssrf_smsNumber AS [SMS Number], COUNT(*) [Total Request Count],
    SUM( CASE WHEN ssrf_restartTimestamp IS NULL OR ssrf_restartTimestamp > GETDATE() OR ssrf_restartTimestamp >= ssrf_stopTimestamp THEN 1 ELSE 0 END ) AS [Active Request Count],
    SUM( CASE WHEN ssrf_restartTimestamp IS NULL OR ssrf_restartTimestamp > GETDATE() OR ssrf_restartTimestamp >= ssrf_stopTimestamp THEN 0 ELSE 1 END ) AS [Inactive Request Count]
FROM
    [dbo].[SMSStopRequestFact]
WHERE
    ssrf_stopDateLocal >= 20210101 AND ssrf_stopDateLocal < 20220101
GROUP BY
    ssrf_smsNumber
ORDER BY ssrf_smsNumber;

-- Usage report
-- SMS number, SMS UserId, requesting number, stop request date, start request date, is an active request
SELECT
    ssrf_smsNumber AS [SMS Number],
    ssrf_userId AS [SMS UserId],
    ssrf_fromNumber AS [Requesting Number],
    CONVERT(varchar, ssrf_stopTimestamp, 120) AS [Stop Request Date],
    CASE WHEN ssrf_restartTimestamp IS NOT NULL THEN CONVERT(varchar, ssrf_restartTimestamp, 120) ELSE 'N/A' END AS [Start Request Date],
    CASE WHEN (ssrf_restartTimestamp IS NULL) OR ssrf_restartTimestamp > GETDATE() OR ssrf_restartTimestamp >= ssrf_stopTimestamp THEN 'true' ELSE 'false' END AS [Active Request]
FROM
    [dbo].[SMSStopRequestFact]
WHERE
    ssrf_stopDateLocal >= 20210101 AND ssrf_stopDateLocal < 20220101
ORDER BY ssrf_stopDateLocal;