fiends based on values of another.
What I would like to add a billing total by saying more or less the
following:
SELECT labor_hours, labor_cost, expidite_fee, flat_rate,
include_repair_cost, include_cal, include_flat_rate, include_parts,
cur_bill,
(labor_hours * labor_cost) AS labor_total,
(ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER BY
dateCAL DESC),0)) AS cal_total,
(
ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
repair_partsID WHERE orderID=79559),0) +
ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
misc_part_assocID WHERE orderID=79559),0)
) AS parts_total,
(
(labor_hours * labor_cost) + expidite_fee + flat_rate +
ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER BY
dateCAL DESC),0) +
ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
repair_partsID WHERE orderID=79559),0) +
ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
misc_part_assocID WHERE orderID=79559),0)
) AS actual_total,
(
expidite_fee
IF include_repair_cost = 1
+ (labor_hours * labor_cost)
IF include_flat_rate = 1
+ flat_rate
IF include_cal = 1
+ ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER
BY dateCAL DESC),0)
IF include_parts = 1
+ ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
repair_partsID WHERE orderID=79559),0) +
ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
misc_part_assocID WHERE orderID=79559),0)
) AS billing_total
FROM view_inventory
WHERE orderID=79559
I know the IF part is whacked, that's where I need the help. Is this
type of thing even possible? Or even efficent? Is it wise to subquery
for totals (not like I have a choice based on the application
requirements)? help.On 18 Mar 2005 07:56:07 -0800, Rob Kopp wrote:
(snip)
>(
>expidite_fee
>IF include_repair_cost = 1
>+ (labor_hours * labor_cost)
>IF include_flat_rate = 1
>+ flat_rate
>IF include_cal = 1
>+ ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER
>BY dateCAL DESC),0)
>IF include_parts = 1
>+ ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
>repair_partsID WHERE orderID=79559),0) +
>ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
>misc_part_assocID WHERE orderID=79559),0)
>) AS billing_total
>FROM view_inventory
>WHERE orderID=79559
>I know the IF part is whacked, that's where I need the help. Is this
>type of thing even possible? Or even efficent?
Hi Rob,
You'll need to use CASE:
(
expidite_fee +
CASE WHEN include_repair_cost = 1
THEN (labor_hours * labor_cost)
ELSE 0 END +
CASE WHEN include_flat_rate = 1
THEN flat_rate
ELSE 0 END +
CASE WHEN include_cal = 1
THEN ISNULL((subquery cal_cost), 0)
ELSE 0 END +
CASE WHEN include_parts = 1
THEN ISNULL((subquery gptotal), 0) +
ISNULL((subquery gnptotal), 0)
ELSE 0 END
) AS billing_total
> Is it wise to subquery
>for totals (not like I have a choice based on the application
>requirements)? help.
Well, you can do some things to speed up the query.
Since you use the same subquery in two places, you could use a derived
table. Like this:
SELECT a, b, c, a + b + c AS GrandTotal
FROM (SELECT complicated_expression AS a,
complicated_expression AS b,
complicated expression AS c
FROM YourTable
WHERE ...) AS x
Another possibility is to use a join between your inventory table and
derived tables where the grouping has already been done:
SELECT ...,
gptotal,
...,
complicated expression using gptotal,
...
FROM view_inventory AS vi
LEFT OUTER JOIN (SELECT orderID,
SUM((qty * cost) + premium_charge) AS gptotal
FROM repair_partsID
GROUP BY orderID) AS a
ON a.orderID = vi.orderID
LEFT OUTER JOIN (...) AS b
ON b.orderID = vi.orderID
(etc)
WHERE vi.orderID = 79559
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||You are the man, Hugo. I bow to your majesty.sql
