earwig75
asked on
How can I use one field instead of another in an SQL query when conditions apply
In the query below, if personOne is null, I want to output personTwo as "personOne". Can someone assist? I tried using this but it does't work
SELECT
id,
timeIn,
CASE WHEN timeOut IS NULL THEN GETDATE() ELSE timeOut END AS TimeOut, -- or ISNULL(timeOut, GETDATE()) AS timeOut,
carType,
CASE WHEN personOne IS NULL THEN personTwo ELSE personOne END AS personOne, --this doesn't work.
limitTimeToCheckIn,
CASE WHEN name_me_1 > timeOut THEN 1 ELSE NULL END AS name_me_2
FROM (
SELECT id, timeIn, timeOut, carType, personOne, personTwo, limitTimeToCheckIn, DATEADD(mi, limitTimeToCheckIn, timeIn) AS name_me_1
FROM masterCarTable
LEFT JOIN carTypesTable ON masterCarTable.carType = carTypesTable.carType) a
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>if personOne is null, I want to output personTwo as "personOne".
The below will return the first non-NULL value it finds, so if PersonTwo IS NULL it will return personOne.
ISNULL(personTwo, PersonOne)
... if you meant the string "PersonOne", then ...
ISNULL(personTwo, 'PersonOne')
... if you want to do this with more than two values ...
COALESCE(personTwo, personOne, 'Nobody Loves Me')
The below will return the first non-NULL value it finds, so if PersonTwo IS NULL it will return personOne.
ISNULL(personTwo, PersonOne)
... if you meant the string "PersonOne", then ...
ISNULL(personTwo, 'PersonOne')
... if you want to do this with more than two values ...
COALESCE(personTwo, personOne, 'Nobody Loves Me')
ASKER
I should have mentioned, there are other conditions, not just NULL.
personOne and personTwo are both columns, not strings.
I updated the query below:
personOne and personTwo are both columns, not strings.
I updated the query below:
SELECT
id,
timeIn,
CASE WHEN timeOut IS NULL THEN GETDATE() ELSE timeOut END AS TimeOut, -- or ISNULL(timeOut, GETDATE()) AS timeOut,
carType,
CASE WHEN personOne IS NULL and status = 'Done' THEN personTwo ELSE personOne END AS personOne, --this doesn't work.
limitTimeToCheckIn,
CASE WHEN name_me_1 > timeOut THEN 1 ELSE NULL END AS name_me_2
FROM (
SELECT id, timeIn, timeOut, carType, status, personOne, personTwo, limitTimeToCheckIn, DATEADD(mi, limitTimeToCheckIn, timeIn) AS name_me_1
FROM masterCarTable
LEFT JOIN carTypesTable ON masterCarTable.carType = carTypesTable.carType) a
So, with your last update, give us a situation that the expected result is not the expected one.
ASKER
When I try the query with my last update, personOne is null, status is "done" and personTwo is a value, but instead of outputting personOne with the value in personTwo, it stays null.
Thank you.
Thank you.
Maybe personTwo is also NULL it that case?
Try this..
Hope it helps !!
SELECT
id
,timeIn
,CASE WHEN timeOut IS NULL THEN GETDATE() ELSE timeOut END TimeOut
,carType
,CASE WHEN personOne IS NULL THEN personTwo ELSE personOne END personOne
,limitTimeToCheckIn
,CASE WHEN DATEADD(mi, limitTimeToCheckIn, timeIn) > timeOut THEN 1 ELSE NULL END name_me_2
FROM masterCarTable
LEFT JOIN carTypesTable ON masterCarTable.carType = carTypesTable.carType
Hope it helps !!
You can have multiple conditions . . . this is just a sample but see below. Please define what "doesn't means" or what your expected results should be as requested by others.
CASE WHEN personOne IS NULL THEN personTwo
WHEN personTwo IS NULL THEN 'Unknown'
ELSE personOne END AS personOne,
Are you sure that PersonOne is NULL? Perhaps it's blank. That would require a different test.
Re:
WHEN personOne IS NULL THEN personTwo ELSE personOne END AS personOne
Your end overrides your assignment to personTwo
WHEN personOne IS NULL THEN personTwo ELSE personOne END AS personOne
Your end overrides your assignment to personTwo
ASKER
I apologize, I noticed I had the same column listed twice in my SQL. My original question was answered correctly so I will credit that answer. Thanks again.
The syntax certainly looks fine. What results are you getting?
Kent