如何计算期初和期末数量余额 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

 Item Opening RecQty IssueQty BalanceQty10 15 0 25

 Item Opening RecQty IssueQty BalanceQty45 0 40 5

>

创建表#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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！

将可变参数列表传递给 SqlServer2008 存储过程的理智/快速方法

Sane/fast method to pass variable parameter lists to SqlServer2008 stored procedure(将可变参数列表传递给 SqlServer2008 存储过程的理智/快速方法)...

2023-10-26 数据库问题

1

为什么SqlServer select语句会选择匹配的行和匹配并带有尾随空格的行

Why would SqlServer select statement select rows which match and rows which match and have trailing spaces(为什么SqlServer select语句会选择匹配的行和匹配并带有尾随空格的行)...

2023-10-08 数据库问题

3

SQLSERVER 中的 ListAGG

ListAGG in SQLSERVER(SQLSERVER 中的 ListAGG)...

2023-07-18 数据库问题

7

相当于 mySQL 中的 SQLServer 函数 SCOPE_IDENTITY()?

The equivalent of SQLServer function SCOPE_IDENTITY() in mySQL?(相当于 mySQL 中的 SQLServer 函数 SCOPE_IDENTITY()?)...

2023-04-28 数据库问题

59

使用 spark sql 在 sqlserver 上执行查询

execute query on sqlserver using spark sql(使用 spark sql 在 sqlserver 上执行查询)...

2023-04-04 数据库问题

8

Debezium 如何使用 Kafka Connect 正确注册 SqlServer 连接器 - 连接被拒绝

Debezium How do I correctly register the SqlServer connector with Kafka Connect - connection refused(Debezium 如何使用 Kafka Connect 正确注册 SqlServer 连接器 - 连接被拒绝)...

2023-04-03 数据库问题

3