SQL - Queries

1

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.SQL

2

Sure .. I will take a stab at it ..soon .. probably in the next 4 hrs

Just curious .. whats this for ?? is it office , homework , learning , someone else
Sorry for asking :slight_smile:

3

Hi, thanks, this is for self learning actually.

4

do you want the answers !!!

OR

how do you get to the answers ?? thinking ... how to connect !! ETC ETC
( process )

What ever you want is FINE
:slight_smile:

5

Hi, thanks, I was able to create the SQL queries for a few but I am stuck on these.

Is it possible to create the SQL queries and I can then work my head around them.

Thanks.

6

it always helps
to create the tables and populate them with sample data
.. in the tables for all possible scenarios ( data )

Here's SQL for

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.

Here's my stab in the dark .. !!!

; with cte as 
(
  select 
	 a.caseid as acaseid 
	, a.casenumber
	, a.casedate
	, a.contactid 
	, b.quoteid
	, b.caseid as bcaseid
	, b.quotenumber
	, b.quotedate 
  from 
   tbl_case a 
      left join 
	      tbl_quote b 
		     on a.caseid = b.caseid 
  where 
     year(casedate) = 2010 
	    and 
		  datepart(mm,casedate) in (7,8,9,10,11,12) 
) 
select 
  datediff(dd ,a.quotedate ,case when bcaseid is null then getdate() else minquotedate end )
    from 
         ( select * from cte ) a 
		       join 
			   ( select caseid,min(quotedate) as minquotedate  from cte  group by caseid ) b 
                   on a.caseid = b.caseid
1 Like
7

Thanks very much for your kind help. It helped me immensely.

10

Thanks very much again, Harish ! Appreciate the time you spent on this.

13

hi Walter

Please click arrow to the left for ... drop create tables
with sample data

drop create tables with sample data 
drop table #tbl_quote

create table  #tbl_quote
(
QuoteID int PRIMARY KEY, 
CaseID int FOREIGN KEY REFERENCES #tbl_case(caseid),
QuoteNumber int , 
Value int ,
QuoteDate date 
)

insert into #tbl_quote select 1,11,1,100,'2020-02-09'
insert into #tbl_quote select 2,11,2,200,'2020-02-10'
insert into #tbl_quote select 3,11,3,300,'2020-02-11'
insert into #tbl_quote select 4,11,4,400,'2020-02-11'
insert into #tbl_quote select 5,11,5,500,'2020-02-11'
insert into #tbl_quote select 6,11,6,700,'2020-02-11'


select 'tbl_quote',* from #tbl_quote 



drop table #tbl_case 
go 

create table #tbl_case 
(
caseid int PRIMARY KEY,
casenumber int ,
casedate date  , 
ContactID int FOREIGN KEY REFERENCES #tbl_Contact(contactid)
)

insert into #tbl_case select 11,2,'2020-09-10',145 
insert into #tbl_case select 12,3,'2020-07-10',147

select 'tbl_case',* from #tbl_case

drop table #tbl_contact 
go 

create table #tbl_Contact 
(
contactid int PRIMARY KEY,
accountid int FOREIGN KEY REFERENCES #tbl_account(accountid), 
firstname varchar(100),
lastname varchar(100),
emailaddress varchar(100),
TelePhoneNumber int 
)
go 

insert into #tbl_Contact select 145,1002,'ops','sam','sure@gmail.com',98888
insert into #tbl_Contact select 147,1003,'har','sap','ok@gmail.com',985666
go 

select 'tbl_contact',* from #tbl_Contact
go 


drop table #tbl_account 
go 

create table #tbl_account 
(
AccountId int PRIMARY KEY,
ParentAccountId int ,
Address1 varchar(100),
Address2 varchar(100),
Address3 varchar(100),
PostCode int 
)
go 

insert into #tbl_account select 1002,999,'sssss','yyyy','zzzz',500023
insert into #tbl_account select 1003,999,'asass','qwsq','edrr',500024
go 

select 'tbl_account',* from #tbl_account

; WITH cte 
     AS (SELECT Row_number() 
                  OVER( 
                    partition BY d.accountid 
                    ORDER BY value DESC) AS rn 
                ,QuoteID 
				,b.CaseID 
				,QuoteNumber 
				,Value 
				,QuoteDate 
				,casenumber 
				,casedate 
				,c.ContactID 
				,d.accountid 
				,lastname 
				,emailaddress 
				,TelePhoneNumber 
				,ParentAccountId 
				,Address1 
				,Address2 
				,Address3 
				,PostCode 
         FROM   #tbl_quote a 
                JOIN #tbl_case b 
                  ON a.caseid = b.caseid 
                JOIN #tbl_contact c 
                  ON b.contactid = c.contactid 
                JOIN #tbl_account d 
                  ON c.accountid = d.accountid
				  ) 
SELECT 'SQL Output',
       AccountId AS [account name], 
       lastname    AS [contact last name], 
       casenumber  AS [case number], 
       quotenumber AS [quote number], 
       quotedate   AS [quote date], 
       value       AS [quote value] 
FROM CTE 
WHERE  postcode = 500023 -- EC1 Area   
       AND rn = 3

image
image891×682 18.4 KB

1 Like
14

Hi Harish,

Many thanks again.

I have followed your instructions and it's working correctly. Thanks and sorry for taking your precious time.

15

Hi Walter

Question 3 - Select name and full address of all accounts with more than 5 policies created in 2011

Please see SQL for this !!!

; with cte as 
(
	SELECT 
                QuoteID 
				,b.CaseID 
				,QuoteNumber 
				,Value 
				,QuoteDate 
				,casenumber 
				,casedate 
				,c.ContactID 
				,d.accountid 
				,firstname
				,lastname 
				,emailaddress 
				,TelePhoneNumber 
				,ParentAccountId 
				,Address1 
				,Address2 
				,Address3 
				,PostCode 
			 FROM   #tbl_quote a 
					JOIN #tbl_case b 
					  ON a.caseid = b.caseid 
					JOIN #tbl_contact c 
					  ON b.contactid = c.contactid 
					JOIN #tbl_account d 
					  ON c.accountid = d.accountid
	WHERE YEAR(b.casedate) = 2011
),
cte_morethan5 as 
(				  
	SELECT 
		AccountId
		,count(distinct caseid)  as cnt5
	  from 
		  cte 
			group by AccountID
			   having count(distinct caseid) > 5 
)
	select 
		firstname,
		lastname ,
		Address1 ,
		Address2 ,
		Address3 ,
		PostCode  
	from 
	   cte a 
		 join 
		   cte_morethan5 b 
			 on a.accountid = b.accountid
1 Like
16

hi

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

Please see sql below

; with cte as 
(
	SELECT 
                QuoteID 
				,b.CaseID 
				,QuoteNumber 
				,Value 
				,QuoteDate 
				,casenumber 
				,casedate 
				,c.ContactID 
				,d.accountid 
				,firstname
				,lastname 
				,emailaddress 
				,TelePhoneNumber 
				,ParentAccountId 
				,Address1 
				,Address2 
				,Address3 
				,PostCode 
			 FROM   #tbl_quote a 
					JOIN #tbl_case b 
					  ON a.caseid = b.caseid 
					JOIN #tbl_contact c 
					  ON b.contactid = c.contactid 
					JOIN #tbl_account d 
					  ON c.accountid = d.accountid	
				where 
					PostCode = 111111 -- EC1 Area 
) , cte_recent_oldest as 
(
select 
   min(QuoteDate) as minqd
   ,max(QuoteDate) as maxqd
     from 
	   cte 
)
select 
	firstname
	,lastname 
	,ContactID 
	,Accountid 
	,QuoteDate
       from 
	      cte a 
		    join 
			  cte_recent_oldest b
			    on 
				 (  a.QuoteDate = b.minqd 
				       OR 
					     a.QuoteDate = b.maxqd   )
17

Awesome !!! Many thanks again, Harish.