0

Ok, so here is the problem I have two tables in my SQL DB. Table 1 is a simple contact table with a record for each individual in my DB. Table 2 is an education/Exam table where there is a single record for each individual in the DB but each record has multiple tests in it. I am trying to query the two tables to get me a list of all records who have expired values(older than today) so I can flag the record for deletion. All dates stored in the DB are the expire dates. The kicker on this is that since an individuals exam records all exist in a single row for the record, if even one of the exam dates is not expired, the entire record needs to be kept. I can only delete a record if I have a combination of null values and expired dates. Not everyone has 4 records, most have one or two with the remaining records as Null. So here is my code to get the data:

select n.id, n.FULL_NAME, e.ExamDate1, e.ExamDate2, e.ExamDate3, e.ExamDate4
from Name n inner join Education e on n.id = e.id

This gets me 57k records and they are all a mix. A snippet of the results look like this:

| ID | FullName | ExamDate1 | ExamDatew | ExamDate3 | ExamDate4 |
| 10001 | John Doe | 1/1/2008 | Null | Null | 10/25/2015 |
| 10002 | Jane Doe | 10/8/2020 | 1/25/2015 | 4/16/2014 | Null |
| 10003 | John Smith | 1/1/2010 | 6/5/2008 | 9/15/2013 | 7/8/2001 |
| 10004 | Debbie Sue | 8/14/2020 | 6/5/2016 | Null | Null |
| 10005 | Suzy Q | 5/9/2016 | 4/15/2014 | 4/9/2017 | Null |

I need to be able to take this data and sort through it and flag each person on good or bad. Good if at least one listed date is not expired and bad if all listed dates are expired or all dates are expired and the records without dates are null.

Any ideas on what the easiest way to do this is? I was thinking a case statement in a procedure where I run through each record using a case for each exam date. Have a stored variable that starts as blank and add a value to that stored variable as variable = Variable + 'X' and at the end of the cycle, look at the value of the variable. If the value is yyyy then the record is flagged(shown) if not, then the record is left alone. Once the cycle is done, reset the variable to blank and start it over.

I hope this is making sense as it does in my head but not always on paper.

Thanks.

Jayson Larner
  • 73
  • 6

2 Answers2

0

Sql is a set based language. Rather than flagging as good or bad, the language itself is meant to return a specific set. In this case, you probably want the set of expired records. You have all the information in front of you, so a case statement or cycling through data is not necessary. In general, you should try not to cycle through data in sql.

So, what you want is a list of all the people that fit the following criteria: Exam Date 1 is null or expired and exam 2 is null or expired and exam 3 in null or expired and exam 4 is null or expired. These conditions translate into sql really nicely.

select n.id, n.FULL_NAME, e.ExamDate1, e.ExamDate2, e.ExamDate3, e.ExamDate4
from Name n inner join Education e on n.id = e.id
where (e.ExamDate1 is null or e.ExamDate1 < cast(getDate() as date))
and (e.ExamDate2 is null or e.ExamDate2 < cast(getDate() as date))
and (e.ExamDate3 is null or e.ExamDate3 < cast(getDate() as date))
and (e.ExamDate4 is null or e.ExamDate4 < cast(getDate() as date))
Jenn
  • 795
  • 1
  • 4
  • 16
  • Ok, so this looks good but will this take into account for any combination of good/bad? Just because there is an exam one date, doesn't mean there is an exam2 date. So a person could have only a date in Exam1 and in exam3 and null in exam2 but exam1 is expired and exam3 is not and pardon any stupidity here but doesn't an 'and' command look from left to right but not jump around or does this allow it to jump around to see all the records and report accordingly? This is one where I need any possible combination of good/bad. – Jayson Larner Sep 30 '14 at 15:49
  • and means that the statements need to evaluate to true. True and false evaluates to false. This is all based on boolean logic. So, the first part says examDate1 must be null or less than today's date. If this is true, we look at the next part. If ExamDate2 is greater than today, the evaluation stops and the record is not returned. – Jenn Sep 30 '14 at 15:54
  • Great. Thanks. I guess I was over thinking this way too much and didn't even associate a simple and statement for this. This looks to have worked just like I need it too. – Jayson Larner Sep 30 '14 at 17:24
0

list of "good" guys

select n.id, n.FULL_NAME, e.ExamDate1, e.ExamDate2, e.ExamDate3, e.ExamDate4
from Name n inner join Education e on n.id = e.id
where
  (
    ISNULL(e.ExamDate1,'19000101') >= GETDATE()
    or
    ISNULL(e.ExamDate2,'19000101') >= GETDATE()
    or
    ISNULL(e.ExamDate3,'19000101') >= GETDATE()
    or
    ISNULL(e.ExamDate4,'19000101') >= GETDATE()
    or
  )

flag bad guys (only in Name table)

update n
set n.YOUR_FLAG_COLUMN = 'Y'
from Name n inner join Education e on n.id = e.id
where
  (
    ISNULL(e.ExamDate1,'19000101') < GETDATE()
    and
    ISNULL(e.ExamDate2,'19000101') < GETDATE()
    and
    ISNULL(e.ExamDate3,'19000101') < GETDATE()
    and
    ISNULL(e.ExamDate4,'19000101') < GETDATE()        
  )

or if you have foregin key between Name and Education with ON CASCADE DELETE even DELETE them...

MrKidzio
  • 11
  • 2