Блог

У меня проблема с SQL Server. При подсчете вхождений система должна учитывать, что последовательные даты (до 5) считаются единичными, если они относятся к одному и тому же коду исключения, и исключать субботу и воскресенье при рассмотрении последовательных дней.

Я попробовал с CTE, ниже приведен результат, который я получаю.

 DROP TABLE

IF EXISTS #Occurrence
    CREATE TABLE #Occurrence (
        EmpId INT
        ,DATE DATE
        ,Day VARCHAR(100)
        ,ExceptionCode VARCHAR(100)
        )

INSERT INTO #Occurrence (
    EmpId
    ,DATE
    ,Day
    ,ExceptionCode
    )
SELECT 1
    ,'2020-12-01'
    ,'Tuesday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-02'
    ,'Wednesday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-03'
    ,'Thursday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-04'
    ,'Friday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-05'
    ,'Saturday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-06'
    ,'Sunday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-07'
    ,'Monday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-08'
    ,'Tuesday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-09'
    ,'Wednesday'
    ,'Casual Leave'

UNION ALL

SELECT 1
    ,'2020-12-10'
    ,'Thursday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-11'
    ,'Friday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-12'
    ,'Saturday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-13'
    ,'Sunday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-14'
    ,'Monday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-15'
    ,'Tuesday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-16'
    ,'Wednesday'
    ,'Casual Leave'

UNION ALL

SELECT 1
    ,'2020-12-17'
    ,'Thursday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-18'
    ,'Friday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-19'
    ,'Saturday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-20'
    ,'Sunday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-21'
    ,'Monday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-22'
    ,'Tuesday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-23'
    ,'Wednesday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-24'
    ,'Thursday'
    ,'Casual Leave'

UNION ALL

SELECT 1
    ,'2020-12-25'
    ,'Friday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-26'
    ,'Saturday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-27'
    ,'Sunday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-28'
    ,'Monday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-29'
    ,'Tuesday'
    ,'Casual Leave'

UNION ALL

SELECT 1
    ,'2020-12-30'
    ,'Wednesday'
    ,'Sick Leave'

UNION ALL

SELECT 1
    ,'2020-12-31'
    ,'Thursday'
    ,'Sick Leave';

WITH CTE
AS (
    SELECT EmpId
        ,DATE
        ,DateName(DW, DATE) DayName
        ,ExceptionCode
    FROM #Occurrence WITH (NOLOCK)
    )
SELECT CTE.*
    ,CASE 
        WHEN NEWCount = 1
            THEN 1
        ELSE 0
        END AS Occr_Count
FROM CTE
LEFT JOIN (
    SELECT EmpID
        ,ExceptionCode
        ,MIN(DATE) AS DATE
        ,ROW_NUMBER() OVER (
            PARTITION BY EmpID
            ,ExceptionCode
            ,MIN(DATE) ORDER BY EmpID
                ,ExceptionCode
                ,MIN(DATE)
            ) AS NEWCount
    FROM (
        SELECT EmpID
            ,DATE
            ,ExceptionCode
            ,Datediff(D, ROW_NUMBER() OVER (
                    ORDER BY EmpID
                        ,DATE ASC
                    ), DATE) AS Diff
        FROM CTE
        ) AS X
    GROUP BY EmpID
        ,ExceptionCode
        ,Diff
    ) AS Derived ON CTE.EmpID = Derived.EmpID
    AND CTE.ExceptionCode = Derived.ExceptionCode
    AND CAST(CTE.DATE AS DATE) = CAST(Derived.DATE AS DATE)
ORDER BY CTE.EmpID
    ,CTE.DATE
 

Но ниже приведен результат, который я ожидаю:

 SELECT
    EmpId,
    Date,
    DateName(DW, DATE) DayName,
    ExceptionCode,
    CASE WHEN PrevDate IS NOT NULL THEN 0 ELSE 1 END Occr_Count
FROM (
    SELECT *,
        CASE WHEN DATENAME(DW, Date) NOT IN ('Sunday','Saturday') THEN
            LAG(Date) OVER (PARTITION BY
                EmpID,
                ExceptionCode,
                CASE WHEN DATENAME(DW, Date) IN ('Sunday','Saturday') THEN 1 ELSE 0 END
                ORDER BY Date)
        END PrevDate
    FROM #Occurrence
    WHERE 
) t

ORDER BY CTE.EmpID, CTE.DATE;
 

Существуют более эффективные методы для этого, но этого будет достаточно, если только у вас нет проблем с perf.