September 3, 2014 at 2:51 pm
I have a Contact table where I enter a "Parent" (Mother or Father) with IsSubscriber = 1. I also enter all of their children in this same table, with IsDependent = 1.
I then have a Relationship table that relates each child to the appropriate parent record in the Contact table.
I need to assign a sequence number to each child ONLY if they were a multiple birth (twins, triplets, etc.; all have the same DOB). I've been successful at writing a query using ROW_NUMBER(), but it includes the single births (no other child of the same parent has the same DOB).
Stripped down version of Tables and Data and my failed attempt to write a query to do what I want:
IF OBJECT_ID('TempDB..#Contact','U') IS NOT NULL
DROP TABLE #Contact
CREATE TABLE #Contact (
ContactId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
, IsSubscriber BIT
, IsDependent BIT
, DOB DATE
, FirstName NVARCHAR(50)
, LastName NVARCHAR(50)
)
SET IDENTITY_INSERT #Contact ON
INSERT INTO #Contact (
ContactId
, IsSubscriber
, IsDependent
, DOB
, FirstName
, LastName
) (
SELECT 1, 1, 0, '', 'Parent', 'Contact'
UNION ALL
SELECT 2, 0, 1, '1/1/1999', 'Child1', 'Contact'
UNION ALL
SELECT 3, 0, 1, '10/26/2010', 'Child2 (Twin)', 'Contact'
UNION ALL
SELECT 4, 0, 1, '10/26/2010', 'Child3 (Twin)', 'Contact'
)
SET IDENTITY_INSERT #Contact OFF
IF OBJECT_ID('TempDB..#Relationship','U') IS NOT NULL
DROP TABLE #Relationship
CREATE TABLE #Relationship (
RelationshipId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
, ContactId INT
, ParentContactId INT
)
SET IDENTITY_INSERT #Relationship ON
INSERT INTO #Relationship (
RelationshipId
, ContactId
, ParentContactId
) (
SELECT 1, 2, 1
UNION ALL
SELECT 2, 3, 1
UNION ALL
SELECT 3, 4, 1
)
SET IDENTITY_INSERT #Relationship OFF
SELECT * FROM #Contact
SELECT * FROM #Relationship
;WITH cteMultipleBirths AS (
SELECT
t.ParentContactId
FROM #Relationship AS t
INNER JOIN #Contact AS tContact ON tContact.ContactId = t.ContactId
WHERE
tContact.DOB IS NOT NULL
GROUP BY
t.ParentContactId
, tContact.DOB
HAVING
COUNT(*) > 1
)
SELECT
t.ContactId
, tContact.FirstName
, tContact.LastName
, tContact.DOB
, ROW_NUMBER() OVER (PARTITION BY t.ParentContactId ORDER BY t.ParentContactId) AS BirthSequenceNumber
FROM #Relationship AS t
INNER JOIN #Contact AS tContact ON tContact.ContactId = t.ContactId
WHERE
t.ParentContactId IN (
SELECT
x.ParentContactId
FROM cteMultipleBirths AS x
)
This is as close as I can seem to get.
Any help would be great. And, if I used some bad etiquette in the code I've provided to work with please let me know.
Thank you.
September 3, 2014 at 3:10 pm
Adding DOB in your cte select and then doing an exists in the main query should return what you are looking for I believe.
WITH cteMultipleBirths AS (
SELECT
t.ParentContactId, tContact.DOB
FROM #Relationship AS t
INNER JOIN #Contact AS tContact ON tContact.ContactId = t.ContactId
WHERE
tContact.DOB IS NOT NULL
GROUP BY
t.ParentContactId
, tContact.DOB
HAVING
COUNT(*) > 1
)
SELECT
t.ContactId
, tContact.FirstName
, tContact.LastName
, tContact.DOB
, ROW_NUMBER() OVER (PARTITION BY t.ParentContactId ORDER BY t.ParentContactId) AS BirthSequenceNumber
FROM #Relationship AS t
INNER JOIN #Contact AS tContact ON tContact.ContactId = t.ContactId
WHERE
exists (
SELECT 1
FROM cteMultipleBirths AS x
WHERE x.ParentContactId = t.ParentContactId and x.DOB = tContact.DOB
)
September 3, 2014 at 3:19 pm
Or another way with an exists and no cte, there's probably a million different ways to do this...
select r.ContactId, c.FirstName, c.LastName, c.DOB, ROW_NUMBER() OVER (PARTITION BY r.ParentContactId ORDER BY r.ParentContactId) AS BirthSequenceNumber
from #Relationship r
join #Contact c on c.ContactId = r.ContactId
where exists (select * from #Relationship r2 join #Contact c2 on c2.ContactId = r2.ContactId where r2.ParentContactId = r.ParentContactId and c2.ContactId <> c.ContactId and c2.DOB = c.DOB)
September 4, 2014 at 7:25 am
Well shut the front door! I knew I was missing something! Your answer works great. Thank you.
September 4, 2014 at 7:59 am
Wanted to add one last bit to this post.
We actually have 3 parents that have multiple twins in our database. And, to make sure I always get the same number for each child every time I added the additional ORDER BY.
I changed the ROW_NUMBER() line to this:
ROW_NUMBER() OVER (PARTITION BY t.ParentContactId, tContact.DOB ORDER BY t.ParentContactId, tContact.FirstName) AS BirthSequenceNumber
September 4, 2014 at 8:06 am
Probably not a big deal, but maybe you would want to order by the tContact.ContactID instead of the first name in case you have a situation like George Foreman where all of his son's names are George. Probably not something you'll run into where two twins have the same name, but who knows.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy