sample_SQL.sql

-- VM 25 

 
----------------------------------------------------------
----------------------------------------------------------

 
DROP TABLE #AllCHA

SELECT

       cid.MemberAcesId,
       cid.CallInteractionId,
       cid.CallFormTypeName,
       cid.MemberFirst,
       cid.MemberLast,
       cid.CallIntDisp,
       cid.CallIntDispDesc,
       cid.BookingDatetime,
       cid.ClientId,
       cid.ClientName,
       cid.GeoZoneName,
       cid.GeoZoneId,
       md.Gender,
       md.DateOfBirth,
       da.AppointmentID,
       da.AppointmentDate,
       da.DroppedDate,
       DATEDIFF(day, da.DroppedDate, da.AppointmentDate) AS DayDiffs,
       da.CancelOrReschedule,
       da.DropReason,
       da.ReasonDescription,
       da.GroupName,
       da.Category,
       da.Owner,
       da.DropNotes,
       da.OriginalDropReason,
       da.TelehealthVisit,
       da.MemberCimID,
    da.DroppedAppointmentID,
       ROW_NUMBER() OVER (PARTITION BY cid.CallInteractionId
                       ORDER BY cid.BookingDatetime DESC) AS row_num

INTO #AllCHA

FROM [CIM].[RPT].[vwRptCCACallInteractionDetail] AS cid

INNER JOIN [CIM].[dbo].[MemberDemographics] AS md
        ON cid.MemberAcesId = md.ACESId

LEFT JOIN [SchedulingPrototype].[dbo].[DroppedAppointments2] AS da with(nolock)
        ON cid.MemberAcesId = da.MemberCimID
          AND da.StaffResourceID_Scheduler = cid.SchedulerStaffResourceId
          AND da.AppointmentID = cid.ScheduledVisitId
          AND cast(da.AptDateCreated as date) = cast(cid.BookingDateTime as date)
          AND cid.CallIntDisp IN ('SVISIT','MRSCHD','PRES','RSCHD')

WHERE cid.MemberAcesId IN
      (
         SELECT DISTINCT MemberAcesId
         FROM [CIM].[RPT].[vwRptCCACallInteractionDetail]
         WHERE CallIntDisp IN ('SVISIT')
         AND YEAR(BookingDatetime) IN (2019)
      )
AND YEAR(cid.BookingDatetime) IN (2019)
AND (da.ReasonDescription IS NULL
     OR da.ReasonDescription NOT IN ('Swap to make route more efficient',
                                     'Pulled Forward by CSC'))
AND cid.CallIntDisp IN ('SVISIT','RSCHD','MRSCHD')
AND (da.Category IS NULL
     OR da.Category NOT IN ('Equipment', 'Operations'))
AND (da.GroupName IS NULL
     OR da.GroupName NOT IN ('Equipment'))
AND cid.BookingDatetime <= '2019-12-15'
AND cid.CallFormTypeName in ('CALOUT','CALOUTPREDICTIVE','CALLIN','CALLINWARMTRAN')

ORDER BY cid.MemberAcesId, cid.CallInteractionId

 
SELECT TOP 200 * FROM #AllCHA
 

--SELECT COUNT(*) FROM #AllCHA
--SELECT COUNT(DISTINCT MemberAcesId) FROM #AllCHA
--SELECT MemberAcesId, COUNT(*) FROM #AllCHA GROUP BY MemberAcesId
--SELECT TOP 200 * FROM #AllCHA WHERE MemberAcesId = 3210694
--SELECT CallIntDisp, COUNT(*) FROM #AllCHA GROUP BY CallIntDisp
--SELECT CallInteractionId, COUNT(*) FROM #AllCHA GROUP BY CallInteractionId ORDER BY COUNT(*) DESC

--SELECT * FROM #AllCHA WHERE CallInteractionId = 184286615
--SELECT * FROM #AllCHA WHERE CallInteractionId = 189324840
--SELECT * FROM #AllCHA WHERE CallInteractionId = 189374644

--SELECT * FROM #AllCHA WHERE CallInteractionId = 189324840
--SELECT * FROM [SchedulingPrototype].[dbo].[DroppedAppointments2] WHERE MemberCimID = 13170247


----------------------------------------------------------
----------------------------------------------------------

DROP TABLE #AllCHAClean

SELECT
     cha.*
INTO #AllCHAClean
FROM #AllCHA AS cha
WHERE row_num = 1
ORDER BY cha.MemberAcesId, cha.CallInteractionId


SELECT TOP 100 * FROM #AllCHAClean


--SELECT COUNT(*) FROM #AllCHAClean
--SELECT COUNT(DISTINCT MemberAcesId) FROM #AllCHAClean
--SELECT MemberAcesId, COUNT(*) FROM #AllCHAClean GROUP BY MemberAcesId
--SELECT TOP 200 * FROM #AllCHAClean WHERE MemberAcesId = 3210694
--SELECT CallIntDisp, COUNT(*) FROM #AllCHAClean GROUP BY CallIntDisp
--SELECT CallInteractionId, COUNT(*) FROM #AllCHAClean GROUP BY CallInteractionId ORDER BY COUNT(*) DESC

--SELECT * FROM #AllCHAClean WHERE CallInteractionId = 184286615
--SELECT * FROM #AllCHAClean WHERE CallInteractionId = 189324840
--SELECT * FROM #AllCHAClean WHERE CallInteractionId = 189374644

--SELECT * FROM #AllCHAClean WHERE CallInteractionId = 189324840
--SELECT * FROM [SchedulingPrototype].[dbo].[DroppedAppointments2] WHERE MemberCimID = 13170247

 
---------------------------------------------------------
----------------------------------------------------------


DROP TABLE #AllFeatures_1

SELECT

       t0.MemberAcesId,
       t1.mail_num_month_1,
       t2.mail_num_month_2,
       t3.left_message_num_month_1,
       t4.left_message_num_month_2

INTO #AllFeatures_1

FROM (SELECT
                MemberAcesId
         FROM [CIM].[RPT].[vwRptCCACallInteractionDetail]
         WHERE MemberAcesId IN
      (
         SELECT DISTINCT MemberAcesId
         FROM [CIM].[RPT].[vwRptCCACallInteractionDetail]
         WHERE CallIntDisp IN ('SVISIT')
         AND YEAR(BookingDatetime) = 2019
      )
     GROUP BY MemberAcesId ) AS t0

LEFT JOIN (SELECT
                MemberAcesId,
                COUNT(*) AS 'mail_num_month_1'
         FROM [CIM].[RPT].[vwRptCCACallInteractionDetail]
         WHERE CallIntDisp IN ('MAIL')
         AND YEAR(BookingDatetime) = 2019
         AND MONTH(BookingDatetime) IN (1)
         GROUP BY MemberAcesId, CallIntDisp
        ) AS t1 ON ( t0.MemberAcesId = t1.MemberAcesId )

LEFT JOIN (SELECT
                             MemberAcesId,
                             COUNT(*) AS 'mail_num_month_2'
                      FROM [CIM].[RPT].[vwRptCCACallInteractionDetail]
                      WHERE CallIntDisp IN ('MAIL')
                      AND YEAR(BookingDatetime) = 2019
                      AND MONTH(BookingDatetime) IN (2)
                      GROUP BY MemberAcesId, CallIntDisp
            ) AS t2 ON ( t1.MemberAcesId = t2.MemberAcesId )

LEFT JOIN (SELECT
                             MemberAcesId,
                             COUNT(*) AS 'left_message_num_month_1'
                      FROM [CIM].[RPT].[vwRptCCACallInteractionDetail]
                      WHERE CallIntDisp IN ('LFTMSG')
                      AND YEAR(BookingDatetime) = 2019
                      AND MONTH(BookingDatetime) IN (1)
                      GROUP BY MemberAcesId, CallIntDisp
            ) AS t3 ON ( t1.MemberAcesId = t3.MemberAcesId )

LEFT JOIN (SELECT
                             MemberAcesId,
                             COUNT(*) AS 'left_message_num_month_2'
                      FROM [CIM].[RPT].[vwRptCCACallInteractionDetail]
                      WHERE CallIntDisp IN ('LFTMSG')
                      AND YEAR(BookingDatetime) = 2019
                      AND MONTH(BookingDatetime) IN (2)
                      GROUP BY MemberAcesId, CallIntDisp

            ) AS t4 ON ( t1.MemberAcesId = t4.MemberAcesId )

 
SELECT TOP 1000 * FROM #AllFeatures_1


----------------------------------------------------------
----------------------------------------------------------


SELECT * FROM #AllCHAClean AS a
INNER JOIN #AllFeatures_1 AS b
        ON a.MemberAcesId = b.MemberAcesId
ORDER BY a.MemberAcesId, a.CallInteractionId

 

--SELECT TOP 100 * FROM #AllCHAClean
--SELECT TOP 1000 * FROM #AllFeatures_1

---------------------------------------------------------
----------------------------------------------------------