t-sql 2012 add data row to temp table

Question

t-sql 2012 add data row to temp table

wendy elizabeth

SSCoach

Points: 16915
More actions
June 29, 2016 at 2:45 pm

#327908

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?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Maybe remove the WHERE clause that's filtering out the inactive rows? A guess, because you haven't posted the definition of the procedure, so I have no idea what it inserts into the temp table.

Oh, and drop the DISTINCT unless there's a good and valid reason to have it there.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

wendy elizabeth SSCoach Points: 16915 More actions · Answer 2

I want to select the active year where the value = 1. That currently is end year = 2016. I do not want any of the other columns that have value=0 except for where the EndYear = 2017.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

select EndYear, SchoolYear, Active from #UserCalendar

where (active = 1 OR EndYear = 2017)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass