Compare if multiple DateOfBirth (rows) for each person are equal or not 1

[waubain]

Our legacy system has the ability to enter multiple DateOfBirth entries for the same individual. I am trying to figure out how to compare an unknown number of rows for each person's DateOfBirth. If they match then @NewDOB = DateOfBirth Else NULL. I know I can compare rows with the LEAD function, but not sure how to use that if there are an unknown number of rows. Here is an example of data and an less than stellar attempt at a loop. The id identifies an individual. In id 1-3 the DateOfBirth are equal, but id 4-5 are unequal.
I did see an example of comparing with a WHERE statement, but it is still not clear how to make that work.

I am open to any solution, not necessarily a loop. This was just my old brain's logic.

/*
CREATE TABLE #demo(id int, DateOfBirth date)
INSERT INTO #demo VALUES (1, '1948-11-25')
		        ,(2, '1948-07-31')
		        ,(2, '1948-07-31')
                          ,(3, '1961-03-12)
			,(3, '1961-03-12')
			,(3, '1961-03-12')
			,(4, '1935-06-15')
		         ,(4, '1953-06-15')
                          ,(5, '1900-08-01')
			,(5, '1920-03-12')
			,(5, '1920-12-03') 
*/    

DECLARE @LoopNum int
DECLARE @NewDOB date
SELECT @LoopNum = MAX(RowId) FROM (SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY DateOfBirth) AS RowId FROM #demo) 

Q1

;With dob AS
(
SELECT id, DateOfBirth, ROW_NUMBER() OVER (PARTITION BY id ORDER BY DateOfBirth) AS RowId FROM #demo
)

WHILE (SELECT RowId FROM dob) <= @LoopNum
BEGIN
	SELECT DateOfBirth	FROM #demo
	IF (SELECT DateOfBirth FROM #Demo) <> LEAD(DateOfBirth, RowId) OVER ( PARTITION BY id ORDER BY DateOfBirth)
		SET @NewDOB = NULL
		BREAK
	ELSE
		SET @NewDOB = #demo.DateOfBirth
		CONTINUE
END

Thank you for any assistance.

You don't know what you don't know...

 

[gmmastros]

There's a neat trick you can use for this.

You can easily calculate the max date of birth and the min date of birth. If they are equal, return the date of birth, otherwise NULL.

Like this:

Select Id, 
       Case When Min(DateOfBirth) = Max(DateOfBirth) 
            Then Max(DateOfBirth)
            Else NULL 
            End As DateOfBirth
From   #Demo
Group BY Id

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[waubain]

George,
Thank you.

I am embarassed how simple was this solution. I was making it way to hard.


You don't know what you don't know...

 

[gmmastros]

Please don't be embarrassed. Sometimes it just takes another set of eyes to see what you're looking for.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom