Блог

Я отображал данные о посещаемости за день с помощью сводного SQL.

 declare @startdate datetime = '2016-09-26' 
declare @enddate datetime = '2016-10-01'
declare @CompanyID int = 1

DECLARE @COLUMN VARCHAR(MAX), @SQL NVARCHAR(MAX);

SET @COLUMN = N'';

DECLARE @DATERANGE TABLE (DateToCheck DATE) 
;WITH Temp
AS
(
    SELECT DT =DATEADD(DD,0, @startdate)  
    WHERE DATEADD(DD, 1, @startdate) <= @enddate  
    UNION ALL  
    SELECT DATEADD(DD, 1, DT)  
    FROM Temp  
    WHERE DATEADD(DD, 1, DT) <= @enddate  
)

INSERT INTO @DATERANGE 
SELECT DT From Temp

SELECT @COLUMN  = N', T.'   QUOTENAME(DateRanges) FROM (SELECT CAST(CONVERT(DATE, T.DateToCheck) AS VARCHAR(10)) AS DateRanges FROM @DATERANGE T group by T.DateToCheck) AS A;

SET @SQL = '
DECLARE @DATERANGE TABLE (DateToCheck DATE) 
;WITH Temp
AS
(
    SELECT DT =DATEADD(DD,0, @startdate)  
    WHERE DATEADD(DD, 1, @startdate) <= @enddate  
    UNION ALL  
    SELECT DATEADD(DD, 1, DT)  
    FROM Temp  
    WHERE DATEADD(DD, 1, DT) <= @enddate  
)

INSERT INTO @DATERANGE 
SELECT DT From Temp

SELECT *
FROM (
    SELECT E.FirstName, E.LastName, E.Email, T.DateToCheck, COALESCE(A.val, L.val, H.val, ''Absent'') val
    FROM AspNetUsers E
    CROSS APPLY (
        SELECT DateToCheck FROM @DATERANGE
    ) T--(DateToCheck)
    LEFT JOIN (SELECT ''Holiday'' val, HolidayDate, CompanyID FROM Holidays) H ON H.HolidayDate = T.DateToCheck AND H.CompanyID = @CompanyID
    LEFT JOIN (SELECT ''In : ''   CONVERT(VARCHAR, MIN(AttendanceDateTime), 108)   '' / Out : ''   CONVERT(VARCHAR, MAX(AttendanceDateTime), 108)  val, CAST(AttendanceDateTime As DATE) As AttendanceDate, UserID FROM Attendances GROUP BY CAST(AttendanceDateTime As DATE), UserID) A ON A.AttendanceDate = T.DateToCheck AND A.UserID = E.Id
    LEFT JOIN (SELECT ''Leave'' val, LeaveDate, UserID FROM LeaveApplications) L ON L.LeaveDate = T.DateToCheck AND L.UserID = E.Id
    WHERE E.CompanyID = @CompanyID
    ) T
PIVOT (MAX(val) FOR DateToCheck IN ('   STUFF(REPLACE(@COLUMN, ', T.[', ',['), 1, 1, '')   ')) P';

EXEC sp_executesql @SQL, N'@startdate DATE, @enddate DATE, @CompanyID INT', @startdate, @enddate, @CompanyID
  

И ниже приведен результирующий набор того, как это сейчас

Теперь я хочу добавить больше полей в приведенный выше SQL для отображения таких показателей, как PresentCount, AbsentCount, HolidayCount и LeaveCount

Я уже написал SQL, в котором я мог бы легко отобразить эти подсчеты, но я не могу заставить его работать с приведенным выше СВОДНЫМ SQL.

Таким образом, в этом случае результатом для первой строки будет PresentCount = 0, AbsentCount = 6, HolidayCount = 0 и LeaveCount = 0. Для строки 2 это будет PresentCount = 4, AbsentCount = 2, LeaveCount и HolidayCount оба равны 0.