SQL Query Question
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.
Any ideas?
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.
Any ideas?
Jim Horn
Can you post some sample data and your desired return set? That would help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sure -
Case # Submission # Submission Type Date of Submission
510 3001 Initial 09/10/2015
510 3005 Interim 10/15/2015
511 3002 Initial 10/05/2015
512 3003 Initial 10/07/2015
512 3006 Interim 10/18/2015
512 3008 Final 11/08/2015
513 3004 Initial 10/08/2015
514 3007 Initial 11/02/2015
So, my query would only pull case # 511 and case # 513 (both > 21 days with no interim or final submissions recorded).
Case # Submission # Submission Type Date of Submission
510 3001 Initial 09/10/2015
510 3005 Interim 10/15/2015
511 3002 Initial 10/05/2015
512 3003 Initial 10/07/2015
512 3006 Interim 10/18/2015
512 3008 Final 11/08/2015
513 3004 Initial 10/08/2015
514 3007 Initial 11/02/2015
So, my query would only pull case # 511 and case # 513 (both > 21 days with no interim or final submissions recorded).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT C.[Case #]
FROM [Case] AS CInitial
LEFT OUTER JOIN [Case] AS CInterim
ON CInitial.[Case #] = CInterim.[Case #]
AND CInterim.[Submission Type] = 'Interim'
LEFT OUTER JOIN [Case] AS CFinal
ON CInitial.[Case #] = CFinal.[Case #]
AND CFinal.[Submission Type] = 'Final'
WHERE CInitial.[Submission Type] = 'Initial'
AND CInitial.[Date of Submission] < DATEDIFF(DAY, -21, GETDATE())
AND CInterim.SubmissionID IS NULL
AND CFinal.SubmissionID IS NULLASKER
Thank you Brian and Scott! I was able to make both solutions work. Brian, I do have two tables, one for the unique case # and one for any (or many) submissions per case, so your solution is best. Thank you!