Campaign Connected Call Details Gold Query

-- for mysql

    "campaign_connected_call_details_gold":"""
    INSERT INTO campaign_connected_call_details_gold (
    `Campaign_Name`, 
    DateTime,  
    `Number_Called`, 
    `Call_Duration`,
    agent_name,
    `Agent_ID`,
    Team_Name,
    `Agent_wrap_ups`
)
SELECT 
    c.title AS `Campaign_Name`, 
    va.start_time AS DateTime,  
    va.channel_customer_identifier AS `Number_Called`, 
    va.total_duration AS `Call_Duration`,
    va.sender_name AS agent_name,
    va.sender_id AS `Agent_ID`,
    t.team_name AS Team_Name,
    GROUP_CONCAT(DISTINCT wrapup_label ORDER BY wrapup_label SEPARATOR ', ') AS `Agent_wrap_ups`
FROM campaign_scheduler cs  
INNER JOIN campaigns c  
    ON JSON_UNQUOTE(JSON_EXTRACT(cs.customer, '$.campaign')) = c.id 
INNER JOIN (
    SELECT 
        va.activity_timestamp,
        va.start_time,
        call_leg_start_time AS min_start_time, 
        va.channel_session_id,
        va.conversation_id, 
        va.channel_customer_identifier, 
        vca.reason_code,
        va.sender_type,
        va.total_duration, 
        va.sender_name,
        va.sender_id  
    FROM voice_activities va  
    INNER JOIN voice_connector_activities vca  
        ON va.channel_session_id = vca.message_id 
        AND CAST(va.activity_timestamp AS DATE) = CAST(vca.activity_timestamp AS DATE)
    WHERE vca.reason_code = 'NORMAL_CLEARING'
      AND va.start_time BETWEEN '{start_time}' AND '{end_time}'
    GROUP BY 
        va.activity_timestamp,
        va.start_time,
        call_leg_start_time, 
        va.channel_session_id,
        va.conversation_id, 
        va.channel_customer_identifier, 
        vca.reason_code,
        va.sender_type,
        va.total_duration,
        va.sender_name,
        va.sender_id 
) AS va 
    ON cs.activity_data_id = va.channel_session_id 
    AND CAST(cs.activity_timestamp AS DATE) = CAST(va.activity_timestamp AS DATE)
INNER JOIN (
    SELECT 
        conversation_id, 
        MIN(call_leg_start_time) AS min_start_time 
    FROM voice_activities 
    WHERE start_time BETWEEN '{start_time}' AND '{end_time}' 
    GROUP BY conversation_id 
) AS min_times 
    ON va.conversation_id = min_times.conversation_id 
    AND va.min_start_time = min_times.min_start_time
LEFT JOIN (
    SELECT 
        t.team_name, 
        tm.user_id 
    FROM team_members tm 
    INNER JOIN teams t ON t.team_id = tm.team_id
) AS t 
    ON t.user_id = va.sender_id
LEFT JOIN wrapup_detail wd 
    ON wd.conversation_id = va.conversation_id 
    AND CAST(va.start_time AS DATE) = CAST(wd.wrapup_time AS DATE)
WHERE 
    cs.message_type = 'VOICE' 
    AND va.start_time BETWEEN '{start_time}' AND '{end_time}'
GROUP BY 
    va.conversation_id, 
    c.title, 
    va.start_time, 
    va.channel_customer_identifier, 
    va.total_duration,
    va.sender_name,
    va.sender_id,
    t.team_name;

    """,

-- for mssql
 "campaign_connected_call_details_gold_MSSQL":"""
    INSERT INTO campaign_connected_call_details_gold (
    [Campaign_Name], 
    DateTime, 
    [Number_Called], 
    [Call_Duration], 
    agent_name, 
    [Agent_ID], 
    Team_Name, 
    [Agent_wrap_ups]
)
SELECT 
    c.title AS [Campaign_Name], 
    va.start_time AS DateTime, 
    va.channel_customer_identifier AS [Number_Called], 
    va.total_duration AS [Call_Duration], 
    va.sender_name AS agent_name, 
    va.sender_id AS [Agent_ID], 
    t.team_name AS Team_Name,
    STUFF((
        SELECT DISTINCT ', ' + wd2.wrapup_label
        FROM wrapup_detail wd2
        WHERE wd2.conversation_id = va.conversation_id
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS [Agent_wrap_ups]
FROM campaign_scheduler cs  
INNER JOIN campaigns c  
    ON JSON_VALUE(cs.customer, '$.campaign') = c.id 
INNER JOIN (
    SELECT 
        va.activity_timestamp, 
        va.start_time, 
        call_leg_start_time AS min_start_time,
        va.channel_session_id, 
        va.conversation_id, 
        va.channel_customer_identifier, 
        vca.reason_code, 
        va.sender_type, 
        va.total_duration, 
        va.sender_name, 
        va.sender_id  
    FROM voice_activities va  
    INNER JOIN voice_connector_activities vca  
        ON va.channel_session_id = vca.message_id 
        AND CAST(va.activity_timestamp AS DATE) = CAST(vca.activity_timestamp AS DATE)
    WHERE vca.reason_code = 'NORMAL_CLEARING' 
      AND va.start_time BETWEEN LEFT('{start_time}', 23) AND LEFT('{end_time}', 23)
    GROUP BY 
        va.activity_timestamp,
        va.start_time,
        call_leg_start_time, 
        va.channel_session_id,
        va.conversation_id, 
        va.channel_customer_identifier,
        vca.reason_code, 
        va.sender_type, 
        va.total_duration, 
        va.sender_name, 
        va.sender_id
) AS va 
    ON cs.activity_data_id = va.channel_session_id 
    AND CAST(cs.activity_timestamp AS DATE) = CAST(va.activity_timestamp AS DATE)
INNER JOIN (
    SELECT 
        conversation_id, 
        MIN(call_leg_start_time) AS min_start_time 
    FROM voice_activities 
    WHERE start_time BETWEEN LEFT('{start_time}', 23) AND LEFT('{end_time}', 23)
    GROUP BY conversation_id 
) AS min_times 
    ON va.conversation_id = min_times.conversation_id 
    AND va.min_start_time = min_times.min_start_time
LEFT JOIN (
    SELECT 
        t.team_name, 
        tm.user_id 
    FROM team_members tm 
    INNER JOIN teams t ON t.team_id = tm.team_id
) AS t 
    ON t.user_id = va.sender_id
LEFT JOIN wrapup_detail wd 
    ON wd.conversation_id = va.conversation_id 
    AND CAST(va.start_time AS DATE) = CAST(wd.wrapup_time AS DATE)
WHERE 
    cs.message_type = 'VOICE' 
    AND va.start_time BETWEEN LEFT('{start_time}', 23) AND LEFT('{end_time}', 23)
GROUP BY 
    va.conversation_id, 
    c.title, 
    va.start_time, 
    va.channel_customer_identifier, 
    va.total_duration, 
    va.sender_name, 
    va.sender_id, 
    t.team_name;
    
    """,