June 7, 2014 at 2:09 am
In 2008 t-sql I have the sql listed below where I obtain results by calling a stored procedure [test].[dbo].[sroom]. I am joining the results from calling a stored procedure with an existing table called Transactions. I am joining the 2 tables by the value by the SchoolNumber and the school.
The problem is that sometimes there is more than one row returned by the stored procedure by the studentNumber. The student number is not a unique key. The problem is that more than one studentNumber data row can be returned by the query below. My problem is that I only want 1 row for each studentnumber.
I have tried to select top 1 for the sql listed below with no success. Thus can you show me modifed sql listed below where only one row for each unique studentNumber in the temp table called #hResults is joined to the test.dbo.Transactions table? The user does not care what row is returned by studentNumber they just want one row returned.
IF OBJECT_ID('tempdb..#hResults') IS NOT NULL
DROP table studentNumber
CREATE TABLE #hResults(
personID INT,
studentNumber VARCHAR(15),
firstName VARCHAR(35),
middleName VARCHAR(30),
lastName VARCHAR(40),
suffix VARCHAR(50),
fullName VARCHAR(108),
enrollmentID INT,
structureID INT,
grade VARCHAR(4),
calendarID INT,
calendarName VARCHAR(30),
endYear SMALLINT,
schoolNumber VARCHAR(7),
schoolName VARCHAR(40),
courseID INT,
courseNumber VARCHAR(13),
courseName VARCHAR(30),
sectionID INT,
sectionNumber SMALLINT,
homeroomSection BIT,
teacherDisplay VARCHAR(30),
teacherPersonID INT)
INSERT #hResults
EXEC [test].[dbo].[sroom] @endYear, @calendarID, N'C'
SELECT s.SchoolNumber,
s.SchoolName,
homeroom.courseNumber ,
homeroom.sectionNumber AS Section,
homeroom.teacherDisplay as Teacher,
f.StudentNumber,
replace(StudentLastName,'"','') + ' ' + replace(StudentFirstName,'"','')
as 'Student Name'
,TransactionAmount
,CONVERT(smalldatetime,substring(TransactionPaymentDate,5,4) + '-' +
substring (TransactionPaymentDate,1,2)+ '-' +
substring(TransactionPaymentDate,3,2)) as TransactionPaymentDate
from test.dbo.Transactions f
INNER JOIN
test.dbo.Schools s
on rtrim(ltrim(s.SchoolNumber)) =rtrim(ltrim( f.SchoolNumber))
LEFT JOIN #hResults homeroom
ON homeroom.studentNumber = f.StudentNumber
WHERE rtrim(ltrim(s.SchoolNumber)) = rtrim(ltrim(@SchoolNumber))
AND
Cast(Right(TransactionPaymentDate,4)+Left(TransactionPaymentDate,4) as
Date)
>= @StartDate
AND
Cast(Right(TransactionPaymentDate,4)+Left(TransactionPaymentDate,4) as Date)
<= @EndDate
group by s.SchoolNumber,
s.SchoolName,
homeroom.courseNumber,
homeroom.sectionNumber,
homeroom.roomName,
homeroom.teacherDisplay,
f.StudentNumber,
replace(StudentLastName,'"','') + ' ' +
replace(StudentFirstName,'"','')
,TransactionAmount
,TransactionPaymentDate
,TransactionPaymenttime
,PaymentType
,EventNumber
homeroom.courseNumber,
homeroom.sectionNumber,
homeroom.roomName,
homeroom.teacherDisplay,
StudentNumber,
TransactionPaymentDate
IF OBJECT_ID('tempdb..#hResults') IS NOT NULL
DROP table #hResults
Thus can you show me modifed sql listed above where only one row for each unique studentNumber in the temp table called #hResults is joined to the test.dbo.Transactions table?
June 7, 2014 at 9:58 am
Not really sure what you are looking for here since we can't see what you see.
It would help if you could post the DDL for the tables involved (including index definitions), sample data for the tables (INSERT statements), and the expected results based on the sample data. By seeing what you are working with and what you are expecting in return we can help you get the code you need and have it tested as well.
June 9, 2014 at 8:43 am
I will give the dat structures next time.
June 9, 2014 at 11:26 am
the temp table #hResults is returning multiple rows for each student number, and for each student number, you're making use of distinct values of homeroom.courseNumber, sectionNumber, roomName, teacherDisplay, etc...
Once you choose to get only one row for each student from #hResults, you'll have to choose _which_ courseNumber, sectionNumber, roomName, teacherDisplay etc for each respective and distinct student number in #hResults.
Is this whats needed?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply