如何计算期初和期末数量余额 SQL Server

How to calculate Opening and Closing quantity Balances SQL Server(如何计算期初和期末数量余额 SQL Server)
本文介绍了如何计算期初和期末数量余额 SQL Server的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

我有以下表格结构

TransactionDate Item RecQty IssueQty1-jun-2011 A 10 02-jun-2011 A 15 02011 年 6 月 3 日 A 20 02011 年 6 月 4 日 A 0 202011 年 6 月 4 日 A 0 20

我想要特定日期的结果,即 2-jun-2011

 Item Opening RecQty IssueQty BalanceQty10 15 0 25

2011 年 1 月 4 日

 Item Opening RecQty IssueQty BalanceQty45 0 40 5

解决方案

我打算至少假设 SQL Server 2005.将来,请使用您需要支持的最低版本指定/标记问题.

>

创建表#x(交易日期 SMALLDATETIME,项目 CHAR(1), RecQty INT, IssueQty INT);插入 #x 选择 '20110601','A',10,0UNION ALL SELECT '20110602','A',15,0UNION ALL SELECT '20110603','A',20,0UNION ALL SELECT '20110604','A',0,20UNION ALL SELECT '20110604','A',0,20;声明 @StartDate SMALLDATETIME = '20110601',@Date SMALLDATETIME = '20110602';WITH x(Item, prevR, prevI, curR, curI) AS(选择物品,SUM(CASE WHEN TransactionDate < @Date THEN RecQty ELSE 0 END),SUM(CASE WHEN TransactionDate < @Date THEN IssueQty ELSE 0 END),SUM(CASE WHEN TransactionDate = @Date THEN RecQty ELSE 0 END),SUM(CASE WHEN TransactionDate = @Date THEN IssueQty ELSE 0 END)FROM #x WHERE TransactionDate BETWEEN @StartDate 和 @Date按项目分组)选择物品,开场 = prevR - prevI,RecQty = curR,IssueQty = curI,BalanceQty = (prevR - prevI) + (curR - curI)从 x;删除表#x;

I have the following table strucature

TransactionDate   Item  RecQty  IssueQty

1-jun-2011         A      10      0
2-jun-2011         A      15      0
3-jun-2011         A      20      0
4-jun-2011         A       0     20
4-jun-2011         A       0     20

And I want the result on specific Date i.e 2-jun-2011

 Item    Opening     RecQty  IssueQty  BalanceQty
  A       10            15     0         25

And on 4-Jan-2011

 Item    Opening     RecQty  IssueQty  BalanceQty
  A       45            0     40         5

解决方案

I'm going to assume at least SQL Server 2005. In the future, please specify/tag the question with the minimum version you need to support.

CREATE TABLE #x
(
    TransactionDate SMALLDATETIME,
    Item CHAR(1), RecQty INT, IssueQty INT
);

INSERT #x SELECT '20110601','A',10,0
UNION ALL SELECT '20110602','A',15,0
UNION ALL SELECT '20110603','A',20,0
UNION ALL SELECT '20110604','A',0,20
UNION ALL SELECT '20110604','A',0,20;

DECLARE @StartDate SMALLDATETIME = '20110601', 
        @Date      SMALLDATETIME = '20110602';

WITH x(Item, prevR, prevI, curR, curI) AS
(
    SELECT 
        Item,
        SUM(CASE WHEN TransactionDate < @Date THEN RecQty ELSE 0 END),
        SUM(CASE WHEN TransactionDate < @Date THEN IssueQty ELSE 0 END),
        SUM(CASE WHEN TransactionDate = @Date THEN RecQty ELSE 0 END),
        SUM(CASE WHEN TransactionDate = @Date THEN IssueQty ELSE 0 END)
    FROM #x WHERE TransactionDate BETWEEN @StartDate AND @Date
    GROUP BY Item
)
SELECT 
    Item,
    Opening = prevR - prevI,
    RecQty = curR,
    IssueQty = curI,
    BalanceQty = (prevR - prevI) + (curR - curI)
FROM x;

DROP TABLE #x;

这篇关于如何计算期初和期末数量余额 SQL Server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Convert varchar to float IF ISNUMERIC(将 varchar 转换为 float IF ISNUMERIC)
multi part identifier could not be bound sql(无法绑定多部分标识符 sql)
Any benefit to explicitly dropping local temporary tables at the end of a stored procedure?(在存储过程结束时显式删除本地临时表有什么好处?)
Check if a column contains text using SQL(使用 SQL 检查列是否包含文本)
Select value if condition in SQL Server(SQL Server 中的条件选择值)
Control flow in T-SQL SP using IF..ELSE IF - are there other ways?(使用 IF..ELSE IF 在 T-SQL SP 中控制流 - 还有其他方法吗?)