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
x
;
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;
Advertisement
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;