Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 [RESOLVED] one to many average calculation

Author  Topic 

HenryFulmer
Posting Yak Master

110 Posts
Posted - 2012-03-08 : 17:19:08
I have two tables:
Table 1 with 2 columns
ItemNumber | Quantity
1234562    | 1

Table 1 with 4 columns
ItemNumber | Quantity | UnitPrice | Date 
1234562    | 1        | 10.50     | 2011-02-24
1234562    | 2        | 12.00     | 2011-05-12
1234562    | 1        | 11.50     | 2011-06-03
1234562    | 4        | 11.80     | 2011-08-29


Table 1 represents the Qty that I have per each item and Table 2 represents each time I have received an item and to which price.
'ItemNumber' in Table 1 has only one record per ItemNumber whereas ItemNumber in Table 2 can have n-amount of records.

I need to be able to combine these two tables into one result set that will give me Table1.ItemNumber, Table1.Quantity and Table2.Unitprice

The UnitPrice value I need from Table2 depends on the Quantity in Table1. I always need the last price value applicable. In the example above I have only Quantity '1' left , so the UnitPrice value I need would be '11.80' because the last time I received this item I received '4' at the price of 11.80. Having 1 left the price for that 1 would be 11.80.

If my table 1 would show a quantity of 5 for that part number the price value that I need would be a combination of the 2 last entries in table 2:
4x11.80 = 47.20
1x11.50 = 11.50
47.20 + 11.50 = 58.70

Then I would divide the total value by the Quantity and my average UnitPrice for the quantity I have left in this case would be 11.74.

This is somewhat cumbersome to explain. Anybody have an idea how to go about solving this issue?
Thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-08 : 17:47:54
how do you always assume its last set of item thats remaining always? why cant it be last set item that gets sold before previous sets?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

HenryFulmer
Posting Yak Master

110 Posts
Posted - 2012-03-08 : 18:10:02
The system behind it works on a First In First Out basis, so the oldest pieces get sold first.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-08 : 18:35:12
[code]
;With PriceCalc
AS
(
SELECT t1.ItemNumber,t1.Quantity,t2.UnitPrice,t2.Quantity AS AllocQty,t2.Date,t2.Quantity AS runQty
FROM Table1 t1
CROSS APPLY (SELECT TOP 1 UnitPrice,Quantity,Date
FROM table2
WHERE ItemNumber = t1.ItemNumber
ORDER BY Date DESC) t2
UNION ALL

SELECT p.ItemNumber,p.Quantity,q.UnitPrice,q.Quantity ,q.Date,CASE WHEN p.runQty + q.Quantity > p.Quantity THEN p.Quantity-p.runQty ELSE q.Quantity END
FROM PriceCalc p
CROSS APPLY (SELECT TOP 1 UnitPrice,Quantity
FROM table2
WHERE ItemNumber = p.ItemNumber
AND Date < p.Date
ORDER BY Date DESC) tq
WHERE p.runQty + q.Quantity = p.Quantity
OR (p.runQty < p.Quantity AND p.runQty + q.Quantity > p.Quantity)
)

SELECT ItemNumber,SUM(UnitPrice*runQty)
FROM PriceCalc
GROUP BY ItemNumber
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

HenryFulmer
Posting Yak Master

110 Posts
Posted - 2012-03-09 : 10:21:26
Thank you. I added the Date field to the second SELECT TOP 1 statement. However I'm getting the error:
"TOP operator is not allowed in the recursive part of a recursive common table expression 'PriceCalc'."
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-09 : 11:50:48
show me the query used

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

HenryFulmer
Posting Yak Master

110 Posts
Posted - 2012-03-09 : 12:04:22
[code]
;With PriceCalc
AS
(
SELECT t1.ItemNumber,t1.Quantity,t2.UnitPrice,t2.Quantity AS AllocQty,t2.Date,t2.Quantity AS runQty
FROM Table1 t1
CROSS APPLY (SELECT TOP 1 UnitPrice,Quantity,Date
FROM table2
WHERE ItemNumber = t1.ItemNumber
ORDER BY Date DESC) t2
UNION ALL

SELECT p.ItemNumber,p.Quantity,q.UnitPrice,q.Quantity ,q.Date,CASE WHEN p.runQty + q.Quantity > p.Quantity THEN p.Quantity-p.runQty ELSE q.Quantity END
FROM PriceCalc p
CROSS APPLY (SELECT TOP 1 UnitPrice,Quantity,Date
FROM table2
WHERE ItemNumber = p.ItemNumber
AND Date < p.Date
ORDER BY Date DESC) q
WHERE p.runQty + q.Quantity = p.Quantity
OR (p.runQty < p.Quantity AND p.runQty + q.Quantity > p.Quantity)
)

SELECT ItemNumber,SUM(UnitPrice*runQty)
FROM PriceCalc
GROUP BY ItemNumber
[/code]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-09 : 12:12:33
quote:
Originally posted by HenryFulmer


;With PriceCalc
AS
(
SELECT t1.ItemNumber,t1.Quantity,t2.UnitPrice,t2.Quantity AS AllocQty,t2.Date,t2.Quantity AS runQty
FROM Table1 t1
CROSS APPLY (SELECT TOP 1 UnitPrice,Quantity,Date  
             FROM table2
             WHERE ItemNumber = t1.ItemNumber 
             ORDER BY Date DESC) t2
UNION ALL

SELECT p.ItemNumber,p.Quantity,q.UnitPrice,q.Quantity ,q.Date,CASE WHEN p.runQty + q.Quantity  > p.Quantity THEN p.Quantity-p.runQty ELSE q.Quantity END
FROM PriceCalc p
CROSS APPLY (SELECT TOP 1 UnitPrice,Quantity,Date  
             FROM table2
             WHERE ItemNumber = p.ItemNumber 
             AND Date < p.Date
             ORDER BY Date DESC) q
WHERE p.runQty + q.Quantity = p.Quantity
OR (p.runQty < p.Quantity AND  p.runQty + q.Quantity > p.Quantity)
)

SELECT ItemNumber,SUM(UnitPrice*runQty)
FROM PriceCalc
GROUP BY ItemNumber




what about this?

;With PriceCalc
AS
(
SELECT t1.ItemNumber,t1.Quantity,t2.UnitPrice,t2.Quantity AS AllocQty,t2.Date,t2.Quantity AS runQty
FROM Table1 t1
CROSS APPLY (SELECT TOP 1 UnitPrice,Quantity,Date  
             FROM table2
             WHERE ItemNumber = t1.ItemNumber 
             ORDER BY Date DESC) t2
UNION ALL

SELECT p.ItemNumber,p.Quantity,q.UnitPrice,q.Quantity ,q.Date,CASE WHEN p.runQty + q.Quantity  > p.Quantity THEN p.Quantity-p.runQty ELSE q.Quantity END
FROM PriceCalc p
INNER JOIN (SELECT UnitPrice,Quantity,Date  
             FROM table2
            ) q
ON q.ItemNumber = p.ItemNumber 
AND q.Date = (SELECT MAX(Date)
              FROM table2
              WHERE ItemNumber = q.ItemNumber 
              AND Date < p.Date
              )
AND (p.runQty + q.Quantity = p.Quantity
OR (p.runQty < p.Quantity AND  p.runQty + q.Quantity > p.Quantity)
)

SELECT ItemNumber,SUM(UnitPrice*runQty)
FROM PriceCalc
GROUP BY ItemNumber


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

HenryFulmer
Posting Yak Master

110 Posts
Posted - 2012-03-09 : 13:36:29
Still having a few problems.
I needed to add ItemNumber to the statement:
(SELECT ItemNumber,UnitPrice,Quantity,Date FROM table2) q

Now I'm getting the following error message:
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'PriceCalc'.


quote:


;With PriceCalc
AS
(
SELECT t1.ItemNumber,t1.Quantity,t2.UnitPrice,t2.Quantity AS AllocQty,t2.Date,t2.Quantity AS runQty
FROM Table1 t1
CROSS APPLY (SELECT TOP 1 UnitPrice,Quantity,Date  
             FROM table2
             WHERE ItemNumber = t1.ItemNumber 
             ORDER BY Date DESC) t2
UNION ALL

SELECT p.ItemNumber,p.Quantity,q.UnitPrice,q.Quantity ,q.Date
,CASE WHEN p.runQty + q.Quantity  > p.Quantity THEN p.Quantity-p.runQty ELSE q.Quantity END
FROM PriceCalc p
INNER JOIN (SELECT ItemNumber,UnitPrice,Quantity,Date  
             FROM table2
            ) q
ON q.ItemNumber = p.ItemNumber 
AND q.Date = (SELECT MAX(Date)
              FROM table2
              WHERE ItemNumber = q.ItemNumber 
              AND Date < p.Date
              )
AND (p.runQty + q.Quantity) = p.Quantity
OR (p.runQty < p.Quantity AND  p.runQty + q.Quantity > p.Quantity)
)

SELECT ItemNumber,SUM(UnitPrice*runQty)
FROM PriceCalc
GROUP BY ItemNumber
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-10 : 23:40:38
ok here you go with full illustration

change the quantity in @Tbl1 and see how output changes


DECLARE @Tbl1 table
(
ItemNumber int,
Quantity int
)
INSERT @Tbl1
SELECT 1234562,15 UNION ALL
SELECT 3423555,8 

DECLARE @Tbl2 table
(

ItemNumber int,
Quantity int,
UnitPrice Decimal(5,2),
Date datetime
)
INSERT @Tbl2
SELECT 1234562,12,8.50,'2011-01-20' UNION ALL
SELECT 1234562,1,10.50,'2011-02-24' UNION ALL
SELECT 1234562 , 2 , 12.00 ,'2011-05-12' UNION ALL
SELECT 1234562, 3 , 11.50 ,'2011-06-03' UNION ALL
SELECT 1234562,4 , 11.80 ,'2011-08-29' UNION ALL
SELECT 3423555, 21 , 15.50 ,'2011-03-13' UNION ALL
SELECT 3423555, 12 , 21.50 ,'2011-06-03' UNION ALL
SELECT 3423555, 1 , 28.50 ,'2011-11-23' 

;With tbl2mod
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY ItemNumber ORDER BY Date DESC) AS Rn,*
FROM @Tbl2
)
,PriceCalc
AS
(
SELECT t1.ItemNumber,t1.Quantity,t2.UnitPrice,CASE WHEN t2.Quantity > t1.Quantity THEN t1.Quantity ELSE t2.Quantity END AS AllocQty,t2.Date,CASE WHEN t2.Quantity > t1.Quantity THEN t1.Quantity ELSE t2.Quantity END  AS runQty,t2.Rn
FROM @Tbl1 t1
CROSS APPLY (SELECT TOP 1 UnitPrice,Quantity,Date ,Rn 
             FROM tbl2mod
             WHERE ItemNumber = t1.ItemNumber 
             ORDER BY Date DESC) t2
 
UNION ALL

SELECT p.ItemNumber,p.Quantity,q.UnitPrice,CASE WHEN p.runQty + q.Quantity  > p.Quantity THEN p.Quantity - p.runQty ELSE q.Quantity END,q.Date,CASE WHEN p.runQty + q.Quantity  > p.Quantity THEN p.Quantity ELSE p.runQty + q.Quantity END,q.Rn
FROM PriceCalc p
INNER JOIN tbl2mod q
ON q.ItemNumber = p.ItemNumber
AND q.Rn = p.Rn + 1
WHERE p.runQty + q.Quantity <= p.Quantity
OR (p.runQty < p.Quantity AND  p.runQty + q.Quantity > p.Quantity)
)

SELECT ItemNumber,SUM(UnitPrice*runQty) AS Total
FROM PriceCalc
GROUP BY ItemNumber
ORDER BY ItemNumber,Date DESC


output
--------------------------------------------------------------------
ItemNumber	Total
1234562	468.20
3423555	200.50




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

HenryFulmer
Posting Yak Master

110 Posts
Posted - 2012-03-12 : 11:51:25
Thank you so much for your comprehensive help, visakh.
This makes perfect sense to me now.
All I had to do is substitute the [runQty] with [AllocQty] in the last SELECT statement and get rid of the [Date] in the GROUP BY clause and I am getting exactly the results I need.
I have to study table expressions and iterative queries more, very helpful feature. Thanks again.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-12 : 12:15:41
welcome

glad that I could be of help to you

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-13 : 09:32:08
what are indexes you've on tables? can you check execution plan? also I hope you're using actual tables and not @ tables in your practical case

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

HenryFulmer
Posting Yak Master

110 Posts
Posted - 2012-03-13 : 15:28:46
I am using two actual tables for my query. Table1 has ItemNumber as the PRIMARY KEY. Table2 has a FOREIGN KEY on ItemNumber and a non clustered index on ItemNumber and on Date. I have generated an execution plan but I'm not very familiar with how to read/use it.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-13 : 15:49:43
see for costly steps in it and start analysing that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

HenryFulmer
Posting Yak Master

110 Posts
Posted - 2012-03-13 : 17:25:30
In the execution plan I have a Sort as the second step at 48% and Concatenation at step nine with 49%. Unfortunately I'm not really sure what that means or how to optimize that...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-13 : 23:04:14
can you post it in some shared server and give the link?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

HenryFulmer
Posting Yak Master

110 Posts
Posted - 2012-03-14 : 10:41:42
The execution plan can be downloaded here:
http://www.freedrive.com/file/1591100
Thank you so much for your help!

I have restarted the query yesterday after I've removed all records that only had 1 entry in table2 thereby reducing the records in table2 to 68,000. The job has been running for close to 45 hours now.
Go to Top of Page

HenryFulmer
Posting Yak Master

110 Posts
Posted - 2012-03-16 : 09:19:48
The query finished running after 52 hours. Thanks again for the help.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site