May 2, 2009 at 12:12 am
I would like to ask for help regarding this query where I would like to filter the records base on the value of the "C_ProfitPercentage". This filed is a computed value.
SELECT
J.JobID, J.PrintCost, J.DeliveryCost, J.OtherAmountCost,
(J.PrintCost + J.DeliveryCost + J.OtherAmountCost) AS C_PrintCost,
J.PrintPrice, J.DeliveryPrice, J.OtherAmount, J.DesignPriceInc, J.DesignPrice,
CASE WHEN J.DesignPriceInc = '1' THEN
(J.PrintPrice - J.DesignPrice) + J.DeliveryPrice + J.OtherAmount
ELSE
J.PrintPrice + J.DeliveryPrice + J.OtherAmount
END AS C_PrintPrice,
CASE WHEN J.DesignPriceInc = '1' THEN
((J.PrintPrice - J.DesignPrice) + J.DeliveryPrice + J.OtherAmount) - (J.PrintCost + J.DeliveryCost + J.OtherAmountCost)
ELSE
(J.PrintPrice + J.DeliveryPrice + J.OtherAmount) - (J.PrintCost + J.DeliveryCost + J.OtherAmountCost)
END AS C_PrintProfit,
CASE WHEN (J.PrintCost + J.DeliveryCost + J.OtherAmountCost) > 0 THEN
CASE WHEN J.DesignPriceInc = '1' THEN
((((J.PrintPrice - J.DesignPrice) + J.DeliveryPrice + J.OtherAmount) - (J.PrintCost + J.DeliveryCost + J.OtherAmountCost))/(J.PrintCost + J.DeliveryCost + J.OtherAmountCost))*100
ELSE
(((J.PrintPrice + J.DeliveryPrice + J.OtherAmount) - (J.PrintCost + J.DeliveryCost + J.OtherAmountCost))/(J.PrintCost + J.DeliveryCost + J.OtherAmountCost))*100
END
ELSE
100
END AS C_ProfitPercentage
FROM Jobs J
Lets say I would like to get the records WHERE C_ProfitPercentage > 20.
Is there a way to do this on SQL instead on having to filter the data on the front end?
May 2, 2009 at 1:04 am
Hi,
Try this
select * from
(
SELECT
J.JobID,
/*YOUR SELECT*/ AS C_ProfitPercentage
FROM Jobs J
)AS X
WHERE C_ProfitPercentage > 20
ARUN SAS
May 2, 2009 at 2:31 am
Thanks a lot arun.sas
Your solution worked. It was really a big help.
May 2, 2009 at 5:12 am
Sorry again.. I could really say I am just a beginner.
Would it be possible to get the AVG of "C_ProfitPercentage"?
===================
SELECT * FROM
(
SELECT
CASE WHEN (J.PrintCost + J.DeliveryCost + J.OtherAmountCost) > 0 THEN
CASE WHEN J.DesignPriceInc = '1' THEN
((((J.PrintPrice - J.DesignPrice) + J.DeliveryPrice + J.OtherAmount) - (J.PrintCost + J.DeliveryCost + J.OtherAmountCost))/(J.PrintCost + J.DeliveryCost + J.OtherAmountCost))*100
ELSE
(((J.PrintPrice + J.DeliveryPrice + J.OtherAmount) - (J.PrintCost + J.DeliveryCost + J.OtherAmountCost))/(J.PrintCost + J.DeliveryCost + J.OtherAmountCost))*100
END
ELSE
100
END AS C_ProfitPercentage
FROM Jobs AS J
) AS X
WHERE C_ProfitPercentage <= 20.00
===================
I have tried my best but my best is still not good enough...
Any suggestion would really be a great help.. Thanks in advance
May 2, 2009 at 5:20 am
Hi,
select AVG(C_ProfitPercentage)AVG_C_ProfitPercentage from
(
SELECT
J.JobID,
/*YOUR SELECT*/ AS C_ProfitPercentage
FROM Jobs J
)AS X
WHERE C_ProfitPercentage <= 20.00
ARUN SAS
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply