EXCEPT function using on 2 temp tables results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

EXCEPT function using on 2 temp tables results

Author

Topic

davidvo68
Starting Member

2 Posts

Posted - 2012-09-19 : 19:13:30

I am having a difficult time trying to get fields on the second temp table to show up on the output.
I am trying to get the result of this field [b][EnteredByStaffSID][/b] from #Courses temp database to show on the result after the script is done but I am not able to.

if object_id ('tempdb..#Appointments') is not null drop table #Appointements
if object_id ('tempdb..#Courses') is not null drop table #Courses
if object_id ('tempdb..#CombineTwoTables') is not null drop table #CombineTwoTables

DECLARE @StartApptDate datetime;
DECLARE @EndApptDate datetime;
DECLARE @StartCourseDate datetime;
DECLARE @EndCourseDate datetime;
SET @StartApptDate = '2012-09-12 00:00:01';
SET @EndApptDate = '2012-09-12 23:59:59';
SET @StartCourseDate = '2012-08-12 00:00:01';
SET @EndCourseDate = '2012-09-26 23:59:59';

SELECT DISTINCT
B.[LocationName]
,A.Sta3n
,A.[LocationIEN]
,C.[StudentName]
,A.[StudemtSID]
,[AppointmentDateTime]
,[DataEntryStaffSID]
,D.[StaffName]
INTO #Appointments
FROM [LSV].[Appt].[Appointment] AS A INNER JOIN [LSV].[dim].[Location] AS B
ON A.[LocationSID] = B.[LocationSID]
INNER JOIN [LSV].[SStudent].[SStudent] AS C
ON A.[StudentSID] = C.[StudentSID]
INNER JOIN [LSV].[SStaff].[SStaff] AS D
ON A.[DataEntryStaffSID]= D.[StaffSID]

WHERE A.[AppointmentDateTime]BETWEEN @StartApptDate AND @EndApptDate AND
B.[LocationIEN] IN ('6253','4378','6205','5854')
AND A.[CancellationReason] IS NULL
AND A.Sta3n = 516
ORDER BY A.[StudentSID] ASC;

SELECT
,A.[StudentSID]
,A.[EnteredByStaffSID]
,A.[EnteredDateTime]
,A.[CourseStatusSID]
,E.[StaffName]
,A.[CourseStartDateTime]
,A.[CourseStopDateTime]

INTO #Courses
FROM [LSV].[CPRSOrder].[CPRSOrder]AS A
INNER JOIN [LSV].[CPRSOrder].[OrderedItem] AS B
ON A.[CPRSOrderSID] = B.[CPRSOrderSID]
INNER JOIN [LSV].[dim].[OrderableItem]AS C
ON B.OrderableItemSID = C.OrderableItemSID
INNER JOIN [LSV].[dim].[CourseStatus] AS D
ON A.[CourseStatusSID]= D.[OrderStatusSID]
INNER JOIN [LSV].[SStaff].[SStaff]AS E
ON A.[OrderStaffSID] = E.[StaffSID]
WHERE A.[OrderStartDateTime]BETWEEN @StartCourseDate AND @EndCourseDate
AND A.[PackageSID]='1200006012'
AND D.[CourseStatusShortName]in('pend', 'actv')

ORDER BY A.[StudentSID] ASC;

SELECT
#Appointments.StudentSID

INTO #CombineTwoTables
FROM #Appointments
EXCEPT
SELECT
#Courses.PatientSID
FROM #Courses
SELECT * FROM #Appointments
INNER JOIN #CombineTwoTables ON #CombineTwoTables.StudentSID = #Appointments.StudentSID

DROP TABLE #Appointments;
DROP TABLE #Courses;
DROP TABLE #CombineTwoTables;

David

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-09-19 : 19:57:30

This is expected. The tables in the EXCEPT list are only used to eliminate records from the original SELECT list. This is similar to using a "colA NOT IN (select PatientSID from #Courses)" construct in that you wouldn't expect to output the data from the NOT IN select.

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

davidvo68
Starting Member

2 Posts

Posted - 2012-09-20 : 13:29:50

So how would I code so that the field of EnteredByStaffSID from the Courses table to print for my output?
Thank you for your input.

David

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-20 : 23:36:11

for that you need to add a separate select on #Courses

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources