-
Monster Query Assistance
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!
-
You can't nest CASE statement.
-
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
-
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,
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|