There are 2 tables
dbo.Application :

 | id | RealResID | +----+-----------+ |337 | 4 | dbo.Resolutions | id | AppID | ResID | date | CreatedAt | +------+-------+-------+------------+------------+ | 114 | 337 | 2 | null | 2016-12-12 | | 257 | 337 | 3 | 2016-10-27 | 2016-12-12 | | 300 | 337 | 4 | 2016-11-01 | 2016-12-12 |

You need to select a date if ResID = 4 then select a date not according to your own, but on 2016-10-27 ( ResID = 3 )
And in other cases, choose a date according to your own (if
ResID = 2 then null , ResID = 3 then 2016-10-27 )
My request:

 select id, case when ResID = 3 then date case when ResID = 4 then (case when ResID = 3 then date) else createdAt from dbo.Application a left join Resolutions r on a.id = r.appID and r.ResID = a.RealResID
  • case when ... then [else] end ! Here is the select id, case when ResID = 4 then cast( '2016-10-27' as date ) else date end from dbo.Application and the feature, the type in case when should be the same . Well, go to msdn on the website of the Borg, everything is beautiful with examples. - nick_n_a
  • A very similar question was already on SO ru.stackoverflow.com/q/557974/17974 - nick_n_a
  • 2
    I should add, it should not be, but preferably, mssql leads both answers from case when to the type that is first. If the expression after then is a string, then else converts to a string. Therefore, I set a cast, but you can cast a non- select id, case when ResID <> 4 then date else '2016-10-27' end from dbo.Application or a parameter ? or @date instead of a constant. - nick_n_a
  • A completely irresponsible description ... but from what you can think out - you need to use the second copy of the table to take data from it, if the condition of substitution is true. - Akina

1 answer 1

Describe what you want to get.

But try this query:

 select src.id, case src.ResId when 3 then src.date when 4 then repl.date else src.CreatedAt end as VeryCoolDate from dbo.Application src LEFT JOIN dbo.Application repl ON src.ResId = repl.id

The dependent subquery in the CASE construction is not very well used for various reasons, but it is also possible:

 select id, case ResId when 3 then date when 4 then ( select date from dbo.Application repl where src.ResId = repl.id ) else CreatedAt end as VeryCoolDate from dbo.Application src