Selecting records on condition that others exist.
-
I am attempting to produce a claims ratio for a book of insurance. In general I can do this.
What I have to know more specifically is this: do customers with multiple policies cost us more per policy in claims than those with only one policy.
As it happens the policies have a service level. First policies are always labelled '1' and second or subsequent policies are labelled '2'. Naturally I can select, sum or count the level '1' or '2' or both but I am unable to select the customers that have level '1' and also have a level '2' policy (or more).
If level '1' is A and level '2' is B then I think that in a Boolean sense I am looking for: A cap B for customers with multiple polices and A not B for single policy holders.
How can I sum, count or list these policies? I hope this makes sense to you.
-
Please take a few minutes to read the following article.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Follow the guidlines provided in the article and provide use with more information so that we may better assist you in answering your question. From your short narrative, I really don't know where to start.
😎
-
Is this what you are looking for?
MyTable
CustomerNumber INT
PolicyNumber INT
StartDate DATETIME
Premium MONEY
EndDate DATETIME
Where each customer may have many policies. I would like to sum only those policies where a custoemr has more than one policy as follows:
Select sum (Premium)
From MyTable
Where more than one policy per customer
-
Partially. We need the DDL for the table(s), create statements. Sample data for the tables, in the format of insert statements that can be cut, paste and executed to populate the tables. The expected results based on the sample data provided. And last, but not least, what you have already done to try and solve your problem at hand. Sometimes it is check a tweak of what you have already started and sometimes it's something completely different. The point with this last requirement is so that we know what you have already tried.
-
We need more details BUT....
Select sum (Premium)
From MyTable
Group BY CustomerNumber
HAVING COUNT(CustomerNumber) > 1
"Keep Trying"
-
We need the DDL for the table(s), create statements. The tables are already there I did not create them. I am simply querying the data. I have never created a table before but I will try to if required.
Sample data for the tables. This is a sample.
CustomerNumber PolicyNumber StartDate Premium EndDate
10234567 80567655 3/15/2007 97.0000 3/14/2008
10234567 80567656 3/15/2007 45.0000 3/14/2008
10239656 80577655 3/15/2007 97.0000 3/14/2008
10240076 80578066 3/22/2007 67.9800 3/21/2008
10240076 80578067 3/22/2007 22.0000 3/21/2008
10240076 80579001 5/22/2007 18.4587 3/21/2008
10241034 80580677 6/03/2007 44.0000 6/02/2008
10242446 80581433 7/13/2007 44.0000 7/12/2008
in the format of insert statements that can be cut, paste and executed to populate the tables. I assume I will need to know how to create a table before I know how to do this.
The expected results based on the sample data provided. The sum for all policies is £435.4387 and the mean average £54.4298. The sum of all the single policies (customers with only one policy) is £185 (records 3, 7 and 8) and the average £61.6667. The sum of all the multiple policies (customers with more than one policy is £250.4387 (records 1, 2, 4, 5 and 6) and the average is £50.0877.
And last, but not least, what you have already done to try and solve your problem at hand. I cannot comprehend how to solve the problem by query. What we currently do is to dump the table into Excel and use filters and formulas. I have no idea how to deal with one to many relationships.
Sometimes it is check a tweak of what you have already started and sometimes it's something completely different. The point with this last requirement is so that we know what you have already tried. I do understand this. I can sum data in records by conditions within the records themselves but I have no idea how to frame a condition based on the existance of a related record.
-
Davroche (10/29/2008)
We need the DDL for the table(s), create statements. The tables are already there I did not create them. I am simply querying the data. I have never created a table before but I will try to if required.Sample data for the tables. This is a sample.
CustomerNumber PolicyNumber StartDate Premium EndDate
10234567 80567655 3/15/2007 97.0000 3/14/2008
10234567 80567656 3/15/2007 45.0000 3/14/2008
10239656 80577655 3/15/2007 97.0000 3/14/2008
10240076 80578066 3/22/2007 67.9800 3/21/2008
10240076 80578067 3/22/2007 22.0000 3/21/2008
10240076 80579001 5/22/2007 18.4587 3/21/2008
10241034 80580677 6/03/2007 44.0000 6/02/2008
10242446 80581433 7/13/2007 44.0000 7/12/2008
in the format of insert statements that can be cut, paste and executed to populate the tables. I assume I will need to know how to create a table before I know how to do this.
The expected results based on the sample data provided. The sum for all policies is £435.4387 and the mean average £54.4298. The sum of all the single policies (customers with only one policy) is £185 (records 3, 7 and 8) and the average £61.6667. The sum of all the multiple policies (customers with more than one policy is £250.4387 (records 1, 2, 4, 5 and 6) and the average is £50.0877.
And last, but not least, what you have already done to try and solve your problem at hand. I cannot comprehend how to solve the problem by query. What we currently do is to dump the table into Excel and use filters and formulas. I have no idea how to deal with one to many relationships.
Sometimes it is check a tweak of what you have already started and sometimes it's something completely different. The point with this last requirement is so that we know what you have already tried. I do understand this. I can sum data in records by conditions within the records themselves but I have no idea how to frame a condition based on the existance of a related record.
If you script the table following the rules from this post made by Jeff Moden, I'm sure many will be happy to help you with your problem
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Ronald
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
-
with cte as (select CustomerNumber, PolicyNumber ,StartDate , Premium , EndDate,
count(*) over(partition by CustomerNumber) as cn
from mytable)
select sum(Premium) as [sum for all policies],
avg(Premium) as [avg for all policies],
sum(case when cn=1 then Premium end) as [sum of all the single policies],
avg(case when cn=1 then Premium end) as [avg of all the single policies],
sum(case when cn>1 then Premium end) as [sum of all the multiple policies],
avg(case when cn>1 then Premium end) as [avg of all the multiple policies]
from cte
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Davroche (10/29/2008)
We need the DDL for the table(s), create statements. The tables are already there I did not create them. I am simply querying the data. I have never created a table before but I will try to if required.Sample data for the tables. This is a sample.
CustomerNumber PolicyNumber StartDate Premium EndDate
10234567 80567655 3/15/2007 97.0000 3/14/2008
10234567 80567656 3/15/2007 45.0000 3/14/2008
10239656 80577655 3/15/2007 97.0000 3/14/2008
10240076 80578066 3/22/2007 67.9800 3/21/2008
10240076 80578067 3/22/2007 22.0000 3/21/2008
10240076 80579001 5/22/2007 18.4587 3/21/2008
10241034 80580677 6/03/2007 44.0000 6/02/2008
10242446 80581433 7/13/2007 44.0000 7/12/2008
in the format of insert statements that can be cut, paste and executed to populate the tables. I assume I will need to know how to create a table before I know how to do this.
The expected results based on the sample data provided. The sum for all policies is £435.4387 and the mean average £54.4298. The sum of all the single policies (customers with only one policy) is £185 (records 3, 7 and 8) and the average £61.6667. The sum of all the multiple policies (customers with more than one policy is £250.4387 (records 1, 2, 4, 5 and 6) and the average is £50.0877.
And last, but not least, what you have already done to try and solve your problem at hand. I cannot comprehend how to solve the problem by query. What we currently do is to dump the table into Excel and use filters and formulas. I have no idea how to deal with one to many relationships.
Sometimes it is check a tweak of what you have already started and sometimes it's something completely different. The point with this last requirement is so that we know what you have already tried. I do understand this. I can sum data in records by conditions within the records themselves but I have no idea how to frame a condition based on the existance of a related record.
First, if you read the article written by Jeff Moden (a link to it is below) it shows you how to do everything we are asking from you.
Second, looking at the sample data above, which are primary policies and which are secondary? Your original post indicated that they are flagged, but I don't see anything in the sample data.
Third, and this may be more for me, I'd rather see what you expect the output to look like rather than have a verbal description. I am more of a visual type individual so seeing works better than a simple description. Plus, it gives us something to check against as we develop the query.
-
Davroche (10/29/2008)
We need the DDL for the table(s), create statements. The tables are already there I did not create them. I am simply querying the data. I have never created a table before but I will try to if required.If you are using SQL Server Management Studio, simply right click on the table, select 'Script Table As', select 'Create To', select 'New Query Editor Window'
Sample data for the tables.
in the format of insert statements that can be cut, paste and executed to populate the tables. I assume I will need to know how to create a table before I know how to do this.
We need INSERT Scripts with sample data. Something like
INSERT INTO YourTableName (ColumnName)
SELECT SomeValue
Please read this article for more information on how to post questions on the forum
Forum Etiquette: How to post data/code on a forum to get the best help
We'd be happy to help implement the solution when you post sample data. 🙂
-
ggraber:
If you are using SQL Server Management Studio, simply right click on the table, select 'Script Table As', select 'Create To', select 'New Query Editor Window'
I have not used the studio yet, just the Enterprise Management Console. When the "Other Tasks -> Generate SQL Script" is used, it only lists the column names and types.
does studio also automatically generate the script for indexes, identity and other constraints as well or does this require an additional step? The original poster is a newbie...
-
To: Davroche.
We all started as newbies and the forum is great in that respect, helping others.
Do not take this wrong:
1. We enjoy helping others as others helped us, not just to show off. By "helping", I mean explaining how things work, to help others work out the solution to the problem. By "helping", I do NOT mean that this forum should supply a turnkey solution -- this would (sometimes) get you out of a fix but does not do much in terms of building up your knowledge. By "sometimes" I mean that, based on the information supplied, the offered fix will address the question asked, but not the whole context of your environment, so based on additional information not posted in the original question and any gotchas springing up later cannot be blamed on forum contributions.
2. From the questions you asked on how to create a table script, I advise you not to get over your head -- make sure you understand what you are dealing with, since producing a report for a major business decision that later turns out to be wrong (for instance, the effects of NULLS in a JOIN, etc.) can be far-reaching. SQL Server is powerful and has many subtleties that must be learned. Forget the idea that Structure Query Language was about allowing to retrieve data by writing English-like queries so that the layman could use it as well, without having developed experience in SQL programming.
3. The point that was repeated in reply to your original post was that you have to provide us with the means to do some basic testing. Which means:
. a) we must be able to recreate the exact tables you are using
. b) we also need representative test data to enter in these tables
. WITHOUT having to type it in.
4. The link given in previous responses to your request is a guide as HOW to ask a question so that forum members can provide a meaningful answer. If you have not done so yet, please return to this and review it.
5. Instead of receiving multiple questions to your own questions, you probably would have a working solution by now if you had followed the proper way of asking the question.
So, however unpleasant my observations might be, I am in no way criticizing you for asking help in solving a real case, I enjoy helping. But you have do to some work first. Why should a half-dozen forum contributors be forced to type in bogus data instead of you posting the exact data you want to use for testing? After all, you do want TESTED suggestions... (at lest BASIC testing). And the bogus data devised by people outside your company may just miss important exceptions that only you can know about.
Welcome to the forum. A brave new world awaits you.
-
Nice post J.
Davroche, we were all newbies once!
Here's the type of code we were asking for, so you'll know next time you post.
Now, all you need to do is supply us with a sample result set and we'll get working 😉
[font="Courier New"]
--create a temp table to store our data
CREATE TABLE #MyTable
(
CustomerNumber INT
,PolicyNumber INT
,StartDate DATETIME
,Premium MONEY
,EndDate DATETIME
)
--now we need to insert some sample data into the table
INSERT INTO #MyTable
SELECT 10234567, 80567655, '3/15/2007', 97.0000, '3/14/2008'
UNION ALL SELECT 10234567, 80567656, '3/15/2007', 45.0000, '3/14/2008'
UNION ALL SELECT 10239656, 80577655, '3/15/2007', 97.0000, '3/14/2008'
UNION ALL SELECT 10240076, 80578066, '3/22/2007', 67.9800, '3/21/2008'
UNION ALL SELECT 10240076, 80578067, '3/22/2007', 22.0000, '3/21/2008'
UNION ALL SELECT 10240076, 80579001, '5/22/2007', 18.4587, '3/21/2008'
UNION ALL SELECT 10241034, 80580677, '6/03/2007', 44.0000, '6/02/2008'
UNION ALL SELECT 10242446, 80581433, '7/13/2007', 44.0000, '7/12/2008'
SELECT * FROM #MyTable
--clean up
DROP TABLE #MyTable[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply