policyStatusAndSubStatusChanges.sql

--- Statuses Changes
SELECT leads.Ref, 
       statuses.Name, 
       leads.StatusDate, 
       leads.StatusChangeReason, 
       users.Username AS 'Status Changed By'
FROM dbo.LEAD AS leads
     INNER JOIN dbo.STATUS AS statuses ON leads.StatusID = statuses.ID
     LEFT JOIN dbo.Users AS users ON leads.StatusChangedBy = users.ID
WHERE(leads.StatusDate >= '2019-12-04'
      AND leads.StatusDate < '2019-12-05')
ORDER BY leads.StatusDate DESC;

--- Sub-Statuses Changes
SELECT leads.Ref, 
       substatuses.Name, 
       leads.SubStatusChangedDate, 
       users.Username AS 'SubStatus Changed By'
FROM dbo.LEAD AS leads
     INNER JOIN dbo.SubStatus AS substatuses ON leads.SubStatusID = substatuses.ID
     LEFT JOIN dbo.Users AS users ON leads.SubStatusChangedBy = users.ID
WHERE(leads.SubStatusChangedDate >= '2019-12-04'
      AND leads.SubStatusChangedDate < '2019-12-05')
ORDER BY leads.SubStatusChangedDate DESC;

-- Status Counts
SELECT DISTINCT
       (users.Username) AS 'Status Changed By', 
       COUNT(*)
FROM dbo.LEAD AS leads
     INNER JOIN dbo.STATUS AS statuses ON leads.StatusID = statuses.ID
     LEFT JOIN dbo.Users AS users ON leads.StatusChangedBy = users.ID
WHERE(leads.StatusDate >= '2019-12-01'
      AND leads.StatusDate < '2019-12-05')
GROUP BY users.Username;

-- SubStatus Counts
SELECT DISTINCT
       (users.Username) AS 'SubStatus Changed By', 
       COUNT(*)
FROM dbo.LEAD AS leads
     INNER JOIN dbo.SubStatus AS substatuses ON leads.SubStatusID = substatuses.ID
     LEFT JOIN dbo.Users AS users ON leads.SubStatusChangedBy = users.ID
WHERE(leads.StatusDate >= '2019-12-01'
      AND leads.StatusDate < '2019-12-05')
GROUP BY users.Username;