another date part question dealing with DOB

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

SQL Server 2008 Forums

Transact-SQL (2008)

another date part question dealing with DOB

Author

Topic

kpgraci
Yak Posting Veteran

68 Posts

Posted - 2011-02-16 : 14:14:22

I am trying to write a birth-date query. The user enters a start_date and end_date and I want to retrieve all clients who have a dob in that range.

So I really want to discount the year portion of the dates, unless the end_date extends into the next year.

So some cases may be: Feb/1/2011 to Mar/1/2011 or Nov/15/2010 to Jan/20/2011 for example. It would make no sense to have a range that extends over a year, because that would select all clients, so it can be assumed if end_date_month > start_date_month then we are talking about a data range that includes Jan 1 and is less than 1 year in length.

I could do this in multiple queries since I have a data class responsible for returning the results, so appending multiple query results is not a problem, but perhaps a performance issue. In this case I could query only the month portions, but this still leaves a problem of mid-month dates, for example, how would I select the range Nov/15 to Nov/30, returning all DOB in that date ranges regardless of year?

Any help appreciated!

kpg

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2011-02-16 : 14:58:44

[code]

-- Input parameters
DECLARE @start_date datetime = '11/1/11', @end_date datetime = '2/14/12';

-- Test data setup
DECLARE @BirthDates TABLE(BIRTH_DATE DATETIME);
INSERT @BirthDates VALUES
('2010-03-17 00:00:00.000'),
('2010-06-18 00:00:00.000'),
('2010-09-29 00:00:00.000'),
('2009-08-11 00:00:00.000'),
('2009-12-31 00:00:00.000'),
('2009-07-31 00:00:00.000'),
('2009-04-19 00:00:00.000'),
('2009-04-28 00:00:00.000'),
('2009-03-02 00:00:00.000'),
('2009-05-20 00:00:00.000'),
('2009-10-12 00:00:00.000'),
('2008-08-13 00:00:00.000'),
('2010-12-28 00:00:00.000'),
('2011-01-11 00:00:00.000'),
('2009-08-24 00:00:00.000'),
('2009-06-11 00:00:00.000'),
('2009-09-28 00:00:00.000'),
('2010-11-15 00:00:00.000'),
('2009-04-16 00:00:00.000'),
('2009-02-07 00:00:00.000');

-- First CTE normalizes birthdates to the current year
WITH cteBirthDateCurrentYear
AS
(
SELECT DATEADD(yy,DATEDIFF(yy,bd.BIRTH_DATE,@start_date),bd.BIRTH_DATE) BIRTH_DATE
FROM @BirthDates bd
)
-- Second CTE ensures birthdates match across year boundaries
, cteBirthDateCrossyear
AS
(
SELECT DATEADD(yy,CASE WHEN bd.BIRTH_DATE < @start_date THEN 1 ELSE 0 END,bd.BIRTH_DATE) BIRTH_DATE
FROM cteBirthDateCurrentYear bd
)
-- Return all results to show what matches and what doesn't
SELECT *, CASE WHEN c.BIRTH_DATE BETWEEN @start_date and @end_date THEN '**MATCHED**' ELSE '' END AS Status
FROM cteBirthDateCrossyear c
ORDER BY c.BIRTH_DATE;

[/code]

Not super happy with this, because this is non-sargable and will generate a table scan every time. If the table in question is small and will always stay small, this should work ok.

If you have a lot of data or expect to eventually have a lot of data, you should consider another solution.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2011-02-16 : 15:39:41

I don't have showplan rights on our SQL 2008 box, but I believe this method will make use of the index if you have a lot of data:


-- Input parameters
DECLARE @start_date datetime = '12/31/11', @end_date datetime = '1/1/12';


-- Test data setup
IF OBJECT_ID('tempdb..#BirthDates') IS NOT NULL DROP TABLE #BirthDates;
CREATE TABLE #BirthDates (ID INT IDENTITY(1,1) PRIMARY KEY, BIRTH_DATE DATETIME);

INSERT #BirthDates
select DATEADD(d,(spt1.number + spt2.number * POWER(2,11)) % 40515,'1/1/1900') as BIRTH_DATE
from master..spt_values spt1
JOIN master..spt_values spt2
ON spt1.type = spt2.type
AND spt2.number < 512
where spt1.[type] = 'P' 
ORDER BY NEWID(); --randomize insert

CREATE NONCLUSTERED INDEX ix_#BirthDates ON #BirthDates (BIRTH_DATE);

-- create a CTE to simplify the formula for the WHERE clause in the select
WITH cte1
AS
(
SELECT bd.*
,DATEADD(yy,DATEDIFF(yy,bd.BIRTH_DATE,0),bd.BIRTH_DATE) AS NORMALIZED_BIRTH_DATE
,DATEADD(yy,DATEDIFF(yy,@start_date,bd.BIRTH_DATE),@start_date) AS start_date
,DATEADD(yy,DATEDIFF(yy,@end_date,bd.BIRTH_DATE) + DATEDIFF(yy,@start_date,@end_date),@end_date) AS end_date
FROM #BirthDates bd
)
SELECT c.ID, c.BIRTH_DATE, c.NORMALIZED_BIRTH_DATE
FROM cte1 c
WHERE c.BIRTH_DATE 
BETWEEN DATEADD(yy,CASE WHEN c.BIRTH_DATE < c.start_date THEN -1 ELSE 0 END,c.start_date)
AND DATEADD(yy,CASE WHEN c.BIRTH_DATE < c.start_date THEN -1 ELSE 0 END,c.end_date)
ORDER BY c.NORMALIZED_BIRTH_DATE, c.BIRTH_DATE

IF OBJECT_ID('tempdb..#BirthDates') IS NOT NULL DROP TABLE #BirthDates;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources