I have a query returning the following values:

customer_order_id
ticket_id
product_id
unit_of_measure
taxable_flag
tax_rate
product_type
quantity
price

I need to calculate the total per ticket_id and per order_id.  Each
ticket_id may have multiple line items and each order_id may have
multiple ticket_ids.  Additionally, there may be line items on the
ticket_id which have a dependency on another line item on the same
ticket.  For example:

customer_order_id: 12345
ticket_id: 001
product_id: 1
uom: cubicyard
quantity: 10
product_id: 136
uom: percubicyard
quantity: 1

So I need to multiply the quantity of product_id 136 times the
quantity of product_id 001.  Once I am shown how to do this I should
be able to apply the logic to our other business rules and to the tax
rate.  The result set should have one row per ticket whereas I
currently have multiple rows per ticket.
That will handle the ticket level quantity and pricing.

On the order level I need to be able to roll-up all ticket level
calculations so that I have one row of data per customer_order_id.

My preference is to avoid a cursor and use a temp table(s) or staging
table(s).  I also assume I'll need at least two queries to pull the
results since I want one result set with order level summaries and
another result set with ticket level summaries.

Thanks in advance.  Regards, Krickles

---

Request for Question Clarification by hammer-ga on 10 Jan 2005 09:07 PST

krickles,

Your comments below illustrate some of the coding problems.

For example, you list 3 items on Ticket ID 001. They have quantities
of 10, 1, and 1. Your summary quantity for this ticket is 10.

How do I (speaking as a computer) know from which of the three items
to derive the summary quantity?

The second of the three items also multiplies by the quantity of the
"master" item. The first does not (apparantly because it is the master
item). The third does not but I see no indicator as to why this one is
different from the second item.

How do I identify which is the "master" item? How do I know which of
the non-master items should be multiplied by the master item's
quantity?

- Hammer

---

Clarification of Question by krickles-ga on 10 Jan 2005 10:45 PST

Sorry.  I'm too close to the data and business.  Anyway, I'm about to
be on a fairly long conference call but will try to clarify some more
asap.

KRickles,

It might be helpful if you could post an example of a few sample
records, and what you want the final result to look like based on the
sample. Your references to "dependency on another line item" seem to
indicate a level of complexity that isn't entirely clear to me in your
explanation.

- Hammer

Sample records:

cust_ord_id | ticket_id | prod_numb | prod_type | uom | tax_flag |
tax_rate | qty | price

12345 | 001 | 3000 | readymix | cubicyards | 1 | .08 | 10 | 100
12345 | 001 | fiber | other | peryard | 1 | .08 | 1 | 10
12345 | 001 | enviro | other | each | 0 | 0 | 1 | 5
12345

Oops. Finger fumble...a premature post.  Please standby for the complete posting.

Sample records:

cust_ord_id | ticket_id | prod_numb | prod_type | uom | tax_flag |
tax_rate | qty | price

12345 | 001 | 3000 | readymix | cubicyards | 1 | .08 | 10 | 100
12345 | 001 | fiber | other | peryard | 1 | .08 | 1 | 7
12345 | 001 | enviro | other | each | 0 | 0 | 1 | 5
12345 | 002 | 3000 | readymix | cubicyards | 1 | .08 | 6 | 100
12345 | 002 | fiber | other | peryard | 1 | .08 | 1 | 10
12345 | 002 | enviro | other | each | 0 | 0 | 1 | 5

-------------------------------------------------------------------

Need 1)  Summarize ticket info into one row with the following fields:

cust_ord_id | ticket_id | tax_rate | qty | total

12345 | 001 | .08 | 10 | 1159.6
12345 | 002 | .08 | 6 | 697.36

I arrived at the totals by the following method:

Ticket 001
                     
   Product 3000 --> (qty x price x tax) = 10 x $100 x 1.08 = $1080
   Product fiber --> (qty x product 3000 qty x price x tax) = 1 x 10 x
$7 x 1.08 = $75.6
   Product enviro --> (qty x price x tax) = 1 x $4 x 1.00 = $4
	Total = $1159.60

Ticket 002

   Product 3000 --> (qty x price x tax) = 6 x $100 x 1.08 = $648
   Product fiber --> (qty x product 3000 qty x price x tax) = 1 x 6 x
$7 x 1.08 = $45.36
   Product enviro --> (qty x price x tax) = 1 x $4 x 1.00 = $4
	Total = $697.36

-------------------------------------------------------------------

Need 2) Summarize order info into one row with the following fields:

cust_ord_id | tax_rate | qty | total

12345 | .08 | 16 | $1856.96

I arrived at the totals by adding the qty and total together from the
Need 1 ticket rows.

I hope this helps.  I'll be watching as much as I can and will reply
as quickly as possible.  Krickles