How to search data with specific criteria

[niladri.prima]

Hi All,

In a table (dbo.UnquotedContacts) I have few columns from where I am trying to extract few columns. But My crcern is as follows

IF the field 'Date_ABC_Updated' is NOT empty AND the field 'subscribed_to_Business_XL' = 'SUB', the contact has an ACTIVE BXL subscription

IF the field 'Date_ABC_Updated' is NOT empty AND the field 'subscribed_to_Business_XL' does NOT contain a value (IS EMPTY), the the contact has an INACTIVE BXL subscription. I have tried a query

SELECT  source,
    unique_contact_code,
    bxl_amount_paid,Date_ABC_Updated,       
    (Case 

    WHEN Date_ABC_Updated IS not null and Subscribed_to_Business_XL is null then 'Active'
    when Date_ABC_Updated IS not null and Subscribed_to_Business_XL IS null then 'Inactive'     
End )
    Subscribed_to_Business_XL,
    Audit_Q,
    Name_of_Company_Requestee,
    Job_Title_of_Company_Requestee,
    bxl_sub_type,
    bxl_amount_paid

FROM dbo.UnquotedContacts

But it is not working

Please help!!!!

[pritaeas]

But it is not working

Explain what's not working. Show error messages if you have them.

My guess is that Subscribed_to_Business_XL as alias won't work.

[ChrisHunter]

Put AS after the brackets and before Subscribed_to_Business_XL see if that works.

[BitBlt]

You simply neglected to change the criteria on the two parts of the CASE clause. Look at this:

SELECT  source,
    unique_contact_code,
    bxl_amount_paid,
    Date_ABC_Updated,       
    (Case 
    WHEN Date_ABC_Updated IS not null and Subscribed_to_Business_XL = 'SUB' then 'Active'    -- <------ Notice this! 
    when Date_ABC_Updated IS not null and Subscribed_to_Business_XL IS null then 'Inactive'
    End) Subscribed_to_Business_XL,
    Audit_Q,
    Name_of_Company_Requestee,
    Job_Title_of_Company_Requestee,
    bxl_sub_type,
    bxl_amount_paid
FROM dbo.UnquotedContacts

I built a little test table in SQL2008, populated it with junk data and tried this statement out. It seemed to work fine, and even didn't care about the alias for the "Case" column having the same name as a table column.

Hope it works out for you too! Good luck!

[niladri.prima]

Hi All,

thanks for prompt reply

it is now working...............

Niladri Sekahr Biswas

[BitBlt]

Good news! Please mark the thread as solved.