Evaluating Derived Columns within a Select
-
Hi
I'm updating an old Access application to SQL Server and am currently trying to decipher one of the reports on the old application. It appears to be evaluating a derived column from one query (qryStudentSuspGroup.Suspension) in the Select statement of another. I have tried to put the query that creates the derived column in as a nested query into the other query but can't get it to work. This is all a bit beyond my rudimentary SQL skills! Any help would be greatly appreciated!
The original Access SQL appears below:
SELECT [Enter the academic year (4 digits)] AS [input], ResearchStudent.Department, ResearchStudent.DateAwarded,
ResearchStudent.StudentNumber, Person.Forenames AS fore, Person.Surname AS Sur, ResearchStudent.Mode,
ResearchStudent.RegistrationDate, StudentExamination.Decision,
IIf(([Suspension]) Is Null Or [Suspension]=0,([DateAwarded]-[RegistrationDate])/365,(([DateAwarded]-[RegistrationDate])-([Suspension]))/365) AS CompDate,
ResearchStudent.EnrollmentCategory, qryStudentSuspGroup.Suspension
FROM ((ResearchStudent LEFT JOIN Person ON ResearchStudent.ResearchStudentID = Person.PersonID)
LEFT JOIN qryStudentSuspGroup ON ResearchStudent.ResearchStudentID = qryStudentSuspGroup.ResearchStudentID)
LEFT JOIN StudentExamination ON ResearchStudent.ResearchStudentID = StudentExamination.ResearchStudentID
WHERE (((Year([DateAwarded]))>=[Enter the academic year (4 digits)]
And (Year([DateAwarded]))<=([Enter the academic year (4 digits)]+1))
AND ((IIf(Year([DateAwarded])=[Enter the academic year (4 digits)],Month([DateAwarded])>8,Month([DateAwarded])<9))<>False))
ORDER BY ResearchStudent.Department, ResearchStudent.Mode, ([DateAwarded]-[RegistrationDate])/365
-
Jude
I think all you need to do is turn your IIF statement into a CASE statement (look this up in Books Online) and alias your subquery, thus:
...
ResearchStudent.EnrollmentCategory, q.Suspension
FROM ((ResearchStudent LEFT JOIN Person ON ResearchStudent.ResearchStudentID = Person.PersonID)
LEFT JOIN qryStudentSuspGroup ON ResearchStudent.ResearchStudentID = qryStudentSuspGroup.ResearchStudentID)
LEFT JOIN StudentExamination ON ResearchStudent.ResearchStudentID = StudentExamination.ResearchStudentID
WHERE (((Year([DateAwarded]))>=[Enter the academic year (4 digits)]
And (Year([DateAwarded]))<=([Enter the academic year (4 digits)]+1))
AND ((IIf(Year([DateAwarded])=[Enter the academic year (4 digits)],Month([DateAwarded])>8,Month([DateAwarded])<9))<>False)) q
ORDER BY ResearchStudent.Department, ResearchStudent.Mode, ([DateAwarded]-[RegistrationDate])/365
John
-
Are you looking for SQL Server solution?
Translate Access to TSQL first
SELECT @YearInput AS [input],
s.Department,
s.DateAwarded,
s.StudentNumber,
p.Forenames AS fore,
p.Surname AS Sur,
s.Mode,
s.RegistrationDate,
x.Decision,
(DATEDIFF(day,RegistrationDate,DateAwarded)-ISNULL(Suspension,0)) / 365 AS [CompDate],
s.EnrollmentCategory,
g.Suspension
FROM ResearchStudent s
LEFT JOIN Person p
ON p.PersonID = s.ResearchStudentID
LEFT JOIN qryStudentSuspGroup g
ON g.ResearchStudentID = s.ResearchStudentID
LEFT JOIN StudentExamination x
ON x.ResearchStudentID = s.ResearchStudentID
WHERE (YEAR(DateAwarded) = @YearInput AND MONTH(DateAwarded > 8)
OR (YEAR(DateAwarded) = @YearInput+1 AND MONTH(DateAwarded < 9)
ORDER BY s.Department, s.Mode, DATEDIFF(year,RegistrationDate,DateAwarded)
You should be able to convert qryStudentSuspGroup to TSQL and put it in the query to replace qryStudentSuspGroup (put brackets around the query) or you could create the query as a view and substitue qryStudentSuspGroup with the view name
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks for your help both, I've got it working now.
-
One more question!
I'm trying to set up a modified version of this code as a view on my SQL Server database. I'm getting 2 errors, one says that the "Query Designer does not support the Case SQL Construct" but I have examples of Case being used in other views and have set it up to be the same.
It then goes on to give me the second error which says that "View definition includes no output columns or includes no items in the FROM clause".
I'm baffled by both of these errors!!! The statement I am trying to put into the view is as follows:
SELECT ResearchStudent.DateAwarded, ResearchStudent.StudentNumber, Person.Forenames AS fore, Person.Surname AS Sur, ResearchStudent.Mode, ResearchStudent.RegistrationDate, Department.Name, EnrolmentCategory.ECName, StudentExamination.Decision,
(CASE StudentSuspension.Suspension WHEN 'NULL' OR '0'
THEN DATEDIFF(Day, RegistrationDate, DateAwarded) / 365.0
ELSE (DATEDIFF(Day, RegistrationDate, DateAwarded) - StudentSuspension.Suspension) / 365.0 END) AS CompDate
FROM ResearchStudent
LEFT JOIN Person ON ResearchStudent.ResearchStudentID = Person.PersonID
LEFT JOIN Department ON ResearchStudent.DeptID = Department.ID
LEFT JOIN EnrolmentCategory ON ResearchStudent.EnrolmentCategoryID = EnrolmentCategory.ID
LEFT JOIN StudentExamination ON ResearchStudent.ResearchStudentID = StudentExamination.ResearchStudentID
LEFT JOIN
(SELECT StudentSuspension.ResearchStudentID, DATEDIFF(Day, StudentSuspension.StartDate, StudentSuspension.EndDate) AS Suspension
FROM StudentSuspension)
StudentSuspension ON ResearchStudent.ResearchStudentID = StudentSuspension.ResearchStudentID
ORDER BY Department.Name, ResearchStudent.Mode, DATEDIFF(day, [RegistrationDate], [DateAwarded]) / 365.0
-
You'll have to edit it in query analyser. The query builder doesn't support the use of the case statement (even when legal).
-
Thanks, that's working fine now.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply