CODEWITHSUNDEEP
sql-server-2008-r2recursive-query
NonProgrammer
NonProgrammer

Reputation: 1387

Create Data for missing years in SQL Server 2008R2

It is mandatory that I include data for years 2017-2020. However, for some records there is no data for these years. Is there a way to populate it?

Here's the sample table structure:

 CREATE TABLE #Temp
 (
 RefLeaseAssumptionID    VARCHAR(30),
 Scenario   VARCHAR(15),
 RefBuildingID    VARCHAR(15),
 BuildingName           Varchar(50),
 TenantName    varchar(50),
 UnitID      varchar(15),
 StartDate   date,
 EndDate  date,
 Area int,
 Years  int,
 GAAPRevenue  int
)

INSERT INTO #Temp VALUES ('20400-TSPECTENB-LSPECTENB-0190','2017_B03', '20400', 'ABCD-123', 'New Tenant (spec)',  '2700', '2019-05-01', '2024-04-30', 45788, 2019, 800000 ) 
INSERT INTO #Temp VALUES ('20400-TSPECTENB-LSPECTENB-0190','2017_B03', '20400', 'ABCD-123', 'New Tenant (spec)',  '2700', '2019-05-01', '2024-04-30', 45788, 2020, 900000 ) 
INSERT INTO #Temp VALUES ('45000-TSPECTENB-LSPECTENB-0100','2017_B03', '45000', 'WXYZ-123', 'New Tenant (spec)',  '0100', '2017-05-01', '2018-05-30', 20000, 2017, 300000 ) 
INSERT INTO #Temp VALUES ('45000-TSPECTENB-LSPECTENB-0100','2017_B03', '45000', 'WXYZ-123', 'New Tenant (spec)',  '0100', '2017-05-01', '2018-05-30', 20000, 2018, 350000 ) 
INSERT INTO #Temp VALUES ('25000-TSPECTENB-LSPECTENB-1','2017_B03', '25000', 'XZZZ-123', 'New Tenant (spec)',  '0300', '2017-05-01',    '2020-10-31', 10000, 2018, 100000 ) 
INSERT INTO #Temp VALUES ('25000-TSPECTENB-LSPECTENB-1','2017_B03', '25000', 'XZZZ-123', 'New Tenant (spec)',  '0300', '2017-05-01',    '2020-10-31', 10000, 2019, 125000 ) 
INSERT INTO #Temp VALUES ('25000-TSPECTENB-LSPECTENB-1','2017_B03', '25000', 'XZZZ-123', 'New Tenant (spec)',  '0300', '2017-05-01',    '2020-10-31', 10000, 2020, 170000 )

Result I am looking for:

Notice the difference between #temp and #temp2 is additional rows with the years missing and 0 for the GAAPRevenue.

CREATE TABLE #Temp2
(
 RefLeaseAssumptionID    VARCHAR(30),
 Scenario   VARCHAR(15),
 RefBuildingID    VARCHAR(15),
 BuildingName           Varchar(50),
 TenantName    varchar(50),
 UnitID      varchar(15),
 StartDate   date,
 EndDate  date,
 Area int,
 Years  int,
 GAAPRevenue  int
)

INSERT INTO #Temp2 VALUES ('20400-TSPECTENB-LSPECTENB-0190','2017_B03', '20400', 'ABCD-123', 'New Tenant (spec)',  '2700', '2019-05-01',    '2024-04-30', 45788, 2017, 0 ) 
INSERT INTO #Temp2 VALUES ('20400-TSPECTENB-LSPECTENB-0190','2017_B03', '20400', 'ABCD-123', 'New Tenant (spec)',  '2700', '2019-05-01',    '2024-04-30', 45788, 2018, 0 ) 
INSERT INTO #Temp2 VALUES ('20400-TSPECTENB-LSPECTENB-0190','2017_B03', '20400', 'ABCD-123', 'New Tenant (spec)',  '2700', '2019-05-01',    '2024-04-30', 45788, 2019, 800000 ) 
INSERT INTO #Temp2 VALUES ('20400-TSPECTENB-LSPECTENB-0190','2017_B03', '20400', 'ABCD-123', 'New Tenant (spec)',  '2700', '2019-05-01',    '2024-04-30', 45788, 2020, 900000 ) 
INSERT INTO #Temp2 VALUES ('45000-TSPECTENB-LSPECTENB-0100','2017_B03', '45000', 'WXYZ-123', 'New Tenant (spec)',  '0100', '2017-05-01',    '2018-05-30', 20000, 2017, 300000 ) 
INSERT INTO #Temp2 VALUES ('45000-TSPECTENB-LSPECTENB-0100','2017_B03', '45000', 'WXYZ-123', 'New Tenant (spec)',  '0100', '2017-05-01',    '2018-05-30', 20000, 2018, 350000 ) 
INSERT INTO #Temp2 VALUES ('45000-TSPECTENB-LSPECTENB-0100','2017_B03', '45000', 'WXYZ-123', 'New Tenant (spec)',  '0100', '2017-05-01',    '2018-05-30', 20000, 2019, 0 ) 
INSERT INTO #Temp2 VALUES ('45000-TSPECTENB-LSPECTENB-0100','2017_B03', '45000', 'WXYZ-123', 'New Tenant (spec)',  '0100', '2017-05-01',    '2018-05-30', 20000, 2020, 0 ) 
INSERT INTO #Temp2 VALUES ('25000-TSPECTENB-LSPECTENB-1','2017_B03', '25000', 'XZZZ-123', 'New Tenant (spec)',  '0300', '2017-05-01',   '2020-10-31', 10000, 2017, 0 ) 
INSERT INTO #Temp2 VALUES ('25000-TSPECTENB-LSPECTENB-1','2017_B03', '25000', 'XZZZ-123', 'New Tenant (spec)',  '0300', '2017-05-01',   '2020-10-31', 10000, 2018, 100000 ) 
INSERT INTO #Temp2 VALUES ('25000-TSPECTENB-LSPECTENB-1','2017_B03', '25000', 'XZZZ-123', 'New Tenant (spec)',  '0300', '2017-05-01',   '2020-10-31', 10000, 2019, 125000 ) 
INSERT INTO #Temp2 VALUES ('25000-TSPECTENB-LSPECTENB-1','2017_B03', '25000', 'XZZZ-123', 'New Tenant (spec)',  '0300', '2017-05-01',   '2020-10-31', 10000, 2020, 170000 )

Upvotes: 0

Views: 31

Answers (1)

DancingFool
DancingFool

Reputation: 1267

This has a manual list of the required years (the years CTE), and gets a list of the distinct leases (the Leases CTE). Then you cross join the two lists to get all the required rows, left outer join to the existing rows, and ignore any that have a match. This select gets you all the missing data.

I have added the insert to add the missing rows back to #Temp, but commented the line out. To actually add the data, just uncomment the row before running.

You could also write this with the CTEs replaced with an inline subquery if you prefer.

With Years as 
(Select 2017 as YearNo 
UNION ALL Select 2018
UNION ALL Select 2019
UNION ALL Select 2020
), Leases as (
Select distinct RefLeaseAssumptionID, Scenario, RefBuildingID, BuildingName, TenantName, UnitID, StartDate, EndDate, Area
From #Temp1
)
--Insert into #Temp (RefLeaseAssumptionID, Scenario, RefBuildingID, BuildingName, TenantName, UnitID, StartDate, EndDate, Area, Year, GAAPRevenue
Select L.RefLeaseAssumptionID, L.Scenario, L.RefBuildingID, L.BuildingName, L.TenantName, L.UnitID, L.StartDate, L.EndDate, L.Area, Y.YearNo, 0
from Leases L
    Cross Join Years Y
    left outer join #Temp T1 on T1.RefLeaseAssumptionID = L.RefLeaseAssumptionID and T1.Years = Y.YearNo
Where T1.RefLeaseAssumptionID is Null;

Upvotes: 1

Related Questions