If Statement

[#1]

Hi,

I have "last_order_date" in my table.

For some customers this filed is empty. I.e. customer has not send any order ( - ).

Example:

customer          last_order_date             Status

1                         -                                   Passive

2                    11-02-01 13:00:00               Passive

3                    12-10-12 13:00:12               Active

4                         -                                   Passive

5                    13-01-14 13:00:12               Active

The Status filed is created as:

if(last_order_date>Active_date, 'Active', if(last_order_date>Passive_date,'Passive','passive_not_customer')) as Status;

where

getdate()-366 as Active_date,

getdate()-365*3-1 as Passive_date,

How to I get the "passive_not_customer"- customers? Now they have status Passive, see customer 1 and 4!

It seems like it doesnt feel the emty field last_order_date for theese customers?

How do I do?

/Julia

[Gabriel]

Hi Julia,

Looking at the if expression, it practically does same thing.

if(last_order_date>Active_date, 'Active', if(last_order_date>Passive_date,'Passive','passive_not_customer')) as Status;

May be it will work if you change the > sign for the second if statement to <.

Let me know if it work

Regards,

Gabriel

[#3]

No, then all the Passive Statuses turns into passive_not_customer.

And I only get Statuses: Active and passive_not_customer.

/Julia

[Gysbert_Wassenaar]

You could try using the rangesum function so if last_order_date is Null it returns 0. And 0 can be compared with Active_date or Passive_date in the if statements.

if(rangesum(0,last_order_date)>Active_date, 'Active', if(rangesum(0,last_order_date)>Passive_date,'Passive','passive_not_customer')) as Status;

[#5 · Accepted solution]

Is this makes sense ?

if(IsNull(last_order_date), 'passive_not_customer', if(last_order_date>Active_date, 'Active', if(last_order_date>=Passive_date,'Passive'))) as Status;