How to differentiate the age field based on ages? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to differentiate the age field based on ages?

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2011-03-21 : 11:15:01

Hi I need to define the relationship field based on ages.

1.Family = (2 age) cst.age >18 and 1-4 cst.age < 18
2.Single = 1 cst.age> 18 plus 1-4 cst.age< 18
3.Couple = 2 cst.age both > 18
4.everything else = Individual

in my db, if there are 5 customers in a policy(for ex father/mother+ 3 children),then i have 5 rows with same policyid but they have diferent name and ages

Here the main question is,

1.How to differntiate only 2 cst.age is 18 + and other 4 is less than 18,if i have a policy which has 5 cst.age

2.How to write a query based on all these condition to satisfied?

Any help would be highly appreciated.Many Thanks.

Regards,
SG

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-21 : 11:20:56

You're going to have to provide table structure (CREATE TABLE statement) along with some sample data and the expected output based on that sample.

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-21 : 11:26:16

and supply sample data and expected results

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

satheesh
Posting Yak Master

152 Posts

Posted - 2011-03-21 : 11:50:50

Hi All,

I attched few sample data.Need to update the relationship field like i mentioned.

ex data

PolicyidAge Relationship(expected outcome)
1000 70 Individual
1000 66 Individual
1000 70 Individual
2000 41 Single Family
2000 11 Single Family
2000 13 Single Family
3000 20 Couple
3000 53 Couple

Like this my table will be there.For ex:poliyid 1000 has 3 customer age all are above 18 so the relationship be individual.

id -2000 has 1 age 18+ and other 2 age less than 18 so the relationship be single family.

like this i need to validate the rule and need to update the relationship field.

Hope you understand.Thanks

Regards,
SG.

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-21 : 13:02:59

What's the rule for couple?

2 Rows over 18?

Have you heard of Charlie Sheen?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-21 : 13:20:54

[code]

CREATE TABLE myTable99(Policyid int,Age int, Relationship varchar(50))
GO

INSERT INTO myTable99(Policyid, Age, Relationship)
SELECT 1000, 70, 'Individual' UNION ALL
SELECT 1000, 66, 'Individual' UNION ALL
SELECT 1000, 70, 'Individual' UNION ALL
SELECT 2000, 41, 'Single Family' UNION ALL
SELECT 2000, 11, 'Single Family' UNION ALL
SELECT 2000, 13, 'Single Family' UNION ALL
SELECT 3000, 20, 'Couple' UNION ALL
SELECT 3000, 53, 'Couple'
GO

SELECT DISTINCT PolicyId, 'Single Family' AS Relationship
FROM myTable99 o
WHERE EXISTS (SELECT PolicyId, COUNT(*)
FROM myTable99 i1
WHERE Age < 18
AND o.PolicyId = i1.PolicyId
GROUP BY PolicyId
HAVING COUNT(*) <> 0)
AND EXISTS (SELECT PolicyId, COUNT(*)
FROM myTable99 i2
WHERE Age > 18
AND o.PolicyId = i2.PolicyId
GROUP BY PolicyId
HAVING COUNT(*) > 0)
UNION ALL
SELECT DISTINCT PolicyId, 'Couple' AS Relationship
FROM myTable99 o
WHERE NOT EXISTS (SELECT PolicyId, COUNT(*)
FROM myTable99 i1
WHERE Age < 18
AND o.PolicyId = i1.PolicyId
GROUP BY PolicyId
HAVING COUNT(*) <> 0)
AND EXISTS (SELECT PolicyId, COUNT(*)
FROM myTable99 i2
WHERE Age > 18
AND o.PolicyId = i2.PolicyId
GROUP BY PolicyId
HAVING COUNT(*) = 2)
UNION ALL
SELECT DISTINCT PolicyId, 'Individual' AS Relationship
FROM myTable99 o
WHERE NOT EXISTS (SELECT PolicyId, COUNT(*)
FROM myTable99 i1
WHERE Age < 18
AND o.PolicyId = i1.PolicyId
GROUP BY PolicyId
HAVING COUNT(*) <> 0)
AND EXISTS (SELECT PolicyId, COUNT(*)
FROM myTable99 i2
WHERE Age > 18
AND o.PolicyId = i2.PolicyId
GROUP BY PolicyId
HAVING COUNT(*) > 2)
GO

DROP TABLE myTable99
GO
[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources