Select 'DV-'+Convert(Varchar,VoucherNo) As VoucherNo, DebitVouchersBody.AccountNo, AccountName, VoucherDate AS Date,     DebitVouchersBody.Narration, Debit, 0.00 As Credit
From DebitVouchersBody
Join ChartOfAccounts ON DebitVouchersBody.AccountNo=ChartOfAccounts.AccountNo
Where DebitVouchersBody.AccountNo = '" & txtAccountId.Text & "'
Union All

Select 'CV-'+Convert(Varchar,VoucherNo) As VoucherNo, CreditVouchersBody.AccountNo, AccountName, VoucherDate AS Date, CreditVouchersBody.Narration, 0.00 As Debit, Credit
From CreditVouchersBody
Join ChartOfAccounts ON CreditVouchersBody.AccountNo=ChartOfAccounts.AccountNo
Where CreditVouchersBody.AccountNo = '" & txtAccountId.Text & "'
Union All

Select 'JV-'+Convert(Varchar,VoucherNo) As VoucherNo, JournalVouchersBody.AccountNo, AccountName, VoucherDate AS Date, JournalVouchersBody.Narration, Debit, Credit
From JournalVouchersBody
Join ChartOfAccounts ON JournalVouchersBody.AccountNo=ChartOfAccounts.AccountNo
Where JournalVouchersBody.AccountNo = '" & txtAccountId.Text & "'
Union All

Select 'CD-'+Convert(Varchar,VoucherNo) As VoucherNo, BankDeposit.BankAccountNo, AccountName, VoucherDate AS Date, BankDeposit.Narration, Amount As Debit, 0.00 As Credit
From BankDeposit Join ChartOfAccounts ON BankDeposit.BankAccountNo=ChartOfAccounts.AccountNo
Where BankDeposit.BankAccountNo = '" & txtAccountId.Text & "' AND IsCheque='False'
Union All

Select 'BD-'+Convert(Varchar,VoucherNo) As VoucherNo, BankDeposit.BankAccountNo, AccountName, VoucherDate AS Date, BankDeposit.Narration, Amount As Debit, 0.00 As Credit
From BankDeposit
Join ChartOfAccounts ON BankDeposit.BankAccountNo=ChartOfAccounts.AccountNo
Where BankDeposit.BankAccountNo = '" & txtAccountId.Text & "' AND IsCheque='True'
Union All

Select 'BD-'+Convert(Varchar,VoucherNo) As VoucherNo, BankDeposit.ReceivedFromAccountNo, AccountName, VoucherDate AS Date, BankDeposit.Narration, 0.00 As Debit, Amount As Credit
From BankDeposit Join ChartOfAccounts ON BankDeposit.ReceivedFromAccountNo=ChartOfAccounts.AccountNo
Where BankDeposit.ReceivedFromAccountNo = '" & txtAccountId.Text & "' AND IsCheque='True'
Union All

Select 'CT-'+Convert(Varchar,VoucherNo) As VoucherNo, BankCheques.BankAccountNo, AccountName, VoucherDate AS Date, BankCheques.Narration, Amount As Debit, 0.00 As Credit
From BankCheques Join ChartOfAccounts ON BankCheques.BankAccountNo=ChartOfAccounts.AccountNo
Where BankCheques.BankAccountNo = '" & txtAccountId.Text & "' AND IsOnline='False'
Union All

Select 'OT-'+Convert(Varchar,VoucherNo) As VoucherNo, BankCheques.AccountNoPayTo, AccountName, VoucherDate AS Date, BankCheques.Narration, 0.00 As Debit, Amount As Credit
From BankCheques
Join ChartOfAccounts ON BankCheques.AccountNoPayTo=ChartOfAccounts.AccountNo
Where BankCheques.AccountNoPayTo = '" & txtAccountId.Text & "' AND IsOnline='True'
Union All

Select 'SI-'+Convert(Varchar,Sales.SaleID) As VoucherNo, CustomerId As AccountNo, AccountName, Date, Sales.Narration, NetValue As Debit, PaidAmount As Credit
From Sales
Join ChartOfAccounts ON Sales.CustomerId=ChartOfAccounts.AccountNo
Where CustomerId= '" & txtAccountId.Text & "'
Union All

Select 'SR-'+Convert(Varchar,SaleReturns.SaleReturnID) As VoucherNo, CustomerId As AccountNo, AccountName, Date, SaleReturns.Narration, PaidAmount As Debit, NetValue As Credit
From SaleReturns
Join ChartOfAccounts ON SaleReturns.CustomerId=ChartOfAccounts.AccountNo
Where CustomerId= '" & txtAccountId.Text & "'
Union All

Select 'PI-'+Convert(Varchar,Purchases.PurchaseId) AS VoucherNo, VendorId As AccountNo, AccountName, Date, Purchases.Narration, AmountPaid AS Debit, NetValue AS Credit
From Purchases
Join ChartOfAccounts ON Purchases.VendorId=ChartOfAccounts.AccountNo
Where VendorId= '" & txtAccountId.Text & "'
Union All

Select 'PR-'+Convert(Varchar,PurReturns.PurReturnId) AS VoucherNo, VendorId As AccountNo, AccountName, Date, PurReturns.Narration, NetValue AS Debit, AmountPaid AS Credit
From PurReturns
Join ChartOfAccounts ON PurReturns.VendorId=ChartOfAccounts.AccountNo
Where VendorId= '" & txtAccountId.Text & "'
Union All

Select 'CC-'+Convert(Varchar,CExpiryClaims.ClaimID) As VoucherNo, CustomerId As AccountNo, AccountName, Date, NULL As Narration, 0.00 As Debit, TTLValue As Credit
From CExpiryClaims
Join ChartOfAccounts ON CExpiryClaims.CustomerId=ChartOfAccounts.AccountNo
Where CustomerId= '" & txtAccountId.Text & "'
Union All

Select 'CR-'+Convert(Varchar,CExpiryReplies.ReplyID) As VoucherNo, CustomerId As AccountNo, AccountName, CexpiryReplies.Date, NULL As Narration, (IsNull(CexpiryReplies.TTLValue,0))+(IsNull(RepliedAmount,0)) As Debit, 0.00 As Credit
From CExpiryReplies
Join CExpiryClaims On CExpiryClaims.ClaimID=CExpiryReplies.ReplyID
Join ChartOfAccounts ON CExpiryClaims.CustomerId=ChartOfAccounts.AccountNo
Where CustomerId= '" & txtAccountId.Text & "'
Union All

Select 'VC-'+Convert(Varchar,ExpiryClaims.ClaimID) As VoucherNo, VendorId As AccountNo, AccountName, Date, NULL As Narration, TTLValue As Debit, 0.00 As Credit
From ExpiryClaims
Join ChartOfAccounts ON ExpiryClaims.VendorId=ChartOfAccounts.AccountNo
Where VendorId= '" & txtAccountId.Text & "'
Union All

Select 'CR-'+Convert(Varchar,ExpiryReplies.ReplyID) As VoucherNo, VendorId As AccountNo, AccountName, ExpiryReplies.Date, NULL As Narration, 0.00 As Debit, (IsNull(expiryReplies.TTLValue,0))+(IsNull(RepliedAmount,0)) As Credit
From ExpiryReplies
Join ExpiryClaims On ExpiryClaims.ClaimID=ExpiryReplies.ReplyID
Join ChartOfAccounts ON ExpiryClaims.VendorId=ChartOfAccounts.AccountNo
Where VendorId= '" & txtAccountId.Text & "'
Union All

Select 'RI-'+Convert(Varchar,RecoveryBodyInvoice.RecoveryID) As VoucherNo, CustomerId As AccountNo, AccountName, RecoveryDate, REcoveryBodyInvoice.Narration, 0.00 As Debit, Amount As Credit
From RecoveryBodyInvoice
Join Recovery ON RecoveryBodyInvoice.RecoveryID = Recovery.RecoveryID
join sales ON RecoveryBodyInvoice.SaleID=sales.SaleId
Join ChartOfAccounts ON Sales.CustomerId=ChartOfAccounts.AccountNo
Where CustomerId= '" & txtAccountId.Text & "'
Union All

Select 'RR-'+Convert(Varchar,RecoveryBodyWOInvoice.RecoveryID) As VoucherNo, CustomerId As AccountNo, AccountName, RecoveryDate, REcoveryBodyWOInvoice.Narration, 0.00 As Debit, Amount As Credit
From RecoveryBodyWOInvoice
Join Recovery ON RecoveryBodyWOInvoice.RecoveryID = Recovery.RecoveryID
Join ChartOfAccounts ON CustomerId=ChartOfAccounts.AccountNo
Where CustomerId= '" & txtAccountId.Text & "'
Union All

Select Null AS VoucherNo, Null As AccountNo, NULL As Name,Null As Date,'Opening Balance' AS Narration, OpeningDebit AS Debit, OpeningCredit As Crdit
From ChartOfAccounts
Where AccountNo = '" & txtAccountId.Text & "'
Order By VoucherDate, VoucherNo