SQLTeam.com | Weblogs | Forums

Update a column based on 2 columns from another table



i have a problem that i dont know how to resolve. i have 2 tables and i need to update a column from one of them based on 2 columns from the other table.

table Y

ID Final

table X:
ID Date Quote
A febr 2016 valid
A nov 2017 expired
B ian 2015 valid
B aug 2014 valid
C sept 2017 expired
D july 2016 expired
D aug 2017 valid
F blank valid

i need to fill in the column Final based on the column ID, Date and Quote as such:

table Y

ID Final
A febr 2016
B aug 2014
C all quotes expired
D aug 2017
E blank
F no quotes

where it finds the ID from table Y in table X, to retreive in column Final the oldest date but the quote to be valid.
for example: it finds the ID=A in table X with 2 dates: febr 2016 and nov 2017. For febr 2016 the quote is valid and for the other one is expired. The oldest one is febr 2016 and the quote is valid, so in the Final column it puts febr 2016.

ID=B. in table X it has 2 dates ian 2015 and aug 2014. Both of them are valid so it puts in column Final aug 2014.

ID=C in table X has only a date that is expired so it puts in column Final : all quotes expired. So where it finds all the quotes=expired for that particular ID (no matter the date), it has to put in column Final = all quotes expired

ID=D in table X has 2 dates: july 2016 and aug 2017. The oldest date is july 2016 but the quote is expired so it takes aug 2017 because the quote is valid.

ID=E it puts blank because it doesnt find this ID in table X

ID=F it puts "no quotes" because it doesnt have any date. So if it finds the ID in table X but it doesnt see any date, even if i have data in the Quote column, it put "no quotes"

i hope that someone can help.

thank you

SET Final = x.Final