Select different columns based on specific date in different columns

**amitpatel66** · Feb 15 '08, 01:43 PM

Originally posted by gertie2008

Say I have a ContactDetails table with the following fields:
ContactID, ContactName, DateOfBirth, SpouseName, SpouseDateOfBir th, ChildName, ChildDateOfBirt h (and some other fields not worth mentioning)
I pass in 2 parameters: eg. fromDate='19001 111' and toDate='2007111 1'
If DateOfBirth is between fromDate and toDate I want to return ContactName and DateOfBirth
AND If SpouseDateOfBir th is between fromDate and toDate I want to return SpouseName and SpouseDateOfBir th
AND If ChildDateOfBirt h is between fromDate and toDate I want to return ChildName and ChildDateOfBirt h

If it is possible, how would a query look which returns these columns?

Code:

SELECT CASE WHEN DateOfBirth BETWEEN DATE_FORMAT('19001111','%Y%m%d') AND DATE_FORMAT('20071111','%Y%m%d') THEN contactname ELSE NULL END,
 CASE WHEN spouseDateOfBirth BETWEEN DATE_FORMAT('19001111','%Y%m%d') AND DATE_FORMAT('20071111','%Y%m%d') THEN spousename ELSE NULL END,
CASE WHEN childDateOfBirth BETWEEN DATE_FORMAT('19001111','%Y%m%d') AND DATE_FORMAT('20071111','%Y%m%d') THEN childname ELSE NULL END
FROM
ContactDetails

**gertie2008** · Mar 17 '08, 10:23 AM

Originally posted by amitpatel66

[code=mysql]

SELECT CASE WHEN DateOfBirth BETWEEN DATE_FORMAT('19 001111','%Y%m%d ') AND DATE_FORMAT('20 071111','%Y%m%d ') THEN contactname ELSE NULL END,
CASE WHEN spouseDateOfBir th BETWEEN DATE_FORMAT('19 001111','%Y%m%d ') AND DATE_FORMAT('20 071111','%Y%m%d ') THEN spousename ELSE NULL END,
CASE WHEN childDateOfBirt h BETWEEN DATE_FORMAT('19 001111','%Y%m%d ') AND DATE_FORMAT('20 071111','%Y%m%d ') THEN childname ELSE NULL END
FROM
ContactDetails

[/code]

Thanks for your help

Select different columns based on specific date in different columns

Select different columns based on specific date in different columns

Comment

Comment