Would this work ? Grouping Question

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

Would this work ? Grouping Question

Author

Topic

steve4134
Starting Member

24 Posts

Posted - 2012-07-20 : 00:05:42

Hey All

Thanks again for all your help. I have written a query which I think is correct. I would love if one of you masterminds can confirm. My goal is to group by contact ID so I don't have more than one owner of each of these policies and then have them ordered by state. Pretty much just want to count how many members have the following active statuses which i have in my where.Thats really it.

--------------------------------------------------------------------

SELECT MIN(insured_state_code) AS Expr1, contact_id, MIN(policy_status_code) AS Expr2
FROM cstd_policy
WHERE (policy_status_code = N'I') OR
(policy_status_code = N'I4') OR
(policy_status_code = N'IE') OR
(policy_status_code = N'II') OR
(policy_status_code = N'IP') OR
(policy_status_code = N'IR') OR
(policy_status_code = N'IV') OR
(policy_status_code = N'Y') OR
(policy_status_code = N'22')
GROUP BY contact_id
ORDER BY Expr1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-20 : 00:29:05

its synatctically correct but please keep in mind that it will just give you one value each for state and status codes for each contact_id which will be thier minimum values alphabetically but they may not necessarily be from the same row. Dunno whether thats exactly what you're looking at? posting sample data with expected output might help us to suggest

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-20 : 06:06:59

maybe yoou want something more like
;with cte as
(
SELECT insured_state_code AS Expr1, contact_id, policy_status_code AS Expr2 ,
seq = row_number() over (partition by contact_id order by insured_state_code)
FROM cstd_policy
WHERE policy_status_code in (N'I', N'I4',N'IE',N'II',N'IP',N'IR',N'IV',N'Y',N'22')
)
select * from cte where seq = 1

That will give the row for the minimum state code for the contact.
Not sure if that's what you want. I would expect the last one - so maybe order by a date desc?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

steve4134
Starting Member

24 Posts

Posted - 2012-07-21 : 13:22:13

Maybe I didn't explain my outcome clear enough.I have a bunch of members with multiple policies,I would like to first pull only all active policies.Then the policies that are active have all those codes indicated in the where. I only want 1 member even if they have multiple active polices.That's where I have the grouping on the contact_id.Then just sorted by insured state code and then a count of each of those codes.

steve4134
Starting Member

24 Posts

Posted - 2012-07-21 : 13:27:13

Sample of data as requested.

steve4134
Starting Member

24 Posts

Posted - 2012-07-21 : 13:33:26

What about this ?.

SELECT DISTINCT contact_id AS ID, MIN(insured_state_code) AS State
FROM cstd_policy
WHERE (policy_status_code IN (N'I', N'I4', N'IE', N'II', N'IP', N'IR', N'IV', N'Y', N'22'))
GROUP BY contact_id
ORDER BY State

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-21 : 14:41:01

quote:
Originally posted by steve4134

What about this ?.

SELECT DISTINCT contact_id AS ID, MIN(insured_state_code) AS State
FROM cstd_policy
WHERE (policy_status_code IN (N'I', N'I4', N'IE', N'II', N'IP', N'IR', N'IV', N'Y', N'22'))
GROUP BY contact_id
ORDER BY State

whats the sample output you're expecting out of it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

steve4134
Starting Member

24 Posts

Posted - 2012-07-22 : 11:56:35

Just to have it show how many individual contact id's we have with active polices. I havent even gotten to the count of each by state_id because i could always just export it to excel and chop it up there

steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-23 : 00:35:35

[code]
SELECT contact_id AS ID
FROM cstd_policy
WHERE (policy_status_code IN (N'I', N'I4', N'IE', N'II', N'IP', N'IR', N'IV', N'Y', N'22'))
GROUP BY contact_id
ORDER BY State
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources