# Get Buying Cost From Current Stock

on
Hi All,

I have stock table structure below:

TMSTOCK:

1. ItemCode
2. TrsId
3. TrsDate
4. StockIn
5. StockOut

Key : ItemCode + TrsId

Sample Data:

ItemCode  TrsId  TrsDate        StockIn  StockOut   BuyingPrice
CCL           B1     2009/01/01   100                        1
CCL           S1     2009/01/01   0           50             0
CCL           S1     2009/01/01   0           30             0
FNT            B1     2009/01/01   200                        1
FNT            S1     2009/01/01   0           50             0
FNT            S1     2009/01/01   0           30             0
FNT            S1     2009/01/01   0           30             0

The query result wanted:

CCL           B1     2009/01/01    80             1
FNT            B1     2009/01/01    90             1

How to query it ?

Thank you.
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Oops.

The query result wanted:

CCL           B1     2009/01/01    20             1
FNT            B1     2009/01/01    90             1

Thank you.

Commented:
What is StockLast and how do you calculate it?
What are your selection criteria for selecting these two rows?
BuyingPrice or TrsId could be it ..

Commented:
Hi allmer,

StockLast = StockIn - StockOut for certain ItemCode.

What are your selection criteria for selecting these two rows?
This is what I am looking for. The problem is get the StockLast >0 and BuyingPrice.

Thank you.

Commented:
In that case your result seems wrong:
CCL           B1     2009/01/01    300             1
FNT            B1     2009/01/01    300            1

StockLast should either be 300 (all B1s are summed) or 100 and 200 respectively.
How do they calculate to become 20 and 90?

Anyhow, try the query below and let me know whether it works for you.

``````SELECT ItemCode, TrsId, TrsDate, StockIn-StockOut AS StockLast, BuyingPrice
FROM TMSTOCK
WHERE TrsId='B1';
``````

Commented:
SELECT   itemcode,  trsdate,
(select distinct trsid from tmstock a where  a.stockin > 0 and a. itemcode = tmstock. itemcode  ) trsid,
sum(DECODE(trsid, 'B1', stockin, 0)) - sum(DECODE(trsid, 'S1', stockout, 0)) StockLast,
FROM tmstock
GROUP BY itemcode,  trsdate

try this

Commented:
Hi allmer,

StockLast = StockIn - StockOut for certain ItemCode.

CCL :

20 = 100 - (50 + 30)

FNT :

90= 200 - (50 + 30 + 30)

Thank you.

Commented:
Sorry I mistook TrsID for Itemcode.

So I assume now you would like to group on Itemcode.
``````SELECT ItemCode, TrsId, TrsDate, sum(StockIn)-sum(StockOut) AS StockLast, BuyingPrice
FROM TMSTOCK
GROUB BY ItemCode
``````

Commented:
Hi allmer,

Your code obviously will not work at a glance.

Thank you.

Commented:
Hi shru_0409,

Let me try it.

Thank you.

Commented:
Hi shru_0409,

SELECT     BarangCode, TglTransaksi,
(SELECT DISTINCT NoTransaksi
FROM          TDMUTSTOKBRG2010 AS B
WHERE      (MasukSupplier > 0) AND (A.BarangCode = BarangCode)) AS NoTransaksi, SUM(MasukSupplier) - SUM(KeluarCustomer) AS StockLast,
MAX(HargaSatuanStok) AS HargaSatuanStok
FROM         TDMUTSTOKBRG2010 AS A
WHERE     (MasukSupplier > 0)
GROUP BY BarangCode, TglTransaksi

Subquery return more than 1 value.

What's the problem?

Thank you.

Commented:
SELECT     BarangCode, TglTransaksi,
(SELECT DISTINCT NoTransaksi
FROM          TDMUTSTOKBRG2010 AS B
WHERE      (MasukSupplier > 0) AND (A.BarangCode = BarangCode) AND row_number()= 1) AS NoTransaksi, SUM(MasukSupplier) - SUM(KeluarCustomer) AS StockLast,
MAX(HargaSatuanStok) AS HargaSatuanStok
FROM         TDMUTSTOKBRG2010 AS A
WHERE     (MasukSupplier > 0)
GROUP BY BarangCode, TglTransaksi

try the row number = 1

check the row_number() function is working in SQL server....

Commented:
Incorrect syntax near row_number() , expected OVER()

Thank you.

Commented:
Did you try my code?
I do believe it should work.

Let me know how the result differs from your expectation.

What is the table TDMUTSTOKBRG2010  used by shru and what is MasukSupplier ... ?

Commented:
Hi allmer,

SELECT ItemCode, TrsId, TrsDate, sum(StockIn)-sum(StockOut) AS StockLast, BuyingPrice
FROM TMSTOCK
GROUB BY ItemCode

into

SELECT     BarangCode, NoTransaksi, TglTransaksi, SUM(MasukSupplier - KeluarCustomer) AS StockLast, HargaSatuanStok
FROM         TDMUTSTOKBRG2010
GROUP BY BarangCode

First, the Group By syntax is not complete, should be GROUP BY BarangCode, NoTransaksi, TglTransaksi,HargaSatuanStok
Second the result is not what we expected.

Thank you.

Commented:
If that is your translation of the attributes you gave in the question, then it is wrong.

SELECT     BarangCode, NoTransaksi, TglTransaksi, SUM(MasukSupplier - KeluarCustomer) AS StockLast, HargaSatuanStok
FROM         TDMUTSTOKBRG2010
GROUP BY BarangCode

should be:

SELECT     BarangCode, NoTransaksi, TglTransaksi, SUM(MasukSupplier) - sum(KeluarCustomer) AS StockLast, HargaSatuanStok
FROM         TDMUTSTOKBRG2010
GROUP BY BarangCode

Anyway, I resign from this question, good luck.

Commented:
Hi shru_0409,

SELECT     NoTransaksi, TglTransaksi, BarangCode, MasukSupplier, KeluarCustomer, HargaSatuanStok
FROM         TDMUTSTOKBRG2010

NoTransaksi                              TglTransaksi               BarangCode      MasukSupplier      KeluarCustomer    HargaSatuanStok
PB/SCM/01/10/00002                  02/01/2010 0:00:00      140000003035   160,0000                 0,0000                     46149,9875
SJ/SCM/01/10/00002                  02/01/2010 0:00:00      140000003035   0,0000                 160,0000             0,0000
SJ/SCM/01/10/00099                  05/01/2010 0:00:00      140000003035   0,0000                 160,0000             0,0000
PB/SCM/01/10/00158                  05/01/2010 0:00:00      140000003035   160,0000                 0,0000                  46149,9875

Thank you.

Commented:
Hi allmer,

Commented:
Hi allmer,

By the way, please check your sql syntax again, the group by column is not enough.

SUM(MasukSupplier - KeluarCustomer) = SUM(MasukSupplier) - sum(KeluarCustomer)

Thank you.
Data Engineer

Commented:
try this.
``````select ItemCode,TrsId,TrsDate,StockIn-StockOut StackLast,BuyingPrice
sum(StockIn) over(partition by ItemCode) StockIn,
sum(StockOut) over(partition by ItemCode) StockOut,
row_number() over (partition by ItemCode order by TrsId) rn
from TMSTOCK) as t1
where rn = 1
``````
Data Engineer

Commented:
sample attached.
Data Engineer

Commented:
attachment is missing in previous post.
``````declare @TMSTOCK as table(ItemCode varchar(20),TrsId varchar(20),TrsDate datetime,StockIn int, StockOut int,BuyingPrice int)
insert into @TMSTOCK values('CCL',            'B1',     '2009/01/01',   100,         null,               1)
insert into @TMSTOCK values('CCL',            'S1',     '2009/01/01',   0  ,         50,             0)
insert into @TMSTOCK values('CCL',            'S1',     '2009/01/01',   0  ,         30,             0   )
insert into @TMSTOCK values('FNT',            'B1',     '2009/01/01',   200,         null,               1)
insert into @TMSTOCK values('FNT',            'S1',     '2009/01/01',   0  ,         50,             0)
insert into @TMSTOCK values('FNT',            'S1',     '2009/01/01',   0  ,         30,             0   )
insert into @TMSTOCK values('FNT',            'S1',     '2009/01/01',   0  ,         30,             0   )

select * from @TMSTOCK
/*
CCL	B1	2009-01-01 00:00:00.000	100	NULL	1
CCL	S1	2009-01-01 00:00:00.000	0	50	0
CCL	S1	2009-01-01 00:00:00.000	0	30	0
FNT	B1	2009-01-01 00:00:00.000	200	NULL	1
FNT	S1	2009-01-01 00:00:00.000	0	50	0
FNT	S1	2009-01-01 00:00:00.000	0	30	0
FNT	S1	2009-01-01 00:00:00.000	0	30	0
*/
sum(StockIn) over(partition by ItemCode) StockIn,
sum(StockOut) over(partition by ItemCode) StockOut,
row_number() over (partition by ItemCode order by TrsId) rn
from @TMSTOCK) as t1
where rn = 1
/*
CCL	B1	2009-01-01 00:00:00.000	20	1
FNT	B1	2009-01-01 00:00:00.000	90	1
*/
``````

Commented:
SELECT   itemcode,  trsdate,
(select trsid from (select distinct trsid,itemcode, row_number() over (partition by itemcode order by itemcode) d   from tmstock a where  a.stockin > 0 ) z where z.itemcode = tmstock.itemcode and z.d=1) trsid,
sum(DECODE(trsid, 'B1', stockin, 0)) - sum(DECODE(trsid, 'S1', stockout, 0)) StockLast,
FROM tmstock
GROUP BY itemcode,  trsdate

try this.....
Data Engineer

Commented:
shru_0409 - DECODE is not a recognized function in SQL Server 2005.

Commented:
Hi Sharath_123

declare @TMSTOCK as table(ItemCode varchar(20),TrsId varchar(20),TrsDate datetime,StockIn int, StockOut int,BuyingPrice int)
insert into @TMSTOCK values('CCL',            'B1',     '2009/01/01',   100,         null,               1)
insert into @TMSTOCK values('CCL',            'S1',     '2009/01/01',   0  ,         50,             0)
insert into @TMSTOCK values('CCL',            'S1',     '2009/01/01',   0  ,         30,             0   )
insert into @TMSTOCK values('CCL',            'B1',     '2009/02/01',   100,         null,               2)
insert into @TMSTOCK values('CCL',            'S1',     '2009/02/01',   0  ,         30,             0   )
insert into @TMSTOCK values('CCL',            'B1',     '2009/03/01',   100,         null,               3)
insert into @TMSTOCK values('FNT',            'B1',     '2009/01/01',   200,         null,               1)
insert into @TMSTOCK values('FNT',            'S1',     '2009/01/01',   0  ,         50,             0)
insert into @TMSTOCK values('FNT',            'S1',     '2009/01/01',   0  ,         30,             0   )
insert into @TMSTOCK values('FNT',            'S1',     '2009/01/01',   0  ,         30,             0   )

The result become :

CCL      B1      01/02/2009 0:00:00      190      2
FNT      B1      01/01/2009 0:00:00      90      1

Should be :

CCL      B1      01/02/2009 0:00:00      90      2
CCL      B1      01/03/2009 0:00:00      100      3
FNT      B1      01/01/2009 0:00:00      90      1

How to solve this ?

Thank you.

Commented:
Hi Shru,

DECODE is not recognized IN SQL. I have removed it, but still get the same error message.

Thank you.
Data Engineer

Commented:
Why don't you want the first record in your final output?

insert into @TMSTOCK values('CCL',            'B1',     '2009/01/01',   100,         null,               1)

Data Engineer

Commented:
Are you looking for this output?

CCL      B1      01/02/2009 0:00:00      20      1
CCL      B1      01/02/2009 0:00:00      70      2
CCL      B1      01/03/2009 0:00:00      100    3
FNT      B1      01/01/2009 0:00:00      90      1

Commented:
Hi Sharat,

The previous code your provide with sample data is working.

I have insert more data.

insert into @TMSTOCK values('CCL',            'B1',     '2009/02/01',   100,         null,               2)
insert into @TMSTOCK values('CCL',            'S1',     '2009/02/01',   0  ,         30,             0   )
insert into @TMSTOCK values('CCL',            'B1',     '2009/03/01',   100,         null,               3)

The result become :

CCL      B1      01/02/2009 0:00:00      190      2
FNT      B1      01/01/2009 0:00:00      90      1

Should be :

CCL      B1      01/02/2009 0:00:00      90      2
CCL      B1      01/03/2009 0:00:00      100      3
FNT      B1      01/01/2009 0:00:00      90      1

How to solve this ?

Thank you.

Commented:
Are you looking for this output?

CCL      B1      01/02/2009 0:00:00      20      1
CCL      B1      01/02/2009 0:00:00      70      2
CCL      B1      01/03/2009 0:00:00      100    3
FNT      B1      01/01/2009 0:00:00      90      1

Yes.

Thank you.
Data Engineer

Commented:
In your post, you did not mention the record with CCL, 1, Please see the difference between your post and my assumption.

Commented:

Are you looking for this output?

CCL      B1      01/02/2009 0:00:00      20      1
CCL      B1      01/02/2009 0:00:00      70      2
CCL      B1      01/03/2009 0:00:00      100    3
FNT      B1      01/01/2009 0:00:00      90      1

This is the expected result for the given sample data.

Thank you.
Data Engineer

Commented:
try this.

from (
sum(isnull(StockIn,0)) over(partition by ItemCode,TrsDate) StockIn,
sum(isnull(StockOut,0)) over(partition by ItemCode,TrsDate) StockOut
from TMSTOCK) as t1 where TrsId = 'B1'

Sample attached.
``````declare @TMSTOCK as table(ItemCode varchar(20),TrsId varchar(20),TrsDate datetime,StockIn int, StockOut int,BuyingPrice int)
insert into @TMSTOCK values('CCL',            'B1',     '2009/01/01',   100,         null,               1)
insert into @TMSTOCK values('CCL',            'S1',     '2009/01/01',   0  ,         50,             0)
insert into @TMSTOCK values('CCL',            'S1',     '2009/01/01',   0  ,         30,             0   )
insert into @TMSTOCK values('CCL',            'B1',     '2009/02/01',   100,         null,               2)
insert into @TMSTOCK values('CCL',            'S1',     '2009/02/01',   0  ,         30,             0   )
insert into @TMSTOCK values('CCL',            'B1',     '2009/03/01',   100,         null,               3)
insert into @TMSTOCK values('FNT',            'B1',     '2009/01/01',   200,         null,               1)
insert into @TMSTOCK values('FNT',            'S1',     '2009/01/01',   0  ,         50,             0)
insert into @TMSTOCK values('FNT',            'S1',     '2009/01/01',   0  ,         30,             0   )
insert into @TMSTOCK values('FNT',            'S1',     '2009/01/01',   0  ,         30,             0   )

from (
sum(isnull(StockIn,0)) over(partition by ItemCode,TrsDate) StockIn,
sum(isnull(StockOut,0)) over(partition by ItemCode,TrsDate) StockOut
fr
/*
CCL	B1	2009-01-01 00:00:00.000	20	1
CCL	B1	2009-02-01 00:00:00.000	70	2
CCL	B1	2009-03-01 00:00:00.000	100	3
FNT	B1	2009-01-01 00:00:00.000	90	1
*/
``````

Commented:
Sharath, I don't quite understand, when I change the data, for example date of buying then the result is different.

Would you please give me a clue, I want to learn it.

Please use below final sample :

declare @TMSTOCK as table(ItemCode varchar(20),TrsId varchar(20),TrsDate datetime,StockIn int, StockOut int,BuyingPrice int)
insert into @TMSTOCK values('CCL',            'B1',     '2009/01/01',   100,         null,               1)
insert into @TMSTOCK values('CCL',            'S1',     '2009/01/01',   0  ,         50,             0)
insert into @TMSTOCK values('CCL',            'S2',     '2009/01/01',   0  ,         30,             0   )
insert into @TMSTOCK values('CCL',            'B2',     '2009/02/01',   100,         null,               2)
insert into @TMSTOCK values('CCL',            'S3',     '2009/02/01',   0  ,         30,             0   )
insert into @TMSTOCK values('CCL',            'B3',     '2009/02/01',   100,         null,               3)
insert into @TMSTOCK values('CCL',            'B4',     '2009/03/01',   100,         null,               3)
insert into @TMSTOCK values('FNT',            'B5',     '2009/01/01',   200,         null,               1)
insert into @TMSTOCK values('FNT',            'S4',     '2009/01/01',   0  ,         50,             0)
insert into @TMSTOCK values('FNT',            'S5',     '2009/01/01',   0  ,         30,             0   )
insert into @TMSTOCK values('FNT',            'S6',     '2009/01/01',   0  ,         30,             0   )

Thank you.

Commented:
Why use where TrsId = 'B1', what if the trsid is not predictable?

Thank you.
Data Engineer

Commented:
I assume that TrsID, B stands for Buy and S for Sell. If you have TrsIDs as B1,B2 etc as you mentioned, then you can change the filter like below.

from (
sum(isnull(StockIn,0)) over(partition by ItemCode,TrsDate) StockIn,
sum(isnull(StockOut,0)) over(partition by ItemCode,TrsDate) StockOut
from TMSTOCK) as t1 where TrsId LIKE 'B%'

Commented:
what if the trsid is not predictable?

Could we use StockIn for it ?

Using the latest sample, the result is not expected.

CCL      B1      01/01/2009 0:00:00      20      1
CCL      B2      01/02/2009 0:00:00      170      2
CCL      B3      01/02/2009 0:00:00      170      3
CCL      B4      01/03/2009 0:00:00      100      3
FNT      B5      01/01/2009 0:00:00      90      1

Thank you.
Data Engineer

Commented:
Can you post the sample set where it is failing and the expected output?

Commented:
declare @TMSTOCK as table(ItemCode varchar(20),TrsId varchar(20),TrsDate datetime,StockIn int, StockOut int,BuyingPrice int)
insert into @TMSTOCK values('CCL',            'B1',     '2009/01/01',   100,         null,               1)
insert into @TMSTOCK values('CCL',            'S1',     '2009/01/01',   0  ,         50,             0)
insert into @TMSTOCK values('CCL',            'S2',     '2009/01/01',   0  ,         30,             0   )
insert into @TMSTOCK values('CCL',            'B2',     '2009/02/01',   100,         null,               2)
insert into @TMSTOCK values('CCL',            'S3',     '2009/02/01',   0  ,         30,             0   )
insert into @TMSTOCK values('CCL',            'B3',     '2009/02/01',   100,         null,               3)
insert into @TMSTOCK values('CCL',            'B4',     '2009/03/01',   100,         null,               3)
insert into @TMSTOCK values('FNT',            'B5',     '2009/01/01',   200,         null,               1)
insert into @TMSTOCK values('FNT',            'S4',     '2009/01/01',   0  ,         50,             0)
insert into @TMSTOCK values('FNT',            'S5',     '2009/01/01',   0  ,         30,             0   )
insert into @TMSTOCK values('FNT',            'S6',     '2009/01/01',   0  ,         30,             0   )

the expected result :

CCL     B1      2009-01-01 00:00:00.000 20      1
CCL     B2      2009-02-01 00:00:00.000 70      2
CCL     B3      2009-02-01 00:00:00.000 100     3
CCL     B4      2009-03-01 00:00:00.000 100     3
FNT     B5      2009-01-01 00:00:00.000 90      1

Thank you.
Data Engineer

Commented:
check the attached script. observer the output. I grouped B3 and S3 values for CCL not B2 and S3.
``````declare @TMSTOCK as table(ItemCode varchar(20),TrsId varchar(20),TrsDate datetime,StockIn int, StockOut int,BuyingPrice int)
insert into @TMSTOCK values('CCL',            'B1',     '2009/01/01',   100,         null,               1)
insert into @TMSTOCK values('CCL',            'S1',     '2009/01/01',   0  ,         50,             0)
insert into @TMSTOCK values('CCL',            'S2',     '2009/01/01',   0  ,         30,             0   )
insert into @TMSTOCK values('CCL',            'B2',     '2009/02/01',   100,         null,               2)
insert into @TMSTOCK values('CCL',            'S3',     '2009/02/01',   0  ,         30,             0   )
insert into @TMSTOCK values('CCL',            'B3',     '2009/02/01',   100,         null,               3)
insert into @TMSTOCK values('CCL',            'B4',     '2009/03/01',   100,         null,               3)
insert into @TMSTOCK values('FNT',            'B5',     '2009/01/01',   200,         null,               1)
insert into @TMSTOCK values('FNT',            'S4',     '2009/01/01',   0  ,         50,             0)
insert into @TMSTOCK values('FNT',            'S5',     '2009/01/01',   0  ,         30,             0   )
insert into @TMSTOCK values('FNT',            'S6',     '2009/01/01',   0  ,         30,             0   )

from (
select *,
sum(isnull(StockIn,0)) over (partition by ItemCode,TrsDate,cnt) StockIn_New,
sum(isnull(StockOut,0)) over (partition by ItemCode,TrsDate,cnt) StockOut_New,
row_number() over (partition by ItemCode,TrsDate,cnt order by TrsId) rn
from (select *,
where t1.ItemCode = t2.ItemCode and t1.TrsDate = t2.TrsDate and t1.TrsID >= t2.TrsID) cnt
from @TMSTOCK t1) as t1) as t2
where rn = 1
/*
CCL	B1	2009-01-01 00:00:00.000	20	1
CCL	B2	2009-02-01 00:00:00.000	100	2
CCL	B3	2009-02-01 00:00:00.000	70	3
CCL	B4	2009-03-01 00:00:00.000	100	3
FNT	B5	2009-01-01 00:00:00.000	90	1
*/
``````

Commented:
Hi Sharath,

Sorry, just back again.

Why the result is not what we expected?

/*
CCL     B1      2009-01-01 00:00:00.000 20      1
CCL     B2      2009-02-01 00:00:00.000 100     2
CCL     B3      2009-02-01 00:00:00.000 70      3
CCL     B4      2009-03-01 00:00:00.000 100     3
FNT     B5      2009-01-01 00:00:00.000 90      1
*/

the expected result :

CCL     B1      2009-01-01 00:00:00.000 20      1
CCL     B2      2009-02-01 00:00:00.000 70      2
CCL     B3      2009-02-01 00:00:00.000 100     3
CCL     B4      2009-03-01 00:00:00.000 100     3
FNT     B5      2009-01-01 00:00:00.000 90      1

Thank you.

Commented:
Hi Sharath,

I've got an idea, why don't we get the stock last per ItemCode (summary) and then get its detail stock ?

What do you think?

Thank you.
Data Engineer

Commented:
Because if you order the values, B2 comes first, then B3 and S3.

Commented:
Ok.

How to solve this problem?

Thank you.

Commented:
Yes, the order are B2, B3, S3.

B2 still have stock to sales to S3?

We get the stock last per ItemCode (summary) first and then get its detail stock ?

Thank you.
Data Engineer

Commented:
Lot of thinking :) Check this script.
``````declare @TMSTOCK as table(ItemCode varchar(20),TrsId varchar(20),TrsDate datetime,StockIn int, StockOut int,BuyingPrice int)
insert into @TMSTOCK values('CCL',            'B1',     '2009/01/01',   100,         null,               1)
insert into @TMSTOCK values('CCL',            'S1',     '2009/01/01',   0  ,         50,             0)
insert into @TMSTOCK values('CCL',            'S2',     '2009/01/01',   0  ,         30,             0   )
insert into @TMSTOCK values('CCL',            'B2',     '2009/02/01',   100,         null,               2)
insert into @TMSTOCK values('CCL',            'S3',     '2009/02/01',   0  ,         30,             0   )
insert into @TMSTOCK values('CCL',            'B3',     '2009/02/01',   100,         null,               3)
insert into @TMSTOCK values('CCL',            'B4',     '2009/03/01',   100,         null,               3)
insert into @TMSTOCK values('FNT',            'B5',     '2009/01/01',   200,         null,               1)
insert into @TMSTOCK values('FNT',            'S4',     '2009/01/01',   0  ,         50,             0)
insert into @TMSTOCK values('FNT',            'S5',     '2009/01/01',   0  ,         30,             0   )
insert into @TMSTOCK values('FNT',            'S6',     '2009/01/01',   0  ,         30,             0   )

;with cte_1 as (
select *,row_number() over (order by (select 1)) rn from @TMSTOCK),
cte_2 as (
select *,(select max(rn) from cte_1 c1 where c1.rn < c2.rn and c1.BuyingPrice <> 0) new_rn
from cte_1 c2),
cte_3 as (
select *,case BuyingPrice when 0 then (select BuyingPrice from cte_2 c2 where c2.rn = c1.new_rn) else BuyingPrice end Seq
from cte_2 c1),
cte_4 as (
select *,
sum(isnull(StockIn,0)) over (partition by ItemCode,TrsDate,Seq) StockIn_New,
sum(isnull(StockOut,0)) over (partition by ItemCode,TrsDate,Seq) StockOut_New,
row_number() over (partition by ItemCode,TrsDate,Seq order by TrsId) row_num
from cte_3)
from cte_4 where row_num = 1
/*
CCL	B1	2009-01-01 00:00:00.000	20	1
CCL	B2	2009-02-01 00:00:00.000	70	2
CCL	B3	2009-02-01 00:00:00.000	100	3
CCL	B4	2009-03-01 00:00:00.000	100	3
FNT	B5	2009-01-01 00:00:00.000	90	1
*/
``````

Commented:
It works, great.

Would you please describe in english step by step of the query? The query seems to be complicated to me.

That would be very helpful for me to learn.

For example :

from cte_4 where row_num = 1

This get the last result and why row_num = 1 ?

Thank you.
Data Engineer
Commented:
execute each CTE individually and observe the result. You will come to know what I did. If still doubts, let me know.
``````declare @TMSTOCK as table(ItemCode varchar(20),TrsId varchar(20),TrsDate datetime,StockIn int, StockOut int,BuyingPrice int)
insert into @TMSTOCK values('CCL',            'B1',     '2009/01/01',   100,         null,               1)
insert into @TMSTOCK values('CCL',            'S1',     '2009/01/01',   0  ,         50,             0)
insert into @TMSTOCK values('CCL',            'S2',     '2009/01/01',   0  ,         30,             0   )
insert into @TMSTOCK values('CCL',            'B2',     '2009/02/01',   100,         null,               2)
insert into @TMSTOCK values('CCL',            'S3',     '2009/02/01',   0  ,         30,             0   )
insert into @TMSTOCK values('CCL',            'B3',     '2009/02/01',   100,         null,               3)
insert into @TMSTOCK values('CCL',            'B4',     '2009/03/01',   100,         null,               3)
insert into @TMSTOCK values('FNT',            'B5',     '2009/01/01',   200,         null,               1)
insert into @TMSTOCK values('FNT',            'S4',     '2009/01/01',   0  ,         50,             0)
insert into @TMSTOCK values('FNT',            'S5',     '2009/01/01',   0  ,         30,             0   )
insert into @TMSTOCK values('FNT',            'S6',     '2009/01/01',   0  ,         30,             0   )

select * from @TMSTOCK
/*
CCL	B1	2009-01-01 00:00:00.000	100	NULL	1
CCL	S1	2009-01-01 00:00:00.000	0	50	0
CCL	S2	2009-01-01 00:00:00.000	0	30	0
CCL	B2	2009-02-01 00:00:00.000	100	NULL	2
CCL	S3	2009-02-01 00:00:00.000	0	30	0
CCL	B3	2009-02-01 00:00:00.000	100	NULL	3
CCL	B4	2009-03-01 00:00:00.000	100	NULL	3
FNT	B5	2009-01-01 00:00:00.000	200	NULL	1
FNT	S4	2009-01-01 00:00:00.000	0	50	0
FNT	S5	2009-01-01 00:00:00.000	0	30	0
FNT	S6	2009-01-01 00:00:00.000	0	30	0
*/

select *,row_number() over (order by (select 1)) rn from @TMSTOCK

/*
CCL	B1	2009-01-01 00:00:00.000	100	NULL	1	1
CCL	S1	2009-01-01 00:00:00.000	0	50	0	2
CCL	S2	2009-01-01 00:00:00.000	0	30	0	3
CCL	B2	2009-02-01 00:00:00.000	100	NULL	2	4
CCL	S3	2009-02-01 00:00:00.000	0	30	0	5
CCL	B3	2009-02-01 00:00:00.000	100	NULL	3	6
CCL	B4	2009-03-01 00:00:00.000	100	NULL	3	7
FNT	B5	2009-01-01 00:00:00.000	200	NULL	1	8
FNT	S4	2009-01-01 00:00:00.000	0	50	0	9
FNT	S5	2009-01-01 00:00:00.000	0	30	0	10
FNT	S6	2009-01-01 00:00:00.000	0	30	0	11
*/

;with cte_1 as (
select *,row_number() over (order by (select 1)) rn from @TMSTOCK),
cte_2 as (
select *,(select max(rn) from cte_1 c1 where c1.rn < c2.rn and c1.BuyingPrice <> 0) new_rn
from cte_1 c2)
select * from cte_2
/*
CCL	B1	2009-01-01 00:00:00.000	100	NULL	1	1	NULL
CCL	S1	2009-01-01 00:00:00.000	0	50	0	2	1
CCL	S2	2009-01-01 00:00:00.000	0	30	0	3	1
CCL	B2	2009-02-01 00:00:00.000	100	NULL	2	4	1
CCL	S3	2009-02-01 00:00:00.000	0	30	0	5	4
CCL	B3	2009-02-01 00:00:00.000	100	NULL	3	6	4
CCL	B4	2009-03-01 00:00:00.000	100	NULL	3	7	6
FNT	B5	2009-01-01 00:00:00.000	200	NULL	1	8	7
FNT	S4	2009-01-01 00:00:00.000	0	50	0	9	8
FNT	S5	2009-01-01 00:00:00.000	0	30	0	10	8
FNT	S6	2009-01-01 00:00:00.000	0	30	0	11	8
*/

;with cte_1 as (
select *,row_number() over (order by (select 1)) rn from @TMSTOCK),
cte_2 as (
select *,(select max(rn) from cte_1 c1 where c1.rn < c2.rn and c1.BuyingPrice <> 0) new_rn
from cte_1 c2),
cte_3 as (
select *,case BuyingPrice when 0 then (select BuyingPrice from cte_2 c2 where c2.rn = c1.new_rn) else BuyingPrice end Seq
from cte_2 c1)
select * from cte_3
/*
CCL	B1	2009-01-01 00:00:00.000	100	NULL	1	1	NULL	1
CCL	S1	2009-01-01 00:00:00.000	0	50	0	2	1	1
CCL	S2	2009-01-01 00:00:00.000	0	30	0	3	1	1
CCL	B2	2009-02-01 00:00:00.000	100	NULL	2	4	1	2
CCL	S3	2009-02-01 00:00:00.000	0	30	0	5	4	2
CCL	B3	2009-02-01 00:00:00.000	100	NULL	3	6	4	3
CCL	B4	2009-03-01 00:00:00.000	100	NULL	3	7	6	3
FNT	B5	2009-01-01 00:00:00.000	200	NULL	1	8	7	1
FNT	S4	2009-01-01 00:00:00.000	0	50	0	9	8	1
FNT	S5	2009-01-01 00:00:00.000	0	30	0	10	8	1
FNT	S6	2009-01-01 00:00:00.000	0	30	0	11	8	1
*/

;with cte_1 as (
select *,row_number() over (order by (select 1)) rn from @TMSTOCK),
cte_2 as (
select *,(select max(rn) from cte_1 c1 where c1.rn < c2.rn and c1.BuyingPrice <> 0) new_rn
from cte_1 c2),
cte_3 as (
select *,case BuyingPrice when 0 then (select BuyingPrice from cte_2 c2 where c2.rn = c1.new_rn) else BuyingPrice end Seq
from cte_2 c1),
cte_4 as (
select *,
sum(isnull(StockIn,0)) over (partition by ItemCode,TrsDate,Seq) StockIn_New,
sum(isnull(StockOut,0)) over (partition by ItemCode,TrsDate,Seq) StockOut_New,
row_number() over (partition by ItemCode,TrsDate,Seq order by TrsId) row_num
from cte_3)
select *
from cte_4
/*
CCL	B1	2009-01-01 00:00:00.000	100	NULL	1	1	NULL	1	100	80	1
CCL	S1	2009-01-01 00:00:00.000	0	50	0	2	1	1	100	80	2
CCL	S2	2009-01-01 00:00:00.000	0	30	0	3	1	1	100	80	3
CCL	B2	2009-02-01 00:00:00.000	100	NULL	2	4	1	2	100	30	1
CCL	S3	2009-02-01 00:00:00.000	0	30	0	5	4	2	100	30	2
CCL	B3	2009-02-01 00:00:00.000	100	NULL	3	6	4	3	100	0	1
CCL	B4	2009-03-01 00:00:00.000	100	NULL	3	7	6	3	100	0	1
FNT	B5	2009-01-01 00:00:00.000	200	NULL	1	8	7	1	200	110	1
FNT	S4	2009-01-01 00:00:00.000	0	50	0	9	8	1	200	110	2
FNT	S5	2009-01-01 00:00:00.000	0	30	0	10	8	1	200	110	3
FNT	S6	2009-01-01 00:00:00.000	0	30	0	11	8	1	200	110	4
*/
;with cte_1 as (
select *,row_number() over (order by (select 1)) rn from @TMSTOCK),
cte_2 as (
select *,(select max(rn) from cte_1 c1 where c1.rn < c2.rn and c1.BuyingPrice <> 0) new_rn
from cte_1 c2),
cte_3 as (
select *,case BuyingPrice when 0 then (select BuyingPrice from cte_2 c2 where c2.rn = c1.new_rn) else BuyingPrice end Seq
from cte_2 c1),
cte_4 as (
select *,
sum(isnull(StockIn,0)) over (partition by ItemCode,TrsDate,Seq) StockIn_New,
sum(isnull(StockOut,0)) over (partition by ItemCode,TrsDate,Seq) StockOut_New,
row_number() over (partition by ItemCode,TrsDate,Seq order by TrsId) row_num
from cte_3)
from cte_4 where row_num = 1
/*
CCL     B1      2009-01-01 00:00:00.000 20      1
CCL     B2      2009-02-01 00:00:00.000 70      2
CCL     B3      2009-02-01 00:00:00.000 100     3
CCL     B4      2009-03-01 00:00:00.000 100     3
FNT     B5      2009-01-01 00:00:00.000 90      1
*/
``````

Commented:
Yes, that's help alot.

Thank you very much for your help.

Commented:
Hi Sharath,

I sorry to bother you again. I add some more data, and the result become unexpected.

CCL      B1      01/01/2009 0:00:00      100      NULL      1      1      NULL
CCL      S1      01/01/2009 0:00:00      0      50      0      2      1
CCL      S2      01/01/2009 0:00:00      0      30      0      3      1
CCL      B2      01/02/2009 0:00:00      100      NULL      2      4      1
CCL      S3      01/02/2009 0:00:00      0      30      0      5      4
CCL      B3      01/02/2009 0:00:00      100      NULL      3      6      4
CCL      B4      01/03/2009 0:00:00      100      NULL      3      7      6
CCL      S4      01/04/2009 0:00:00      0      30      0      8      7
CCL      S5      01/04/2009 0:00:00      0      30      0      9      7
FNT      B5      01/01/2009 0:00:00      200      NULL      1      10      7
FNT      S6      01/01/2009 0:00:00      0      50      0      11      10
FNT      S7      01/01/2009 0:00:00      0      30      0      12      10
FNT      S8      01/01/2009 0:00:00      0      30      0      13      10

Please see the last colum 7,7,7. CCL Cross FNT ?

; WITH cte_1 AS
(SELECT     *, row_number() OVER (ORDER BY
(SELECT     1)) rn
FROM         @TMSTOCK), cte_2 AS
(SELECT     *,
(SELECT     max(rn)
FROM          cte_1 c1
WHERE      c1.rn < c2.rn AND c1.BuyingPrice <> 0) new_rn
FROM          cte_1 c2)
SELECT     *
FROM         cte_2

Last Result :

CCL      B1      01/01/2009 0:00:00      20      1
CCL      B2      01/02/2009 0:00:00      70      2
CCL      B3      01/02/2009 0:00:00      100      3
CCL      B4      01/03/2009 0:00:00      100      3
CCL      S4      01/04/2009 0:00:00      -60      0
FNT      B5      01/01/2009 0:00:00      90      1

Thank you.
``````DECLARE @TMSTOCK AS TABLE (ItemCode varchar(20), TrsId varchar(20), TrsDate datetime, StockIn int, StockOut int, BuyingPrice int) INSERT INTO @TMSTOCK
VALUES      ('CCL', 'B1',
'2009/01/01',
100, NULL, 1)
INSERT
INTO            @TMSTOCK
VALUES     ('CCL', 'S1', '2009/01/01', 0, 50, 0)
INSERT
INTO            @TMSTOCK
VALUES     ('CCL', 'S2', '2009/01/01', 0, 30, 0)
INSERT
INTO            @TMSTOCK
VALUES     ('CCL', 'B2', '2009/02/01', 100, NULL, 2)
INSERT
INTO            @TMSTOCK
VALUES     ('CCL', 'S3', '2009/02/01', 0, 30, 0)
INSERT
INTO            @TMSTOCK
VALUES     ('CCL', 'B3', '2009/02/01', 100, NULL, 3)
INSERT
INTO            @TMSTOCK
VALUES     ('CCL', 'B4', '2009/03/01', 100, NULL, 3)
INSERT
INTO            @TMSTOCK
VALUES     ('CCL', 'S4', '2009/04/01', 0, 30, 0)
INSERT
INTO            @TMSTOCK
VALUES     ('CCL', 'S5', '2009/04/01', 0, 30, 0)
INSERT
INTO            @TMSTOCK
VALUES     ('FNT', 'B5', '2009/01/01', 200, NULL, 1)
INSERT
INTO            @TMSTOCK
VALUES     ('FNT', 'S6', '2009/01/01', 0, 50, 0)
INSERT
INTO            @TMSTOCK
VALUES     ('FNT', 'S7', '2009/01/01', 0, 30, 0)
INSERT
INTO            @TMSTOCK
VALUES     ('FNT', 'S8', '2009/01/01', 0, 30, 0)
``````
Data Engineer

Commented:
Post the expected result.

Commented:

Expected Result :

CCL      B1      01/01/2009 0:00:00      20      1
CCL      B2      01/02/2009 0:00:00      10      2
CCL      B3      01/02/2009 0:00:00      100      3
CCL      B4      01/03/2009 0:00:00      100      3
FNT      B5      01/01/2009 0:00:00      90      1

Thank you.
Data Engineer

Commented:
There is no record with B1 in the recent sample set provided.

Commented:
Oops, missed in sql syntax.

the data :

CCL      B1      01/01/2009 0:00:00      100      NULL      1      1      NULL
CCL      S1      01/01/2009 0:00:00      0      50      0      2      1
CCL      S2      01/01/2009 0:00:00      0      30      0      3      1
CCL      B2      01/02/2009 0:00:00      100      NULL      2      4      1
CCL      S3      01/02/2009 0:00:00      0      30      0      5      4
CCL      B3      01/02/2009 0:00:00      100      NULL      3      6      4
CCL      B4      01/03/2009 0:00:00      100      NULL      3      7      6
CCL      S4      01/04/2009 0:00:00      0      30      0      8      7
CCL      S5      01/04/2009 0:00:00      0      30      0      9      7
FNT      B5      01/01/2009 0:00:00      200      NULL      1      10      7
FNT      S6      01/01/2009 0:00:00      0      50      0      11      10
FNT      S7      01/01/2009 0:00:00      0      30      0      12      10
FNT      S8      01/01/2009 0:00:00      0      30      0      13      10

CCL      S4      01/04/2009 0:00:00      0      30      0      8      7
CCL      S5      01/04/2009 0:00:00      0      30      0      9      7

To your lates data also its TrsId.

Thank you.

Commented:
Hi Sharath,

DECLARE @TMSTOCK AS TABLE (ItemCode varchar(20), TrsId varchar(20), TrsDate datetime, StockIn int, StockOut int, Cost int) INSERT INTO @TMSTOCK
VALUES     ('CCL', 'B1',
'2009/01/01', 100, NULL,
1)
INSERT
INTO            @TMSTOCK
VALUES     ('CCL', 'S1', '2009/01/01', 0, 50, 0)
INSERT
INTO            @TMSTOCK
VALUES     ('CCL', 'S2', '2009/01/01', 0, 30, 0)
INSERT
INTO            @TMSTOCK
VALUES     ('CCL', 'B2', '2009/02/01', 100, NULL, 2)
INSERT
INTO            @TMSTOCK
VALUES     ('CCL', 'S3', '2009/02/01', 0, 30, 0)
INSERT
INTO            @TMSTOCK
VALUES     ('CCL', 'B3', '2009/02/01', 100, NULL, 3)
INSERT
INTO            @TMSTOCK
VALUES     ('CCL', 'B4', '2009/03/01', 100, NULL, 3)
INSERT
INTO            @TMSTOCK
VALUES     ('CCL', 'S4', '2009/01/01', 0, 30, 0)
INSERT
INTO            @TMSTOCK
VALUES     ('CCL', 'S5', '2009/01/01', 0, 30, 0)
INSERT
INTO            @TMSTOCK
VALUES     ('FNT', 'B5', '2009/01/01', 200, NULL, 1)
INSERT
INTO            @TMSTOCK
VALUES     ('FNT', 'S6', '2009/01/01', 0, 50, 0)
INSERT
INTO            @TMSTOCK
VALUES     ('FNT', 'S7', '2009/01/01', 0, 30, 0)
INSERT
INTO            @TMSTOCK
VALUES     ('FNT', 'S8', '2009/01/01', 0, 30, 0); WITH cte_1 AS
(SELECT     *, row_number() OVER (ORDER BY
(SELECT     1)) rn
FROM         @TMSTOCK), cte_2 AS
(SELECT     *,
(SELECT     max(rn)
FROM          cte_1 c1
WHERE      c1.rn < c2.rn AND c1.Cost <> 0) new_rn
FROM          cte_1 c2), cte_3 AS
(SELECT     *, CASE Cost WHEN 0 THEN
(SELECT     TrsID
FROM          cte_2 c2
WHERE      c2.rn = c1.new_rn) ELSE TrsID END TrsID_New
FROM          cte_2 c1), cte_4 AS
(SELECT     *, sum(isnull(StockIn, 0)) OVER (partition BY ItemCode, TrsDate, TrsID_New) StockIn_New, sum(isnull(StockOut, 0)) OVER (partition BY ItemCode,
TrsDate, TrsID_New) StockOut_New, row_number() OVER (partition BY ItemCode, TrsDate, TrsID_New
ORDER BY TrsId) row_num
FROM         cte_3)
SELECT     TrsId, TrsDate, ItemCode, StockIn_New - StockOut_New Stack_Last, Cost
FROM         cte_4
WHERE     row_num = 1

Thank you.

Data Engineer

Commented: