Как рассчитать баланс открытия и закрытия в транзакции

ProgramBox

Как рассчитать баланс открытия и закрытия в транзакции

Post author:admin
Запись опубликована:16 января, 2022
Post category:Вопросы по программированию

#sql #sql-server #stored-procedures #sql-server-2012 #sql-query-store

Вопрос:

Мне нужно рассчитать транзакцию открытия и закрытия баланса. У меня есть три таблицы OB, Покупка и использование. Уникальным ключом всех таблиц является идентификатор продукта. Мне нужна хранимая процедура для получения результатов. На основе выбранного идентификатора продукта необходимо рассчитать баланс открытия и закрытия.

Структура таблицы: Приведенный ниже код содержит таблицу и примеры данных

 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

Только что попробовал: я не знаю, как написать хранимую процедуру на основе транзакций. Я просто получал все результаты таблицы с помощью union.

 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

Требуемый Выходной Результат:

1. В соответствии с руководством по вопросам, пожалуйста, не размещайте изображения кода, данных, сообщений об ошибках и т.д. — Скопируйте или введите текст в вопрос. Пожалуйста, зарезервируйте использование изображений для диаграмм или демонстрации ошибок рендеринга, вещей, которые невозможно точно описать с помощью текста.

2. @Nandha типы данных находятся в varchar. Для числовых значений это должно быть int.

3. @AmitVerma типы данных без проблем. Использование приведения для преобразования. Мне просто нужна хранимая процедура для вывода выше

4. Declare @1stOpeningBalance decimal(12,3)= (select ProductOB from TBLProductOB where Productid=2) Это утверждение просто чепуха. Очевидно, что вы можете (и, вероятно, делаете это в реальных данных) иметь несколько строк на продукт. Вы разрешаете механизму присваивать значение из любой соответствующей строки. Вам повезло, что ваши данные образца не содержат нескольких строк для продукта 2 — этот факт приведет к ошибке выполнения. Вам необходимо указать, в какой конкретной строке должно быть указано значение начального баланса.

5. И, возможно, вы еще не подумали о том, что должно произойти на дату, в которой нет строки покупки или использования в течение интересующего вас периода? Подумай об этом.

Ответ №1:

Для этого вы можете использовать функцию ОПЕРЕЖЕНИЯ или ЗАДЕРЖКИ

Убедитесь, что у вас есть только одна запись на дату ввода. Если у вас несколько записей на одну и ту же дату, вам необходимо использовать уникальный ключ для заказа

Предположение: вы можете рассчитать баланс закрытия и только начальный баланс, необходимый для расчета и нуждающийся в системном начальном начальном балансе

 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

Выход

1. Я думаю, что функция ЗАДЕРЖКИ необходима.

2. Этот ответ подходит в качестве подсказки, но не является полным ответом. Вам нужно предоставить рабочий запрос, чтобы он был полным ответом.

3. Приведенный выше запрос не подходит для моего требования. На самом деле у меня есть три таблицы под названием TBLProductOB, TBLProductPurchase, TBLProductUsage. объедините все таблицы, а затем получите вышеуказанный результат. Столбцы таблицы 1 : Ввод, товар, ob , Столбцы таблицы 2 : Ввод, товар, покупка, столбцы таблицы 3 : Ввод, продукты, использование

4. У продукта есть только один открывающий файл, записи о покупке в нескольких строках, записи об использовании в нескольких строках.

Метки: Как рассчитать баланс открытия и закрытия в транзакции