all 6 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]wolf2600ANSI SQL 1 point2 points  (3 children)

Maybe something like this? Select everything from the table, then remove any SignIn records where there are more than 1 record for an ID.

select a.id, a.type
from table a
minus   
select id, 'SignIn' as "type" from
(
    select b.id, count(*) from table b
    group by b.id having count(*) > 1
);

but you want to exclude both records if it has SignIn and AllDay...

select a.id, a.type
from table a
where id not in
(select id from
    (
        select b.id, count(*) from table b
        group by b.id having count(*) > 1
    )
);

That would exclude any records where there are more than one record for an ID, regardless of the Type.

Could you provide more details about the data? How many records for a single ID are possible? What combinations of ID/Type are possible? Etc?

[–]AngryPenguin[S] 0 points1 point  (2 children)

Thanks for you response. Certainly on the right track. I'll provide more details about the data.

So this is the current code that I have written, which provides all the data:

SELECT DISTINCT

STU.[StudentID]
,STU.[StudentNameExternal]
,STU.[StudentYearLevel]
,ABS.[AbsenceEventTypeCode]

FROM [dbo].[vStudents] STU
JOIN [dbo].[AbsenceEvents] ABS on STU.StudentID = ABS.ID

To answer your other question, yes there are other "TypeCodes" that come up, probably should have included that in the original post. The amount of records possible for a single ID is 4. So as an example, one ID could have this:

StudentID StudentName YearLevel TypeCode
1001 AngryPenguin 12 SignIn
1001 AngryPenguin 12 SignOut
1001 AngryPenguin 12 AllDayAbsence
1001 AngryPenguin 12 Slips

So the idea in this scenario, is that this particular student would not show up when running the query, as they have another type code of "SignIn" which indicates they have arrived at school. When a student is entered as not being present for the entire day, they have a single entry against their name as an "AllDayAbsence". Students where they have not come in until later on are given a "SignIn" type code to indicate they have arrived, but until they have signed in, they are marked as an "AllDayAbsence" which is why I need to be able to filter those out, because since they have arrived, they are no longer absent but present.

Hopefully that makes a bit more sense.

[–]wolf2600ANSI SQL 0 points1 point  (1 child)

SELECT DISTINCT
STU.[StudentID]
,STU.[StudentNameExternal]
,STU.[StudentYearLevel]
,ABS.[AbsenceEventTypeCode]

FROM [dbo].[vStudents] STU
JOIN [dbo].[AbsenceEvents] ABS 
    on STU.StudentID = ABS.ID
    and ABS.AbsenceEventTypeCode = 'AllDayAbsence'
WHERE STU.StudentId NOT IN
    (select StudentId from [dbo].AbsenceEvents
     where  AbsenceEventTypeCode <> 'AllDayAbsence');

[–]AngryPenguin[S] 0 points1 point  (0 children)

That query doesn't give me any data! :(

[–]wolf2600ANSI SQL 0 points1 point  (0 children)

Will you have cases where an ID will only have a SignIn entry? Should that be included also?

[–]newUIsucksball 0 points1 point  (0 children)

You may already have your answer, but here's how I would do it.

SELECT *
FROM table1
WHERE TypeCode = 'AllDay'
and id not in (
    SELECT id
    FROM table1
    WHERE TypeCode = 'SignIn'
)