interactionC_Group_Call.sql

SELECT
datetime(ZINTERACTIONS.ZSTARTDATE + 978307200, 'UNIXEPOCH') AS "Call Date/Time",
datetime(ZINTERACTIONS.ZENDDATE + 978307200, 'UNIXEPOCH') AS "Call End Date/Time",
ZBUNDLEID AS "Service Provider",
CASE
	WHEN ZDIRECTION IS 1 THEN "Outgoing"
	WHEN ZDIRECTION IS 0 THEN "Incoming"
END AS "Call Direction",
GROUP_CONCAT(ZDISPLAYNAME, ', ') AS "Display Name",
GROUP_CONCAT(ZIDENTIFIER, ', ') AS "Phone Number",
CASE
	WHEN ZRECIPIENTCOUNT IS 1 THEN "Direct Call"
	WHEN ZRECIPIENTCOUNT > 1 THEN 'Group Call - ' || COUNT(ZRECIPIENTCOUNT) || ' other parties'
	ELSE ZRECIPIENTCOUNT
END AS "Call Description",
strftime('%H:%M:%S',(ZENDDATE-ZSTARTDATE), 'unixepoch')  AS "Interaction Duration"
FROM ZINTERACTIONS
LEFT OUTER JOIN Z_2INTERACTIONRECIPIENT on Z_2INTERACTIONRECIPIENT.Z_3INTERACTIONRECIPIENT is ZINTERACTIONS.Z_PK
LEFT OUTER JOIN ZCONTACTS on Z_2INTERACTIONRECIPIENT.Z_2RECIPIENTS is ZCONTACTS.Z_PK
WHERE ZBUNDLEID like "com.apple.InCallService" or ZBUNDLEID like "com.apple.facetime" or ZBUNDLEID like "com.apple.mobilephone"
GROUP BY Z_3INTERACTIONRECIPIENT