Page History

-- 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;

-- 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;