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
See Question&Answers more detail:
os