September 26, 2008 at 6:56 am
Hi all,
I´m having some issues with computing values from fields in different tables.
See the attatched image for database design, showing only PKs and FKs.
I PurchaseOrderHeader I store all info on the purchaseorders (duh!),
and in PurchaseOrderInput I store all info regarding registered errors on each order.
TransitInput and CrossdockInput contains registered errors that only apply to certain orders,
I wanted to put these in separate tables as there are orders in PurchaseOrderHeader that these type of errors would never apply to.
In CostOfError I store the cost for each error in PurchaseOrderInput, TransitInput and CrossdockInput.
My issue is calculating the cost of the errors. What I´m doing now is creating a view, something like this
SELECT PurchaseOrderInput.OrderError * CostOfError.OrderError AS OrderErrorCost,
TransitInput.TransitError * CostOfError.TransitError AS TransitErrorCost, CrossdockInput.CrossdockError * CostOfErrors.CrossdockError AS CrossdockErrorCost
From ....... INNER JOIN ......
and so on. My fields aren´t named OrderError and such, but you´ll get the basic idea.
But I´m thinking there must be a better way to solve this kind of computations?
Any hints?
September 26, 2008 at 12:06 pm
Why all the multiplying in the calculation? Do you have multiples of the same error?
I'm used to seeing stuff like this turned into a flat query and then Sum() used on that. Can you simply return a list of all of the errors, 1 error per row, including the cost?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 27, 2008 at 1:07 am
Why all the multiplying in the calculation? Do you have multiples of the same error?
I'm used to seeing stuff like this turned into a flat query and then Sum() used on that. Can you simply return a list of all of the errors, 1 error per row, including the cost?
My thought was this: Let´s say that I have 0 different kinds of errors.
For each order with any error, one datarow is stored.
If I also want to store the cost of each order in the same datarow, I get another 4 columns.
By storing the costs in a separate table I only have to store the costs in one place,
and to get the cost of each order I need to do this calculation.
But I´m pretty inexperienced, maybe this is a very stupid idea?
September 29, 2008 at 8:10 am
No, making it vertical is better. I'm just trying to understand how it works.
Let's say you have an order with 4 errors. That means you have 4 rows that records the order (a foreign key of some sort), and the errors (another foreign key), do you store the cost in that table, or is it standardized?
For example, "Error X always costs $125", would be standardized. "Error X cost $125 this time, and $3000 last time" would be per-record. Which way does this work in your system?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 29, 2008 at 8:34 am
An order always appears only once in the PurchaseOrderHeader table.
If the order had 4 errors, it could actually appear in the error table (PurchaseOrderInput)
anywhere between 1 to 4 times, depending on what date the error occured
(since the supplier sometimes deliveres a partial order).
Let´s say for simplicity that an order has 4 errors which occured on different dates,
so we have 4 rows with these errors in PurchaseOrderInput.
The cost is not standardized. In the table where the costs are stored,
I have an "CostOfErrorId"-column, which is PK/identity for that table.
The CostOfErrorId is also FK in the PurchaseOrderInput table,
so in theory these for different errors could have different costs for each row,
given that someone changed the error cost between every registration of the errors
for that order (again this is in theory, in reality the costs might be changed once every six months or so).
Hope that´s an understandable explanation 🙂
September 30, 2008 at 7:05 am
They way I think I'd do it, in that case, is have the cost be a column in the table where you log the error dates. Then you can just Sum() that column, and you'll have your data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 30, 2008 at 7:13 am
dli,
You're not exactly making this 100% clear, but let me take a stab at it anyway. One of two scenarios comes to mind. Either the record that records the occurrence of the error derives the cost of that error at that time, and includes it in the error row, and all you have to do is sum those rows together grouped by the order id, or, you wait until a given point in time and then have to join the orders to the error rows, and then also to the table that tells you what each error cost is, and sum those together. My point is that each error's cost needs to have a one-to-one relationship with the associated error row. However, what I've been reading in your posts suggests that the cost of any given error could change between the time it's recorded and the time it's reported on, which could cause all kinds of problems with interpretation of the results, and has the potential to render said results meaningless. Thus I would be far more concerned with ensuring that the specific cost of a given order error be recorded with the error row, at the time of the error, rather than trying to compute it later. While you indicated that such changes are perhaps six months apart, any attempt to run a report that computes the cost any time other than when the error occurs could have questionable results for a good month or more after any changes to the error costs are made, depending heavily on how the data is being used.
The part I'm not real clear on is the mention of multiple costs for a given order. An order has one, and only one, total cost, even if it's made up of the cost of the individual items in the order along with the cost of shipping and labor and various other costs of doiing business. However, what I'm reading suggests that you may be referring to multiple error costs. For example: say an order has 4 errors, 2 of which are the same kind of error (thus the same error cost for each one), and then 2 other different errors. Perhaps then what you need is the count of errors, by order, then by error type, which could then be joined to the error cost table, and then back to the orders table, and the count of errors times the cost per error, then summed by order, would be the result you're after.
I'm not real sure about what you're looking for, so can you at least expand on whether or not I'm providing any helpful detail?
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 30, 2008 at 8:01 am
I´ll try make a better explanation!
The error cost indeed does have a one-to-one relationship with the error row. The error table has a FK from the error cost table, which means that even if the cost of an error would change, it would never affect an error that has already been recorded.
Example:
2008-09-01 error type 1 gets recorded on order A.
The cost for error type 1 at this point is 100.
2008-09-03 the cost of error type 1 is changed to 200.
2008-09-05 error type 1 is recorded once again for order A (e.g. partial delivery).
If I were to sum the cost if error type one for order A
2008-09-06, the total cost would be 300.
This since every time a cost is updated in the cost table, a new row is inserted, rather than updating an existing one.
The thing is that I´m currently only storing the errors for each order, and the cost for each error type.
I´m not storing the actual cost for an order,
I´m computing it through a view. Which might be a very bad idea... Maybe it just better to store both the cost and the errors for an order in the same table?
Hope that explained it a bit better, otherwise, feel free to ask again.
September 30, 2008 at 8:44 am
If I understand you correctly, you appear to be saying that a change in the cost is reflected by a new record in the error cost table? Can I assume that each record in the error cost table has a date field that has the date that record is supposed to take effect? If that's not the case, then you have a design problem with the existing error cost table, and will need to re-think the entire process. If there is a date, then you need a query that matches the error cost record from the error cost table that has the largest date value (MAX) that's less than or equal to the date the error occurred, so that you get a single result cost for a given error. Then you can sum the results because there's a one-to-one relationship between an error and it's cost.
Something along the lines of:
SELECT O.ORDER_ID, SUM(C.ERROR_COST)
FROM ORDER_TABLE AS O INNER JOIN ERROR_TABLE AS E
ON O.ORDER_ID = E.ORDER_ID
LEFT OUTER JOIN (
SELECT MAX(COST_EFFECTIVE_DATE), ERROR_COST
FROM ERROR_COST_TABLE
WHERE COST_EFFECTIVE_DATE <= E.ERROR_DATE) AS C
You'll have to adapt this, and possibly change your joins based on the actual tables involved, but does the concept at least make sense? Given that there's a correlated sub-query, there's certainly going to be the potential for poor performance, so once you at least have a working query, you'll need to check to be sure you have appropriate indexes. Also, you can always return here and get help improving the query performance.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 30, 2008 at 10:29 am
There is a date in the error cost table, but also a PK identity column, which as I mentioned earlier is FK in the error table.
So to get the error and the cost, I´m currently doing something like this (don´t have the exact code avalible atm)
SELECT E.ORDERID, E.ERROR1, (E.ERROR1 * C.ERROR1) AS ERROR1COST
FROM ERROR_TABLE E
INNER JOIN COSTOFERRORS C
ON E.COSTOFERRORID = C.COSTOFERRORID
My question really is if it´s a good idea to keep the cost in one table and the errors in another, calculating the cost for each row of errors, or if I should store the cost for each row together with the errors.
September 30, 2008 at 10:38 am
I'd choose the latter, as the net result is that all you then have to do is add up the costs on a per order basis. Just remember that you still need date logic to generate the cost of the error at the time it occurs, as you need to match that error cost based on the record from the error cost table that has the most recent "efffective date" for that particular error type, that is less than or equal to the date the error occurs.
Steve
(aka smunson)
:):):)
dli (9/30/2008)
There is a date in the error cost table, but also a PK identity column, which as I mentioned earlier is FK in the error table.So to get the error and the cost, I´m currently doing something like this (don´t have the exact code avalible atm)
SELECT E.ORDERID, E.ERROR1, (E.ERROR1 * C.ERROR1) AS ERROR1COSTFROM ERROR_TABLE E
INNER JOIN COSTOFERRORS C
ON E.COSTOFERRORID = C.COSTOFERRORID
My question really is if it´s a good idea to keep the cost in one table and the errors in another, calculating the cost for each row of errors, or if I should store the cost for each row together with the errors.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply