inventory calculation result not going well....

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 2000 Forums

Transact-SQL (2000)

inventory calculation result not going well....

Author

Topic

hai
Yak Posting Veteran

84 Posts

Posted - 2009-02-25 : 15:30:06

Hi all,

Below is the code which is not running correct. For trxClmid 102, the result should have something similar to this:


ID	trxdate	trxClmid	QTY	Purch_Price	PurchTranId	trxdate	Sale_Price	Sale_QTY	QTYinStock
1318	3/18/2005	102	700	50.1	1378	5/1/2005	45.25	30	670
1318	3/18/2005	102	700	50.1	1379	6/18/2005	42.25	420	250
1339	4/18/2005	102	150	50.1			0	0	150

The code below do not display the last entry...which result in incorrect value. Any suggestion on how to fixed the code is greatly appreciate.
thanks




IF OBJECT_ID('tempdb..#buy','U') IS NOT NULL
    DROP TABLE #buy
IF OBJECT_ID('tempdb..#sell','U') IS NOT NULL
    DROP TABLE #sell
IF OBJECT_ID('tempdb..#tblOrderTable','U') IS NOT NULL
    DROP TABLE  #tblOrderTable        

SELECT        IDENTITY(INT, 1, 1) AS RowID,
        cast(ID as int)ID,
        trxdate,
        Qty,
        Price,
        trxClmid
        
INTO        #Buy
FROM (
select 1317 as ID, '03/18/2005' as trxDate, 44.40 as Price, 700 as QTY,101 as trxClmid union all
select 1339	,'04/18/2005', 	50.10,	150,	101 union all
select 1318	,'03/18/2005', 	50.10,	700,	102 union all
select 1349	,'04/18/2005', 	50.10,	150,	102 union all
select 1319	,'03/18/2005', 	50.10,	700,	103 union all
select 1340	,'04/18/2005', 	50.10,	150,	103 
    )tbl1
    order by trxdate

SELECT        IDENTITY(INT, 1, 1) AS RowID,
        cast(ID as int)ID,
        trxdate,
        Qty,
        Price,
        trxClmid
        
INTO        #Sell
FROM (
select 1376 as ID, '05/18/2005' as trxDate, 45.25 as Price, 430 as QTY,101 as trxClmid union all
select 1377	,'07/07/2005', 	42.25,	420,	101 union all
select 1378	,'05/01/2005', 	45.25,	30,	102 union all
select 1379	,'06/18/2005', 	42.25,	420,	102 union all
select 1380	,'05/10/2005', 	45.25,	1500,	103 union all
select 1381	,'06/08/2005', 	42.25,	150,	103 

    )tbl1  
    order by trxdate 


select * from #buy order by trxClmid
select * from #sell order by trxClmid

SELECT   max(isnull(B1.id,0))ID,
         B1.trxdate,
         B1.trxClmid,
         B1.QTY,
         max(isnull(B1.Price,0)) as Purch_Price,
         max(isnull(S1.id,0)) AS PurchTranId,
         S1.trxdate,
         max(isnull(S1.Price,0)) as Sale_Price,
         CASE 
           WHEN 0 > ((SELECT Sum(B2.QTY)
                      FROM   [#Buy] B2
                      WHERE  B2.rowid <= B1.rowid
                             AND B2.trxClmid = B1.trxClmid) - 
					(SELECT Sum(S2.QTY)
					 FROM   [#Sell] S2
					 WHERE  S2.rowid <= S1.rowid
							AND S2.trxClmid = S1.trxClmid)) THEN Isnull(S1.QTY,0) + 
					((SELECT Sum(B2.QTY)
					 FROM   [#Buy] B2
					 WHERE  B2.rowid <= B1.rowid
							AND B2.trxClmid = B1.trxClmid) - (SELECT Sum(S2.QTY)
                            FROM   [#Sell] S2
                                WHERE  S2.rowid <= S1.rowid
                                       AND S2.trxClmid = S1.trxClmid))
           WHEN isnull(S1.QTY,0) > B1.QTY - 
					((SELECT Sum(B2.QTY)
					FROM   [#Buy] B2
					WHERE  B2.rowid <= B1.rowid
					AND B2.trxClmid = B1.trxClmid) - (SELECT Sum(S2.QTY)
					FROM   [#Sell] S2
					WHERE  S2.rowid <= S1.rowid
					AND S2.trxClmid = S1.trxClmid)) THEN (B1.QTY - ((SELECT Sum(B2.QTY)
                   FROM   [#Buy] B2
                            WHERE  B2.rowid <= B1.rowid
                                   AND B2.trxClmid = B1.trxClmid) - (SELECT Sum(S2.QTY)
                                                                   FROM   [#Sell] S2
                                                                   WHERE  S2.rowid <= S1.rowid
                                                                          AND S2.trxClmid = S1.trxClmid)))
           ELSE Isnull(S1.QTY,0)
         END AS Sale_QTY,
         CASE 
           WHEN ((SELECT Sum(B2.QTY)
                  FROM   [#Buy] B2
                  WHERE  B2.rowid <= B1.rowid
                         AND B2.trxClmid = B1.trxClmid) - (SELECT Sum(S2.QTY)
                                                         FROM   [#Sell] S2
                                                         WHERE  S2.rowid <= S1.rowid
                                                                AND S2.trxClmid = S1.trxClmid)) > B1.QTY THEN B1.QTY
           WHEN ((SELECT Sum(B2.QTY)
                  FROM   [#Buy] B2
                  WHERE  B2.rowid <= B1.rowid
                         AND B2.trxClmid = B1.trxClmid) - (SELECT Sum(S2.QTY)
                                                         FROM   [#Sell] S2
                                                         WHERE  S2.rowid <= S1.rowid
                                                                AND S2.trxClmid = S1.trxClmid)) < 0 THEN 0
           ELSE ((SELECT Sum(B2.QTY)
                  FROM   [#Buy] B2
                  WHERE  B2.rowid <= B1.rowid
                         AND B2.trxClmid = B1.trxClmid) - (SELECT Sum(S2.QTY)
                                                         FROM   [#Sell] S2
                                                         WHERE  S2.rowid <= S1.rowid
                                                                AND S2.trxClmid = S1.trxClmid))
         END AS QTYinStock

FROM     dbo.[#Buy] B1
         LEFT OUTER JOIN [#Sell] S1

           ON B1.trxClmid = S1.trxClmid
GROUP BY B1.rowid,
         B1.trxdate,
         B1.trxClmid,
         B1.QTY,
         S1.rowid,
         S1.trxdate,
         S1.QTY,
         S1.trxClmid
HAVING   (((SELECT Sum(B2.QTY)
            FROM   [#Buy] B2
            WHERE  B2.rowid <= B1.rowid
                   AND B2.trxClmid = B1.trxClmid) - (SELECT Sum(isnull(S2.QTY,0))
                                                   FROM   [#Sell] S2
                                                   WHERE  S2.rowid <= S1.rowid
                                                          AND S2.trxClmid = S1.trxClmid)) + Isnull(S1.QTY,0) > 0
          AND B1.QTY > ((SELECT Sum(B2.QTY)
                              FROM   [#Buy] B2
                              WHERE  B2.rowid <= B1.rowid
                                     AND B2.trxClmid = B1.trxClmid) - (SELECT Sum(S2.QTY)
                                                                     FROM   [#Sell] S2
                                                                     WHERE  S2.rowid <= S1.rowid
                                                                            AND S2.trxClmid = S1.trxClmid)))
          OR Isnull(B1.QTY,0) = 0
order by B1.trxClmid, B1.trxdate

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-27 : 05:51:32

Why don't you explain what out you need basis your 2 tables,buy and sell.

hai
Yak Posting Veteran

84 Posts

Posted - 2009-02-27 : 08:49:10

Hi,

Perhaps, following description can explain the scenario better.

For trclcmid 101:
On 03/18/2005
- Buy with quantity 700
On 05/18/2005
- Sell (430) quantity, so remaining quantity in Buy (700-430)=270
On 07/07/2005
- distribute out 420
- Buy with quantity 270 from period so remaining quantity in order = 0 for 03/18/2005
on 04/18/2005
- Buy with quantuty 150
- distribute outstanding (420-270)=150 from 07/07/2005 quantity on 04/18/2005 = 0

For trclcmid 102:
On 03/18/2005
- Buy with quantity 700
On 05/01/2005
- Sell (30) quantity, so remaining quantity in Buy (700-30)=670
On 06/18/2005
- distribute out 420
- Buy with quantity 670 from period so remaining quantity in order (670-420)= 250 for 03/18/2005
on 04/18/2005
- Buy with quantuty 150
- No distribute so order should have 150+250 =400 left.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-27 : 08:57:08

your description doesn't match the data you have posted. 05/18/2005??

hai
Yak Posting Veteran

84 Posts

Posted - 2009-02-27 : 09:19:15

Hi Sakets,

There is a sale (distribute) of 430 on 5/18/2005 in #sales, I'm not sure what I'm missing.

souLTower
Starting Member

39 Posts

Posted - 2009-02-27 : 09:36:41

Are you trying to have a system that tells the number of items on a particular date? So your output would be:

For trclcmid 102:

partNumber transDate change total
102 03/18/2005 700 700
102 05/01/2005 -30 670
102 06/18/2005 -420 250
102 06/18/2005 670 920
102 4/18/2005 150 1070

God Bless

hai
Yak Posting Veteran

84 Posts

Posted - 2009-02-27 : 11:08:57

Hi SoulTower,

Similar to that...however your is a bit off, should be:
partNumber transDate change total
102 03/18/2005 700 700
102 05/01/2005 -30 670
102 06/18/2005 -420 250
102 4/18/2005 150 400

however, this is what I'm looking for:
partNumber transDate Original SellDate change total_Remain
102 3/18/2005 700 5/1/2005 -30 670
102 3/18/2005 700 6/18/2005 -420 250
102 4/18/2005 150 - 0 400

thanks

souLTower
Starting Member

39 Posts

Posted - 2009-02-27 : 11:20:07

OK, we're close. Please explain:


102 3/18/2005 700 5/1/2005 -30 670

102 = part number
3/18/2005 = what?
700 = quantity when?
5/1/2005 = transaction date
-30 = transaction amount
670 = total after transaction

God Bless

hai
Yak Posting Veteran

84 Posts

Posted - 2009-02-27 : 11:43:40

Hi,

3/18/2005, 700 are the date, and qty of the #buy table. I should mention this operation is a FIFO method of Sale(Distribute) and Buy (Order).

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


IF OBJECT_ID('tempdb..#buy','U') IS NOT NULL
    DROP TABLE #buy
IF OBJECT_ID('tempdb..#sell','U') IS NOT NULL
    DROP TABLE #sell
IF OBJECT_ID('tempdb..#tblOrderTable','U') IS NOT NULL
    DROP TABLE  #tblOrderTable        

SELECT        IDENTITY(INT, 1, 1) AS RowID,
        cast(ID as int)ID,
        trxdate,
        Qty,
        Price,
        trxClmid
        
INTO        #Buy
FROM (
select 1317 as ID, '03/18/2005' as trxDate, 44.40 as Price, 700 as QTY,101 as trxClmid union all
select 1339	,'04/18/2005', 	50.10,	150,	101 union all
select 1318	,'03/18/2005', 	50.10,	700,	102 union all
select 1349	,'04/18/2005', 	50.10,	150,	102 union all
select 1319	,'03/18/2005', 	50.10,	700,	103 union all
select 1340	,'04/18/2005', 	50.10,	150,	103 
    )tbl1
    order by trxClmid,trxdate

SELECT        IDENTITY(INT, 1, 1) AS RowID,
        cast(ID as int)ID,
        trxdate,
        Qty,
        Price,
        trxClmid
        
INTO        #Sell
FROM (
select 1376 as ID, '05/18/2005' as trxDate, 45.25 as Price, 430 as QTY,101 as trxClmid union all
select 1377	,'07/07/2005', 	42.25,	420,	101 union all
select 1378	,'05/01/2005', 	45.25,	30,	102 union all
select 1379	,'06/18/2005', 	42.25,	420,	102 union all
select 1380	,'05/10/2005', 	45.25,	1500,	103 union all
select 1381	,'06/08/2005', 	42.25,	150,	103 

    )tbl1  
    order by trxClmid,trxdate 


select * from #buy where trxclmid=102 order by trxClmid, trxdate 
select * from #sell where trxclmid=102 order by trxClmid, trxdate

souLTower
Starting Member

39 Posts

Posted - 2009-02-27 : 11:51:55

OK. See if this script gets you closer.


-- ==========================================
-- Transaction System
--===========================================

declare @trans table(
  transID int identity(1, 1), 
  partNo varchar(10),
  transDate datetime,
  quantity int
)

-- Create some data
insert into @trans (partNo, transDate, quantity)
  select 'V101', '1/5/2009', 900 union all
  select 'V101', '1/7/2009', -5  union all
  select 'V101', '1/9/2009', -15 union all
  select 'V101', '1/11/2009', 80 union all
  select 'V102', '1/5/2009', 200 union all
  select 'V102', '1/6/2009', 300 union all
  select 'V102', '1/9/2009', -400 union all
  select 'V102', '1/12/2009', -12 union all
  select 'V102', '1/18/2009', 25 union all
  select 'V102', '1/25/2009', -100 

-- Make sure my data is there
-- select * from @trans

select 
-- This is the base information 
  T.partNo, 

-- This is the last known amount for the item
  ISNULL(
    (select sum(P.quantity) from @trans P where P.partNo = T.partNo and P.transDate < T.transDate)
    , 0) as lastQuantity, 

-- This is the last known date this item
  ISNULL(
    (select top 1 cast(P.transDate as varchar) from @trans P 
     where P.partNo = T.partNo and P.transDate < T.transDate
     order by P.transDate desc)
    , 'None') as lastTransDate, 

-- Transaction date and amount
T.transDate, T.quantity as transactionAmount, 

-- Get the running total for this part number
  (select sum(P.quantity) from @trans P where P.partNo = T.partNo and P.transDate <= T.transDate)
  as runningTotal
from @trans T order by T.partNo, T.transDate

God Bless

hai
Yak Posting Veteran

84 Posts

Posted - 2009-02-27 : 13:13:05

Thank you very much for all your help. I will try to tweak the code, and let you know the result. Once again, thank you very much.

souLTower
Starting Member

39 Posts

Posted - 2009-02-28 : 12:08:55

You wrote:


This is what I really want:

partNo lastQuantity lastTransDate transDate transactionAmount runningTotal
V101 900 January 5, 2009 1/7/2009 -5 895
V101 895 January 5, 2010 1/9/2009 -15 880
V101 880 January 5, 2011 none 0 880
V101 80 January 11, 2009 none 0 960
V102 200 January 5, 2011 1/9/2009 -700 -500
V102 300 January 6, 2009 1/9/2009 -500 -200
V102 25 January 18, 2009 1/9/2009 -200 -175
V102 0 none 1/12/2009 -12 -187
V102 0 none 1/25/2009 -100 -287

This looks like you want to the output to be ordered with the sold items first followed by the purchased items. Is this correct.

God Bless

hai
Yak Posting Veteran

84 Posts

Posted - 2009-03-01 : 12:09:30

Well,kinda like that...because it is (first in first out) calculation....
eg:
v101 have 2 Purchase: 1/5/09 with 900 and 1/11/2009 with 80, and 2 Sold: 1/7/09 for -5 and 1/9/2009 for -15. Base on this
the result I would like is this based on first in first out for v101:
partNo,PurchDate, pQTY, SaleDate, sQty, qtyRemain
v101, 1/5/2009', 900, 1/7/2009, -5 , 895
v101, 1/5/2009', 895, 1/9/2009, -15, 880
v101, 1/5/2009', 880, none, -0, 880
v101, 1/11/2009', 80, none ,-0, 960
For v102 (which is wrong from above), base from 3 purchase 1/5 for 200, 1/6 for 300, and 1/18 for 25
with 3 Sold - 1/9 fro 400, 1/12 for 12 and 1/25 for 100. should be like this:
v102, PurchDate, pQTY, SaleDate, sQty, qtyRemain
v102, 1/5/2009', 200, 1/9/2009, -400 , -200
v102, 1/6/2009', 300, 1/9/2009, -200, 100
v102, 1/6/2009', 100, 1/12/2009, -12, 88
v102, 1/6/2009', 88, 1/25/2009 ,-100, -12
v102, 1/18/2009',25, 1/25/2009,-12, 13
v102, 1/18/2009',13, none,0, 13

souLTower
Starting Member

39 Posts

Posted - 2009-03-02 : 07:47:31

I'm sorry but I just don't understand your data. From your description:


Transactions
-------------------------
1/5 for 200, 
1/6 for 300, 
1/9 fro -400, 
1/12 for -12
1/18 for 25
1/25 for -100


Data
---------------------------------------------------------------------------
IID	partNo,	PurchDate, 	pQTY,	SaleDate, 	sQty, 	qtyRemain
---------------------------------------------------------------------------
1	v102, 	1/5/2009', 	200, 	1/9/2009, 	-400 , 	-200

2	v102, 	1/6/2009', 	300, 	1/9/2009, 	-200, 	100
3	v102, 	1/6/2009', 	100, 	1/12/2009, 	-12, 		88
4	v102, 	1/6/2009', 	88, 	1/25/2009 ,	-100, 	-12

5	v102, 	1/18/2009',	25, 	1/25/2009,	-12, 		13
6	v102, 	1/18/2009',	13, 	none,		0, 		13

I can see that line 1 represents that the first purchase was made 1-5 in the amount of 200 and that the very next sale was made 1-9 (-400). The next line confises me though. It represents that the next purchase was made 1-6 in the amount of 300 however the sale date doesn't correspond to a transaction. I can assume that it means that since the first sale of the item was on 1-9 that it is reflected in this row but that would invalidate the prior row.

Do you have a requirement that explains what the output rows are to relect? To be honest I see what your posting but I don't understand it.

Possibly you could write for me a row by row explanation of this data set. That may help me to understand it better.

Thanks

God Bless

hai
Yak Posting Veteran

84 Posts

Posted - 2009-03-02 : 10:56:52

Here is how inventory cost is calculated using the First in First out method:


    * Assume a product which is made in three batches. The quantity of each batch are:
          o Batch 1: 1/5 Quantity 200 pieces
          o Batch 2: 1/6 Quantity 300 pieces
          o Batch 3: 1/18 Quantity 25 pieces
    * Let's say you sold 3 batch for 525 
          o Batch 1: 1/9 Quantity 400 pieces
          o Batch 2: 1/12 Quantity 12 pieces
          o Batch 3: 1/25 Quantity 100 pieces

With that here how it run:

1) 1/5,  200 , 1/9, 400  (200-400) which leave -200 remain for the next batch:
2) 1/6,  300 - This must be clear with the prior remain sold item which is -200 before  continue, so:
   1/6,  300, 1/9 -200 (300-200) which leave 100 product left
3) Because there are 100 left in the product, this also need to be clear by the next sold qty:
   1/6,  100, 1/12, 12 (100-12) which still leave 88 product
4) Although item sold is 12, but the remain product still have 88, this need to be clear by the next sold item:
   1/6,  88,  1/25, 100 which have -12 remain of sold item
5) the next batch have 25 for 1/18 so:
   1/18, 25 1/25, 12 (25-12) 13 left: 


Data
---------------------------------------------------------------------------
IID	partNo,	PurchDate, 	pQTY,	SaleDate, 	sQty, 	qtyRemain
---------------------------------------------------------------------------
1	v102, 	1/5/2009', 	200, 	1/9/2009, 	-400 , 	-200		

2	v102, 	1/6/2009', 	300, 	1/9/2009, 	-200, 	100
3	v102, 	1/6/2009', 	100, 	1/12/2009, 	-12, 		88
4	v102, 	1/6/2009', 	88, 	1/25/2009 ,	-100, 	-12

5	v102, 	1/18/2009',	25, 	1/25/2009,	-12, 		13
6	v102, 	1/18/2009',	13, 	none,		0, 		13

thanks

souLTower
Starting Member

39 Posts

Posted - 2009-03-02 : 11:29:10

I'm sorry, Hai, but I'm not able to help on this. I think I understand what you're trying to accomplish but I can't direct you on how to achieve it. Possibly someone else can pick up this tread and shed some light on it for you. Please, when you get it working, post your solution. I'm interested in finding what I've missed.

God Bless

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources