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?
Marcos27Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Can you post some sample data and your desired return set?  That would help.
0
Brian CroweDatabase AdministratorCommented:
If i understand correctly you have a Case table and a Submission table, btw actual schema information is helpful in these questions in addition to the prose.

I took some guesses about your schema so try adapting this:

SELECT C.CaseID
FROM [Case] AS C
INNER JOIN Submission AS SInitial
	ON C.CaseID = SInitial.CaseID
	AND SInitial.[type] = 'initial'
LEFT OUTER JOIN Submission AS SInterim
	ON C.CaseID = SInterim.CaseID
	AND SInterim.[type] = 'Interim'
LEFT OUTER JOIN Submission AS SFinal
	ON C.CaseID = SFinal.CaseID
	AND SFinal.[type] = 'Final'
WHERE SInitial.[date] < DATEDIFF(DAY, -21, GETDATE())
	AND SInterim.SubmissionID IS NULL
	AND SFinal.SubmissionID IS NULL

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Marcos27Author Commented:
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).
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
SELECT Case#,
    MAX(Submission#) AS Submission#,
    MAX(Date_Of_Submission) AS Date_Of_Submission
FROM table_name
GROUP BY Case#
HAVING MAX(CASE WHEN Submission_Type = 'Initial' AND Date_Of_Submission < DATEADD(DAY, -21, GETDATE()) THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN Submission_Type <> 'Initial' THEN 1 ELSE 0 END) = 0
ORDER BY Case#
0
Brian CroweDatabase AdministratorCommented:
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 NULL

Open in new window

0
Marcos27Author Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.