How can i filter on where this new IIF column using SQL on Microsoft SQL MANAGER-CodePudding

I have this query with an IIF expression on select. How can I filter that new generated column called "ESTADO" on the where part of the SQL

Here is the code:

SELECT 
        EM.Id
        , C.Descripcion AS Clase
        , T.IdClaseEM
        , T.Descripcion AS Tipo
        , EM.IdTipoEM
        , EM.IdCentroMedico
        , CM.Descripcion AS CentroMedico
        , EM.FechaEvaluacion
        , EM.IdEmpleado
        , P.Nombres   P.ApellidoPaterno   P.ApellidoMaterno AS Persona
        , EM.Aptitud
        , IIF(EM.FechaCaducidad > GETDATE(), 'Vencido' , 'Vigente') AS Estado 
        , COUNT(*) OVER() TotalRecords
    FROM
        EvaluacionMedica AS EM
        INNER JOIN TipoEM AS T ON EM.IdTipoEM = T.Id            
        INNER JOIN ClaseEM AS C ON T.IdClaseEM = C.Id
        INNER JOIN Empleado AS E ON EM.IdEmpleado =  E.Id
        INNER JOIN Persona AS P ON E.IdPersona = P.Id
        LEFT JOIN CentroMedico AS CM ON EM.IdCentroMedico = CM.Id

And I want to do something like this

SELECT 
        EM.Id
        , C.Descripcion AS Clase
        , T.IdClaseEM
        , T.Descripcion AS Tipo
        , EM.IdTipoEM
        , EM.IdCentroMedico
        , CM.Descripcion AS CentroMedico
        , EM.FechaEvaluacion
        , EM.IdEmpleado
        , P.Nombres   P.ApellidoPaterno   P.ApellidoMaterno AS Persona
        , EM.Aptitud
        , IIF(EM.FechaCaducidad > GETDATE(), 'Vencido' , 'Vigente') AS Estado 
        , COUNT(*) OVER() TotalRecords
    FROM
        EvaluacionMedica AS EM
        INNER JOIN TipoEM AS T ON EM.IdTipoEM = T.Id            
        INNER JOIN ClaseEM AS C ON T.IdClaseEM = C.Id
        INNER JOIN Empleado AS E ON EM.IdEmpleado =  E.Id
        INNER JOIN Persona AS P ON E.IdPersona = P.Id
        LEFT JOIN CentroMedico AS CM ON EM.IdCentroMedico = CM.Id
    WHERE
        Estado = 'Vencido'

But it obviously it throw and error because there is no Estado column on the table. Can I just filter it by using the new created column on the IIF expression?

CodePudding user response：

You'd just do this in your where class:

WHERE
    IIF(EM.FechaCaducidad > GETDATE(), 'Vencido' , 'Vigente')= 'Vencido'

or as an alternative:

SELECT * FROM (SELECT 
    EM.Id
    , C.Descripcion AS Clase
    , T.IdClaseEM
    , T.Descripcion AS Tipo
    , EM.IdTipoEM
    , EM.IdCentroMedico
    , CM.Descripcion AS CentroMedico
    , EM.FechaEvaluacion
    , EM.IdEmpleado
    , P.Nombres   P.ApellidoPaterno   P.ApellidoMaterno AS Persona
    , EM.Aptitud
    , IIF(EM.FechaCaducidad > GETDATE(), 'Vencido' , 'Vigente') AS Estado 
    , COUNT(*) OVER() TotalRecords
FROM
    EvaluacionMedica AS EM
    INNER JOIN TipoEM AS T ON EM.IdTipoEM = T.Id            
    INNER JOIN ClaseEM AS C ON T.IdClaseEM = C.Id
    INNER JOIN Empleado AS E ON EM.IdEmpleado =  E.Id
    INNER JOIN Persona AS P ON E.IdPersona = P.Id
    LEFT JOIN CentroMedico AS CM ON EM.IdCentroMedico = CM.Id) t WHERE Estado = 'Vencido'

this last option, you embed the main query, and then you can filter on the column called Estado.