STOCKSALEAUDIT

CREATE view dbo.STOCKSALEAUDIT as 
        WITH Audit_CTE 
    as (
        select T.AUDITID
            ,T.AUDITRECORDID
            ,T.AUDITKEY
            ,T.AUDITTYPECODE
            ,T.FT_COUNT
            ,T.RE_COUNT
            ,DENSE_RANK() over (PARTITION by T.AUDITRECORDID order by T.RE_RECORDID asc) [RANK]
            ,T.AUDITCHANGEAGENTID
            ,T.AUDITDATE
            ,T.AUDITTYPE
            ,T.STOCKDETAILID
            ,T.SALEDATE
            ,T.SALEAMOUNT
            ,T.FEE
            ,T.NUMBEROFUNITS
            ,T.LOWPRICE
            ,T.MEDIANPRICE
            ,T.HIGHPRICE
            ,T.SALEPOSTDATE
            ,T.SALEPOSTSTATUSCODE
            ,T.ADDEDBYID
            ,T.CHANGEDBYID
            ,T.DATEADDED
            ,T.DATECHANGED
            ,T.ORGANIZATIONSALEAMOUNT
            ,T.ORGANIZATIONFEE
            ,T.ORGANIZATIONLOWPRICE
            ,T.ORGANIZATIONMEDIANPRICE
            ,T.ORGANIZATIONHIGHPRICE
            ,T.TRANSACTIONSALEAMOUNT
            ,T.TRANSACTIONFEE
            ,T.TRANSACTIONLOWPRICE
            ,T.TRANSACTIONMEDIANPRICE
            ,T.TRANSACTIONHIGHPRICE
            ,T.BASECURRENCYID
            ,T.ORGANIZATIONEXCHANGERATEID
            ,T.TRANSACTIONCURRENCYID
            ,T.BASEEXCHANGERATEID
        from (
            select FT.AUDITID
                ,null [RE_RECORDID]
                ,FT.AUDITRECORDID
                ,FT.AUDITTYPECODE
                ,ROW_NUMBER() over (PARTITION by FT.AUDITRECORDID order by FT.AUDITDATE, FT.AUDITTYPECODE asc) [FT_COUNT]
                ,ROW_NUMBER() over (PARTITION by FT.AUDITRECORDID order by FT.AUDITDATE, FT.AUDITTYPECODE desc) [RE_COUNT]
                ,FT.AUDITKEY
                ,FT.AUDITCHANGEAGENTID
                ,FT.AUDITDATE
                ,FT.AUDITTYPE
                ,null [STOCKDETAILID]
                ,null [SALEDATE]
                ,FT.BASEAMOUNT as SALEAMOUNT
                ,null [FEE]
                ,null [NUMBEROFUNITS]
                ,null [LOWPRICE]
                ,null [MEDIANPRICE]
                ,null [HIGHPRICE]
                ,FT.POSTDATE as SALEPOSTDATE
                ,cast(case FT.POSTSTATUSCODE when 2 then 0 when 1 then 1 when 3 then 2 end as tinyint) as SALEPOSTSTATUSCODE
                ,FT.ADDEDBYID
                ,FT.CHANGEDBYID
                ,FT.DATEADDED
                ,FT.DATECHANGED
                ,FT.ORGAMOUNT as ORGANIZATIONSALEAMOUNT
                ,null as ORGANIZATIONFEE
                ,null as ORGANIZATIONLOWPRICE
                ,null as ORGANIZATIONMEDIANPRICE
                ,null as ORGANIZATIONHIGHPRICE
                ,FT.TRANSACTIONAMOUNT as TRANSACTIONSALEAMOUNT
                ,null as TRANSACTIONFEE
                ,null as TRANSACTIONLOWPRICE
                ,null as TRANSACTIONMEDIANPRICE
                ,null as TRANSACTIONHIGHPRICE
                ,CS.BASECURRENCYID
                ,FT.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID
                ,FT.TRANSACTIONCURRENCYID
                ,FT.BASEEXCHANGERATEID
            from dbo.FINANCIALTRANSACTIONAUDIT FT
            left join dbo.PDACCOUNTSYSTEM S on FT.PDACCOUNTSYSTEMID = S.ID
            left join dbo.CURRENCYSET CS on CS.ID = S.CURRENCYSETID
            where FT.TYPECODE = 21

            union all

            select RE.AUDITID
                ,RE.AUDITRECORDID [RE_RECORDID]
                ,RE.AUDITRECORDID
                ,RE.AUDITTYPECODE
                ,ROW_NUMBER() over (PARTITION by RE.AUDITRECORDID order by RE.AUDITDATE, RE.AUDITTYPECODE asc) [FT_COUNT]
                ,ROW_NUMBER() over (PARTITION by RE.AUDITRECORDID order by RE.AUDITDATE, RE.AUDITTYPECODE desc) [RE_COUNT]
                ,RE.AUDITKEY
                ,RE.AUDITCHANGEAGENTID
                ,RE.AUDITDATE
                ,RE.AUDITTYPE
                ,RE.STOCKDETAILID
                ,RE.SALEDATE
                ,null as SALEAMOUNT
                ,RE.FEE
                ,RE.NUMBEROFUNITS
                ,RE.LOWPRICE
                ,RE.MEDIANPRICE
                ,RE.HIGHPRICE
                ,null as SALEPOSTDATE
                ,null as SALEPOSTSTATUSCODE
                ,RE.ADDEDBYID
                ,RE.CHANGEDBYID
                ,RE.DATEADDED
                ,RE.DATECHANGED
                ,null as ORGANIZATIONSALEAMOUNT
                ,RE.ORGANIZATIONFEE
                ,RE.ORGANIZATIONLOWPRICE
                ,RE.ORGANIZATIONMEDIANPRICE
                ,RE.ORGANIZATIONHIGHPRICE
                ,null as TRANSACTIONSALEAMOUNT
                ,RE.TRANSACTIONFEE
                ,RE.TRANSACTIONLOWPRICE
                ,RE.TRANSACTIONMEDIANPRICE
                ,RE.TRANSACTIONHIGHPRICE
                ,CS.BASECURRENCYID
                ,null [ORGANIZATIONEXCHANGERATEID]
                ,null [TRANSACTIONCURRENCYID]
                ,null [BASEEXCHANGERATEID]
            from dbo.STOCKSALE_EXTAUDIT RE
            left join dbo.FINANCIALTRANSACTION FT on FT.ID = RE.AUDITRECORDID
            left join dbo.PDACCOUNTSYSTEM S on FT.PDACCOUNTSYSTEMID = S.ID
            left join dbo.CURRENCYSET CS on CS.ID = S.CURRENCYSETID
        ) T
    )
    select A.AUDITID
        ,A.AUDITRECORDID
        ,A.AUDITKEY
        ,A.AUDITCHANGEAGENTID
        ,A.AUDITDATE
        ,A.AUDITTYPECODE
        ,A.AUDITTYPE
        ,A.STOCKDETAILID
        ,A.SALEDATE
        ,A.SALEAMOUNT
        ,A.FEE
        ,A.NUMBEROFUNITS
        ,A.LOWPRICE
        ,A.MEDIANPRICE
        ,A.HIGHPRICE
        ,A.SALEPOSTDATE
        ,A.SALEPOSTSTATUSCODE
        ,A.ADDEDBYID
        ,A.CHANGEDBYID
        ,A.DATEADDED
        ,A.DATECHANGED
        ,A.ORGANIZATIONSALEAMOUNT
        ,A.ORGANIZATIONFEE
        ,A.ORGANIZATIONLOWPRICE
        ,A.ORGANIZATIONMEDIANPRICE
        ,A.ORGANIZATIONHIGHPRICE
        ,A.TRANSACTIONSALEAMOUNT
        ,A.TRANSACTIONFEE
        ,A.TRANSACTIONLOWPRICE
        ,A.TRANSACTIONMEDIANPRICE
        ,A.TRANSACTIONHIGHPRICE
        ,A.BASECURRENCYID
        ,A.ORGANIZATIONEXCHANGERATEID
        ,A.TRANSACTIONCURRENCYID
        ,A.BASEEXCHANGERATEID
    from dbo.STOCKSALEAUDITORIGINAL A

    union all

    select CTE1.AUDITID
        ,CTE1.AUDITRECORDID
        ,CTE1.AUDITKEY
        ,CTE1.AUDITCHANGEAGENTID
        ,CTE1.AUDITDATE
        ,CTE1.AUDITTYPECODE
        ,CTE1.AUDITTYPE
        ,coalesce(CTE1.STOCKDETAILID, CTE2.STOCKDETAILID, CASE WHEN CTE2.AUDITID is null THEN RE.STOCKDETAILID ELSE NULL END) STOCKDETAILID
        ,coalesce(CTE1.SALEDATE, CTE2.SALEDATE, CASE WHEN CTE2.AUDITID is null THEN RE.SALEDATE ELSE NULL END) SALEDATE
        ,coalesce(CTE1.SALEAMOUNT, CTE2.SALEAMOUNT, CASE WHEN CTE2.AUDITID is null THEN FT.BASEAMOUNT ELSE null end) SALEAMOUNT
        ,coalesce(CTE1.FEE, CTE2.FEE, CASE WHEN CTE2.AUDITID is null THEN RE.FEE ELSE NULL END) FEE
        ,coalesce(CTE1.NUMBEROFUNITS, CTE2.NUMBEROFUNITS, CASE WHEN CTE2.AUDITID is null THEN RE.NUMBEROFUNITS ELSE NULL END) NUMBEROFUNITS
        ,coalesce(CTE1.LOWPRICE, CTE2.LOWPRICE, CASE WHEN CTE2.AUDITID is null THEN RE.LOWPRICE ELSE NULL END) LOWPRICE
        ,coalesce(CTE1.MEDIANPRICE, CTE2.MEDIANPRICE, CASE WHEN CTE2.AUDITID is null THEN RE.MEDIANPRICE ELSE NULL END) MEDIANPRICE
        ,coalesce(CTE1.HIGHPRICE, CTE2.HIGHPRICE, CASE WHEN CTE2.AUDITID is null THEN RE.HIGHPRICE ELSE NULL END) HIGHPRICE
        ,cast(coalesce(CTE1.SALEPOSTDATE, CTE2.SALEPOSTDATE, CASE WHEN CTE2.AUDITID is null THEN FT.POSTDATE ELSE NULL END) as datetime) SALEPOSTDATE
        ,coalesce(CTE1.SALEPOSTSTATUSCODE, CTE2.SALEPOSTSTATUSCODE, CASE WHEN CTE2.AUDITID is null THEN cast(case FT.POSTSTATUSCODE when 2 then 0 when 1 then 1 when 3 then 2 end as tinyint) ELSE NULL END) SALEPOSTSTATUSCODE
        ,CTE1.ADDEDBYID
        ,CTE1.CHANGEDBYID
        ,CTE1.DATEADDED
        ,CTE1.DATECHANGED
        ,coalesce(CTE1.ORGANIZATIONSALEAMOUNT, CTE2.ORGANIZATIONSALEAMOUNT, CASE WHEN CTE2.AUDITID is null THEN FT.ORGAMOUNT ELSE NULL END) ORGANIZATIONSALEAMOUNT
        ,coalesce(CTE1.ORGANIZATIONFEE, CTE2.ORGANIZATIONFEE, CASE WHEN CTE2.AUDITID is null THEN RE.ORGANIZATIONFEE ELSE NULL END) ORGANIZATIONFEE
        ,coalesce(CTE1.ORGANIZATIONLOWPRICE, CTE2.ORGANIZATIONLOWPRICE, CASE WHEN CTE2.AUDITID is null THEN RE.ORGANIZATIONLOWPRICE ELSE NULL END) ORGANIZATIONLOWPRICE
        ,coalesce(CTE1.ORGANIZATIONMEDIANPRICE, CTE2.ORGANIZATIONMEDIANPRICE, CASE WHEN CTE2.AUDITID is null THEN RE.ORGANIZATIONMEDIANPRICE ELSE NULL END) ORGANIZATIONMEDIANPRICE
        ,coalesce(CTE1.ORGANIZATIONHIGHPRICE, CTE2.ORGANIZATIONHIGHPRICE, CASE WHEN CTE2.AUDITID is null THEN RE.ORGANIZATIONHIGHPRICE ELSE NULL END) ORGANIZATIONHIGHPRICE
        ,coalesce(CTE1.TRANSACTIONSALEAMOUNT, CTE2.TRANSACTIONSALEAMOUNT, CASE WHEN CTE2.AUDITID is null THEN FT.TRANSACTIONAMOUNT ELSE NULL END) TRANSACTIONSALEAMOUNT
        ,coalesce(CTE1.TRANSACTIONFEE, CTE2.TRANSACTIONFEE, CASE WHEN CTE2.AUDITID is null THEN RE.TRANSACTIONFEE ELSE NULL END) TRANSACTIONFEE
        ,coalesce(CTE1.TRANSACTIONLOWPRICE, CTE2.TRANSACTIONLOWPRICE, CASE WHEN CTE2.AUDITID is null THEN RE.TRANSACTIONLOWPRICE ELSE NULL END) TRANSACTIONLOWPRICE
        ,coalesce(CTE1.TRANSACTIONMEDIANPRICE, CTE2.TRANSACTIONMEDIANPRICE, CASE WHEN CTE2.AUDITID is null THEN RE.TRANSACTIONMEDIANPRICE ELSE NULL END) TRANSACTIONMEDIANPRICE
        ,coalesce(CTE1.TRANSACTIONHIGHPRICE, CTE2.TRANSACTIONHIGHPRICE, CASE WHEN CTE2.AUDITID is null THEN RE.TRANSACTIONHIGHPRICE ELSE NULL END) TRANSACTIONHIGHPRICE
        ,coalesce(CTE1.BASECURRENCYID, CTE2.BASECURRENCYID) BASECURRENCYID
        ,coalesce(CTE1.ORGANIZATIONEXCHANGERATEID, CTE2.ORGANIZATIONEXCHANGERATEID, CASE WHEN CTE2.AUDITID is null THEN FT.ORGEXCHANGERATEID ELSE NULL END) ORGANIZATIONEXCHANGERATEID
        ,coalesce(CTE1.TRANSACTIONCURRENCYID, CTE2.TRANSACTIONCURRENCYID, CASE WHEN CTE2.AUDITID is null THEN FT.TRANSACTIONCURRENCYID ELSE NULL END) TRANSACTIONCURRENCYID
        ,coalesce(CTE1.BASEEXCHANGERATEID, CTE2.BASEEXCHANGERATEID, CASE WHEN CTE2.AUDITID is null THEN FT.BASEEXCHANGERATEID ELSE NULL END) BASEEXCHANGERATEID
    from Audit_CTE [CTE1]
    left join Audit_CTE [CTE2] on (CTE1.AUDITRECORDID = CTE2.AUDITRECORDID) and (CTE1.AUDITTYPECODE != 2 and (((CTE1.[RANK] - 1 = CTE2.[RANK]) and (CTE2.FT_COUNT = 1)) or ((CTE1.[RANK] + 1 = CTE2.[RANK]) and (CTE2.RE_COUNT = 2))) or (CTE1.AUDITTYPECODE = 2 and CTE2.AUDITTYPECODE = 2 and CTE1.AUDITID != CTE2.AUDITID))
    left join dbo.FINANCIALTRANSACTION FT on FT.ID = CTE1.AUDITRECORDID
    left join dbo.STOCKSALE_EXT RE on RE.ID = CTE1.AUDITRECORDID;