January 6, 2018 at 7:19 pm
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
January 7, 2018 at 11:59 am
You could use
WHERE EXISTS and correlated subqueries. It will slow things down, but right answers are better than fast answers.
Do you inherit these stored procedures or write them? What's with the NOLOCK hints? You know what that does, right?
January 7, 2018 at 8:31 pm
The following pertains to what you mentioned above:
1. 'You could use WHERE EXISTS and correlated subqueries,. Would you show me an example of using the where exists? Would you also show me the correlated subqueries? Would you show me both examples of the sql that I listed above?
2. I will not use the NOLOCK hints here.
3. This is sql that I am in the process of writing.
January 7, 2018 at 9:21 pm
I find it hard to believe you've been coding this long (and asking questions) and can't write a simple correlated subquery.
Say I want to show all the details about customers without a sale in the past 30 days...
SELECT c.*
FROM Customer c
WHERE NOT EXISTS (SELECT 1
FROM Invoices i
WHERE i.CustomerID = c.CustomerID
AND i.InvoiceDate >= GETDATE()-30)
Note the join between Invoices i and Customer c in the WHERE clause... that's what makes this work.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply