I have a reporting system which our students are required to utilize. I need to run a report off the database (which I developed) that links to an external database (our Student Information System).
My database doesn't use names, just student IDs. I need to query the SIS for the student's first and last name. Below is the query I am using -- which is working to give me all of the report entries and whether or not they were on-time or late. What I am not getting are the missing entries. We need to no if a student did not report at all for that week. I feel like I'm close, but I'm not sure what I'm doing wrong. I will include some sample data and the SQL query.
WITH students (StudentID) AS
SELECT n.soc_sec AS [StudentID]
FROM [LINKEDSERVER].[SIS].[dbo].[name] n INNER JOIN [LINKEDSERVER].[SIS].[dbo].[nmcrs] c
ON n.soc_sec = c.soc_sec
WHERE c.course='REQU0007' AND c.enrollcode='EN' AND c.sch_yr='201415' AND c.semester='1'
reportstd (Report, ReportPeriod, LateOn) AS
SELECT CONVERT(varchar(10),s.reportOpen,102) AS [Report],
CONVERT(varchar(10),s.reportBegin,102) + ' - ' +
CONVERT(varchar(10),s.reportEnd,102) AS [ReportPeriod],
s.lateOn AS [LateOn]
FROM reports s
WHERE s.reportOpen BETWEEN '08/01/2014' AND GETDATE()
SELECT nm.soc_sec AS [StudentID],
nm.last_name AS [LastName],
nm.first_name AS [FirstName],
[Status] = CASE
WHEN r.submitted > s.LateOn THEN 'LATE'
WHEN r.submitted < s.LateOn THEN 'ON TIME'
WHEN r.submitted IS NULL THEN 'NO REPORT'
FROM [LINKEDSERVER].[SIS].[dbo].[name] nm LEFT JOIN Reported r
ON nm.soc_sec = r.student LEFT JOIN reportstd s
ON r.csgTerm = s.Report
WHERE (r.student IN (SELECT StudentID FROM students) OR
r.student NOT IN (SELECT StudentID FROM students)) AND
ReportPeriod IS NOT NULL AND
Report IS NOT NULL
ORDER BY LastName, FirstName, s.Report