DECLARE @LastCode VARCHAR(4) = '0416';

DECLARE @Report TABLE
(
        CaseDetailsId INT,
        ClientId INT,
        SetId INT,
        CaseId INT,
        ClientCaseNumber NVARCHAR(256),
        [querry1 CaseActionId] INT,
        [querry2 CaseActionId] INT,
        [querry1 ActionStr] NVARCHAR(4),
        [querry1 Description] NVARCHAR(256),
        [querry2 ActionStr] NVARCHAR(4),
        [querry2 Description] NVARCHAR(256),
        [querry1 DateAdded] DATETIME2,
        [querry2 DateAdded] DATETIME2,
        [querry1 Login] NVARCHAR(64),
        [querry2 Login] NVARCHAR(64)
)
INSERT INTO @Report
    SELECT DISTINCT
        querry1.CaseDetailsId,
        querry1.ClientId,
        querry1.SetId,
        querry1.CaseId,
        querry1.ClientCaseNumber,
        querry1.CaseActionId as [querry1 CaseActionId],
        querry2.CaseActionId as [querry2 CaseActionId],
        querry1.ActionStr as [querry1 ActionStr],
        querry1.Description as [querry1 Description],
        querry2.ActionStr as [querry2 ActionStr],
        querry2.Description as [querry2 Description],
        querry1.DateAdded as [querry1 DateAdded],
        querry2.[DateAdded] as [querry2 DateAdded],
        querry1.[Login] as [querry1 Login],
        querry2.[Login] as [querry2 Login]
    FROM
        ( -- wybieranie spraw z ostatnim kodem które nie były dodane przez system
            SELECT DISTINCT
                cd.CaseDetailsId,
                cd.ClientId,
                cd.SetId,
                cd.CaseId,
                cd.ClientCaseNumber,
                cah.CaseActionId,
                cad.ActionStr,
                cad.Description,
                cah.DateAdded,
                u.Login
            FROM
                ( -- wybieranie ostatniego kodu nie uwzględniając kodów z listy
                    SELECT
                        cd.CaseDetailsId,
                        MAX(cah.CaseActionId) AS [CaseActionId]
                    FROM
                        CaseDetails cd
                        LEFT JOIN CaseActionHistory cah on cah.CaseDetailId = cd.CaseDetailsId
                        LEFT JOIN CaseActionDefinition cad on cad.DefinitionId = cah.CaseActionDefinitionId
                        LEFT JOIN Users u on u.UserId = cah.UserId
                    WHERE
                        cah.CaseDetailId IN
                            ( -- wybranie spraw które mamy w obsłudze
                                SELECT
                                    cdv.CaseDetailsId
                                FROM
                                    CaseDetailsView cdv
                                WHERE
                                    CAST(GETDATE() AS DATE) >= CAST(cdv.DateImported AS DATE)
                                    AND
                                    CAST(GETDATE() AS DATE) <= CAST(cdv.EndServiceDate AS DATE)
                            )
                            AND
                            u.Login NOT IN ('admin', 'system')
                    GROUP BY
                        cd.CaseDetailsId
                ) querry
                LEFT JOIN CaseActionHistory cah on cah.CaseActionId = querry.CaseActionId
                LEFT JOIN CaseActionDefinition cad on cad.DefinitionId = cah.CaseActionDefinitionId
                LEFT JOIN CaseDetails cd on cd.CaseDetailsId = querry.CaseDetailsId
                LEFT JOIN Users u on u.UserId = cah.UserId
            WHERE
                cad.[ActionStr] = @LastCode
        ) querry1
    LEFT JOIN
        ( -- wybieranie spraw z ostatnim kodem
            SELECT DISTINCT
                cd.CaseDetailsId,
                cd.ClientId,
                cd.SetId,
                cd.CaseId,
                cd.ClientCaseNumber,
                cah.CaseActionId,
                cad.ActionStr,
                cad.Description,
                cah.DateAdded,
                u.Login
            FROM
                ( -- wybieranie ostatniego kodu nie uwzględniając kodów z listy
                    SELECT
                        cd.CaseDetailsId,
                        MAX(cah.CaseActionId) AS [CaseActionId]
                    FROM
                        CaseDetails cd
                        LEFT JOIN CaseActionHistory cah on cah.CaseDetailId = cd.CaseDetailsId
                        LEFT JOIN CaseActionDefinition cad on cad.DefinitionId = cah.CaseActionDefinitionId
                        LEFT JOIN Users u on u.UserId = cah.UserId
                    WHERE
                        cah.CaseDetailId IN
                            ( -- wybranie spraw które mamy w obsłudze
                                SELECT
                                    cdv.CaseDetailsId
                                FROM
                                    CaseDetailsView cdv
                                WHERE
                                    CAST(GETDATE() AS DATE) >= CAST(cdv.DateImported AS DATE)
                                    AND
                                    CAST(GETDATE() AS DATE) <= CAST(cdv.EndServiceDate AS DATE)
                            )
                    GROUP BY
                        cd.CaseDetailsId
                ) querry
                LEFT JOIN CaseActionHistory cah on cah.CaseActionId = querry.CaseActionId
                LEFT JOIN CaseActionDefinition cad on cad.DefinitionId = cah.CaseActionDefinitionId
                LEFT JOIN CaseDetails cd on cd.CaseDetailsId = querry.CaseDetailsId
                LEFT JOIN Users u on u.UserId = cah.UserId
        ) querry2 on querry2.[CaseDetailsId] = querry1.[CaseDetailsId]

update casedetails set CaseStatusId = 1
from casedetails
where
CaseDetailsId in
(
SELECT DISTINCT
    cd.CaseDetailsId
FROM
    @Report r
    left join casedetails cd on cd.CaseDetailsId = r.CaseDetailsId
    left join CaseStatusDefinition csd on csd.DefinitionId = cd.CaseStatusId
WHERE
    r.[querry1 DateAdded] >= r.[querry2 DateAdded]
    AND
    r.[querry1 DateAdded] <= getdate()-4
    and
    cd.CaseStatusId = 8
)