Post reply

How to use if condition in where clause

  • August 28, 2009 at 4:04 am

    #74094

    Hi Experts,

    select distinct assign.trainee_id

    ,trn.First_Name+' '+trn.Last_Name as traineeName,assign.assignment_details_id,

    corp.First_Name+' '+corp.Last_Name as corpname,

    assign.corp_lead_id as corpId,

    mentor.First_Name+' '+mentor.Last_Name as mentorname,

    assign.actual_end_date as EndDate,

    assign.planned_end_date as PLEND,

    assign.assigned_by_id as mentorId

    from assignment_details_tbl assign,employee_tbl corp ,employee_tbl mentor,employee_tbl trn

    where

    corp.employee_id=assign.corp_lead_id and trn.employee_status = 'A' and

    mentor.employee_id=assign.assigned_by_id

    and assign.trainee_id = 390200551 and

    trn.employee_id = assign.trainee_id

    and

    ( (assign.actual_end_date is null and assign.planned_end_date < (getdate()-5) )
    or (assign.actual_end_date is not null and assign.actual_end_date and assign.actual_end_date in (select max(actual_end_date) from assignment_details_tbl where trainee_id=390200551 and actual_end_date)
    ) [/code]

    Have attached the result set.
    I want to make changes to the query part

    [i](assign.actual_end_date is null and assign.planned_end_date < (getdate()-5) )
    or (assign.actual_end_date is not null and assign.actual_end_date and assign.actual_end_date in (select max(actual_end_date) from assignment_details_tbl where trainee_id=390200551 and actual_end_date)[/i]

    so that i get the result set as either EndDate is null or EndDate is not null.
    Not both together

    Please help

    Tanx 😀

  • August 28, 2009 at 4:30 am

    #1046099

    If cannot be used within a query. The CASE statement is what you want. There should be some good examples on how to use it in Books Online.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply