我有两个带有主键和外键的表(MaterialId)

Material 表(多种 Material )

MaterialId MaterialName OpeningStock
1 笔 100
2 铅笔 50

Material 库存(多个 Material 条目)

MaterialId PurchaseQty SalesQty 日期
1 500 0 2016-12-15
1 0 0 2016-12-16
1 300 0 2016-12-17
1 0 400 2016-12-18
1 0 0 2016-12-19
1 0 0 2016-12-20
1 0 400 2016-12-21
1 200 100 2016-12-22

现在当我通过 @FromDate 和 @Todate 时
我想输出如下:

日期 Material 名称 OpeningStock PurchaseQty SalesQty ClosingStock
2016-12-15 笔 100 500 0 600
2016-12-16 笔 600 0 0 600
2016-12-17 笔 600 300 0 900
2016-12-18 笔 900 0 400 500
2016-12-19 笔 500 0 0 500
2016-12-20 笔 500 0 0 500
2016-12-21 笔 500 0 400 100
2016-12-22 笔 100 200 100 200

笔记 :
1.如果数据库表有问题，请指导我如何处理这种情况。
2. 并从两个表中找到当前日期股票

请您参考如下方法:

您正在寻找各种数量值的滚动总和。一种方法是使用相关子查询:

SELECT 
    t1.Date, 
    mt.MaterialName, 
    (SELECT OpeningStock FROM [Material Table] WHERE MaterialId = t1.MaterialId) + 
    COALESCE((SELECT SUM(t2.PurchaseQty - t2.SalesQty) FROM [Material Stock] t2 
     WHERE t2.Date < t1.Date AND t1.MaterialId = t2.MaterialId), 0) AS OpeningStock, 
    t1.PurchaseQty, 
    t1.SalesQty, 
    (SELECT OpeningStock FROM [Material Table] WHERE MaterialId = t1.MaterialId) + 
COALESCE((SELECT SUM(t2.PurchaseQty - t2.SalesQty) FROM [Material Stock] t2 
     WHERE t2.Date <= t1.Date AND t1.MaterialId = t2.MaterialId), 0) AS ClosingStock 
FROM [Material Stock] t1 
INNER JOIN [Material Table] mt 
    ON t1.MaterialId = mt.MaterialId 
ORDER BY 
    mt.MaterialName, 
    t1.Date; 

请注意，将期初库存值存储在与 Material 库存表不同的表中是一种糟糕的表设计。这意味着上面的查询不会返回铅笔记录。更好的方法是将种子记录插入到每个 Material 的 Material 库存中，数量为初始库存。

输出:



演示在这里:

Rextester