Hi,
Based upon the attached table relationships, could somebody please help in the SQL queries for the following questions -
Question 1 - Select time elapsed in days between the case date for cases created in the second half of 2010 and its first quote. If no quote has been created yet find elapsed time from case date to today’s date.
Question 2 - Select account name, contact last name, case number, quote number, quote date and quote value for the third largest quote ever created for each of the accounts in the EC1 area.
Question 3 - Select name and full address of all accounts with more than 5 policies created in 2011
Question 4 - Select case number and case date for all case in the system with quotes created in two different months (NB Jan 2016 and Jan 2015 would be considered different months)
Question 5 - Select first name and last name for each contact working in accounts in the EC1 area and the date of the most recent and the oldest quote
Many thanks for your help.
April 24, 2020 at 1:14 pm
You've been here long enough to know the drill. Please provide sample DDL, INSERT statements and desired results if you want others to write SQL for you.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 24, 2020 at 5:50 pm
This sounds like a homework assignment...
Question 1 is pretty trivial as you are just using datediff on the Cases table with a WHERE to put the date greater than or equal to July 1st, 2010 joining that to the quotes table. The datediff is going to use a case when quote is NULL then getdate() else quotedate.
Question 2 is going to be using top and order by as you select from a few tables joined together.
Question 3 you need to define what a "policy" is. Is it a case, a quote, an account, a child account, a parent account? How do you define a policy?
Question 4 is probably the most complicated one, but even that one isn't too bad. I'd probably do it with a CTE (possibly multiple) and select distinct quotes ID, include the datepart year and datepart month in the CTE, then do a count partitioned by the quote ID and join the results where the count is > 1 back to the cases.
Question 5, getting the contact information is pretty easy. Once you have that, it is 2 different joins - one for the MIN date and one for the MAX date. Plus joining through the chain to get the contact back to a quote.
What have you tried so far and which question are you having trouble with?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Sounds like homework to me...
AS PP said, no code without DDL and populated tables... but here is my approach:
Q1: CTE to filter cases to 2010H2, CTE to apply RANK() OVER() function to quotes. LEFT JOIN CTE_Cases to CTE_quotes where RANK = 1. This will give you all of the cases and the first quote if it exists. Use DATEDIFF and ISNULL to calculate the days between case and quote/today
Q2: CTE to RANK() OVER() quotes, partition by Account and order by Value. JOIN Account to CTE_quotes and filter for RANK = 3 and postcode like 'EC1%'
Q3: question is ambiguous. Do you want Accounts Created in 2011 with more than 5 policies, or do you want all accounts, with more than 5 policies created in 2011. FWIW, the question as posed is typical of what you get from a user. This is why you need BAs to gather requirements 🙂 Either way you are going to be doing a GROUP BY and a HAVING
Q4: CTE of the quotes table with a derived field to represent the year and month of the claim. You can either concatenate the year and month name (cast the year to a string field) or (year(quteDate) * 100)+month(quoteDate). JOIN cases to CTE_quote and WHERE MIN(derivedField) <> MAX(derivedField). This will give you all cases with quotes in AT LEAST two different months. If you want EXACTLY two months then you will need to use DENSE_RANK() instead of the derivedField (same logic, but different derivation) and then WHERE MAX(DENSERANK) = 2
Q5: Brian's suggestion is probably the simplest; you could also use MIN() OVER() and MAX() OVER() to save a query.
Do I get the job?
April 26, 2020 at 9:43 am
Thanks for your kind help. Will try to proceed now.
April 26, 2020 at 9:44 am
This sounds like a homework assignment...
Question 1 is pretty trivial as you are just using datediff on the Cases table with a WHERE to put the date greater than or equal to July 1st, 2010 joining that to the quotes table. The datediff is going to use a case when quote is NULL then getdate() else quotedate.
Question 2 is going to be using top and order by as you select from a few tables joined together.
Question 3 you need to define what a "policy" is. Is it a case, a quote, an account, a child account, a parent account? How do you define a policy?
Question 4 is probably the most complicated one, but even that one isn't too bad. I'd probably do it with a CTE (possibly multiple) and select distinct quotes ID, include the datepart year and datepart month in the CTE, then do a count partitioned by the quote ID and join the results where the count is > 1 back to the cases.
Question 5, getting the contact information is pretty easy. Once you have that, it is 2 different joins - one for the MIN date and one for the MAX date. Plus joining through the chain to get the contact back to a quote.
What have you tried so far and which question are you having trouble with?
Many Thanks for your suggestions.
April 26, 2020 at 9:45 am
Sounds like homework to me...
AS PP said, no code without DDL and populated tables... but here is my approach:
Q1: CTE to filter cases to 2010H2, CTE to apply RANK() OVER() function to quotes. LEFT JOIN CTE_Cases to CTE_quotes where RANK = 1. This will give you all of the cases and the first quote if it exists. Use DATEDIFF and ISNULL to calculate the days between case and quote/today
Q2: CTE to RANK() OVER() quotes, partition by Account and order by Value. JOIN Account to CTE_quotes and filter for RANK = 3 and postcode like 'EC1%'
Q3: question is ambiguous. Do you want Accounts Created in 2011 with more than 5 policies, or do you want all accounts, with more than 5 policies created in 2011. FWIW, the question as posed is typical of what you get from a user. This is why you need BAs to gather requirements 🙂 Either way you are going to be doing a GROUP BY and a HAVING
Q4: CTE of the quotes table with a derived field to represent the year and month of the claim. You can either concatenate the year and month name (cast the year to a string field) or (year(quteDate) * 100)+month(quoteDate). JOIN cases to CTE_quote and WHERE MIN(derivedField) <> MAX(derivedField). This will give you all cases with quotes in AT LEAST two different months. If you want EXACTLY two months then you will need to use DENSE_RANK() instead of the derivedField (same logic, but different derivation) and then WHERE MAX(DENSERANK) = 2
Q5: Brian's suggestion is probably the simplest; you could also use MIN() OVER() and MAX() OVER() to save a query.
Do I get the job?
Thanks a lot, you definitely got the job. !!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply