Count Quotes between Dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count Quotes between Dates

Author

Topic

JohnGault777
Starting Member

31 Posts

Posted - 2011-02-14 : 09:22:37

Initially disclaimer: I'm an extreme novice to SQL. I've been reading various books (SAMS Teach Yourself in 10 Minutes, Inside Microsoft SQL Serve 2005: T-SQL Querying, and SQL Queries for Mere Mortals) trying to get up to speed.

I've gotten to the point of understanding that I think you call a Select statement inside of a Select statement. I think of it as the Select Statement has created a table and you are using it in the FROM section.

So I wrote the following code and it keeps telling me "Incorrect syntax near the keyword 'Where'". Can someone clarify why this isn't working?

Select Count(uniquequotecount)
FROM (SELECT dateadd(dd,datediff(dd,0,dbo.policy.adddate),0) as OverallADDDay, count(distinct dbo.policy.insuredid) as UniqueQuoteCount
FROM dbo.Insured INNER JOIN
dbo.Policy ON dbo.Insured.insuredID = dbo.Policy.insuredID
WHERE dbo.Policy.policytype = '4'
GROUP BY dateadd(dd,datediff(dd,0,dbo.policy.adddate),0))
Where OverallADDDay >= '20090701' and OverallADDDay < '20090901'

To clarify the purpose of this statement: The statement is trying to determine the number of customers (InsuredID) that call in to receive a quote on a particular day; this would be what I'm calling a Quote Count. This is because a customer can actually receive hundreds of quotes in a particular day. However, I really want to count a single quote as a customer receiving 1 or greater quotes on a single day.

JG777

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-14 : 09:36:22

You need to alias the derived table
GROUP BY dateadd(dd,datediff(dd,0,dbo.policy.adddate),0)) as derivedTable

Jim

P.S. I'd read T-SQL fundamentals before t-SQL Querying. I just finished it and although I didn't get a lot out of it, I really wish I has read it a few years ago; and what i did get out of it was still worth it.

Jim

Everyday I learn something that somebody else already knew

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-14 : 10:55:02

You don't necessarily need the derived table.

In your case you are doing a COUNT of the derived tables COUNT..which may not be correct for what your stated purpose is. I read that you want to reduce many quotes to basically a true/false that they recieved 1 or more?

Either this would provide you with the needed rows where at least 1 quote was had (you would have to add columns for more info like Policy ID or something and group by that:


SELECT   dateadd(dd,datediff(dd,0,dbo.policy.adddate),0)) as OverallAddDay
      	FROM dbo.Insured INNER JOIN dbo.Policy 
			ON dbo.Insured.insuredID = dbo.Policy.insuredID
Where   dateadd(dd,datediff(dd,0,dbo.policy.adddate),0) >= '20090701' and
		dateadd(dd,datediff(dd,0,dbo.policy.adddate),0)< '20090901'
		
GROUP BY dateadd(dd,datediff(dd,0,dbo.policy.adddate),0))

OR This would use your existing syntax to something similar.

Select OverallADDDay,CASE WHEN UniqueQuotecount > 0 then 1 else 0 end) as HasRecievedQuote
FROM (	SELECT dateadd(dd,datediff(dd,0,dbo.policy.adddate),0) as OverallADDDay
		,count(distinct dbo.policy.insuredid) as UniqueQuoteCount
		FROM dbo.Insured INNER JOIN
		dbo.Policy ON dbo.Insured.insuredID = dbo.Policy.insuredID
		WHERE dbo.Policy.policytype = '4'
		GROUP BY dateadd(dd,datediff(dd,0,dbo.policy.adddate),0)) as d
Where OverallADDDay >= '20090701' and OverallADDDay < '20090901'

It is easy to get things in your mind's eye overcomplicated. Start with a SELECT that returns the rows/records you need to work with, THEN figure out how to aggregate then the way you need to.

I would presume this:


SELECT dateadd(dd,datediff(dd,0,dbo.policy.adddate),0) as OverallADDDay
		,Policy.InsuredID
FROM dbo.Insured INNER JOIN dbo.Policy 
ON dbo.Insured.insuredID = dbo.Policy.insuredID
WHERE dbo.Policy.policytype = '4'

Gives you the correct records you want?

If so, then you can simply

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources