Replacing NULL's in results 2

[aplusc]

Lets say I have a table called TRANSACTIONS with the following columns:

[tt]
customer item quantity total_cost
-------- ---- -------- ----------
Bob 121 4 400.00
Mary 115 1 50.00
John 100 2 100.00
Mike 121 7 [NULL]
[/tt]

I want to select all the records from the table however, for Mike instead of displaying NULL for the total cost I need the query to somehow CALCULATE the total_cost by finding another customer who bought the item and then dividing their total_cost by their quantity and then multiplying it by the quantity for the customer who is missing the value.

So for Mike you would have 400.00 (Bob's total) / 4 (Bob's quantity) * 7 (Mike's quantity) = 700.00 and the results would look like:

[tt]
customer item quantity total_cost
-------- ---- -------- ----------
Bob 121 4 400.00
Mary 115 1 50.00
John 100 2 100.00
Mike 121 7 700.00
[/tt]

It is assumed that the price for each item is the same for all customers. Also if there is no other record with a customer purchasing that item then it's OK to leave the field null.

Thanks for your help.

 

[Zathras]

It would help if you said what DBMS you are using (Access? SQL Server? SQL Base?)

One approach would be to create a view

CREATE VIEW PRICES (vitem int, vquant int, vccost dec(9,2))
AS SELECT ITEM, SUM(QUANTITY), SUM(COST)
     FROM TRANSACTIONS
     WHERE COST IS NOT NULL
     GROUP BY ITEM
     HAVING SUM(QUANTITY) > 0
[code][/color]
and then outer join it with transactions something like
[COLOR=blue][code]
SELECT customer, item, quantity, 
        IsNull( total_cost, vcost/IsNull(vquant,1) ) as total_cost
  FROM TRANSACTIONS
  LEFT OUTER JOIN PRICES ON item = vitem

The specifics will vary according to which DBMS you are using. If you can't use views, you should be able at least to use a temp table.

 

[Zathras]

Sorry, the second line of the final SELECT should have been something like

IsNull( total_cost, quantity * vcost/IsNull(vquant,1) ) as total_cost

Sorry for any inconvenience.
BTW, the code is not tested, so you may have to work with it a bit.

 

[dnoeth]

select
customer,
item,
quantity,
coalesce(total_cost,
quantity *
(
-- average cost per item
-- could be max/min for cheating statistics ;-)
select avg(total_cost / quantity)
from transactions t2
where t1.item = t2.item
)
)
from transactions t1

if your dbns doesn't support scalar subqueries:

select
t1.customer,
t1.item,
t1.quantity,
coalesce(t1.total_cost, t1.quantity * t2.avg_cost)
from transactions t1
left outer join
(
select item, avg(total_cost / quantity) avg_cost
from transactions t2
group by item
) t2
on t1.item = t2.item

Dieter

 

[Zathras]

I see some other typos in my original post (COST instead of total_cost) but I hope you got the idea.

Unfortunately, I don't have SQL Server here to test that syntax, but in Microsoft Access, you can create two queries as follows:

PRICES
======

SELECT [ITEM] AS vitem, Sum([QUANTITY]) AS vquant, Sum([TOTAL_COST]) AS vcost
FROM TRANSACTIONS
WHERE ((([TRANSACTIONS].[total_cost]) Is Not Null))
GROUP BY [TRANSACTIONS].[item]
HAVING (((Sum(TRANSACTIONS.quantity))>0));

After creating that query, you can then get the results you want with this one:

TransactionsWithPrices
======================

SELECT TRANSACTIONS.customer, TRANSACTIONS.item, TRANSACTIONS.quantity, IIf(IsNull([TRANSACTIONS]![total_cost]),[TRANSACTIONS]![quantity]*[PRICES]![vcost]/[PRICES]![vquant],[TRANSACTIONS]![total_cost]) AS total_cost
FROM TRANSACTIONS LEFT JOIN PRICES ON TRANSACTIONS.item = PRICES.vitem;

Hope this helps.

If you need SQL Server syntax, let me know and I can post an answer on Monday.

 

[manolito]

First my recommendations for the future so that you don't run into this problem again:

R1. Create new field UNIT_PRICE in the TRANSACTIONS table.
R2. Create new field TRX_DATE in TRANSACTIONS table... so that price variations over time can be captured. This is unsolicited advise... but hey, I've been there...
R3. ...or even better, create an ITEM_MASTER table, which you should join to pick the correct values.
R4. ...last and most importantly, make sure theat all items have at least one record with TOTAL_COST and QUANTITY populated, otherwise you have an impossible situation to resolve.

Now to your question. The answer is not as straight forward as it seems. There are a few scenarios:

S1. NO UPDATE NEEDED - PRICE DOES NOT CHANGE WITH TIME
S2. NO UPDATE NEEDED - PRICE CHANGES WITH TIME
S3. UPDATE NEEDED - PRICE DOES NOT CHANGE WITH TIME
S4. UPDATE NEEDED - PRICE CHANGES WITH TIME

For each scenario there's a solution, however, in the scenarios where price changes over time then you need to come up with the logic to decide which record to use to pick the desired pricing info.

Sorry, I am not providing any code since I believe that reasoning comes first, then the code follows.

Good luck,
Manuel

 

[aplusc]

Thanks for all of your help.

Zathras your code looks like it would work, but unfortunately I only have SELECT access on the table and cannot create any objects (even views). The avg/min/max solution seems better but I'm worried that this might be a performance issue. In the actual table there are over 1,000,000 records (and about 10,000 of them have the NULL problem), but I will have to try it tomorrow and see.

BTW, I'm using Sybase ASE 12.

 

[Zathras]

I don't have Sybase. There are eight Sybase forums in Tek-Tips. (Use the search "Find a Forum" at the top.)

Here is a script that works in SQL Server, perhaps someone at a Sybase forum can "translate" it into Sybase code:

CREATE TABLE #t (customer varchar(8), item int, quantity int, total_cost dec(9,2))
INSERT INTO #t VALUES('Bob',121,4,400)
INSERT INTO #t VALUES('Mary',115,1,50)
INSERT INTO #t VALUES('John',100,2,100)
INSERT INTO #t (customer,item,quantity) VALUES('Mike',121,7)
INSERT INTO #t (customer,item,quantity) VALUES('Sam',123,5)

SELECT customer, item, quantity, 
         IsNull( total_cost, 
              quantity * vcost/IsNull(vquant,1) )
            as total_cost
  FROM #t t
  LEFT OUTER JOIN 
    (SELECT vitem=ITEM, vquant=SUM(quantity),
                       vcost=SUM(total_cost)
       FROM #t
       WHERE total_cost IS NOT NULL
       GROUP BY item
       HAVING SUM(quantity) > 0) v
    ON ( vitem=t.item )

DROP TABLE #t

Hope this helps.