Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
No, then all the Passive Statuses turns into passive_not_customer.
And I only get Statuses: Active and passive_not_customer.
/Julia
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;
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;