Super query of the day - 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

Site Related Forums

The Yak Corral

Super query of the day

Author

Topic

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-12-23 : 15:40:36

[code]INSERT INTO UserPhone(UserID, Phone, PhoneType, Rank)
SELECT b.UserID, LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(b.Phone,'(', ''), ')', ''), ' ', ''), '-', ''))) As Phone, b.PhoneType,
(SELECT COUNT(*) + 1
FROM ( SELECT dbo.Users.intUserID AS UserID, dbo.Users.PreferredPhoneNumber As Phone, dbo.Users.PreferredPhoneType As PhoneType, 1 AS Rank FROM dbo.Users WHERE dbo.Users.PreferredPhoneNumber <> ''
UNION ALL SELECT dbo.Users.intUserID AS UserID, dbo.Users.Phone, 'Phone', 2 AS Rank FROM dbo.Users WHERE dbo.Users.Phone <> '' AND (dbo.Users.Phone <> PreferredPhoneNumber OR dbo.Users.PreferredPhoneType <> 'Phone')
UNION ALL SELECT dbo.Users.intUserID AS UserID, dbo.Users.TTY, 'TTY', 3 AS Rank FROM dbo.Users WHERE dbo.Users.TTY <> '' AND (dbo.Users.TTY <> PreferredPhoneNumber OR dbo.Users.PreferredPhoneType <> 'TTY')
UNION ALL SELECT dbo.Users.intUserID AS UserID, dbo.Users.Cell, 'Cell', 4 AS Rank FROM dbo.Users WHERE dbo.Users.Cell <> '' AND (dbo.Users.Cell <> PreferredPhoneNumber OR dbo.Users.PreferredPhoneType <> 'Cell')
UNION ALL SELECT dbo.Users.intUserID AS UserID, dbo.Users.Fax, 'Fax', 5 AS Rank FROM dbo.Users WHERE dbo.Users.Fax <> '' AND (dbo.Users.Fax <> PreferredPhoneNumber OR dbo.Users.PreferredPhoneType <> 'Fax')
UNION ALL SELECT dbo.Users.intUserID AS UserID, dbo.Users.Pager, 'Pager', 6 AS Rank FROM dbo.Users WHERE dbo.Users.Pager <> '' AND (dbo.Users.Pager <> PreferredPhoneNumber OR dbo.Users.PreferredPhoneType <> 'Pager')) c
WHERE c.UserID = a.intUserID
AND b.Rank > c.Rank) AS Rank
FROM dbo.Users a INNER JOIN (
SELECT dbo.Users.intUserID AS UserID, dbo.Users.PreferredPhoneNumber As Phone, dbo.Users.PreferredPhoneType As PhoneType, 1 AS Rank FROM dbo.Users WHERE dbo.Users.PreferredPhoneNumber <> ''
UNION ALL SELECT dbo.Users.intUserID AS UserID, dbo.Users.Phone, 'Phone', 2 AS Rank FROM dbo.Users WHERE dbo.Users.Phone <> '' AND (dbo.Users.Phone <> PreferredPhoneNumber OR dbo.Users.PreferredPhoneType <> 'Phone')
UNION ALL SELECT dbo.Users.intUserID AS UserID, dbo.Users.TTY, 'TTY', 3 AS Rank FROM dbo.Users WHERE dbo.Users.TTY <> '' AND (dbo.Users.TTY <> PreferredPhoneNumber OR dbo.Users.PreferredPhoneType <> 'TTY')
UNION ALL SELECT dbo.Users.intUserID AS UserID, dbo.Users.Cell, 'Cell', 4 AS Rank FROM dbo.Users WHERE dbo.Users.Cell <> '' AND (dbo.Users.Cell <> PreferredPhoneNumber OR dbo.Users.PreferredPhoneType <> 'Cell')
UNION ALL SELECT dbo.Users.intUserID AS UserID, dbo.Users.Fax, 'Fax', 5 AS Rank FROM dbo.Users WHERE dbo.Users.Fax <> '' AND (dbo.Users.Fax <> PreferredPhoneNumber OR dbo.Users.PreferredPhoneType <> 'Fax')
UNION ALL SELECT dbo.Users.intUserID AS UserID, dbo.Users.Pager, 'Pager', 6 AS Rank FROM dbo.Users WHERE dbo.Users.Pager <> '' AND (dbo.Users.Pager <> PreferredPhoneNumber OR dbo.Users.PreferredPhoneType <> 'Pager')) b ON (a.intUserID = b.UserID)
WHERE LEN(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(b.Phone,'(', ''), ')', ''), ' ', ''), '-', '')))) = 10
ORDER BY a.strLastName ASC, a.strFirstName ASC, a.intUserID ASC, b.Rank ASC[/code]

This query converts the following crappy design of having multiple phone numbers from this

Users
UserID
UserName
FirstName
LastName
Phone
TTY
Cell
Fax
Pager
PreferredPhoneNumber
PreferredPhoneType

To this

UserPhone
UserID
Phone
PhoneType
Rank - If rank is equal to 1 then it is their preferred phone type.

Does anyone else have a better super query of the day?

Dustin Michaels

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-12-23 : 16:03:54

[code]Select [snicker] = 'I''m off until ' + convert(nvarchar,dateadd(d,38353,0),101)[/code]

nice query though

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources