我怎样才能获得opening_stock和closing_stock [英] How can I get opening_stock and closing_stock

问题描述

我有两个表（Tbl_Items）和（tbl_Items_Journals）

表（Tbl_Items）包含

itemid项目名称open_stock

1 Persil_HS 100

2 Persil_LS 100

3 Persil_Gl 100

4 General_7 100



table （Tbl_Items_Journals）包含

id itemid open_stock购买销售日期

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希望显示结果（从01/02/2018到01/04/2018之间的时间段） ）



itemid open_stock购买销售closing_stock

1 100 50 75 75

2 100 20 30 90

3 100 0 0 100

4 100 0 0 100



我的尝试：

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

推荐答案

编写查询以汇总销售和购买，按itemid和open_stock分组。然后另一个查询来计算收盘价。这是一个例子

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

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

这篇关于我怎样才能获得opening_stock和closing_stock的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！