Count the number of mobile phones and generate a summary using T-SQL

This is one of the challenges taken from BeyondRelational.

Below is the problem statement.

The challenge is to count the number of mobile phones each person has and generate a summary row.

Sample Input Data

Person Table

PersonId PersonName

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

1        Deepak Kumar Goyal

2        Niladri Biswas

ContactDetail Table

PersonId MobileNumber

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

1        9886551234,9445612356

2        9886334510

Expected Output

PersonId            PersonName            MobileNumber                            RecordCount

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

1                Deepak Kumar Goyal      9886551234,9445612356                      2

2                Niladri Biswas                  9886334510                                          1

                  Total Mobiles:                                                                                3

Script to generate the data

DECLARE @Person TABLE(PersonId INT,PersonName VARCHAR(20))

INSERT INTO @Person 

SELECT 1,'Deepak Kumar Goyal' UNION ALL

SELECT 2,'Niladri Biswas' 

SELECT * FROM @Person

DECLARE @ContactDetail TABLE(PersonId INT,MobileNumber VARCHAR(100))

INSERT INTO @ContactDetail

SELECT 1, '9886551234,9445612356' UNION ALL  

SELECT 2,'9886334510' 

SELECT * FROM @ContactDetail     

Also, below are the other Restrictions or notes that should be considered.

PersonId should be sorted in Ascending Order

If a person does not have any mobile, then his/her Record Count will be 0

If a person does not have any name (Blank or NULL assigned)or Mobile number, his/her record should not be counted

If a person does not have any name (Blank or NULL assigned) but is having Mobile Number, his/her record should not be counted

The program has to be done by a single query and should begin either with a SELECT or WITH statement with no variables, temporary table, table variables permitted.

You cannot use RBAR, cursors, loops etc. in your code. 

Solution

;WITH PC_Data

AS

(

      SELECT p.PersonId, p.PersonName, c.MobileNumber

      FROM @Person p

            LEFT OUTER JOIN @ContactDetail c

                  ON p.PersonId = c.PersonId

      WHERE p.PersonName IS NOT NULL AND

              ISNULL(p.PersonName,'') <> ''                    

),

COUNT_NUMBERS

AS

(

      SELECT p.PersonId,

               CASE WHEN p.MobileNumber IS NULL OR ISNULL(p.MobileNumber,'') = '' THEN 0

                        ELSE 1

               END AS count,

               CASE WHEN p.MobileNumber IS NULL OR ISNULL(p.MobileNumber,'') = '' THEN 0

                        ELSE CHARINDEX(',', p.MobileNumber,0)

               END AS char_index,

               CASE WHEN p.MobileNumber IS NULL OR ISNULL(p.MobileNumber,'') = '' THEN NULL

                        WHEN CHARINDEX(',', p.MobileNumber,0) <> 0

                              THEN SUBSTRING(p.MobileNumber, CHARINDEX(',', p.MobileNumber,0)+1, LEN(p.MobileNumber))

               END AS truncatedNumber                          

      FROM PC_Data p

     

      UNION ALL

     

      SELECT c.PersonId,

               CASE WHEN c.truncatedNumber IS NULL OR ISNULL(c.truncatedNumber,'') = '' THEN c.count

                        ELSE c.count + 1

               END AS count,

               CASE WHEN c.truncatedNumber IS NULL OR ISNULL(c.truncatedNumber,'') = '' THEN 0

                        ELSE CHARINDEX(',', c.truncatedNumber ,0)

               END AS char_index,

               CASE WHEN c.truncatedNumber IS NULL OR ISNULL(c.truncatedNumber,'') = '' THEN NULL

                        WHEN CHARINDEX(',', c.truncatedNumber,0) <> 0

                              THEN SUBSTRING(c.truncatedNumber, CHARINDEX(',', c.truncatedNumber,0)+1, LEN(c.truncatedNumber))

               END AS truncatedNumber                                        

      FROM COUNT_NUMBERS c

      WHERE truncatedNumber IS NOT NULL        

),

TOTAL

AS

(

      SELECT SUM(count) AS Total from COUNT_NUMBERS WHERE truncatedNumber IS NULL

),

RESULT

AS

(

      SELECT CONVERT (VARCHAR,T.PersonId) AS PersonId,T.PersonName, T.MobileNumber, T.RecordCount, ROW_NUMBER()OVER(ORDER BY T.PersonId) as rowNumber FROM

      (

            SELECT c.PersonId, p.PersonName, p.MobileNumber, c.count as RecordCount

                  FROM COUNT_NUMBERS c

                  INNER JOIN PC_Data p

                        ON c.PersonId = p.PersonId

            WHERE truncatedNumber IS NULL

           

            UNION

           

            SELECT 2147483647 AS PersonID, 'Total Mobiles:' as PersonName, '' as MobileNumber, Total

            FROM TOTAL

      )T

)

SELECT CASE WHEN R.PersonId = 2147483647 THEN '' ELSE R.PersonId END AS PersonId,

      R.PersonName, R.MobileNumber, R.RecordCount

FROM RESULT R

ORDER BY R.rowNumber

You can find other solutions for the same problem at BeyondRelational