Re: Nested aggregation for Decrementing Cost of item
Posted by:
Grey Chap
Date: November 14, 2013 09:15PM
OK worked it out using Peter's Nested aggregation link as a guide.
I believe it is the right way to do this, but if not let me know.
SELECT
s.cust_id,
s.amt AS bill_Count,
ROUND(
SUM(
IF (s.amt<=100,ROUND(s.amt * .175,2),
IF((s.amt>100 AND s.amt<=200),ROUND(((s.amt - 100) * .17)+ 100 * .175,2),
IF((s.amt>200 AND s.amt<=300),ROUND(((s.amt - 200) * .165) + (100 * .17) + 100 * .175,2),
IF((s.amt>300 AND s.amt<=400),ROUND(((s.amt - 300) * .16) + (100 * .165) + (100 * .17) + 100 * .175,2),
IF((s.amt>400 AND s.amt<=500),ROUND(((s.amt - 400) * .155) + (100 * .160) + (100 * .165) + (100 * .17) + 100 * .175,2),
IF(s.amt>500,ROUND(((s.amt - 500) * .15) + (100 * .155) + (100 * .160) + (100 * .165) + (100 * .17) + 100 * .175,2),0))))))
),2 ) AS bill_Total
FROM
(
SELECT cust_id,SUM(sms_job_count) AS amt
FROM sms_job
WHERE MONTH(sms_job_date) = 10
GROUP BY cust_id
) AS s
GROUP BY s.cust_id;
The "MONTH(sms_job_Date) = 10" is to refine SELECT to month of October.