User-1296120440 posted
Hi All,
I have two tables Pending_Invoice and VAT_Account
Pending_Invoice : Holds Invoice with [VAT TAX] and [WITHOUT VAT TAX]
VAT_ACCOUNT : Holds only VAT Transactions
Below is sample table Date with transaction having VAT Tax [MS210/0085/VAT] and General Payment [MS210/0085].
If a transaction is having Vat tax, as per our requirement we have to show only transaction for VAT
[In this case we have to show only First transaction- : MS210/0085/VAT ] and when a transaction is having only General without VAT tax we have to show General transaction.
| InvoiceNo |
Basic Amount |
VAT Amount |
Total Amount |
| MS210/0085/VAT |
540 |
30.38 |
637.88 |
| MS210/0085 |
540 |
0 |
540 |
Querying Used
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- First Query Retrieves Transaction
With VAT
(Select P.Inv_Making_Date as Date,VA.InvoiceNo,(select isnull(BasicValue,0) from pending_invoice where InvoiceNo=left(p.InvoiceNo, len(p.InvoiceNo)-4)) as [Basic Amount],isnull
(Debit,0) as [VAT Amount],(SELECT SUM(ISNULL(BasicValue,0) + ISNULL(SalesTax,0) + ISNULL(VAT,0) + ISNULL(ExciseDuty,0)) FROM Pending_Invoice where InvoiceNo LIKE left
(p.InvoiceNo, len(p.InvoiceNo)-4)+'%')as [Total Amount] from VAT_Account VA join pending_invoice p on p.invoiceno=VA.invoiceno where VatReg_No!='' and VA.Status in
('1','2','3','4') and convert(datetime,Inv_Making_Date) between CONVERT(datetime,'04-04-2016') and CONVERT(datetime,'04-04-2016'))
UNION ALL
Second For General
(select P.Inv_Making_Date as Date,P.InvoiceNo,(select isnull(BasicValue,0) from pending_invoice where InvoiceNo=P.InvoiceNo) as [Basic Amount],'' as [VAT Amount],(SELECT SUM
(ISNULL(BasicValue,0) + ISNULL(SalesTax,0) + ISNULL(VAT,0) + ISNULL(ExciseDuty,0)) FROM Pending_Invoice where InvoiceNo = p.InvoiceNo)as [Total Amount] from pending_invoice
P where p.paymenttype='Supplier' and convert(datetime,Inv_Making_Date) between CONVERT(datetime,'04-04-2016') and CONVERT(datetime,'04-04-2016')) -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
We need to write a Query to Exclude General Payment when VAT Transaction is available for a
Invoice.
Could you please suggest .. ?
Regards,
Vasu.
