470,855 Members | 1,317 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,855 developers. It's quick & easy.

Help with SQL if statement and adding fields together

I have a query that I need a hand on. I am trying to add togther some
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.

Jul 23 '05 #1
2 5468
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)
Jul 23 '05 #2
You are the man, Hugo. I bow to your majesty.

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Preston Landers | last post: by
28 posts views Thread by Siv | last post: by
7 posts views Thread by Miro | last post: by
6 posts views Thread by Mangler | last post: by
9 posts views Thread by pic078 via AccessMonster.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.