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 datetimeselect
TOPIC = t.TopicName,
CustomTitle = e.ssCustomTitle,
StartTime = v.StartDateTime,
eFirstName = FirstName,
eLastName = LastName,
eEndTime = ssEndTimeINTO #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 ssStartTimeSELECT @.Topic = Topic,
@.CustomTitle = CustomTitle,
@.FullName = eFirstname + ' ' + eLastName,
@.StartTime = starttime,
@.EndTime = eEndTime
From #tmpworkIF (@.CustomTitle is not null)
IF (not @.CustomTitle = '') --correct problem of ZLS
Begin
set @.Topic = @.CustomTitle
EndSELECT
Topic = @.Topic,
StartTime = @.StartTime,
FullName = @.FullName,
EndTime = @.endtimeINTO #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.dt1PresentationStyleIdWHERE
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.
sql
DECLARE @.Topic varchar(100)
DECLARE @.CustomTitle varchar(150)
DECLARE @.starttime datetime
DECLARE @.tmpname varchar(100)
DECLARE @.eventno int
DECLARE @.endtime datetime
selectTOPIC = 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.dt1PresentationStyleIdWHERE
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
BeginWhile (Select Count(*) From #tmpwork) > 0
BeginSelect @.topic = Topic,
@.starttime = StartTime,
@.tmpname = FullName,
@.eventno = EventNo,
@.endtime = EndTime
From #tmpworkIf (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
EndSelect 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, EventNoDrop Table #tmpwork
DROP Table #Final
