Hello Experts -
I have to produce a set of records using SQL that meet my conditions. This is an SQL 2008 database. In my main table, each unique record is called a case. Each case can then have 0-3 sub-records (we call them documented submissions). Some cases can have just one submission, others have 2 or 3. Each submission has a type (“initial”, “interim”, and “final”), and each type of submission has its own date populated. So, for example, case # 510 could have two submissions, one for “initial” submitted on 09/10/2015, and one for “interim” submitted on 10/15/2015 etc.
Here’s my challenge. I need produce a list of cases where an “initial” submission has been recorded, and where the date difference between today and that “initial submission” is > 21 days, but where an “interim” or “final” submission has not been recorded. If an “interim” or “final” submission has been recorded, then the case should not be pulled in my list.