sql server - Accounts Ledger query in sql

Question

I need a query which maintain my accounts ledger

Here is my tables

CREATE TABLE [dbo].[Accounts](
    [Account_ID] [int] primary key,
    [Account_Name] [nvarchar](80),
    [Account_Type] [nvarchar](15))

CREATE TABLE [dbo].[Purchase_Invoice](
    [Purchase_ID] [int] primary key,
    [Purchase_Date] [datetime],
    [Account_ID] [int],
    [Purchase_SalesTax] [int],
    [Purchase_ExtraTax] [int],
    [Purchase_FutherTax] [int],
    [Purchase_Billno] [nvarchar](50),
    [Purchase_Amount] [decimal](9, 2))

CREATE TABLE [dbo].[Sale_Invoice](
    [Invoice_ID] [int] primary key,
    [Invoice_Date] [datetime],
    [Account_ID] [int],
    [Invoice_SalesTax] [int],
    [Invoice_ExtraTax] [int],
    [Invoice_FurtherTax] [int],
    [Invoice_Amount] [decimal](10, 2))

CREATE TABLE [dbo].[Transection](
    [Trans_ID] [int] primary key,
    [Trans_Date] [datetime],
    [Trans_Desc] [nvarchar](200))

CREATE TABLE [dbo].[DebitCredit](
    [Id] [int] IDENTITY(1,1),
    [Trans_ID] [int],
    [Account_ID] [int],
    [Amount] [decimal](9, 2),
    [Status] [varchar](5))

Insert into Accounts values(1,'Alicia', 'Customer')
Insert into Accounts values(2,'Mike', 'Supplier')
Insert into Accounts values(3,'AbcBank', 'Bank')

Insert into Purchase_Invoice values (1,'12/7/2014',2,0,0,0,'1254',5000)--Payment Debit in Supplier Mike Account

Insert into Sale_Invoice values (1,'12/7/2014',1,0,0,0,1500) --Payment Credit in Customer Alicia Account

Insert into Transection values (1,'12/7/2014','Payment Recevie From Customer')
insert into DebitCredit values (1,1,1500,'DB')--Payment Debit in Customer Alicia Account
insert into DebitCredit values (1,3,1500,'CR')--Payment Credit in Bank AbcBank Account

Insert into Transection values (2,'12/7/2014','Payment Send to Supplier')
insert into DebitCredit values (2,3,1500,'DB')--Payment Debit in Bank AbcBank Account
insert into DebitCredit values (2,2,1500,'CR')--Payment Credit in Supplier Mike Account

I purchased some item from supplier 'Mike' amount of invoice is 5000, Amount is credit on Mike then I sale item to Alicia for 1500, Amount Debit to Alicia.

In transaction table, I receive amount 1500 from Alicia, the amount 1500 now debit to Alicia Account and receive amount credit to abcbank.

Now In transaction table, I send amount 1500 to Mike, the amount 1500 now Credit to Mike Account and debit to abcbank.

Result may be like this

Account_Name | Debit | Credit | Balance
Mike-----------1500-----5000----3500
Alicia---------1500-----1500----0
Abcbank---------1500-----1500----0

Kindly help me

Thanks

Answer 1

Rework your table model to make more sense. I ngeneral you only need one Transaction table. YOu also should - and often are legally required to - keep a running number AND the total post transaction,. Helps a ton with performance.

You seem to avoid having account categories with having tons of tables - one for every account category. And you seem to think that a model where everything is calculated on demand will scale above a minimal example level.