WITH [cteReportEntry] (
    [CaseDetailsId],
    [DATA_PRZEKAZANIA_PAKIETU],
    [KOD_KLIENTA],
    [STAN_SPRAWY]
    )
AS
    (
    SELECT DISTINCT
        casedetails.[CaseDetailsId],
        CONVERT(VARCHAR(12),casedetails.[DateImported],103) + SPACE(1) + CONVERT(VARCHAR(12),casedetails.[DateImported],108),
        casedetails.[ClientCaseNumber],
        CASE
            WHEN
                casedetails.[CaseStatusId] = 2
            THEN
                'Zamknięta'
            WHEN
                casedetails.[CaseStatusId] IN (12,14,17)
            THEN
                'Obsługa prawna'
            ELSE
                'Obsługa polubowna'
            END
    FROM
        [dbo].[CaseDetails] casedetails WITH (NOLOCK)
    WHERE
        casedetails.[ClientId] = 33
        AND
        casedetails.[CaseStatusId] IN (1,2,3,4,5,7,8,12,13,14,17,18,21,24)
    ),
[cteReport] (
    [CaseDetailsId],
    [DATA_PRZEKAZANIA_PAKIETU],
    [KOD_KLIENTA],
    [STAN_SPRAWY],
    [STATUS_SPRAWY]
    )
AS
    (
    SELECT DISTINCT
        report.[CaseDetailsId],
        report.[DATA_PRZEKAZANIA_PAKIETU],
        report.[KOD_KLIENTA],
        report.[STAN_SPRAWY],
        CASE
            WHEN
                report.[STAN_SPRAWY] = 'Obsługa prawna'
                AND
                report.[CaseDetailsId] IN (
                    SELECT DISTINCT
                        caseactionhistory.[CaseDetailId]
                    FROM
                        [dbo].[CaseActionDefinition] caseactiondefinition WITH (NOLOCK)
                        LEFT JOIN [dbo].[CaseActionHistory] caseactionhistory WITH (NOLOCK) ON
                            caseactiondefinition.[DefinitionId] = caseactionhistory.[CaseActionDefinitionId]
                    WHERE
                        caseactiondefinition.[ActionStr] = '0910'
                        AND
                        (caseactionhistory.[IsDeleted] = 'FALSE' OR caseactionhistory.[IsDeleted] IS NULL)
                    )
            THEN
                'Postępowanie sądowe'
            WHEN
                report.[STAN_SPRAWY] = 'Obsługa prawna'
                AND
                report.[CaseDetailsId] IN (
                    SELECT DISTINCT
                        caseactionhistory.[CaseDetailId]
                    FROM
                        [dbo].[CaseActionHistory] caseactionhistory WITH (NOLOCK)
                        JOIN [dbo].[CaseActionDefinition] caseactiondefinition WITH (NOLOCK) ON
                            caseactiondefinition.[DefinitionId] = caseactionhistory.[CaseActionDefinitionId]
                    WHERE
                        caseactiondefinition.[ActionStr] NOT IN ('0910', '0918')
                    )
            THEN
                'EPU'
            WHEN
                report.[STAN_SPRAWY] = 'Obsługa prawna'
                AND
                report.[CaseDetailsId] IN (
                    SELECT DISTINCT
                        caseactionhistory.[CaseDetailId]
                    FROM
                        [dbo].[CaseActionHistory] caseactionhistory WITH (NOLOCK)
                        JOIN [dbo].[CaseActionDefinition] caseactiondefinition WITH (NOLOCK) ON
                            caseactiondefinition.[DefinitionId] = caseactionhistory.[CaseActionDefinitionId]
                    WHERE
                        caseactiondefinition.[ActionStr] = '0918'
                        AND
                            (caseactionhistory.[IsDeleted] = 'FALSE' OR caseactionhistory.[IsDeleted] IS NULL)
                    )
                THEN
                    'Postępowanie egzekucyjne'
            ELSE
                NULL
            END
    FROM
        [cteReportEntry] report
    ),
[cteReportStatusPozwu] (
    [CaseDetailsId],
    [DATA_PRZEKAZANIA_PAKIETU],
    [KOD_KLIENTA],
    [STAN_SPRAWY],
    [STATUS_SPRAWY],
    [TYP_POSTEPOWANIA],
    [STATUS_POZWU]
    )
AS
    (
    SELECT
        report.*,
        CASE
            WHEN
                report.[STATUS_SPRAWY] = 'Postępowanie sądowe'
            THEN
                'Postępowanie sądowe (zwykłe)'
            WHEN
                report.[STAN_SPRAWY] = 'Obsługa prawna'
                AND
                report.[CaseDetailsId] IN (
                    SELECT DISTINCT
                        casedetailsview.[CaseDetailsId]
                    FROM
                        [dbo].[CaseDetailsView] casedetailsview
                        JOIN [dbo].[CaseActionHistory] caseactionhistory
                            ON caseactionhistory.[CaseDetailId] = casedetailsview.[CaseDetailsId]
                        JOIN [dbo].[CaseActionDefinition] caseactiondefinition
                            ON caseactiondefinition.[DefinitionId] = caseactionhistory.[CaseActionDefinitionId]
                    WHERE
                        caseactiondefinition.[ActionStr] IN ('0938', '0939')
            )
        THEN
            'Postępowanie odwoławcze'
        WHEN
            report.[STATUS_SPRAWY] = 'Postępowanie sądowe'
            AND
            report.[CaseDetailsId] IN (
                SELECT DISTINCT
                    casedetailsview.[CaseDetailsId]
                FROM
                    [dbo].[CaseDetailsView] casedetailsview
                    JOIN [dbo].[CaseActionHistory] caseactionhistory
                        ON caseactionhistory.[CaseDetailId] = casedetailsview.[CaseDetailsId]
                    JOIN [dbo].[CaseActionDefinition] caseactiondefinition
                        ON caseactiondefinition.[DefinitionId] = caseactionhistory.[CaseActionDefinitionId]
                WHERE
                    caseactiondefinition.[ActionStr] NOT IN ('0938', '0939')
            )
        THEN
            'EPU'
        ELSE
            'NULL'
    END,
    CASE
        WHEN
            report.[CaseDetailsId] IN (
                SELECT DISTINCT
                    casedetailsview.[CaseDetailsId]
                FROM
                    [dbo].[CaseDetailsView] casedetailsview
                    JOIN [dbo].[CaseActionHistory] caseactionhistory
                        ON caseactionhistory.[CaseDetailId] = casedetailsview.[CaseDetailsId]
                    JOIN [dbo].[CaseActionDefinition] caseactiondefinition
                        ON caseactiondefinition.[DefinitionId] = caseactionhistory.[CaseActionDefinitionId]
                WHERE
                    caseactiondefinition.[ActionStr] = '0900'
            )
        THEN
            'Wysłano pozew'
        WHEN
            report.[CaseDetailsId] IN (
                SELECT DISTINCT
                    casedetailsview.[CaseDetailsId]
                FROM
                    [dbo].[CaseDetailsView] casedetailsview
                    JOIN [dbo].[CaseActionHistory] caseactionhistory
                        ON caseactionhistory.[CaseDetailId] = casedetailsview.[CaseDetailsId]
                    JOIN [dbo].[CaseActionDefinition] caseactiondefinition
                        ON caseactiondefinition.[DefinitionId] = caseactionhistory.[CaseActionDefinitionId]
                WHERE
                    caseactiondefinition.[ActionStr] = '0902'
            )
        THEN
            'Zadekretowano w sądzie'
        WHEN
            report.[CaseDetailsId] IN (
                SELECT DISTINCT
                    casedetailsview.[CaseDetailsId]
                FROM
                    [dbo].[CaseDetailsView] casedetailsview
                    JOIN [dbo].[CaseActionHistory] caseactionhistory
                        ON caseactionhistory.[CaseDetailId] = casedetailsview.[CaseDetailsId]
                    JOIN [dbo].[CaseActionDefinition] caseactiondefinition
                        ON caseactiondefinition.[DefinitionId] = caseactionhistory.[CaseActionDefinitionId]
                WHERE
                    caseactiondefinition.[ActionStr] = '0903'
            )
        THEN
            'Wysłano nakaz zapłaty'
        WHEN
            report.[CaseDetailsId] IN (
                SELECT DISTINCT
                    casedetailsview.[CaseDetailsId]
                FROM
                    [dbo].[CaseDetailsView] casedetailsview
                    JOIN [dbo].[CaseActionHistory] caseactionhistory
                        ON caseactionhistory.[CaseDetailId] = casedetailsview.[CaseDetailsId]
                    JOIN [dbo].[CaseActionDefinition] caseactiondefinition
                        ON caseactiondefinition.[DefinitionId] = caseactionhistory.[CaseActionDefinitionId]
                WHERE
                    caseactiondefinition.[ActionStr] = '0904'
            )
        THEN
            'Nadano klauzulę wykonywalności'
        WHEN
            report.[CaseDetailsId] IN (
                SELECT DISTINCT
                    casedetailsview.[CaseDetailsId]
                FROM
                    [dbo].[CaseDetailsView] casedetailsview
                    JOIN [dbo].[CaseActionHistory] caseactionhistory
                        ON caseactionhistory.[CaseDetailId] = casedetailsview.[CaseDetailsId]
                    JOIN [dbo].[CaseActionDefinition] caseactiondefinition
                        ON caseactiondefinition.[DefinitionId] = caseactionhistory.[CaseActionDefinitionId]
                WHERE
                    caseactiondefinition.[ActionStr] IN ('0911', '0937', '0927', '0928', '0929', '0997')
            )
        THEN
            'Umorzenie postępowania'
    END
FROM
    [cteReport] report
)

SELECT DISTINCT
    report.*,
    CASE
        WHEN
            report.[STATUS_POZWU] = 'Umorzenie postępowania'
            AND
            report.[CaseDetailsId] IN (
                SELECT DISTINCT
                    casedetailsview.[CaseDetailsId]
                FROM
                    [dbo].[CaseDetailsView] casedetailsview
                    JOIN [dbo].[CaseActionHistory] caseactionhistory
                        ON caseactionhistory.[CaseDetailId] = casedetailsview.[CaseDetailsId]
                    JOIN [dbo].[CaseActionDefinition] caseactiondefinition
                        ON caseactiondefinition.[DefinitionId] = caseactionhistory.[CaseActionDefinitionId]
                WHERE
                    caseactiondefinition.[ActionStr] IN ('0911', '0937', '0927', '0928', '0929', '0997')
            )
        THEN
            (
                SELECT DISTINCT TOP 1
                    caseactiondefinition.[ActionStr]
                FROM
                    [dbo].[CaseDetailsView] casedetailsview
                    JOIN [dbo].[CaseActionHistory] caseactionhistory
                        ON caseactionhistory.[CaseDetailId] = casedetailsview.[CaseDetailsId]
                    JOIN [dbo].[CaseActionDefinition] caseactiondefinition
                        ON caseactiondefinition.[DefinitionId] = caseactionhistory.[CaseActionDefinitionId]
                WHERE
                    caseactiondefinition.[ActionStr] IN ('0911', '0937', '0927', '0928', '0929', '0997')
            )
        WHEN
            report.[CaseDetailsId] IN (
                SELECT DISTINCT
                    casedetailsview.[CaseDetailsId]
                FROM
                    [dbo].[CaseDetailsView] casedetailsview
                    JOIN [dbo].[CaseActionHistory] caseactionhistory
                        ON caseactionhistory.[CaseDetailId] = casedetailsview.[CaseDetailsId]
                    JOIN [dbo].[CaseActionDefinition] caseactiondefinition
                        ON caseactiondefinition.[DefinitionId] = caseactionhistory.[CaseActionDefinitionId]
                WHERE
                    caseactiondefinition.[ActionStr] = '0997'
            )
        THEN
            (
                SELECT DISTINCT TOP 1
                    caseactionhistory.[Notes]
                FROM
                    [dbo].[CaseDetailsView] casedetailsview
                    JOIN [dbo].[CaseActionHistory] caseactionhistory
                        ON caseactionhistory.[CaseDetailId] = casedetailsview.[CaseDetailsId]
                    JOIN [dbo].[CaseActionDefinition] caseactiondefinition
                        ON caseactiondefinition.[DefinitionId] = caseactionhistory.[CaseActionDefinitionId]
                WHERE
                    caseactiondefinition.[ActionStr] = '0997'
            )
        ELSE
            'NULL'
    END
    AS [PRZYCZYNA_ZAMKNIECIA_OPIS]
FROM
    [cteReportStatusPozwu] report
ORDER BY
    report.[STATUS_POZWU]