CREATE TABLE [dbo].[LedgerMaster]( [nID] [int] NULL, [LEDGERACCTID] [int] NULL, [LEDGERACCTNAME] [varchar](512) NULL, [ACCTTYPE] [varchar](512) NULL, [OPENINGDATE] [date] NULL, [OPENINGBALANCE] [int] NULL ) ON [PRIMARY] INSERT [dbo].[LedgerMaster] ([nID], [LEDGERACCTID], [LEDGERACCTNAME], [ACCTTYPE], [OPENINGDATE], [OPENINGBALANCE]) VALUES (1, 101, N'CASHACCT', N'DEBIT', CAST(N'2024-01-01' AS Date), 10000) INSERT [dbo].[LedgerMaster] ([nID], [LEDGERACCTID], [LEDGERACCTNAME], [ACCTTYPE], [OPENINGDATE], [OPENINGBALANCE]) VALUES (2, 102, N'BANKACCT157', N'DEBIT', CAST(N'2024-01-01' AS Date), 0) INSERT [dbo].[LedgerMaster] ([nID], [LEDGERACCTID], [LEDGERACCTNAME], [ACCTTYPE], [OPENINGDATE], [OPENINGBALANCE]) VALUES (3, 201, N'VENDOR1', N'CREDIT', CAST(N'2024-01-01' AS Date), 2000) INSERT [dbo].[LedgerMaster] ([nID], [LEDGERACCTID], [LEDGERACCTNAME], [ACCTTYPE], [OPENINGDATE], [OPENINGBALANCE]) VALUES (4, 202, N'VENDOR2', N'CREDIT', CAST(N'2024-01-01' AS Date), 0) INSERT [dbo].[LedgerMaster] ([nID], [LEDGERACCTID], [LEDGERACCTNAME], [ACCTTYPE], [OPENINGDATE], [OPENINGBALANCE]) VALUES (5, 411, N'SALES REVENUE', N'CREDIT', CAST(N'2024-01-01' AS Date), 0) INSERT [dbo].[LedgerMaster] ([nID], [LEDGERACCTID], [LEDGERACCTNAME], [ACCTTYPE], [OPENINGDATE], [OPENINGBALANCE]) VALUES (6, 511, N'PURCHASE', N'DEBIT', CAST(N'2024-01-01' AS Date), 1000) CREATE TABLE [dbo].[CreditMaster]( [nID] [bigint] IDENTITY(1,1) NOT NULL, [CRDATE] [datetime] NULL, [CRINVNO] [varchar](50) NULL, [CRACCTID] [bigint] NULL, [CRACCTNAME] [varchar](50) NULL, [ACCTTYPE] [varchar](50) NULL, [CRAMT] [decimal](18, 2) NULL, [VOUCHER] [varchar](50) NULL, [DESCRIPTION] [varchar](50) NULL, [FNYRID] [bigint] NULL, CONSTRAINT [PK_CreditMaster] PRIMARY KEY CLUSTERED ( [nID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] SET IDENTITY_INSERT [dbo].[CreditMaster] ON INSERT [dbo].[CreditMaster] ([nID], [CRDATE], [CRINVNO], [CRACCTID], [CRACCTNAME], [ACCTTYPE], [CRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (1, CAST(N'2024-01-01T00:00:00.000' AS DateTime), N'SINV-01', 411, N'SALES REVENUE', N'CREDIT', CAST(2000.00 AS Decimal(18, 2)), N'SALES VOUCHER', N'SALES', 1) INSERT [dbo].[CreditMaster] ([nID], [CRDATE], [CRINVNO], [CRACCTID], [CRACCTNAME], [ACCTTYPE], [CRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (2, CAST(N'2024-01-01T00:00:00.000' AS DateTime), N'SINV-02', 411, N'SALES REVENUE', N'CREDIT', CAST(15000.00 AS Decimal(18, 2)), N'SALES VOUCHER', N'SALES', 1) INSERT [dbo].[CreditMaster] ([nID], [CRDATE], [CRINVNO], [CRACCTID], [CRACCTNAME], [ACCTTYPE], [CRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (3, CAST(N'2024-01-01T00:00:00.000' AS DateTime), N'SINV-03', 411, N'SALES REVENUE', N'CREDIT', CAST(25000.00 AS Decimal(18, 2)), N'SALES VOUCHER', N'SALES', 1) INSERT [dbo].[CreditMaster] ([nID], [CRDATE], [CRINVNO], [CRACCTID], [CRACCTNAME], [ACCTTYPE], [CRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (4, CAST(N'2024-01-02T00:00:00.000' AS DateTime), N'SINV-04', 411, N'SALES REVENUE', N'CREDIT', CAST(11000.00 AS Decimal(18, 2)), N'SALES VOUCHER', N'SALES', 1) INSERT [dbo].[CreditMaster] ([nID], [CRDATE], [CRINVNO], [CRACCTID], [CRACCTNAME], [ACCTTYPE], [CRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (5, CAST(N'2024-01-02T00:00:00.000' AS DateTime), N'SINV-05', 411, N'SALES REVENUE', N'CREDIT', CAST(12000.00 AS Decimal(18, 2)), N'SALES VOUCHER', N'SALES', 1) INSERT [dbo].[CreditMaster] ([nID], [CRDATE], [CRINVNO], [CRACCTID], [CRACCTNAME], [ACCTTYPE], [CRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (6, CAST(N'2024-01-02T00:00:00.000' AS DateTime), N'PO-01', 201, N'VENDOR1', N'CREDIT', CAST(5000.00 AS Decimal(18, 2)), N'PURCHASE VOUCHER', N'PURCHASE', 1) INSERT [dbo].[CreditMaster] ([nID], [CRDATE], [CRINVNO], [CRACCTID], [CRACCTNAME], [ACCTTYPE], [CRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (7, CAST(N'2024-01-03T00:00:00.000' AS DateTime), N'PO-02', 201, N'VENDOR1', N'CREDIT', CAST(10000.00 AS Decimal(18, 2)), N'PURCHASE VOUCHER', N'PURCHASE', 1) INSERT [dbo].[CreditMaster] ([nID], [CRDATE], [CRINVNO], [CRACCTID], [CRACCTNAME], [ACCTTYPE], [CRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (8, CAST(N'2024-01-03T00:00:00.000' AS DateTime), N'SINV-06', 411, N'SALES REVENUE', N'CREDIT', CAST(20000.00 AS Decimal(18, 2)), N'SALES VOUCHER', N'SALES', 1) INSERT [dbo].[CreditMaster] ([nID], [CRDATE], [CRINVNO], [CRACCTID], [CRACCTNAME], [ACCTTYPE], [CRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (9, CAST(N'2024-01-03T00:00:00.000' AS DateTime), N'SINV-07', 411, N'SALES REVENUE', N'CREDIT', CAST(3000.00 AS Decimal(18, 2)), N'SALES VOUCHER', N'SALES', 1) INSERT [dbo].[CreditMaster] ([nID], [CRDATE], [CRINVNO], [CRACCTID], [CRACCTNAME], [ACCTTYPE], [CRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (10, CAST(N'2024-01-02T00:00:00.000' AS DateTime), N'PAY-01', 101, N'CASHACCT', N'CREDIT', CAST(2000.00 AS Decimal(18, 2)), N'PAYMENT VOUCHER', N'PAYMENT', 1) INSERT [dbo].[CreditMaster] ([nID], [CRDATE], [CRINVNO], [CRACCTID], [CRACCTNAME], [ACCTTYPE], [CRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (11, CAST(N'2024-01-03T00:00:00.000' AS DateTime), N'PAY-02', 101, N'CASHACCT', N'CREDIT', CAST(2500.00 AS Decimal(18, 2)), N'PAYMENT VOUCHER', N'PAYMENT', 1) INSERT [dbo].[CreditMaster] ([nID], [CRDATE], [CRINVNO], [CRACCTID], [CRACCTNAME], [ACCTTYPE], [CRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (12, CAST(N'2024-01-04T00:00:00.000' AS DateTime), N'PAY-03', 102, N'BANKACCT157', N'CREDIT', CAST(1500.00 AS Decimal(18, 2)), N'PAYMENT VOUCHER', N'PAYMENT', 1) SET IDENTITY_INSERT [dbo].[CreditMaster] off CREATE TABLE [dbo].[DebitMaster]( [nID] [bigint] IDENTITY(1,1) NOT NULL, [DRDATE] [datetime] NULL, [DRINVNO] [varchar](50) NULL, [DRACCTID] [bigint] NULL, [DRACCTNAME] [varchar](50) NULL, [ACCTTYPE] [varchar](50) NULL, [DRAMT] [decimal](18, 2) NULL, [VOUCHER] [varchar](50) NULL, [DESCRIPTION] [varchar](50) NULL, [FNYRID] [bigint] NULL, CONSTRAINT [PK_DebitMaster] PRIMARY KEY CLUSTERED ( [nID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] SET IDENTITY_INSERT [dbo].[DebitMaster] ON INSERT [dbo].[DebitMaster] ([nID], [DRDATE], [DRINVNO], [DRACCTID], [DRACCTNAME], [ACCTTYPE], [DRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (1, CAST(N'2024-01-01T00:00:00.000' AS DateTime), N'SINV-01', 101, N'CASHACCT', N'DEBIT', CAST(2000.00 AS Decimal(18, 2)), N'SALES VOUCHER', N'SALES', 1) INSERT [dbo].[DebitMaster] ([nID], [DRDATE], [DRINVNO], [DRACCTID], [DRACCTNAME], [ACCTTYPE], [DRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (2, CAST(N'2024-01-01T00:00:00.000' AS DateTime), N'SINV-02', 101, N'CASHACCT', N'DEBIT', CAST(15000.00 AS Decimal(18, 2)), N'SALES VOUCHER', N'SALES', 1) INSERT [dbo].[DebitMaster] ([nID], [DRDATE], [DRINVNO], [DRACCTID], [DRACCTNAME], [ACCTTYPE], [DRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (3, CAST(N'2024-01-01T00:00:00.000' AS DateTime), N'SINV-03', 102, N'BANKACCT157', N'DEBIT', CAST(25000.00 AS Decimal(18, 2)), N'SALES VOUCHER', N'SALES', 1) INSERT [dbo].[DebitMaster] ([nID], [DRDATE], [DRINVNO], [DRACCTID], [DRACCTNAME], [ACCTTYPE], [DRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (4, CAST(N'2024-01-02T00:00:00.000' AS DateTime), N'SINV-04', 101, N'CASHACCT', N'DEBIT', CAST(11000.00 AS Decimal(18, 2)), N'SALES VOUCHER', N'SALES', 1) INSERT [dbo].[DebitMaster] ([nID], [DRDATE], [DRINVNO], [DRACCTID], [DRACCTNAME], [ACCTTYPE], [DRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (5, CAST(N'2024-01-02T00:00:00.000' AS DateTime), N'SINV-05', 102, N'BANKACCT157', N'DEBIT', CAST(12000.00 AS Decimal(18, 2)), N'SALES VOUCHER', N'SALES', 1) INSERT [dbo].[DebitMaster] ([nID], [DRDATE], [DRINVNO], [DRACCTID], [DRACCTNAME], [ACCTTYPE], [DRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (6, CAST(N'2024-01-02T00:00:00.000' AS DateTime), N'PO-01', 511, N'PURCHASE', N'DEBIT', CAST(5000.00 AS Decimal(18, 2)), N'PURCHASE VOUCHER', N'PURCHASE', 1) INSERT [dbo].[DebitMaster] ([nID], [DRDATE], [DRINVNO], [DRACCTID], [DRACCTNAME], [ACCTTYPE], [DRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (7, CAST(N'2024-01-03T00:00:00.000' AS DateTime), N'PO-02', 511, N'PURCHASE', N'DEBIT', CAST(10000.00 AS Decimal(18, 2)), N'PURCHASE VOUCHER', N'PURCHASE', 1) INSERT [dbo].[DebitMaster] ([nID], [DRDATE], [DRINVNO], [DRACCTID], [DRACCTNAME], [ACCTTYPE], [DRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (8, CAST(N'2024-01-03T00:00:00.000' AS DateTime), N'SINV-06', 102, N'BANKACCT157', N'DEBIT', CAST(20000.00 AS Decimal(18, 2)), N'SALES VOUCHER', N'SALES', 1) INSERT [dbo].[DebitMaster] ([nID], [DRDATE], [DRINVNO], [DRACCTID], [DRACCTNAME], [ACCTTYPE], [DRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (9, CAST(N'2024-01-03T00:00:00.000' AS DateTime), N'SINV-07', 101, N'CASHACCT', N'DEBIT', CAST(3000.00 AS Decimal(18, 2)), N'SALES VOUCHER', N'SALES', 1) INSERT [dbo].[DebitMaster] ([nID], [DRDATE], [DRINVNO], [DRACCTID], [DRACCTNAME], [ACCTTYPE], [DRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (10, CAST(N'2024-01-02T00:00:00.000' AS DateTime), N'PAY-01', 201, N'VENDOR1', N'DEBIT', CAST(2000.00 AS Decimal(18, 2)), N'PAYMENT VOUCHER', N'PAYMENT', 1) INSERT [dbo].[DebitMaster] ([nID], [DRDATE], [DRINVNO], [DRACCTID], [DRACCTNAME], [ACCTTYPE], [DRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (11, CAST(N'2024-01-03T00:00:00.000' AS DateTime), N'PAY-02', 201, N'VENDOR1', N'DEBIT', CAST(2500.00 AS Decimal(18, 2)), N'PAYMENT VOUCHER', N'PAYMENT', 1) INSERT [dbo].[DebitMaster] ([nID], [DRDATE], [DRINVNO], [DRACCTID], [DRACCTNAME], [ACCTTYPE], [DRAMT], [VOUCHER], [DESCRIPTION], [FNYRID]) VALUES (12, CAST(N'2024-01-04T00:00:00.000' AS DateTime), N'PAY-03', 201, N'VENDOR1', N'DEBIT', CAST(1500.00 AS Decimal(18, 2)), N'PAYMENT VOUCHER', N'PAYMENT', 1) SET IDENTITY_INSERT [dbo].[DebitMaster] off
can anyone please help me how to find opening balance and closing balance and credit ,debit, runig balacne on date wise and acctwise.
for instance : if i select date 2024-01-01 and acct id 101 ..the output will be like below