使用T-SQL查询关闭显示票据

如果只想计算所选日期的状态。应将日期筛选器移动到左侧联接。

SELECT
    s.displayname AS ASSIGNEE,
        COUNT(CASE WHEN e.status = 'open' THEN 1 END) AS [OPEN], 
        COUNT(CASE WHEN e.status = 'closed' THEN 1 END) AS CLOSED
FROM
    dbo.t_helpdeskassignees AS s 
    LEFT OUTER JOIN dbo.t_helpdesktickets AS e ON s.UserID = e.currentlyat
                         AND e.lastStatusUpdate BETWEEN CAST('2019-11-28 10:24:14.000' AS DATE) AND DATEADD(DAY,1, CAST('2019-11-28 10:24:14.000' AS DATE))
GROUP BY 
    s.displayname