SQL Query for checking if there are not duplicate grades

Question

I am trying to write a query to look for students target grades. Sometimes the students will change teachers throughout the year or teachers will leave. When a report is run, it matches target grades on student/subject/teacher. For various reasons, it is easier to copy the old teachers target grades into a table with the new teachers name than it is to try to point the reports to the old teacher.

So I am writing a query to find teachers who are missing target grades for students, but a different teacher has inputted them. I have managed to create one for students who have target grades but now have a different teacher, but it does not remove any lines where the targets have already been copied over, so I get duplicates.

Here is my current code:

SELECT
    staffdets.Surname AS 'Teacher Surname',
    staffDets.PreName AS 'Teacher Forename',
    repStore.txtID AS 'Subject Name',
    repStore.txtsubID AS 'Set Name',
    pupilInfo.txtSurname AS 'Student Surname',
    pupilInfo.txtForename AS 'Student Forename',
    Target.txtGrade AS 'Target Grade',
    Target.Initials AS 'OGTeacher'  
FROM TblReportsManagementCycle AS repCycle
LEFT JOIN TblSchoolManagementTermDates AS termInfo
    INNER JOIN TblSchoolManagementTermNames AS termName
    ON termInfo.intTerm = termName.TblSchoolManagementTermNamesID
ON repCycle.intReportTerm = termInfo.intTerm
AND repCycle.intReportYear = termInfo.intSchoolYear
INNER JOIN TblReportsStore AS repStore
    INNER JOIN TblReportsStorePupilArchive AS pupilArchive
        INNER JOIN TblPupilManagementPupils AS pupilInfo
        ON pupilArchive.txtSchoolID = pupilInfo.txtSchoolID
    ON repStore.txtSchoolID = pupilArchive.txtSchoolID
    AND repStore.intReportCycle = pupilArchive.intReportCycle
    LEFT JOIN TblStaff AS staffDets
    ON repStore.txtSubmitBy = staffDets.User_Code
ON repCycle.TblReportsManagementCycleID = repStore.intReportCycle
OUTER APPLY
    (SELECT 
        pupilInfo1.txtSchoolID,
        repStore1.txtID,
        repGrades1.intReportID,
        repGrades1.txtGrade,
        repStore1.txtSubmitBy,
        Staff1.Initials
    FROM TblReportsManagementCycle AS repCycle1
    INNER JOIN TblReportsStore AS repStore1
        INNER JOIN TblReportsStorePupilArchive AS pupilArchive1
            INNER JOIN TblPupilManagementPupils AS pupilInfo1
            ON pupilArchive1.txtSchoolID = pupilInfo1.txtSchoolID
        ON repStore1.txtSchoolID = pupilArchive1.txtSchoolID
        AND repStore1.intReportCycle = pupilArchive1.intReportCycle
        LEFT JOIN TblReportsStoreGrades repGrades1
            LEFT JOIN iSAMS.dbo.TblReportsManagementTemplatesGrading rGradeTemplate
            ON repGrades1.intGradeID = rGradeTemplate.TblReportsManagementTemplatesGradingID
        ON repStore1.TblReportsStoreID = repGrades1.intReportID
    ON repCycle1.TblReportsManagementCycleID = repStore1.intReportCycle
    LEFT OUTER JOIN tblStaff Staff1
    ON Staff1.User_Code = repStore1.txtSubmitBy
    WHERE repCycle1.txtReportName = CONVERT(nvarchar(255),CONCAT('DO NOT USE - Target Grades ',termInfo.intSchoolYear)) -- TARGET CYCLE 
    AND pupilInfo1.txtSchoolID = pupilInfo.txtSchoolID
    AND rGradeTemplate.txtGradingName LIKE '%Target%'
    AND repStore1.txtID = repStore.txtID
    AND repGrades1.txtGrade <> '#'
    ) AS Target
WHERE repCycle.TblReportsManagementCycleID = 216 -- CURRENT REPORT CYCLE
AND Target.txtSubmitBy <> repStore.txtSubmitBy
AND Target.txtGrade <> ''
ORDER BY staffdets.Surname, repStore.txtsubID, pupilInfo.txtSurname

which produces a table like this:

Teacher Surname	Teacher Forename	Subject Name	Set Name	Student Surname	Student Forename	Target Grade	OGTeacher
Bragg	Billy	Economics	10a-ECO	Joe	Bloggs	5	RXB

If I comment out the row in the WHERE clause asking for it to only show where the current teacher does not match the one who submitted the original target grade (AND Target.txtSubmitBy <> repStore.txtSubmitBy) then it will show both like this:

Teacher Surname	Teacher Forename	Subject Name	Set Name	Student Surname	Student Forename	Target Grade	OGTeacher
Bragg	Billy	Economics	10a-ECO	Joe	Bloggs	5	RXB
Branson	Richard	Economics	10a-ECO	Joe	Bloggs	5	RXB

So what I need is for it to recognise where there is already one where Target.txtSubmitBy = repStore.txtSubmitBy AND there is a result is there and then not show the row with the original grade. So neither of the above lines would show but if the table was to show this...

Teacher Surname	Teacher Forename	Subject Name	Set Name	Student Surname	Student Forename	Target Grade	OGTeacher
Bragg	Billy	Economics	10a-ECO	Joe	Bloggs	5	RXB
Branson	Richard	Economics	10a-ECO	Joe	Bloggs		RXB

...then it wouldn't show the second line, only the first.

Im sorry if this doesn't make sense, I realise I have written it in kind of a rush and my code is not the best looking at the best of times. Any help would be greatly appreciated.

Thanks, Rob

Answer 1

not totally following your logic or your query, but maybe you need a ranking system. you could use row_number, or rank, or dense_rank

so whatever defines your duplicate /ranking. Is it Target.txtSubmitBy <> repStore.txtSubmitBy?

so maybe something like

     row_number() over (
partition by subjectName, SetName, StudentSurname, StudentForename
order by case when Target.txtSubmitBy = repStore.txtSubmitBy then 1 else 0 end)
as rn

so full query could be something like

with t1 as (your original query)
,t2 as (select t1.*,
 row_number() over (
    partition by subjectName, SetName, StudentSurname, StudentForename
    order by case when TargettxtSubmitBy = RepStoretxtSubmitBy then 1 else 0 end)
    as rn
from t1)
select * from t2 where rn = 1