WITH H AS

(

    SELECT

        uid,

        id,

        anlaggningsid,

        iXLNKontraktNr,

        iXLNRecId,

        andratdatum,

        andratuser,

        LAG(uid) OVER

        (

            PARTITION BY id

            ORDER BY uid

        ) AS PrevUid,

        LAG(anlaggningsid) OVER

        (

            PARTITION BY id

            ORDER BY uid

        ) AS PrevAnlaggningsId,

        LAG(iXLNKontraktNr) OVER

        (

            PARTITION BY id

            ORDER BY uid

        ) AS PrevXLNKontraktNr,

        LAG(iXLNRecId) OVER

        (

            PARTITION BY id

            ORDER BY uid

        ) AS PrevXLNRecId

    FROM Avtal_Elavtal_History

)

SELECT

    id,

    PrevUid,

    uid,

    andratdatum,

    andratuser,

    PrevAnlaggningsId,

    anlaggningsid,

    PrevXLNKontraktNr,

    iXLNKontraktNr,

    PrevXLNRecId,

    iXLNRecId

FROM H

WHERE PrevAnlaggningsId IS NOT NULL

  AND PrevAnlaggningsId <> ''

  AND anlaggningsid IS NOT NULL

  AND anlaggningsid <> ''

  AND PrevXLNKontraktNr IS NOT NULL

  AND PrevXLNKontraktNr <> ''

  AND anlaggningsid <> PrevAnlaggningsId

  AND LEN(andratuser) > 5

ORDER BY andratdatum desc

---


---