September 19, 2007 at 7:44 pm
Hey,
Well having a bit of a struggle here so hoping you can help out.
I have 2 queries that return specific data sets that I would like to group together.
From the queries below I would like it to print the first row of the 1st query and then all rows of the 2nd query that much up to the specific student in the 1st query.
The first query is:
Declare @Year Int
Declare @Semester Int
Declare @YearLevel Int
Set @Year = 2007
Set @Semester = 3
Set @YearLevel = 11
Select '"2"' As 'RECORD_ID'
, Case s.StudentYearLevel
When 10 Then (s.FileYear + 2)
When 11 Then (s.FileYear + 1)
When 12 Then (s.FileYear)
End As 'COHORT'
, '""' As 'STUDENT_NR'
, Case s.StudentYearLevel
When 10 Then (s.FileYear + 2)
When 11 Then (s.FileYear + 1)
When 12 Then (s.FileYear)
End As 'CERT_YR'
, '"' + s.StudentSurname + '"' As 'SURNAME'
, '"' + s.StudentGiven1 + ' ' + s.StudentGiven2 + '"' As 'GIVEN_NAME'
, '"F"' As 'GENDER'
, '"' + Convert(VarChar(10), s.StudentBirthDate, 103) + '"' As 'DOB'
, Case
When Len(a.HomeAddress3) = 0 Then
Case
When Len(a.HomeAddress2) = 0 Then '"' + a.HomeAddress1 + '"'
When Len(a.HomeAddress2) > 0 Then '"' + a.HomeAddress1 + ' ' + a.HomeAddress2 + '"'
End
When Len(a.Address3) > 0 Then
Case
When Len(a.HomeAddress2) = 0 Then '"' + a.HomeAddress1 + ' ' + a.HomeAddress3 + '"'
When Len(a.HomeAddress2) > 0 Then '"' + a.HomeAddress1 + ' ' + a.HomeAddress2 + ' ' + a.HomeAddress3 + '"'
End
End As 'ADDRESS_L1'
, '"' + a.Suburb + '"' As 'ADDRESS_L2'
, '"' + a.State + '"' As 'ADDRESS_L3'
, a.PostCode As 'POSTCODE'
, '"N"' As 'SCHOOL_QCS'
, '' As 'HERRLEVEL'
, Case s.StudentYearLevel
When 10 Then Cast((s.FileYear + 1) As VarChar(4)) + Cast(1 As VarChar(1))
When 11 Then Cast((s.FileYear) As VarChar(4)) + Cast(1 As VarChar(1))
When 12 Then Cast((s.FileYear - 1) As VarChar(4)) + Cast(1 As VarChar(1))
End As 'START_SEM'
, '""' As 'OP_ELIG'
, '""' As 'FPA_ELIG'
, '""' As 'FPB_ELIG'
, '""' As 'FPC_ELIG'
, '""' As 'FPD_ELIG'
, '""' As 'FPE_ELIG'
, Case
When Len(s.StudentsVisaType) > 0 Then '"Y"'
When Len(s.StudentsVisaType) = 0 Then '"N"'
End As 'VISA_FLAG'
, '"N"' As 'YR11_FEES'
, '"N"' As 'YR12_FEES'
, '""' As 'VISA_EXEMP'
, '"N"' As 'DOC_SIGHT'
, '"00000000"' As 'ENTER_AUST'
, '"N"' As 'XFER_FLG'
, '"NORM"' As 'CONCESSION'
, '""' As 'FORM_CLASS'
, '"00000000"' As 'EXIT_DATE'
, '"' + Cast(s.StudentID As VarChar(6)) + '"' As 'LOC_STUDNR'
, Case
When s.IndigenousFlag = 0 Then
Case
When s.StudentTSIFlag = 0 Then '"N"'
When s.StudentTSIFlag = 1 Then '"I"'
End
When s.IndigenousFlag = 1 Then
Case
When s.StudentTSIFlag = 0 Then '"A"'
When s.StudentTSIFlag = 1 Then '"B"'
End
End As 'ATSI_FLAG'
, '"' + lc.ExternalSystemCode + '"' As 'CNTRYBRTH'
, '"' + ll.ExternalSystemCode + '"' As 'LANG_HOME'
, '""' As 'STUD_CHNGD'
, '""' As 'VALIDATED'
, '"N"' As 'TYS_FLAG'
, '""' As 'AUDIT_VAL'
, '"F"' As 'FULL_PART'
, '"N"' As 'APPRENTICE'
, '"N"' As 'PSTCMPCERT'
, '"00000000"' As 'PCCAPPRVL'
, '"' + a.HomePhone + '"' As 'HOME_PHONE'
, '"' + s.StudentMobilePhone + '"' As 'MOBILE_PH'
, '""' As 'HOME_EMAIL'
, '"' + a.OccupEmail + '"' As 'SCH_EMAIL'
From vStudents s
Inner Join vCommunityAddresses a
On (s.StudentID = a.ID)
Inner Join luCountry lc
On (s.StudentCountryOfBirthCode = lc.Code)
Inner Join luLanguage ll
On (s.StudentHomeLanguageCode = ll.Code)
Where s.StudentYearLevel > 9
And s.FileYear = @Year
And s.FileSemester = @Semester
And s.StudentYearLevel = @YearLevel
The second query is:
Declare @Year Int
Declare @Semester Int
Declare @YearLevel Int
Set @Year = 2007
Set @Semester = 3
Set @YearLevel = 11
Select '"4"' As 'RECORD_ID'
, Case s.StudentYearLevel
When 10 Then (s.FileYear + 2)
When 11 Then (s.FileYear + 1)
When 12 Then (s.FileYear)
End As 'COHORT'
, '"' + Cast(s.StudentID As VarChar(6)) + '"' As 'STUDENT_NR'
, sub.ClassID As 'SUBJ_ID'
,'""' As 'CLASS'
, '""' As 'LOA'
, '""' As 'SUBJ_SAI'
, '"1"' As 'SEM1_UNIT'
, '"2"' As 'SEM2_UNIT'
, '"3"' As 'SEM3_UNIT'
, '"4"' As 'SEM4_UNIT'
, '' As 'OVERIDUNITS'
, '' As 'UOC'
, '' As 'STMONTH'
, '' As 'STYEAR'
, '' As 'ENDMONTH'
, '' As 'ENDYEAR'
, '""' As 'EXITRESULT'
, '' As 'SEM1_WHEN'
, '' As 'SEM2_WHEN'
, '' As 'SEM3_WHEN'
, '' As 'SEM4_WHEN'
From vStudents s
Inner Join vStudentSubjectClass cl
On (s.StudentID = cl.StudentID)
And (s.FileYear = cl.FileYear)
And (s.FileSemester = cl.FileSemester)
Inner Join SubjectClasses sub
On (cl.ClassCode = sub.ClassCode)
And (cl.FileYear = sub.FileYear)
And (cl.FileSemester = sub.FileSemester)
Where s.FileYear = @Year
And s.FileSemester = @Semester
And s.StudentYearLevel = @YearLevel
And sub.ClassCode not like '%QCS%'
And sub.ClassCode not like '%SPR%'
And sub.ClassCode not like 'HG%'
Order By STUDENT_NR
And example result set would look like:
"2",2007,"",2007,"LastName1","FirstName1 SecondName1","F","10/10/1990","This is the address1","This is the suburb1","State1",9999,"N",1,20061,"N","N","N","N","N","N","N","N","N","","N","00000000","N","NORM","","08/05/2007","1014","N","","1201","20070622091751","20070913165120","N","14984344","P","N","N","00000000","HomePhone1","","","email1@domain.com",
"4",2007,"06514400012",1,"","","","1","2","3","4",,,,,,,"",,,,,
"4",2007,"06514400012",36,"","","","1","2","3","4",,,,,,,"",,,,,
"4",2007,"06514400012",65,"","","","1","2","","",,,,,,,"",,,,,
"2",2007,"06514400024",2007,"LastName2","FirstName1 SecondName2","F","01/01/1990","This is the address1","This is the suburb2","State2",9998,"N",0,20061,"Y","Y","Y","Y","N","N","N","N","N","","N","00000000","N","NORM","","00000000","1015","N","1100","1201","20061031115813","20070913165120","N","71965428","F","N","N","00000000","HomePhone2","","","email2@domain.com",
"4",2007,"06514400024",1,"","","","1","2","3","4",,4,,,,,"",,,,,
"4",2007,"06514400024",21,"","","","1","2","3","4",,4,,,,,"",,,,,
"4",2007,"06514400024",27,"","","","1","2","3","4",,4,,,,,"",,,,,
"4",2007,"06514400024",37,"","","","1","2","3","4",,4,,,,,"",,,,,
"4",2007,"06514400024",40,"","","","1","2","3","4",,4,,,,,"",,,,,
"4",2007,"06514400024",86,"","","","1","2","3","4",,4,,,,,"",,,,,
Thanks
Mark
September 19, 2007 at 7:51 pm
How many columns in you result set?
_____________
Code for TallyGenerator
September 19, 2007 at 7:55 pm
The first query has 47 columns and the 2nd query has 22.
Thanks
Mark
September 19, 2007 at 8:12 pm
So, how many columns in final result set?
_____________
Code for TallyGenerator
September 19, 2007 at 8:26 pm
The final result set would be 47, so I would have to add columns to the 2nd query for this to occur. Still wondering whether it is possible, if I did add the extra columns to the 2nd query, to join the 2 result sets as i specified in the first post?
September 19, 2007 at 9:43 pm
As soon as you add missing columns to 2nd record set you need to use UNION ALL to unite them together and sort final result by some key value which defines which "details" lines belong to which "header" lines.
_____________
Code for TallyGenerator
September 19, 2007 at 10:27 pm
Any suggestions on the code which would enable that sort by its "header" and "details"?
September 19, 2007 at 11:12 pm
You must tell what binds "header" row to "details" rows.
If you know this key then it's simple:
SELECT
{record set 1)
UNION ALL
SELECT
{record set 2)
ORDER BY KeyColumn
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply