Problem with SQL Query not moving through every record

I was wondering if anyone could quickly identify why the query is not parsing through each line in the temp table? I am sure its something stupid and easy, but if anyone has an idea, I would greatly appreciate the help!

My results are the same row repeated exactly the same for the number of rows in the temp table.

Declare @.Topic varchar(150)
Declare @.CustomTitle varchar(150)
Declare @.FullName varchar(100)
Declare @.starttime datetime
Declare @.endtime datetime
select
TOPIC = t.TopicName,
CustomTitle = e.ssCustomTitle,
StartTime = v.StartDateTime,
eFirstName = FirstName,
eLastName = LastName,
eEndTime = ssEndTime
INTO #tmpwork
FROM
brSession e
INNER JOIN v_SessionStartDateTime v on v.ssSessionId=e.ssSessionId
LEFT OUTER JOIN Topic t on t.TopicId=e.ssTopicId
LEFT JOIN brssPresenter ep ON (e.ssSessionID = ep.prSessionId)
LEFT OUTER JOIN Personnel p on p.PersonnelNbr=ep.prPerNbr
LEFT OUTER JOIN brVirtualRoom vr ON vr.vrBriefingId=e.ssBriefingId AND vr.vrVirtualRoomId=e.ssVirtualRoomId
LEFT OUTER JOIN brLocation bl ON bl.loBriefingId=vr.vrBriefingId AND bl.loVirtualRoomId=vr.vrVirtualRoomId
LEFT OUTER JOIN location BR ON BR.LocationId=bl.loLocationId
LEFT OUTER JOIN brssDetail1 dt on dt.dt1SessionId=e.ssSessionId
LEFT OUTER JOIN Competitor c on CompetitorId=dt.dt1CompetitorId
LEFT OUTER JOIN PresentationStyle ps ON ps.PresentationStyleId=dt.dt1PresentationStyleId
WHERE
e.ssBriefingID = 11749
and((not prConfirmModeId = 0) or (prPerNbr is null))
ORDER BY ssStartTime
SELECT @.Topic = Topic,
@.CustomTitle = CustomTitle,
@.FullName = eFirstname + ' ' + eLastName,
@.StartTime = starttime,
@.EndTime = eEndTime
From #tmpwork
IF (@.CustomTitle is not null)
IF (not @.CustomTitle = '') --correct problem of ZLS
Begin
set @.Topic = @.CustomTitle
End
SELECT
Topic = @.Topic,
StartTime = @.StartTime,
FullName = @.FullName,
EndTime = @.endtime
INTO #Final
FROM #tmpwork
select * from #Final
drop table #tmpwork
drop table #Final

SELECT @.Topic = Topic,
@.CustomTitle = CustomTitle,
@.FullName = eFirstname + ' ' + eLastName,
@.StartTime = starttime,
@.EndTime = eEndTime
From #tmpwork

... that doesn't give you an error? That's almost surprising. In this case, I'd have used either a loop or cursors to accomplish it.

Actually... you can combine so much of that into just one giant sql call rather than running temp tables and such.

select
TOPIC = IsNull(e.ssCustomTitle, t.TopicName)
StartTime = v.StartDateTime,
FullName = FirstName + ' ' + LastName,
eEndTime = ssEndTime
INTO #tmpwork
FROM
brSession e
INNER JOIN v_SessionStartDateTime v on v.ssSessionId=e.ssSessionId
LEFT OUTER JOIN Topic t on t.TopicId=e.ssTopicId
LEFT JOIN brssPresenter ep ON (e.ssSessionID = ep.prSessionId)
LEFT OUTER JOIN Personnel p on p.PersonnelNbr=ep.prPerNbr
LEFT OUTER JOIN brVirtualRoom vr ON vr.vrBriefingId=e.ssBriefingId AND vr.vrVirtualRoomId=e.ssVirtualRoomId
LEFT OUTER JOIN brLocation bl ON bl.loBriefingId=vr.vrBriefingId AND bl.loVirtualRoomId=vr.vrVirtualRoomId
LEFT OUTER JOIN location BR ON BR.LocationId=bl.loLocationId
LEFT OUTER JOIN brssDetail1 dt on dt.dt1SessionId=e.ssSessionId
LEFT OUTER JOIN Competitor c on CompetitorId=dt.dt1CompetitorId
 LEFT OUTER JOIN PresentationStyle ps ON ps.PresentationStyleId=dt.dt1PresentationStyleId
WHERE
e.ssBriefingID = 11749
and((not prConfirmModeId = 0) or (prPerNbr is null))
ORDER BY ssStartTime

seems easier than having that and another temp table just to do one or two things.

look into IIF(expression, true, false) and IsNull(field, replacement) methods to streamline your sql to optimum executions.

books online is also a good source of information.|||Thanks for the help. This forum has saved my butt on countless occasions.

The reason I was going through and creating the second temp table was because I needed it to write the word 'Multiple' if the Topic had to speakers (fullName). So my thought was to simply do the first one where I get just the data I want, and on the second table go through and convert the Data into the format I needed it. Im sure there is a way to convert the data on its way into the first tmp table, however I am not sure of the best way to approach it.|||I ended up making the second temp table to accomplish the task. Perhaps there is an easier way, but this seemed to be the easiest way to do it. FWIW, here is the finished code, maybe it will help someone else as well.

DECLARE @.Topic varchar(100)
DECLARE @.CustomTitle varchar(150)
DECLARE @.starttime datetime
DECLARE @.tmpname varchar(100)
DECLARE @.eventno int
DECLARE @.endtime datetime
select
TOPIC = IsNull(e.ssCustomTitle, t.TopicName),
StartTime = v.StartDateTime,
FullName = FirstName + ' ' + LastName,
EndTime = ssEndTime,
EventNo = e.ssSessionId
INTO #tmpwork
FROM
brSession e
INNER JOIN v_SessionStartDateTime v on v.ssSessionId=e.ssSessionId
LEFT OUTER JOIN Topic t on t.TopicId=e.ssTopicId
LEFT JOIN brssPresenter ep ON (e.ssSessionID = ep.prSessionId)
LEFT OUTER JOIN Personnel p on p.PersonnelNbr=ep.prPerNbr
LEFT OUTER JOIN brVirtualRoom vr ON vr.vrBriefingId=e.ssBriefingId AND vr.vrVirtualRoomId=e.ssVirtualRoomId
LEFT OUTER JOIN brLocation bl ON bl.loBriefingId=vr.vrBriefingId AND bl.loVirtualRoomId=vr.vrVirtualRoomId
LEFT OUTER JOIN location BR ON BR.LocationId=bl.loLocationId
LEFT OUTER JOIN brssDetail1 dt on dt.dt1SessionId=e.ssSessionId
LEFT OUTER JOIN Competitor c on CompetitorId=dt.dt1CompetitorId
 LEFT OUTER JOIN PresentationStyle ps ON ps.PresentationStyleId=dt.dt1PresentationStyleId
WHERE
e.ssBriefingID = 11749
and((not prConfirmModeId = 0) or (prPerNbr is null))
ORDER BY ssStartTime
Create Table #Final(
 Topic varchar(180),
 StartTime datetime,
 FullName varchar(100) NULL,
 EventNo int,
 EndTime datetime, RoomNo int
)
If (Select Count(*) From #tmpwork) > 0
Begin
While (Select Count(*) From #tmpwork) > 0
Begin
Select @.topic = Topic,
@.starttime = StartTime,
@.tmpname = FullName,
@.eventno = EventNo,
@.endtime = EndTime
From #tmpwork
If (Select Count(*) From #tmpwork Where EventNo = @.eventno) > 1
Insert Into #Final Values(@.topic, @.starttime, 'Multiple', @.eventno, @.endtime, null)
Else
Insert Into #Final Values(@.topic, @.starttime, @.tmpname, @.eventno, @.endtime, null)
Delete #tmpwork Where EventNo = @.eventno
End
End
Select EventNo,
[Time] = SubString(Convert(varchar(20), StartTime), 13, 8),
EndTime = SubString(Convert(varchar(20), EndTime), 13, 8),
Topic,
FullName,
[Date] = Convert(varchar(20), StartTime, 107),
[WeekDay] = Datename(weekday,StartTime)
From #Final
Order By StartTime, EventNo
Drop Table #tmpwork
DROP Table #Final

sql