T-sql 2012 add data row to temp table

Transact-SQL
1

In t-sql 2012 listed , I want to place rows into the temp table called #UserCalendar for the current school year and the future school. The current school year has the active=1.

Here is the current sql:

declare @userID int =5

CREATE TABLE #UserCalendar (
UserID int,
DistrictID int,
SchoolID int,
CalendarID int,
EndYear varchar(100),
SchoolYear varchar(100),
Active bit,
[Type] varchar(50),
SchoolName varchar(400),
Number varchar(300),
CalendarName varchar(400),
SummerSchool bit,
CatalogID int)

insert into #UserCalendar
exec procUSERCalendar @userID

select distinct EndYear,SchoolYear, Active from #UserCalendar
where active = 1
drop table #UserCalendar

The sql above will display data that looks like:

EndYear SchoolYear Active
2016 15-16 1

I want to somehow modify the sql with a union or whatever you come up with to display the data to be:
EndYear SchoolYear Active
2016 15-16 1
2017 16-17 0

Thus would you show me what I can do to obtain the sql so I can obtain the desired results?

2

This will do it, but I am not sure that this is what you want:
union all
select distinct EndYear+1, CAST(2000-EndYear AS CHAR(2)) + '-' + CAST(2000-EndYear+1 AS CHAR(2)), 0 from #UserCalendar
where active = 1

1 Like