时间戳

首页 > 解决方案 > 如何计算交易中的期初和期末余额

sql - 如何计算交易中的期初和期末余额

问题描述

我需要计算期初和期末余额交易。我有三个表 OB、购买和使用。所有表的唯一键是产品 ID。我需要一个存储过程来获取打击结果。根据所选的产品 id 需要计算期初和期末余额。

表结构：下面的代码包含表和示例数据

USE [BMC]
GO
/****** Object:  Table [dbo].[TBLProductOB]    Script Date: 08-07-2021 01:20:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TBLProductOB](
    [Skey] [int] IDENTITY(1,1) NOT NULL,
    [EntryDate] [date] NULL,
    [Productid] [int] NULL,
    [ProductOB] [decimal](12, 3) NULL,
 CONSTRAINT [PK_TBLProductOB] PRIMARY KEY CLUSTERED 
(
    [Skey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[TBLProductPurchase]    Script Date: 08-07-2021 01:20:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TBLProductPurchase](
    [Skey] [int] IDENTITY(1,1) NOT NULL,
    [Entrydate] [date] NULL,
    [Productid] [int] NULL,
    [P_Purchase] [decimal](12, 3) NULL,
 CONSTRAINT [PK_TBLProductPurchase] PRIMARY KEY CLUSTERED 
(
    [Skey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[TBLProductUsage]    Script Date: 08-07-2021 01:20:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TBLProductUsage](
    [Skey] [int] IDENTITY(1,1) NOT NULL,
    [Entrydate] [date] NULL,
    [Productid] [int] NULL,
    [P_Usage] [decimal](12, 3) NULL,
 CONSTRAINT [PK_TBLProductUsage] PRIMARY KEY CLUSTERED 
(
    [Skey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[TBLProductOB] ON 

INSERT [dbo].[TBLProductOB] ([Skey], [EntryDate], [Productid], [ProductOB]) VALUES (4, CAST(N'2021-04-01' AS Date), 3, CAST(100.000 AS Decimal(12, 3)))
INSERT [dbo].[TBLProductOB] ([Skey], [EntryDate], [Productid], [ProductOB]) VALUES (6, CAST(N'2021-04-01' AS Date), 1, CAST(10.000 AS Decimal(12, 3)))
INSERT [dbo].[TBLProductOB] ([Skey], [EntryDate], [Productid], [ProductOB]) VALUES (7, CAST(N'2021-04-01' AS Date), 2, CAST(150.000 AS Decimal(12, 3)))
SET IDENTITY_INSERT [dbo].[TBLProductOB] OFF
SET IDENTITY_INSERT [dbo].[TBLProductPurchase] ON 

INSERT [dbo].[TBLProductPurchase] ([Skey], [Entrydate], [Productid], [P_Purchase]) VALUES (1, CAST(N'2021-07-06' AS Date), 3, CAST(100.000 AS Decimal(12, 3)))
INSERT [dbo].[TBLProductPurchase] ([Skey], [Entrydate], [Productid], [P_Purchase]) VALUES (7, CAST(N'2021-07-01' AS Date), 3, CAST(50.000 AS Decimal(12, 3)))
INSERT [dbo].[TBLProductPurchase] ([Skey], [Entrydate], [Productid], [P_Purchase]) VALUES (8, CAST(N'2021-07-15' AS Date), 3, CAST(50.000 AS Decimal(12, 3)))
INSERT [dbo].[TBLProductPurchase] ([Skey], [Entrydate], [Productid], [P_Purchase]) VALUES (9, CAST(N'2021-07-01' AS Date), 1, CAST(1.000 AS Decimal(12, 3)))
INSERT [dbo].[TBLProductPurchase] ([Skey], [Entrydate], [Productid], [P_Purchase]) VALUES (10, CAST(N'2021-07-03' AS Date), 1, CAST(1.000 AS Decimal(12, 3)))
INSERT [dbo].[TBLProductPurchase] ([Skey], [Entrydate], [Productid], [P_Purchase]) VALUES (11, CAST(N'2021-07-05' AS Date), 1, CAST(3.000 AS Decimal(12, 3)))
INSERT [dbo].[TBLProductPurchase] ([Skey], [Entrydate], [Productid], [P_Purchase]) VALUES (12, CAST(N'2021-07-01' AS Date), 2, CAST(10.000 AS Decimal(12, 3)))
INSERT [dbo].[TBLProductPurchase] ([Skey], [Entrydate], [Productid], [P_Purchase]) VALUES (13, CAST(N'2021-07-02' AS Date), 2, CAST(10.000 AS Decimal(12, 3)))
INSERT [dbo].[TBLProductPurchase] ([Skey], [Entrydate], [Productid], [P_Purchase]) VALUES (14, CAST(N'2021-07-05' AS Date), 2, CAST(10.000 AS Decimal(12, 3)))
INSERT [dbo].[TBLProductPurchase] ([Skey], [Entrydate], [Productid], [P_Purchase]) VALUES (15, CAST(N'2021-07-01' AS Date), 5, CAST(10.000 AS Decimal(12, 3)))
SET IDENTITY_INSERT [dbo].[TBLProductPurchase] OFF
SET IDENTITY_INSERT [dbo].[TBLProductUsage] ON 

INSERT [dbo].[TBLProductUsage] ([Skey], [Entrydate], [Productid], [P_Usage]) VALUES (7, CAST(N'2021-07-01' AS Date), 3, CAST(10.000 AS Decimal(12, 3)))
INSERT [dbo].[TBLProductUsage] ([Skey], [Entrydate], [Productid], [P_Usage]) VALUES (8, CAST(N'2021-07-02' AS Date), 3, CAST(10.000 AS Decimal(12, 3)))
INSERT [dbo].[TBLProductUsage] ([Skey], [Entrydate], [Productid], [P_Usage]) VALUES (9, CAST(N'2021-07-08' AS Date), 3, CAST(10.000 AS Decimal(12, 3)))
INSERT [dbo].[TBLProductUsage] ([Skey], [Entrydate], [Productid], [P_Usage]) VALUES (10, CAST(N'2021-07-15' AS Date), 3, CAST(30.000 AS Decimal(12, 3)))
INSERT [dbo].[TBLProductUsage] ([Skey], [Entrydate], [Productid], [P_Usage]) VALUES (11, CAST(N'2021-07-01' AS Date), 2, CAST(2.000 AS Decimal(12, 3)))
INSERT [dbo].[TBLProductUsage] ([Skey], [Entrydate], [Productid], [P_Usage]) VALUES (12, CAST(N'2021-07-02' AS Date), 2, CAST(2.000 AS Decimal(12, 3)))
INSERT [dbo].[TBLProductUsage] ([Skey], [Entrydate], [Productid], [P_Usage]) VALUES (13, CAST(N'2021-07-03' AS Date), 2, CAST(2.000 AS Decimal(12, 3)))
INSERT [dbo].[TBLProductUsage] ([Skey], [Entrydate], [Productid], [P_Usage]) VALUES (14, CAST(N'2021-07-05' AS Date), 2, CAST(2.000 AS Decimal(12, 3)))
INSERT [dbo].[TBLProductUsage] ([Skey], [Entrydate], [Productid], [P_Usage]) VALUES (16, CAST(N'2021-07-01' AS Date), 1, CAST(2.000 AS Decimal(12, 3)))
INSERT [dbo].[TBLProductUsage] ([Skey], [Entrydate], [Productid], [P_Usage]) VALUES (17, CAST(N'2021-07-03' AS Date), 1, CAST(3.000 AS Decimal(12, 3)))
INSERT [dbo].[TBLProductUsage] ([Skey], [Entrydate], [Productid], [P_Usage]) VALUES (18, CAST(N'2021-07-04' AS Date), 2, CAST(2.000 AS Decimal(12, 3)))
SET IDENTITY_INSERT [dbo].[TBLProductUsage] OFF

刚试过：我不知道如何写一个基于事务的存储过程。我只是在联合的帮助下获得了所有表格结果。

Declare @1stOpeningBalance decimal(12,3)= (select ProductOB from TBLProductOB where Productid=2)

Select a.Entrydate,a.Productid,Lag(((Sum(a.Ob)+sum(a.Purchase))-sum(a.Usage)),1,@1stOpeningBalance) over (order by Entrydate asc) as Ob, sum(a.Purchase) as Purchase,(Sum(a.Ob)+sum(a.Purchase)) as Total, Sum(a.Usage) as Usage, ((Sum(a.Ob)+sum(a.Purchase))-sum(a.Usage)) as Cb  from
(
select Entrydate,Productid,0 as Ob,Sum(Isnull(P_Purchase,0.000)) as Purchase,0 as Usage from TBLProductPurchase
group by EntryDate,Productid
union all
select Entrydate,Productid,0 as Ob,0 as Purchase,Sum(Isnull(P_Usage,0.000)) as Usage from TBLProductUsage
group by EntryDate,Productid
) as a
where Entrydate between '2021-07-01' and '2021-07-05' and Productid=2 
group by a.EntryDate,a.Productid

以上查询输出

所需的输出结果：

所需输出

标签： sqlsql-serverstored-proceduressql-server-2012sql-query-store

解决方案

您可以使用LEAD或LAG功能来执行此操作

确保您只有一条输入日期的记录。如果您在同一日期有多个条目，则必须使用唯一键来订购

假设：您可以计算期末余额并且只需要计算期初余额并且需要一个系统起始期初余额

Create table #temp_TBL(EntryDate date,Purchase decimal(18,2),total decimal(18,2),Usage decimal(18,2), CB decimal(18,2))


Declare @1stOpeningBalance decimal(18,2)=150

    Insert into #temp_TBL (EntryDate,Purchase,total,Usage,CB)
    values
    ('2021-07-01',10,160,2,158),
    ('2021-07-02',10,168,2,166),
    ('2021-07-03',0,166,2,164),
    ('2021-07-04',0,164,2,162),
    ('2021-07-05',10,172,2,170)
    
    select EntryDate
    ,Lag(CB,1,@1stOpeningBalance) over (order by EntryDate ASC) as OB
    ,Purchase,total,Usage,CB from #temp_TBL

输出

在此处输入图像描述

推荐阅读