[Group By Retention Tickets] #sql #247

groupByRetentionTickets.sql

SELECT SUM(CONVERT(DECIMAL(29, 4), retentions.PremiumPolicy)) AS 'OldValue', 
       SUM(CONVERT(DECIMAL(29, 4), retentions.Premium)) AS 'NewValue', 
       SUM(CONVERT(DECIMAL(29, 4), retentions.PremiumDiff)) AS 'PremiumDiff', 
       retentions.OutcomeID, 
       users1.Username, 
       users1.FirstName, 
       users1.LastName, 
       retentions.CreatedDate
FROM dbo.Retention AS retentions
     --  LEFT JOIN dbo.LEAD AS leads ON retentions.LeadID = leads.ID
     --  LEFT JOIN dbo.STATUS AS statuses ON leads.StatusID = statuses.ID
     -- LEFT JOIN dbo.Department AS departments ON retentions.DepartmentID = departments.ID
     --  LEFT JOIN dbo.RetentionReason AS retentionreasons ON retentions.ReasonID = retentionreasons.ID
     --  LEFT JOIN dbo.RetentionSubReason AS retentionsubreasons ON retentions.SubReasonID = retentionsubreasons.ID
     --   LEFT JOIN dbo.Outcome AS outcomes ON retentions.OutcomeID = outcomes.ID
     --   LEFT JOIN dbo.SubOutcome AS suboutcomes ON retentions.SubOutcomeID = suboutcomes.ID
     LEFT JOIN dbo.Users AS users1 ON retentions.AssignedTo = users1.ID
     LEFT JOIN dbo.Users AS users2 ON retentions.CreatedBy = users2.ID
WHERE retentions.CreatedDate >= '2019-10-01'
      --AND retentions.CreatedDate < '2019-10-20'
      AND retentions.OutcomeID = 2
GROUP BY retentions.CreatedDate, 
         users1.Username, 
         users1.Username, 
         users1.FirstName, 
         users1.LastName, 
         retentions.OutcomeID
ORDER BY retentions.CreatedDate DESC, 
         users1.Username ASC;

retentionTickets.sql

DECLARE @startDate DATETIME= '2019-09-28';
DECLARE @endDate DATETIME= '2019-09-29';
DECLARE @p1 INT= 2;
SELECT users.Username, 
       coverPlansCancelledDelta.counts, 
       coverPlansCancelledOldValueDelta.value1, 
       coverPlansCancelledNewValueDelta.value2
FROM
(
    --- [coverPlansCancelledDelta]
    SELECT COUNT(*) AS 'counts', 
           [t3].AssignedTo
    FROM [Lead] AS [t2]
         INNER JOIN [Retention] AS [t3] ON [t2].[ID] = [t3].[LeadID]
    WHERE([t3].[CreatedDate] >= @startDate)
         AND ([t3].[OutcomeID] = @p1)
    GROUP BY [t3].AssignedTo
) AS coverPlansCancelledDelta
INNER JOIN
(
    --- coverPlansCancelledOldValueDelta
    SELECT SUM([t6].[value]) AS 'value1', 
           [t6].AssignedTo
    FROM
    (
        SELECT CONVERT(DECIMAL(29, 4), [t5].[PremiumPolicy]) AS [value], 
               [t5].[CreatedDate], 
               [t5].[OutcomeID], 
               [t5].AssignedTo
        FROM [Lead] AS [t4]
             INNER JOIN [Retention] AS [t5] ON [t4].[ID] = [t5].[LeadID]
    ) AS [t6]
    WHERE(CAST(FLOOR(CAST([t6].[CreatedDate] AS FLOAT)) AS DATETIME) >= @startDate
          AND CAST(FLOOR(CAST([t6].[CreatedDate] AS FLOAT)) AS DATETIME) < @endDate)
         AND ([t6].[OutcomeID] = @p1)
    GROUP BY [t6].AssignedTo
) AS coverPlansCancelledOldValueDelta ON coverPlansCancelledDelta.AssignedTo = coverPlansCancelledOldValueDelta.AssignedTo
INNER JOIN
(

    --- [coverPlansCancelledNewValueDelta]
    SELECT SUM([t9].[value]) AS 'value2', 
           [t9].AssignedTo
    FROM
    (
        SELECT CONVERT(DECIMAL(29, 4), [t8].[Premium]) AS [value], 
               [t8].[CreatedDate], 
               [t8].[OutcomeID], 
               [t8].AssignedTo
        FROM [Lead] AS [t7]
             INNER JOIN [Retention] AS [t8] ON [t7].[ID] = [t8].[LeadID]
    ) AS [t9]
    WHERE(CAST(FLOOR(CAST([t9].[CreatedDate] AS FLOAT)) AS DATETIME) >= @startDate
          AND CAST(FLOOR(CAST([t9].[CreatedDate] AS FLOAT)) AS DATETIME) < @endDate)
         AND ([t9].[OutcomeID] = @p1)
    GROUP BY [t9].AssignedTo
) AS coverPlansCancelledNewValueDelta ON coverPlansCancelledDelta.AssignedTo = coverPlansCancelledNewValueDelta.AssignedTo
INNER JOIN
dbo.Users as users ON coverPlansCancelledDelta.AssignedTo = users.ID