Toad World® Forums

Combining column values based on the value of a third? Case Subquery?

I am not even sure if this is possible in SQL. It sounds like it might be a Case Subquery, but after playing around with it a bit I am not so sure anymore.

This is about constructing one column of dates, where the value of those dates are determined by two different queries. The decision of which query to run, is based on a third column text value.

So for example:

Main_Table
PerID StatusID bunchofothercolumnsineed
1 10 x
2 21 y
3 32 z
4 43 i
5 54 m

Status_Table
StatusID PerID Status Date
8 1 A 2000
9 1 I 2002
10 1 O 2003
15 2 I 2001
16 2 O 2005
21 2 U 2008
24 3 A 2005
26 3 I 2007
32 3 U 2009

So in some instances, the current Date is OK as in “U”, but in others, such as “O” I need to be able to use the “I” status date.

The current SQL I have would run it and get:
PerID Date bunchofothercolumnsineed
1 2003 x
2 2008 y
3 2009 z

But I need it modified to give me:
PerID Date bunchofothercolumnsineed
1 2002* x
2 2008 y
3 2009 z

I thought that perhaps I might be able to make a case statement and a subquery based on Statuses A and O, something like:

WHERE Main_Table.PerID = Status_Table.PerID AND Status_Table.Status = ‘I’

whereas anything else would be evaluated as (the current date value for I’s and U’s are what we want):

WHERE Main_table.StatusID = Status_Table.StatusID

but thus far I haven’t been able to come up with any syntax that works, nor am I even sure if that is the right method in the first place.

I tried to simplify the example rather than try and include the longer actual SQL and tables, as I really am just interested in if the concept is possible and what approach might work best.

Any insight you might be able to give me would help.

Thanks!