How can I get opening_stock and closing_stock

Question

0.00/5 (No votes)

See more:

i have two tables (Tbl_Items) and (tbl_Items_Journals)
table (Tbl_Items) Consists Of
itemid itemname open_stock
1 Persil_HS 100
2 Persil_LS 100
3 Persil_Gl 100
4 General_7 100

table(Tbl_Items_Journals) Consists of
id itemid open_stock purchase sales date
1 1 100 0 50 01/02/2018
2 1 100 25 0 01/03/2018
3 1 100 0 25 01/03/2018
4 1 100 25 0 01/04/2018
5 2 100 0 10 01/02/2018
6 2 100 20 0 01/03/2018
7 2 100 0 20 01/04/2018

i want to show the result like that (the period between 01/02/2018 and 01/04/2018)

itemid open_stock purchase sales closing_stock
1 100 50 75 75
2 100 20 30 90
3 100 0 0 100
4 100 0 0 100

What I have tried:

SELECT
    t1.Date,
    mt.itemCode,
	mt.itemname,
    (SELECT Open_Stock FROM [Tbl_Items] WHERE itemid = t1.itemid) +
    COALESCE((SELECT SUM(t2.Purshase - t2.Sales) FROM [Tbl_Items_Journals] t2
     WHERE t2.Date < t1.Date AND t1.itemid = t2.itemid), 0) AS OpeningStock,
    t1.Purshase,
    t1.Sales,
    (SELECT Open_Stock FROM [Tbl_Items] WHERE itemid = t1.itemid) +
COALESCE((SELECT SUM(t2.Purshase - t2.Sales) FROM [Tbl_Items_Journals] t2
     WHERE t2.Date <= t1.Date AND t1.itemid = t2.itemid), 0) AS ClosingStock
FROM [Tbl_Items_Journals] t1
INNER JOIN [Tbl_Items] mt
    ON t1.itemid = mt.itemId
ORDER BY
    mt.itemName,
    t1.Date;
but it didnot work for me.  can any one help me

Posted 28-Feb-18 18:33pm

Abuamer

Updated 28-Feb-18 19:12pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bryian Tan · Accepted Answer · 2018-02-28T19:13:00

Write the query to sum the sales and purchase, group by the itemid and open_stock. Then another query to calculate the closing stock. Here is an example

SQL

DECLARE @Tbl_Items TABLE (
	ItemId		INT IDENTITY (1,1), 
	ItemName	VARCHAR(100),
	Open_Stock	INT
)

DECLARE @tbl_Items_Journals TABLE (
	Id			INT IDENTITY (1,1), 
	ItemId		INT, 
	Open_Stock	INT,
	Purchase	INT,
	Sales		INT,
	[Date]		DATE
)

INSERT INTO @Tbl_Items
	SELECT ' Persil_HS', 100 UNION
	SELECT ' Persil_LS', 100 UNION
	SELECT ' Persil_GI', 100 UNION
	SELECT ' General_7', 100 

INSERT INTO @tbl_Items_Journals
	SELECT  1, 100, 0, 50, '01/02/2018' UNION
	SELECT  1, 100, 25, 0, '01/03/2018' UNION
	SELECT  1, 100, 0, 25, '01/03/2018' UNION
	SELECT  1, 100, 25, 0, '01/04/2018' UNION
	SELECT  2, 100, 0, 10, '01/02/2018' UNION
	SELECT  2, 100, 20, 0, '01/03/2018' UNION
	SELECT  2, 100, 0, 20, '01/04/2018' UNION
	SELECT  3, 100, 0, 70, '01/04/2018' UNION
	SELECT  3, 100, 10, 20, '01/04/2018' 

;WITH tempSumPurSales AS ( 
	SELECT b.ItemId, b.Open_Stock, ISNULL(SUM(a.Purchase),0) 'Purchase', 
		ISNULL(SUM(a.Sales),0) 'Sales'
	FROM   @Tbl_Items b LEFT JOIN @tbl_Items_Journals a ON b.ItemId = a.ItemId
	WHERE ((a.[Date] BETWEEN '01/02/2018' and '01/04/2018') OR a.[Date] IS NULL)
	GROUP BY b.ItemId, b.Open_Stock
) SELECT *, (Open_Stock + Purchase) - Sales AS Closing_Stock  FROM tempSumPurSales

Output:

ItemId	Open_Stock	Purchase	Sales	Closing_Stock
1	       100	      50	    75	     75
2	       100	      20	    30	     90
3	       100	      10	    90	     20
4	       100	       0	     0	    100