Monster Query Assistance

[mcupples]

I realize this query is inherently incorrect, but my issue is mainly syntax. The line, "WHEN a.order_id <> b.order_id THEN" is wrong. I want to ensure that a.order_id is not in the settlement table. So I was thinking something along the lines of "WHEN a.order_id not in (select order_id from settlement)" which I know will cause a slower response time, but I'm willing to deal with it. In any case, that syntax doesn't appear to work.

sum(
CASE
WHEN a.ready_to_pay_flag = 'Y' and a.deduction_type = 'E' and (
CASE
WHEN a.order_id <> b.order_id THEN
a.transaction_date
ELSE
b.delivery_date
END) used_date datediff(d,used_date, ".$cutOffDate.") < 30) THEN
a.amount
END) earn_amount_rtp_curr,

Any help here would be hotness!

Thanks!

[skhanal]

You can't nest CASE statement.

[modinrico]

I assume i understand what you are doing...
I would probably use a UDF because you can add decent logic to it... but if not you can do use a very intelligent case statement.
You really only have 1 case in this situation, just a bunch of logic around it...


Hope the statement below helps

sum(
CASE
WHEN a.ready_to_pay_flag = 'Y' and a.deduction_type = 'E' and (((a.order_id <> b.order_id) and (datediff(d,a.transaction_date, ".$cutOffDate.") < 30)) or ((a.order_id = b.order_id) and (datediff(d,b.delivery_date, ".$cutOffDate.") < 30))) then a.amount
else 0
END) earn_amount_rtp_curr

[nosepicker]

Yes you can nest a CASE statement - it just needs to be done correctly. I think this is what you need:

sum(
CASE
WHEN a.ready_to_pay_flag = 'Y' and a.deduction_type = 'E' and
(DATEDIFF(d, (CASE WHEN a.order_id <> b.order_id THEN a.transaction_date ELSE b.delivery_date END), ".$cutOffDate.") < 30) THEN a.amount
END) earn_amount_rtp_curr,