Unique Record - 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

Unique Record

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2013-08-23 : 07:41:10

Hi,

I have the following code which gives results as shown below;

SELECT     dbo.tblPractice.PracticeCode, dbo.tblPractice.Practicename, dbo.tblPractice.DateJoined, dbo.tblRegion.Name, 
                      dbo.tblContacts.FirstName + ' ' + dbo.tblContacts.LastName AS FullName, dbo.tblContactType.ContactType
FROM         dbo.tblPractice INNER JOIN
                      dbo.tblRegion ON dbo.tblPractice.RegionID = dbo.tblRegion.RegionId INNER JOIN
                      dbo.tblContacts ON dbo.tblPractice.PracticeCode = dbo.tblContacts.PracticeCode INNER JOIN
                      dbo.tblContactType ON dbo.tblContacts.ContactTypeID = dbo.tblContactType.ContactTypeID

Results:

Practicecode practicename DateJoined Name FullName ContactType

A1 A 01/01/2001 London John Smith Primary
A1 A 01/01/2001 London Peter Tim Secondary

I want to update my query so that it has ONLY one record as below;

Practicecode practicename DateJoined Name PriFullName SecFullName
A1 A 01/01/2001 London John Smith Peter Tim

Thank you

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-23 : 08:24:59

[code]SELECT DISTINCT dbo.tblPractice.PracticeCode, dbo.tblPractice.Practicename, dbo.tblPractice.DateJoined, dbo.tblRegion.Name,
CASE WHEN dbo.tblContactType.ContactType = 'Primary' THEN dbo.tblContacts.FirstName + ' ' + dbo.tblContacts.LastName AS PriFullName,
CASE WHEN dbo.tblContactType.ContactType = 'Secondary' THEN dbo.tblContacts.FirstName + ' ' + dbo.tblContacts.LastName AS SecFullName
FROM dbo.tblPractice INNER JOIN
dbo.tblRegion ON dbo.tblPractice.RegionID = dbo.tblRegion.RegionId INNER JOIN
dbo.tblContacts ON dbo.tblPractice.PracticeCode = dbo.tblContacts.PracticeCode INNER JOIN
dbo.tblContactType ON dbo.tblContacts.ContactTypeID = dbo.tblContactType.ContactTypeID
[/code]

--
Chandu

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2013-08-23 : 09:00:01

Error Message: Incorrect syntax near the keyword 'AS'.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-23 : 09:03:09

CASE WHEN dbo.tblContactType.ContactType = 'Primary' THEN dbo.tblContacts.FirstName + ' ' + dbo.tblContacts.LastName END AS PriFullName,
CASE WHEN dbo.tblContactType.ContactType = 'Secondary' THEN dbo.tblContacts.FirstName + ' ' + dbo.tblContacts.LastName AS END SecFullName

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-23 : 09:05:22

I think the following query gives one row for each dbo.tblPractice.PracticeCode, dbo.tblPractice.Practicename, dbo.tblPractice.DateJoined, dbo.tblRegion.Name combination

SELECT dbo.tblPractice.PracticeCode, dbo.tblPractice.Practicename, dbo.tblPractice.DateJoined, dbo.tblRegion.Name, 
       MAX(CASE WHEN dbo.tblContactType.ContactType = 'Primary' THEN dbo.tblContacts.FirstName + ' ' + dbo.tblContacts.LastName END )AS PriFullName, 
       MAX(CASE WHEN dbo.tblContactType.ContactType = 'Secondary' THEN dbo.tblContacts.FirstName + ' ' + dbo.tblContacts.LastName END )AS SecFullName
FROM dbo.tblPractice INNER JOIN
     dbo.tblRegion ON dbo.tblPractice.RegionID = dbo.tblRegion.RegionId INNER JOIN
     dbo.tblContacts ON dbo.tblPractice.PracticeCode = dbo.tblContacts.PracticeCode INNER JOIN
     dbo.tblContactType ON dbo.tblContacts.ContactTypeID = dbo.tblContactType.ContactTypeID
GROUP BY dbo.tblPractice.PracticeCode, dbo.tblPractice.Practicename, dbo.tblPractice.DateJoined, dbo.tblRegion.Name

--
Chandu

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2013-08-23 : 09:12:13

Thanks Bandi - the last solution works .. but I noticed you are calling MAX (i.e., one record for primary and secondary) but in some occassions I have 3 primary names and 2 secondary names, meaning I'll have 3 records (2 with both primary + secondary, and 1 record for primary only )

The solution above wont work as required..

Tried..

SELECT DISTINCT 
                      dbo.tblPractice.PracticeCode, dbo.tblPractice.Practicename, dbo.tblPractice.DateJoined, dbo.tblRegion.Name, 
                      (CASE WHEN dbo.tblContactType.ContactType = 'Primary' THEN dbo.tblContacts.FirstName + ' ' + dbo.tblContacts.LastName END) AS PriFullName, 
                      (CASE WHEN dbo.tblContactType.ContactType = 'Secondary' THEN dbo.tblContacts.FirstName + ' ' + dbo.tblContacts.LastName END) 
                      AS SecFullName
FROM         dbo.tblPractice INNER JOIN
                      dbo.tblRegion ON dbo.tblPractice.RegionID = dbo.tblRegion.RegionId INNER JOIN
                      dbo.tblContacts ON dbo.tblPractice.PracticeCode = dbo.tblContacts.PracticeCode INNER JOIN
                      dbo.tblContactType ON dbo.tblContacts.ContactTypeID = dbo.tblContactType.ContactTypeID
GROUP BY dbo.tblPractice.PracticeCode, dbo.tblPractice.Practicename, dbo.tblPractice.DateJoined, dbo.tblRegion.Name, dbo.tblContactType.ContactType, 
                      dbo.tblContacts.FirstName, dbo.tblContacts.LastName

it gives a record for each primary or secondary record

Please advice

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-23 : 09:34:26

If you have more than one primary & secondary name what should be your result?
May be this you want
GROUP BY dbo.tblPractice.PracticeCode, dbo.tblPractice.Practicename, dbo.tblPractice.DateJoined, dbo.tblRegion.Name, dbo.tblContactType.ContactType, dbo.tblContacts.FirstName + ' '+ dbo.tblContacts.LastName

--
Chandu

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2013-08-23 : 09:38:16

Tried

GROUP BY dbo.tblPractice.PracticeCode, dbo.tblPractice.Practicename, dbo.tblPractice.DateJoined, dbo.tblRegion.Name, dbo.tblContactType.ContactType, dbo.tblContacts.FirstName + ' '+ dbo.tblContacts.LastName

Similar results..

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-23 : 09:46:46

Have you tried with

SELECT dbo.tblPractice.PracticeCode, dbo.tblPractice.Practicename, dbo.tblPractice.DateJoined, dbo.tblRegion.Name, 
       MAX(CASE WHEN dbo.tblContactType.ContactType = 'Primary' THEN dbo.tblContacts.FirstName + ' ' + dbo.tblContacts.LastName END) AS PriFullName, 
       MAX(CASE WHEN dbo.tblContactType.ContactType = 'Secondary' THEN dbo.tblContacts.FirstName + ' ' + dbo.tblContacts.LastName END) 
                      AS SecFullName

.
.
.
GROUP BY dbo.tblPractice.PracticeCode, dbo.tblPractice.Practicename, dbo.tblPractice.DateJoined, dbo.tblRegion.Name, 
dbo.tblContactType.ContactType, dbo.tblContacts.FirstName + ' '+ dbo.tblContacts.LastName

--
Chandu

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2013-08-23 : 09:48:47

Yes ...

see below

SELECT     dbo.tblPractice.PracticeCode, dbo.tblPractice.Practicename, dbo.tblPractice.DateJoined, dbo.tblRegion.Name, 
                      MAX(CASE WHEN dbo.tblContactType.ContactType = 'Primary' THEN dbo.tblContacts.FirstName + ' ' + dbo.tblContacts.LastName END) AS PriFullName, 
                      MAX(CASE WHEN dbo.tblContactType.ContactType = 'Secondary' THEN dbo.tblContacts.FirstName + ' ' + dbo.tblContacts.LastName END) 
                      AS SecFullName
FROM         dbo.tblPractice INNER JOIN
                      dbo.tblRegion ON dbo.tblPractice.RegionID = dbo.tblRegion.RegionId INNER JOIN
                      dbo.tblContacts ON dbo.tblPractice.PracticeCode = dbo.tblContacts.PracticeCode INNER JOIN
                      dbo.tblContactType ON dbo.tblContacts.ContactTypeID = dbo.tblContactType.ContactTypeID
GROUP BY dbo.tblPractice.PracticeCode, dbo.tblPractice.Practicename, dbo.tblPractice.DateJoined, dbo.tblRegion.Name, dbo.tblContactType.ContactType, 
                      dbo.tblContacts.FirstName + ' ' + dbo.tblContacts.LastName

same reults

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2013-08-23 : 10:34:59

Pls help

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-27 : 06:21:40

Can You provide some sample data and expected output...?
We will provide you the exact solution

--
Chandu

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2013-08-27 : 06:36:35

[code]SELECT dbo.tblPractice.PracticeCode, dbo.tblPractice.DateJoined, dbo.tblContacts.FirstName + ' ' + dbo.tblContacts.LastName AS FullName, dbo.tblContactType.ContactType
FROM dbo.tblPractice INNER JOIN
dbo.tblRegion ON dbo.tblPractice.RegionID = dbo.tblRegion.RegionId INNER JOIN
dbo.tblContacts ON dbo.tblPractice.PracticeCode = dbo.tblContacts.PracticeCode INNER JOIN
dbo.tblContactType ON dbo.tblContacts.ContactTypeID = dbo.tblContactType.ContactTypeID [/code]

Results:
Practicecode DateJoined FullName ContactType
A1 01/01/2001 John Smith Primary
A1 01/01/2001 Peter Tim Secondary

I want to update my query so that it has ONLY one record as below;

Practicecode DateJoined PriFullName SecFullName
A1 01/01/2001 John Smith Peter Tim

Thank you

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-27 : 07:17:55

>> but in some occassions I have 3 primary names and 2 secondary names, meaning I'll have 3 records (2 with both primary + secondary, and 1 record for primary only )
Provide us the data for above scenario and expected output too.....

NOTE: I have already provided solution for single Primary and secondary contact types....

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-27 : 07:26:08

--May be this?
-- Sample Data with 3 primary & 2 Secondary Full Names
DECLARE @tab TABLE (Practicecode VARCHAR(5), DateJoined DATE, FullName VARCHAR(15), ContactType VARCHAR(15))
insert @tab
SELECT 'A1', '01/01/2001', 'John Smith1', 'Primary' union all
SELECT 'A1', '01/01/2001', 'John Smith2', 'Primary' union all
SELECT 'A1', '01/01/2001', 'John Smith3', 'Primary' union all
SELECT 'A1', '01/01/2001', 'Peter Tim1', 'Secondary' union all
SELECT 'A1', '01/01/2001', 'Peter Tim', 'Secondary'

-- Query
SELECT Practicecode, DateJoined, MAX(CASE WHEN ContactType = 'Primary' THEN FullName END) PrimName,
MAX(CASE WHEN ContactType = 'Secondary' THEN FullName END) SecdryName
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY Practicecode, ContactType ORDER BY (SELECT 1)) RN FROM @tab) T
GROUP BY Practicecode, DateJoined, RN

/*OUTPUT:
Practicecode DateJoined PrimName SecdryName
A1 2001-01-01 John Smith1 Peter Tim1
A1 2001-01-01 John Smith2 Peter Tim
A1 2001-01-01 John Smith3 NULL*/

--
Chandu

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2013-08-27 : 07:35:40

At run time I receive the error;

Msg 1087, Level 15, State 2, Line 3
Must declare the table variable "@tab".

Please help!!

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2013-08-27 : 07:44:19

bandi very clever -

SELECT     dbo.tblPractice.PracticeCode, dbo.tblPractice.Practicename, dbo.tblPractice.DateJoined, dbo.tblRegion.Name, 
                    MAX(CASE WHEN dbo.tblContactType.ContactType = 'Primary' THEN dbo.tblContacts.FirstName + ' ' + dbo.tblContacts.LastName  END) PrimName,
					MAX(CASE WHEN dbo.tblContactType.ContactType = 'Secondary' THEN dbo.tblContacts.FirstName + ' ' + dbo.tblContacts.LastName END) SecdryName
FROM         dbo.tblPractice INNER JOIN
                      dbo.tblRegion ON dbo.tblPractice.RegionID = dbo.tblRegion.RegionId INNER JOIN
                      dbo.tblContacts ON dbo.tblPractice.PracticeCode = dbo.tblContacts.PracticeCode INNER JOIN
                      dbo.tblContactType ON dbo.tblContacts.ContactTypeID = dbo.tblContactType.ContactTypeID
GROUP BY dbo.tblPractice.PracticeCode, dbo.tblPractice.Practicename, dbo.tblPractice.DateJoined, dbo.tblRegion.Name,dbo.tblContacts.FirstName + ' ' + dbo.tblContacts.LastName

this brings all records,

so know I want to update The FROM clause so that it accomodates ur example how can I achieve that..
Thanks you

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-27 : 07:48:01

Hi,

you should use your table name instead of @tab

-- Query

;WITH CteTab
AS 
(SELECT     dbo.tblPractice.PracticeCode, dbo.tblPractice.Practicename, dbo.tblPractice.DateJoined, dbo.tblRegion.Name, 
                      dbo.tblContacts.FirstName + ' ' + dbo.tblContacts.LastName AS FullName, dbo.tblContactType.ContactType
      ,ROW_NUMBER() OVER(PARTITION BY dbo.tblPractice.Practicecode, dbo.tblContactType.ContactType ORDER BY (SELECT 1)) RN 
FROM         dbo.tblPractice INNER JOIN
                      dbo.tblRegion ON dbo.tblPractice.RegionID = dbo.tblRegion.RegionId INNER JOIN
                      dbo.tblContacts ON dbo.tblPractice.PracticeCode = dbo.tblContacts.PracticeCode INNER JOIN
                      dbo.tblContactType ON dbo.tblContacts.ContactTypeID = dbo.tblContactType.ContactTypeID
)
SELECT Practicecode, DateJoined, MAX(CASE WHEN ContactType = 'Primary' THEN FullName END) PrimName,
MAX(CASE WHEN ContactType = 'Secondary' THEN FullName END) SecdryName
FROM CteTab
GROUP BY Practicecode, DateJoined, RN

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-27 : 07:52:30

What about my previous reply (Posted - 08/27/2013 : 07:48:01 ?
Have you look into it?

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources