Query Help

Other SQL Server Topics
1

I have a database with records of students in it. one student can have several rows. I need to select all of a students records from the same academic year(term) when one of their records has been changed in the last 24 hours. The problem I am running into is that I cannot just select all records from a student, and I cannot just select all records from a term. and regardless of if the record was modified in the last 24 hours or not, it will need to be selected if another record from the same term and student has been.

Capture

For example in this picture I would need my result to have each of Tommy's records that are from the 2223 term but not the 2221 term and I would also need Sally's records from the 2122 term but not the 2223 term.

Any Ideas?

2

Welcome
please provide your data not as an image but as real useable data

declare @students table(studentid int,
studentname varchar(50))
--etc all the columns and their data type

insert into @students
select  12345, 'Tommy
3

If you could please simplify the description of the task. Why you dont want Sally s records for both terms?

4

I tried to keep it as simple as possible. I don't want both terms because only sallys 2122 record was changed

5 

;WITH cte_modified_student_term AS (
    SELECT [Student ID], Term
    FROM dbo.table_name
    GROUP BY [Student ID], Term
    HAVING MAX(CASE WHEN [Modified Date] >= DATEADD(DAY, -1, GETDATE()) 
        THEN 1 ELSE 0 END) = 1
)
SELECT tn.*
FROM cte_modified_student_term cm
INNER JOIN dbo.table_name tn ON tn.[Student ID] = cm.[Student ID] AND 
    tn.Term = cm.Term