February 24, 2010 at 6:42 am
I need to select a distinct acct_nb from a table that could contain duplicate acct_nb's with different phone statuses. The requirements are:
Select the distinct acct_nb and apply a stat_cd of 'Y' (when phone_stat_cd = 'G' or 'C') or 'N' (when phone_stat_cd is other than 'G' or 'C') from a table that contains duplicate acct_nb's with different phone_nb's and the same or different phone_stat_cd's.
The purpose is the business wants to know if they can dial an account based on the phone_stat_cd being a 'G' (good phone) or 'C' (cell phone). They don't care how many phone numbers we have for an account, they only want to know if any of the phone numbers have the status of 'G' or 'C', and if so, give them the acct_nb and a 'Y' for the status. Even if the same acct_nb has a different phone with a status other than 'G' or 'C', they deem this account as dialable and want a 'Y' for the indicator. If an account has all the phone_stat_cd's not equal to 'G' or 'C', include the acct_nb and a 'N' for the status.
Data Example:
Acct_nb: 1
Phone_nb: 6165276980
Phone_stat_cd: G
Acct_nb: 1
Phone_nb: 6165277722
Phone_stat_cd: B
Acct_nb: 2
Phone_nb: 5126447480
Phone_stat_cd: N
Acct_nb: 2
Phone_nb: 6165276870
Phone_stat_cd: B
End result should be:
Acct_nb 1 Y
Acct_nb 2 N
Thanks in advance for any help offered. I'm spinning my wheels trying to get this to work.
February 24, 2010 at 6:55 am
You know, the people that help out here are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 24, 2010 at 7:05 am
declare @Tmp table (acct_nb int, phone_nb varchar(10), phone_stat_cd char(1))
insert into @Tmp values (1, '6165276980', 'G')
insert into @Tmp values (1, '6165277722', 'B')
select x.acct_nb
, case when x.phone_stat_cd = 1 then 'Y' else 'N' end as phone_stat_cd
from (
select acct_nb
, max(case when phone_stat_cd in ('G', 'C') then 1 else 0 end) as phone_stat_cd
from @Tmp
group by acct_nb
) x
_____________________________________________________________________
- Nate
February 24, 2010 at 7:23 am
RP_DBA
February 24, 2010 at 7:29 am
RP_DBA - thanks so much. This looks like it's working. I'm going to apply it to all my test cases to ensure.
The max(case when phone_stat_cd in ('G', 'C') then 1 else 0 end) is new to me but provides a good learning experience. I was trying to use a CASE statement but it wasn't producing the desired result and obviously didn't look like the one you provided.
Wayne - thanks for replying and guiding me toward Forum Etiquette. In the future I will do what the Etiquette article suggests. I realize the time it will save people who want to help.
February 24, 2010 at 8:22 am
No problem, glad I could help.
_____________________________________________________________________
- Nate
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply