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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply