Inventory Stock movement

Dear Experts,

In my inventory, I have receiving, withdrawal and adjusting entry as shown in the attachment. What I want is to combine them that shows their movements for easier reconciliation.  

Any help to make this happen would be greatly appreciated.

Thanks.
StockMovement.docx
JimiJ13I T ConsultantAsked:
Who is Participating?
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
First, I created tables to contain your data (when giving sample tables etc for questions like this, it really does help if you provide the DDL for your test data):

(I apologize for using character data types for dates for the purists, it was quicker and easier to do this with CHAR :))

use demo
go

create table receiving (control char(10), stock_code char(9), action_date char(8), qty int, price int);
create table withdrawl (control char(10), stock_code char(9), action_date char(8), qty int, price int);
create table adjustment (control char(10), stock_code char(9), action_date char(8), qty int, price int);

insert into receiving values ('RR 001','1001','20140301',5,10);
insert into receiving values ('RR 002','1001','20140403',5,9);
insert into withdrawl values ('WIR 001','1001','20140305',2,10);
insert into adjustment values ('IA 001','1001','20140331',-1,10);

Open in new window


Next, since you're unioning three tables to get your data, it was easier to read the final solution by creating a view for the union:

create view all_txns as 
(select  control, stock_code, action_date, qty, price from receiving
union
select  control, stock_code, action_date, qty*-1, price from withdrawl
union
select  control, stock_code, action_date, qty, price from adjustment
)

Open in new window


Then ran the following query:

select a.control, a.stock_code, a.action_Date, a.qty, 
       a.qty*a.price as value, sum(b.qty) as run_qty, sum(b.qty*b.price) as run_val,
       a.price
from all_txns a inner join all_txns b
on (b.action_date <= a.action_date)
group by a.control, a.stock_code, a.action_Date, a.qty, a.qty*a.price, a.price
order by stock_code, action_date

Open in new window


To generate this output:

control    stock_code action_Date qty         value       run_qty     run_val     price
---------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
RR 001     1001       20140301    5           50          5           50          10
WIR 001    1001       20140305    -2          -20         3           30          10
IA 001     1001       20140331    -1          -10         2           20          10
RR 002     1001       20140403    5           45          7           65          9

(4 row(s) affected)

Open in new window


That get you what you want ?

(As an aside, since I did this in Oracle first and then realized it was a SQL Server question, this is much easier to do in Oracle):

select control, stock_code, action_Date, qty, price, price*qty as value,
       sum(qty) over (partition by stock_Code order by action_date) run_qty,
       sum(price*qty) over (partition by stock_Code order by action_date) run_val
from
(
select  control, stock_code, action_date, qty, price from receiving
union
select  control, stock_code, action_date, qty*-1, price from withdrawl
union
select  control, stock_code, action_date, qty, price from adjustment
)
/
0
 
JimiJ13I T ConsultantAuthor Commented:
Steve,

Your great effort is truly appreciated, and I think few more tweaks are needed to get my desired output.

When I tried the last query, I got an error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'order'.

select control, stock_code, action_Date, qty, price, price*qty as value,
       sum(qty) over (partition by stock_Code order by action_date) run_qty,
       sum(price*qty) over (partition by stock_Code order by action_date) run_val
from
(
select  control, stock_code, action_date, qty, price from receiving
union
select  control, stock_code, action_date, qty*-1, price from withdrawl
union
select  control, stock_code, action_date, qty, price from adjustment
)

Open in new window

0
 
Steve WalesSenior Database AdministratorCommented:
The last query won't work on SQL Server.  It was an after thought - that's how you do it in Oracle:

"As an aside, since I did this in Oracle first and then realized it was a SQL Server question, this is much easier to do in Oracle"

I did it in Oracle, then realized it was a SQL Server question so had to redo it in SQL Server, but included the Oracle query for (a) sake of comparison and (b) so I could find it easily again later :)
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
JimiJ13I T ConsultantAuthor Commented:
Sorry! I thought the last one was a way of doing it in MS SQL.


Thanks.
0
 
JimiJ13I T ConsultantAuthor Commented:
It did not give me the complete solution (no solution given for MS SQL) but a good lead to coming up with the final via other resources or means.

Please refer to http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28423550.html#a40033357

Thanks.
0
 
Steve WalesSenior Database AdministratorCommented:
Actually, if you recheck the post - the top 90% of the post is the full solution in SQL Server.

I'm sorry if my comments about Oracle at the very bottom clouded the issue for you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.