I have a table 'tblTaxRecs' in my SQL Server DB. Some example records with fields that are pertinent to my question are:
RecID Pay Status DTDYear DTDNum
1 1 10 123
2 2 11 345
3 3 10 123
4 1 12 456
5 1 16 78919
6 1 10 123
7 1 12 456
There may be multiple records in the table with the same DTDYear and DTDNum Combinations or there may only be a single record with that DTDYear and DTDNum combination. Some records don’t have a DTDYear or DTDNum, we don’t want those at all.
The only field selected in the query will be 'RecID'
Select all the record ID's of the records with the same DTDYear and DTDNum, if all the records have a pay status of 1.
If there is only one record with the DTDYear and DTDNum combo and it is paystatus 1, it gets selected.
If there are multiple records with the same DTDYear and DTDNum and all of them have pay status of 1, they all get selected. If they don’t all have pay status of 1, none of them get selected.
From the above data
Result Record ID’s
2 because there was only 1(11 345) and it is paid
4 because there are 2, (12 456) both paid
5 because the was only 1 (16 78919)) and it is paid.
7 because there are 2, (12 456) both paid
I can write a select query that will select only paid records but I don't have any idea how to include the requirement that all occurrences of a given DTDYEar, DTDNum combination must be paid to select all of those record, otherwise none are selected.
I know how to do it by writing some logic but I was hoping it could be done in a query.
Is it possible?