T-sql 2012 error checking logic that combine all the logic together is a shorter form

Transact-SQL
1

I have the following sql that I plan to place in an SSRS 2012 report. The sql listed below is going to be used
to show where the where there is errors in the data.The last 5 select statements are used to show where the errors
in the data exist.

The following sql works fine, but I am wondering if there is a better way to write the error checking logic
can be written to be included in the ssrs report. Thus can you tell me how to change the sql written below to be used
in an ssrs report to the error data can be displayed on the ssrs report?

SeLECT DISTINCT ros.personID studentPersonID, S.[trialID],S.[CourseID],S.[roomID], r.name roomName
, i.lastName, i.firstName, c.calendarID, ros.endDate as rosendDate, s.teacherDisplay
,ssh.personID as TeacherpseronID,ssh.staffType into #RosterStudent1
FROM TEST.dbo.Course c WITH (NOLOCK)
JOIN TEST.dbo.Section s WITH (NOLOCK) ON s.courseId = c.courseid
JOIN TEST.dbo.Trial AS Trial WITH (NOLOCK) ON s.trialID = Trial.trialID AND Trial.active = 1
JOIN TEST.dbo.Roster ros WITH (NOLOCK) ON ros.sectionID = s.sectionID AND ros.trialID = s.trialID
AND (ros.startDate IS NULL OR ros.startdate <= GETDATE())
AND (ros.endDate IS NULL OR ros.endDate + '23:59' >= GETDATE())
JOIN #PeriodSchedule PeriodSchedule on PeriodSchedule.sectionID=s.sectionID
JOIN (TEST.Dbo.SectionPlacement sp WITH (NOLOCK)
JOIN TEST.Dbo.Term tm WITH (NOLOCK) ON tm.termID = sp.termID AND tm.Seq=3
JOIN TEST.Dbo.Period pd WITH (NOLOCK) ON pd.periodID = sp.periodID)
ON sp.sectionID = s.sectionID AND sp.trialID = s.trialID
JOIN TEST.dbo.SectionStaffHistory ssh WITH (NOLOCK)
ON ssh.sectionID = s.sectionId AND ssh.trialID = s.trialID
AND (ssh.startDate <= GETDATE() OR ssh.startDate IS NULL)
AND (ssh.endDate + '23:59' >= GETDATE() OR ssh.endDate IS NULL)
LEFT JOIN TEST.dbo.Person p WITH (NOLOCK) ON p.personID = ssh.personID
LEFT JOIN TEST.dbo.[Identity] i WITH (NOLOCK) ON i.identityID = p.currentIdentityID
LEFT JOIN TEST.dbo.Room r WITH (NOLOCK) ON r.roomId = s.roomid
and c.calendarid in (1234)

SELECT studentPersonID,[trialID],[CourseID],[roomID], count() as RoomCount
from #RosterStudent1
where staffType = 'P'
group by studentPersonID,[trialID],[CourseID],[roomID]
having count() > 1

SELECT studentPersonID,[trialID],[CourseID],[roomID], count() as RoomCount
from #RosterStudent1
group by studentPersonID,[trialID],[CourseID],[roomID]
having count() =0

SELECT studentPersonID,[trialID],[CourseID],[roomID]
from #RosterStudent1
WHERE roomID is null

SELECT studentPersonID,[CourseID],TeacherpseronID,count() as TeacherCount
from #RosterStudent1
where staffType = 'P'
group by studentPersonID,[CourseID],TeacherpseronID
Having count() > 1

SELECT studentPersonID,[CourseID],TeacherpseronID,count() as TeacherCount
from #RosterStudent1
where staffType = 'P'
group by studentPersonID,[CourseID],TeacherpseronID
Having count() = 0

2
  1. Don't use NOLOCK
  2. What part of this script is the error checking logic?
3

I will not use the nolock. I found that is not a good idea.

The error checking logic is the following:

SELECT studentPersonID,[trialID],[CourseID],[roomID], count() as RoomCount
from #RosterStudent1
where staffType = 'P'
group by studentPersonID,[trialID],[CourseID],[roomID]
having count() > 1

SELECT studentPersonID,[trialID],[CourseID],[roomID]
from #RosterStudent1
WHERE roomID is null
SELECT studentPersonID,[CourseID],TeacherpseronID,count() as TeacherCount
from #RosterStudent1
where staffType = 'P'
group by studentPersonID,[CourseID],TeacherpseronID
Having count() > 1

I realized that I can not have count(*) = 0 since nothing would be located.

4

OK -- your queries look reasonable. in the report, you'll check if the result set from each query is empty. if so, no errors!