Get Buying Cost From Current Stock

emi_sastra
emi_sastra used Ask the Experts™
on
Hi All,

I have stock table structure below:

TMSTOCK:

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

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:

ItemCode  TrsId  TrsDate         StockLast   BuyingPrice
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
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Oops.

The query result wanted:

ItemCode  TrsId  TrsDate         StockLast   BuyingPrice
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 ..

Author

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.
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Commented:
In that case your result seems wrong:
ItemCode  TrsId  TrsDate         StockLast   BuyingPrice
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';

Open in new window

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,
         MAX(buyingprice) buyingprice
    FROM tmstock
GROUP BY itemcode,  trsdate

try this

Author

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.
Please try the query below.
SELECT ItemCode, TrsId, TrsDate, sum(StockIn)-sum(StockOut) AS StockLast, BuyingPrice
FROM TMSTOCK
GROUB BY ItemCode

Open in new window

Author

Commented:
Hi allmer,

Your code obviously will not work at a glance.

Thank you.

Author

Commented:
Hi shru_0409,

Let me try it.

Thank you.

Author

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.
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....

Author

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 ... ?

Author

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.

Author

Commented:
Hi shru_0409,

Please see the sample data.

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.

Author

Commented:
Hi allmer,

Thank you for your time.

Author

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.
SharathData Engineer

Commented:
try this.
select ItemCode,TrsId,TrsDate,StockIn-StockOut StackLast,BuyingPrice 
  from(select ItemCode,TrsId,TrsDate,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

Open in new window

SharathData Engineer

Commented:
sample attached.
SharathData 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
*/
select ItemCode,TrsId,TrsDate,StockIn-StockOut StackLast,BuyingPrice 
  from(select ItemCode,TrsId,TrsDate,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
/*
CCL	B1	2009-01-01 00:00:00.000	20	1
FNT	B1	2009-01-01 00:00:00.000	90	1
*/

Open in new window

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,
         MAX(buyingprice) buyingprice
    FROM tmstock
GROUP BY itemcode,  trsdate


try this.....
SharathData Engineer

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

Author

Commented:
Hi Sharath_123

Please try below 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',            '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.

Author

Commented:
Hi Shru,

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

Thank you.
SharathData 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)

SharathData 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

Author

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.

Author

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.
SharathData Engineer

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

Author

Commented:
Please refer to my comment before your last comment.

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.
SharathData Engineer

Commented:
try this.

select ItemCode,TrsId,TrsDate,StockIn-StockOut StackLast,BuyingPrice
  from (
select ItemCode,TrsId,TrsDate,BuyingPrice,
       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   )

select ItemCode,TrsId,TrsDate,StockIn-StockOut StackLast,BuyingPrice
  from (
select ItemCode,TrsId,TrsDate,BuyingPrice,
       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
*/

Open in new window

Author

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.

Author

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

Thank you.
SharathData 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.

select ItemCode,TrsId,TrsDate,StockIn-StockOut StackLast,BuyingPrice
  from (
select ItemCode,TrsId,TrsDate,BuyingPrice,
       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%'

Author

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.
SharathData Engineer

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

Author

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.
SharathData 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   )

select ItemCode,TrsId,TrsDate,StockIn_New-StockOut_New Stack_Last,BuyingPrice
  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 *,
               (select max(BuyingPrice) from @TMSTOCK t2 
                 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
*/

Open in new window

Author

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.

Author

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.
SharathData Engineer

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

Author

Commented:
Ok.

How to solve this problem?

Thank you.

Author

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

B2 still have stock to sales to S3?

What about my idea ?

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

Thank you.
SharathData 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)
select ItemCode,TrsId,TrsDate,StockIn_New-StockOut_New Stack_Last,BuyingPrice
  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
*/

Open in new window

Author

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 :

select ItemCode,TrsId,TrsDate,StockIn_New-StockOut_New Stack_Last,BuyingPrice
  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)
select ItemCode,TrsId,TrsDate,StockIn_New-StockOut_New Stack_Last,BuyingPrice
  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
*/

Open in new window

Author

Commented:
Yes, that's help alot.

Thank you very much for your help.

Author

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)

Open in new window

SharathData Engineer

Commented:
Post the expected result.

Author

Commented:
Glad to hear your again.

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.
SharathData Engineer

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

Author

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

I add :

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.

Author

Commented:
Hi Sharath,

Please see below data and your code, still problem.

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.

SharathData Engineer

Commented:
I answered in your another question. Please check.

Author

Commented:
Yes, the query is what you gave in your answer of my other question.

Ok. I copy my comment above to that question.

Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial