how to change a CTE to a view

I have some code below that is a CTE looking at a view Within a CTE within a CTE. i trying to re code this ( not my code) to have a view look at a view so i can use in a application that dose not support CTEs

    ;
WITH C1 AS (
        SELECT CTenancyPK,CdblRentalAmount, ts, Type
            ,e=CASE Type WHEN 1 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY CTenancyPK, Type ORDER BY LTOdteVacatingDate) END
            ,s=CASE Type WHEN -1 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY CTenancyPK, Type ORDER BY LTOdteOccupiedDate) END
            ,se=ROW_NUMBER() OVER (PARTITION BY CTenancyPK ORDER BY ts, Type DESC)
        FROM dav.LTOwCompany
        CROSS APPLY (
            VALUES (1, LTOdteOccupiedDate), (-1, LTOdteVacatingDate)) a(Type, ts)
        ),

    C3 AS (
        SELECT CTenancyPK,CdblRentalAmount, ts
            ,grpnm=FLOOR((ROW_NUMBER() OVER (PARTITION BY CTenancyPK ORDER BY ts)-1) / 2 + 1)
        FROM C1
        WHERE COALESCE(s-(se-s)-1, (se-e)-e) = 0),
    -- C1, C2, C3, C4 combined remove the overlapping date periods
    C4 AS (
        SELECT CTenancyPK,CdblRentalAmount, datestart=MIN(ts), dateend=MAX(ts)
        FROM C3
        GROUP BY CTenancyPK, grpnm, CdblRentalAmount)


SELECT qryAddress.Address, CTenancyPK, VoidDateStart=MIN(newdate), VoidDateEnd=MAX(newdate),CdblRentalAmount,VoidCost = (CdblRentalAmount * 12 / 365) * case when '2018-04-01' between MIN(newdate) and MAX(newdate) then  datediff(dd,'2018-04-01',MAX(newdate)+1)
else datediff(dd,MIN(newdate),MAX(newdate)+1)
end ,
case when '2018-04-01' between MIN(newdate) and MAX(newdate) then  datediff(dd,'2018-04-01',MAX(newdate)+1)
else datediff(dd,MIN(newdate),MAX(newdate)+1)
end as NoDays

FROM (
    SELECT CTenancyPK,CdblRentalAmount, newdate
        ,rn=ROW_NUMBER() OVER (PARTITION BY CTenancyPK ORDER BY newdate) / 2
    FROM C4 a
    CROSS APPLY (
        VALUES (datestart-1),(dateend+1)) b(newdate)
    ) a
Join Tenancy on Tenancy.TenancyPK = CTenancyPK
Join Property on Property.PropertyPK = Tenancy.PropertyFK
Join qryAddress on qryAddress.AddressPK = Property.AddressFK

GROUP BY Address,CTenancyPK, rn, CdblRentalAmount
HAVING COUNT(*) = 2 and MAX(newdate) >= '2018-04-01' 
ORDER BY CTenancyPK, Voiddatestart;

I want to see is something like this

SELECT qryAddress.Address, CTenancyPK, VoidDateStart=MIN(newdate), VoidDateEnd=MAX(newdate),CdblRentalAmount,VoidCost = (CdblRentalAmount * 12 / 365) * case when '2018-04-01' between MIN(newdate) and MAX(newdate) then  datediff(dd,'2018-04-01',MAX(newdate)+1)
else datediff(dd,MIN(newdate),MAX(newdate)+1)
end ,
case when '2018-04-01' between MIN(newdate) and MAX(newdate) then  datediff(dd,'2018-04-01',MAX(newdate)+1)
else datediff(dd,MIN(newdate),MAX(newdate)+1)
end as NoDays

FROM Dav.LTOvLTO 
-- New view that dose the CTE calculation and looks at Dav.LTOwCompany

Join Tenancy on Tenancy.TenancyPK = CTenancyPK
Join Property on Property.PropertyPK = Tenancy.PropertyFK
Join qryAddress on qryAddress.AddressPK = Property.AddressFK
GROUP BY Address,CTenancyPK, rn, CdblRentalAmount
HAVING COUNT(*) = 2 and MAX(newdate) >= '2018-04-01' 
ORDER BY CTenancyPK, Voiddatestart;

Answer

You can use CTEs with views:

CREATE VIEW v AS
WITH C1 AS (
        SELECT CTenancyPK,CdblRentalAmount, ts, Type
            ,e=CASE Type WHEN 1 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY CTenancyPK, Type ORDER BY LTOdteVacatingDate) END
            ,s=CASE Type WHEN -1 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY CTenancyPK, Type ORDER BY LTOdteOccupiedDate) END
            ,se=ROW_NUMBER() OVER (PARTITION BY CTenancyPK ORDER BY ts, Type DESC)
        FROM dav.LTOwCompany
        CROSS APPLY (
            VALUES (1, LTOdteOccupiedDate), (-1, LTOdteVacatingDate)) a(Type, ts)
        ),

    C3 AS (
        SELECT CTenancyPK,CdblRentalAmount, ts
            ,grpnm=FLOOR((ROW_NUMBER() OVER (PARTITION BY CTenancyPK ORDER BY ts)-1) / 2 + 1)
        FROM C1
        WHERE COALESCE(s-(se-s)-1, (se-e)-e) = 0),
    -- C1, C2, C3, C4 combined remove the overlapping date periods
    C4 AS (
        SELECT CTenancyPK,CdblRentalAmount, datestart=MIN(ts), dateend=MAX(ts)
        FROM C3
        GROUP BY CTenancyPK, grpnm, CdblRentalAmount)
    SELECT *
    FROM c4;

Advertisement

Answer