SELECT
s.TicketNbr AS 'id'
,s.TicketNbr AS 'Ticket_Number'
,s.company_name AS 'Company_Name'
,co.company_id AS 'Company_ID'
,s.contact_name AS 'Contact'
,s.source AS 'Source'
,s.Site_Name AS customer_site
,s.team_name
,s.Territory
,s.location AS 'Location'
,s.board_name AS 'Board'
,s.summary AS 'Summary'
,s.status_description AS 'Status'
,CAST(s.date_entered AS DATETIME) AS 'date_opened'
,CAST(s.last_update AS DATETIME)  AS 'date_last_updated'
,CAST(s.Date_Required AS DATETIME) AS 'Date_Required'
,COALESCE(s.Responded_Minutes,0) + COALESCE(s.Responded_Skipped_Minutes,0) AS 'time_to_acknowledgement_minutes'
,CASE
    WHEN s.Date_Responded_UTC is NULL THEN NULL
    ELSE COALESCE(s.Responded_Minutes,0) + COALESCE(s.Responded_Skipped_Minutes,0)
END AS 'time_to_response_minutes'
,CAST(s.date_responded_utc AS DATETIME) AS acknowledgement_date
,CAST(s.date_responded_utc AS DATETIME) AS response_date
,CASE WHEN s.Date_Responded_UTC IS NOT NULL THEN (CASE WHEN s.Responded_Minutes + s.Responded_Skipped_Minutes <= 
(CASE WHEN slap.Responded_Hours IS NOT NULL THEN slap.Responded_Hours ELSE sla.Responded_Hours END
 * 60) THEN 'Met' ELSE 'Unmet' END) ELSE NULL END AS 'metresponsesla'
,CAST(CAST (s.Resplan_Minutes + s.Resplan_Skipped_Minutes + s.Responded_Minutes AS DECIMAL (9, 2)) / 60.0 AS DECIMAL(10,2)) AS 'time_to_resolution_plan(hours)'
,CAST(s.date_resplan_utc AS DATETIME) AS 'resolution_plan_date'
,CASE WHEN s.Date_Resplan_UTC IS NOT NULL THEN (CASE WHEN s.Resplan_Minutes + s.Resplan_Skipped_Minutes + s.Responded_Minutes <= 
(CASE WHEN slap.Resplan_Hours IS NOT NULL THEN slap.Resplan_Hours ELSE sla.Resplan_Hours END
 * 60) THEN 'Met' ELSE 'Unmet' END) ELSE NULL END AS 'metresplansla'
,CAST(CAST (s.Resolved_Minutes + s.Resplan_Minutes + s.Responded_Minutes AS DECIMAL (9, 2)) / 60.0 AS DECIMAL(10,2)) AS 'time_to_resolution(hours)'
,CAST(s.date_resolved_utc AS DATETIME) AS 'resolution_date'
,CASE WHEN s.Date_Resolved_UTC IS NOT NULL THEN (CASE WHEN s.Resolved_Minutes  + s.resplan_minutes + s.Responded_Minutes <= 
(CASE WHEN slap.Resolution_Hours IS NOT NULL THEN slap.Resolution_Hours ELSE sla.Resolution_Hours END
* 60) THEN 'Met' ELSE 'Unmet' END) ELSE NULL END AS 'metresolutionsla'
,CAST(s.date_closed AS DATETIME)  AS 'date_closed'
,CASE
When DATEDIFF(DD, s.date_entered, s.date_closed) = 0 Then 'Y'
ELSE 'N'
END AS 'same_day_close'
,CASE
WHEN DATEDIFF(DD,s.Date_Responded_UTC,s.Date_Resolved_UTC) = 0 Then 'Y'
ELSE 'N'
END AS 'same_day_resolved'
,s.servicetype AS 'Type'
,s.servicesubtype AS 'SubType'
,s.servicesubtypeitem AS 'Service_Item'
,s.urgency AS 'Priority'
,s.Severity
,s.Impact
,s.Hours_Actual
,s.Hours_Budget
,s.Hours_Scheduled  
,s.Hours_Billable  
,s.Hours_NonBillable   
,s.Hours_Invoiced 
,s.Hours_Agreement
,s.agreement_name
,CASE WHEN s.Date_Resolved_UTC IS NOT NULL THEN CAST(ROUND(DATEDIFF(Hour, s.Date_Entered,    s.Date_Resolved_UTC)/24.0, 0) AS NUMERIC) 
ELSE CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) END AS 'Age (Days)'
,CASE WHEN s.Date_Resolved_UTC IS NULL THEN  
    CASE WHEN 
    CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) < 8 THEN '1. Current'
    WHEN 
    CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) > 7 AND CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) < 15 THEN '2. 1 Week'
    WHEN 
    CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) > 14 AND CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) < 22 THEN '3. 2 Weeks'
    WHEN 
    CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) > 21 AND CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) < 30 THEN '4. 3 Weeks'
    WHEN 
    CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) > 29 THEN '5. 1+ Month'
    END 
ELSE 'Resolved' END AS 'Unresolved Age (Weeks)' 
,CASE WHEN s.date_closed IS NULL THEN  
    CASE WHEN 
    CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) < 8 THEN '1. Current'
    WHEN 
    CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) > 7 AND CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) < 15 THEN '2. 1 Week'
    WHEN 
    CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) > 14 AND CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) < 22 THEN '3. 2 Weeks'
    WHEN 
    CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) > 21 AND CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) < 30 THEN '4. 3 Weeks'
    WHEN 
    CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) > 29 THEN '5. 1+ Month'
    END 
ELSE 'Resolved' END AS 'Unsolved Age (Weeks)' 
,CASE 
    WHEN s.date_responded_utc is NULL THEN '1. Pending Response'
    WHEN s.Date_Resplan_UTC is NULL THEN '2. Pending Resolution Plan'
    WHEN s.Date_Resolved_UTC is NULL THEN '3. Pending Resolution'
    WHEN s.Date_Resolved_UTC is NOT NULL THEN '4. Resolved'
   WHEN s.date_closed is NOT NULL THEN '5. Closed'
END AS SLA_Escalation_Status
,LOWER(s.resolved_by) AS Resolved_By
,LOWER(s.closed_by) AS Closed_By
,LOWER(s.Responded_By) AS responded_by
,LOWER(town.member_id) AS Ticket_Owner_ID
,CAST(town.First_Name AS varchar) + ' ' + cast(town.Last_Name AS varchar) AS Ticket_Owner
,CASE
    WHEN (s.date_resolved_utc IS NOT NULL) THEN 'Resolved'
    ELSE 'Open'
END AS Resolved_Flag
,CASE
    WHEN (s.Closed_Flag = 'True') THEN 'Closed'
    ELSE 'Open'
END AS Closed_Flag
,CASE
    WHEN (SELECT recid FROM Schedule
            WHERE close_flag != 'True' 
                AND Schedule_Type_RecID = 4 
                AND s.ticketnbr = RecID
            GROUP BY recid) IS NOT NULL THEN 'Y'
    ELSE 'N'
END AS Is_Assigned
,CASE 
    WHEN s.config_recids IS NULL THEN 'False'
    ELSE 'True' 
END AS Config_Attached
,COALESCE((SELECT COUNT(t.time_recid) FROM time_entry t WHERE t.sr_service_recid = s.sr_service_recid),0) AS Time_Entry_Count
,al.agr_type_desc AS agreement_type
,al.Agreement_Status
,CAST(CASE 
    WHEN s.Closed_Flag = 'True' THEN NULL
    ELSE (SELECT MIN(Date_Time_Start_UTC)
        FROM schedule
        WHERE recid = s.sr_service_Recid
        AND schedule_type_recid = 4
        AND close_flag = 'False')
END AS DATETIME) AS next_date
FROM v_rpt_service AS s
LEFT JOIN company AS co ON s.company_recid = co.company_recid
LEFT JOIN SR_SLA AS sla ON s.SR_SLA_RecID = sla.SR_SLA_RECID
LEFT JOIN SR_Urgency AS sru ON s.SR_Urgency_RecID = sru.SR_Urgency_RecID
LEFT JOIN SR_SLAPriority AS slap ON s.SR_SLA_RecID = slap.SR_SLA_RecID AND sru.SR_Urgency_RecID = slap.SR_Urgency_RecID
LEFT JOIN member AS town ON town.member_recid = s.Ticket_Owner_RecID
LEFT JOIN v_rpt_agreementlist AS al ON al.agr_header_recid = s.agr_header_recid
INNER JOIN SR_Service AS sr ON s.ticketnbr = sr.sr_service_Recid

WHERE 
(DATEADD(DAY, -210 , Current_Timestamp) <= sr.Last_Update)
AND sr.Parent_Recid is null