Say I have a ContactDetails table with the following fields:
ContactID, ContactName, DateOfBirth, SpouseName, SpouseDateOfBirth, ChildName, ChildDateOfBirth (and some other fields not worth mentioning)
I pass in 2 parameters: eg. fromDate='19001111' and toDate='20071111'
If DateOfBirth is between fromDate and toDate I want to return ContactName and DateOfBirth
AND If SpouseDateOfBirth is between fromDate and toDate I want to return SpouseName and SpouseDateOfBirth
AND If ChildDateOfBirth is between fromDate and toDate I want to return ChildName and ChildDateOfBirth
If it is possible, how would a query look which returns these columns?
2 1643
Say I have a ContactDetails table with the following fields:
ContactID, ContactName, DateOfBirth, SpouseName, SpouseDateOfBirth, ChildName, ChildDateOfBirth (and some other fields not worth mentioning)
I pass in 2 parameters: eg. fromDate='19001111' and toDate='20071111'
If DateOfBirth is between fromDate and toDate I want to return ContactName and DateOfBirth
AND If SpouseDateOfBirth is between fromDate and toDate I want to return SpouseName and SpouseDateOfBirth
AND If ChildDateOfBirth is between fromDate and toDate I want to return ChildName and ChildDateOfBirth
If it is possible, how would a query look which returns these columns?
-
-
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
-
-
-
-
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
-
-
Thanks for your help
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Tom Urbanowicz |
last post by:
I have a table with 100+ columns, for which I'm trying to retrieve
only 1 specific record. For this single record, I do not know which of
the columns are NULL, and which are populated.
I would...
|
by: serge |
last post by:
Using "SELECT * " is a bad practice even
when using a VIEW instead of a table?
I have some stored procedures that are
identical with the difference of one statement
in the WHERE clause. If I...
|
by: grunar |
last post by:
After some thought on what I need in a Python ORM (multiple primary
keys, complex joins, case statements etc.), and after having built
these libraries for other un-named languages, I decided to...
|
by: syounger |
last post by:
Hi. I have a report in Access 2000 that is based on selection made
from a series of interdependent list boxes. The boxes I have right now
are Source, Table, Column, Date. The user chooses Source...
|
by: Vagabond Software |
last post by:
I posted this problem earlier and now have more information. I use the DataTable Select method to get find the most recent "LastModified" date in a table of similarly named files. I then use the...
|
by: MP |
last post by:
vb6,ado,mdb,win2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(const) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source="...
|
by: =?Utf-8?B?SmF5IFBvbmR5?= |
last post by:
Based on wether a row is selected in a GridView I need to HIDE the last two
columns of a gridview. I do NOT need to make the cells invisible I want to
hide the entire column.
When I set the...
|
by: Rickster66 |
last post by:
As Instructed this is a new thread regarding my original post: "Select Only 10 Columns Going Back"
I'm sorry for the late response. I've been gathering up information and carefully with as much...
|
by: omerbutt |
last post by:
hi there i have to select 7 columns from table1, 1 column from table2, and 1 column from tables3 and show the result but i am not getting it right the main thing that i am trying to achieve is that i...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |