Count statement in a view - 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 statement in a view

Author

Topic

spitfire122
Starting Member

11 Posts

Posted - 2011-03-08 : 15:52:47

i have a view and i'm trying to create 2 columns that counts how many valid phone#s are in a single record and how many bad phone#s the first 3 digits ID the bad ph# or blank. there are 6 columns for phone numbers. how would I write this in a view? use a ,case when statement?

AND 
                                    (                                               (LEFT(lp.primary_phone_number, 3) IN('000','111','222','333','444','555','666','777','888','999') OR lp.primary_phone_number = '')
                                                            AND 
                                                                                    (LEFT(lp.secondary_phone_number, 3) IN('000','111','222','333','444','555','666','777','888','999') OR lp.secondary_phone_number = '')
                                                            AND
                                                                                    (LEFT(lp.other_phone_number_1, 3) IN('000','111','222','333','444','555','666','777','888','999')  OR lp.other_phone_number_1 = '')
                                                            AND
                                                                                    (LEFT(lp.other_phone_number_2, 3) IN('000','111','222','333','444','555','666','777','888','999')  OR lp.other_phone_number_2 = '')
                                                            AND
                                                                                    (LEFT(lp.other_phone_number_3, 3) IN('000','111','222','333','444','555','666','777','888','999') OR lp.other_phone_number_3 = '')
                                    )

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-09 : 00:29:28

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

I have a view and I'm trying to create 2 columns that counts how many valid phone numbers are in a single record [sic: rows are not records] and how many bad phone numbers the first 3 digits the bad phone number or blank. there are 6 columns for phone numbers. How would I write this in a view? use a CASE statement [sic: expression, not statement]?

What format are the phone numbers in? International or ten digit local US? We don't fix problem after the fact; we write DDL to prevent it. First, get a list of area codes.

CREATE TABLE Phonebook
(customer_id CHAR(10) NOT NULL
REFERENCES Customers(customer_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
phone_priority INTEGER NOT NULL
CHECK (phone_priority BETWEEN 1 AND 6),
area_code CHAR(3) NOT NULL
CHECK(area_code IN < list here >)
exchange_code CHAR(3) NOT NULL
CHECK(exchange_code LIKE '[1-9][0-9][0-9]'),
phone_nbr CHAR(4) NOT NULL
CHECK( phone_nbr LIKE '[0-9][0-9][0-9][0-9]')
);

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-09 : 07:32:10

You could do something like

SELECT CASE WHEN LEFT(lp.primary_phone_number, 3) IN('000','111','222','333','444','555','666','777','888','999') OR lp.primary_phone_number = '')

THEN 1 ELSE 0 END) as BadPrimaryPhoneNumber

FROM yourTable

It would be a good idea to make a table of bad area codes and join to it, or every time you find a new one you'll have to do a lot code updating.

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-03-09 : 10:52:55

i would second Jimf on creation of new table.

Also why you're having 6 different fields for storing phone numbers?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources