December 14, 2006 at 5:52 pm
Hello,
I need a query which identifies invoices that have not been fully paid, using the following table please:
ARTable Schema:
--------------------
ARID (PK, int), CustomerID (FK, int), InvoiceID (FK,int), TransactionID, ARDate (datetime),DebitAmount (numeric), CreditAmount (numeric)
ARDetailsTable Schema:
--------------------
ARDebit (int), ARCredit (in), Amount (numeric), Discount (numeric)
When an invoice is created one record is added to ARTable and ARTable.DebitAmount increases by the amount.
When an invoice is paid one record is added to ARTable and ARTable.CreditAmount increases by the amount, and also one record is added to ARDetailsTable and ARDetailsTable.Amount increases by the amount which was paid to the particular invoice.
For example:
when invoice id=15 is created for customeri9 for amount=890
ARDetailsTable :
1 9 15 1/1/97 890 0
Now the customer pays an amount of 1000, in which 700 is paid to invoice 9: transactionID=3
ARDetailsTable :
2 9 NULL 3 1/1/97 0 1000
ARDetailsTable :
1 2 700
Here 700 is paid to ARDebit=1 which is invoice=15, by the ARCredit=2 whcih have 700 of 1000 paid. And if we query it it says 300=amountdue
The query could be something like this:
SELECT ART.CustomerID,ART.ARDate, CI.InvoiceID,ART.TransactionID,
(ART.DebitAmount - ART.CreditAmount) AS OriginalAmount,
(ART.Debit - ( SUM(ARP.Amount,0)+SUM(ARP.Discount,0)),
ART.Debit Amount- ART.CreditAmount) as AmountDue FROM ARTable ART
LEFT OUTER JOIN ARDetailsTable ARP ON ART.ARID = ARP.ARDebit
HAVING(ART.DebitAmount > (SUM(ISNULL(ARP.Amount,0))+SUM(ISNULL(ARP.Discount,0))))
Here the query works for most of the cases, but does not work in some cases!
What is a better way please?
--
Mike
December 15, 2006 at 1:01 am
Sorry, I don't fully understand your schema, or how stuff relates to each other.
If you invoice someone for 1000, what gets added to what table?
If the customer then pays 500 or that invoice, what gets added to each table?
The customer then pays another 250. What gets added/changed?
Could you please post the table creations scripts, some sample data (as insert statements) and expected output? That makes it much easier for us to help.
I think it's a fairly easy query, but need to be sure I understand first.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2006 at 8:31 am
If the invoice is for 890 looks like only 190 should be due on this invoice.
(With 300 unapplied credit unless it was applied in its entirety to other invoices). I think your looking for a query that goes something like this: (untested)
select INVOICES.CustomerID, INVOICES.ARDate,INVOICES.InvoiceID,INVOICES.TransactionID,INVOICES.DebitAmount,
PAYMENTS.TotPayAmount from
(select CustomerID, ARDate,InvoiceID,TransactionID,DebitAmount from ARTable where invid is not null) as INVOICES
LEFT JOIN
(select ARDebit, sum(isnull(amount,0)+isnull(discount,0)) as TotPayAmount from ARDetailsTable group by ARDebit) as PAYMENTS
ON INVOICES.TranssactionID=PAYMENTS.ArDebit
WHERE INVOICES.DebitAmount>PAYMENTS.TotPayAmount
December 15, 2006 at 8:48 am
modified to fix two typos (fieldnames), and show the amount due column:
SELECT
INVOICES.CustomerID,
INVOICES.ARDate,
INVOICES.InvoiceID,
INVOICES.TransactionID,
INVOICES.DebitAmount,
PAYMENTS
.TotPayAmount,INVOICES.DebitAmount-PAYMENTS.TotPayAmount as AmountDue
FROM
(
select CustomerID, ARDate,InvoiceID,TransactionID,DebitAmountfrom ARTable where InvoiceID is not null) AS INVOICES
LEFT JOIN
(
select ARDebit, sum(isnull(amount,0)+isnull(discount,0)) as TotPayAmountfrom ARDetailsTable group by ARDebit) AS PAYMENTS
ON
INVOICES.TransactionID=PAYMENTS.ArDebitWHERE
INVOICES.DebitAmount>PAYMENTS.TotPayAmount
December 15, 2006 at 9:49 am
This query did not work. Here are the Schema and a sample data:
CREATE
TABLE [dbo].[ARTable](
[ARID] [int]
IDENTITY(1,1) NOT NULL,
[CustomerID] [int]
NULL,
[InvoiceID] [int]
NULL,
[TransactionID] [int]
NULL,
[ARDate] [datetime]
NULL,
[DebitAmount] [money]
NULL,
[CreditAmount] [money]
NULL,
CONSTRAINT [PK_ARTable] PRIMARY KEY CLUSTERED
(
[ARID]
ASC
)
WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY]
CREATE
TABLE [dbo].[ARDetailsTable](
[ARDebit] [int]
NULL,
[ARCredit] [int]
NULL,
[Amount] [money]
NULL,
[Discount] [money]
NULL
)
ON [PRIMARY]
DATA for ARTable:
SELECT
[ARID]
,[CustomerID]
,[InvoiceID]
,[TransactionID]
,[ARDate]
,[DebitAmount]
,[CreditAmount]
FROM [test].[dbo].[ARTable]
| 1 | 1 | 1 | NULL | 2/3/2006 12:00:00 AM | 600.0000 | NULL |
| 3 | 1 | NULL | 1 | 2/3/2006 12:00:00 AM | NULL | 100.0000 |
DATA for ARDetails table:
SELECT
[ARDebit]
,[ARCredit]
,[Amount]
,[Discount]
FROM [test].[dbo].[ARDetailsTable]
| 1 | 2 | 40.0000 | NULL |
---------------
The example data shows that we created an invoice of 600$ and then paid 40 of 100 with transaction 1.
December 15, 2006 at 9:51 am
This query did not work. Here are the Schema and a sample data:
CREATE
TABLE [dbo].[ARTable](
[ARID] [int]
IDENTITY(1,1) NOT NULL,
[CustomerID] [int]
NULL,
[InvoiceID] [int]
NULL,
[TransactionID] [int]
NULL,
[ARDate] [datetime]
NULL,
[DebitAmount] [money]
NULL,
[CreditAmount] [money]
NULL,
CONSTRAINT [PK_ARTable] PRIMARY KEY CLUSTERED
(
[ARID]
ASC
)
WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY]
CREATE
TABLE [dbo].[ARDetailsTable](
[ARDebit] [int]
NULL,
[ARCredit] [int]
NULL,
[Amount] [money]
NULL,
[Discount] [money]
NULL
)
ON [PRIMARY]
DATA for ARTable:
SELECT
[ARID]
,[CustomerID]
,[InvoiceID]
,[TransactionID]
,[ARDate]
,[DebitAmount]
,[CreditAmount]
FROM [test].[dbo].[ARTable]
| 1 | 1 | 1 | NULL | 2/3/2006 12:00:00 AM | 600.0000 | NULL |
| 3 | 1 | NULL | 1 | 2/3/2006 12:00:00 AM | NULL | 100.0000 |
DATA for ARDetails table:
SELECT
[ARDebit]
,[ARCredit]
,[Amount]
,[Discount]
FROM [test].[dbo].[ARDetailsTable]
| 1 | 2 | 40.0000 | NULL |
---------------
The example data shows that we created an invoice of 600$ and then paid 40 of 100 with transaction 1.
December 15, 2006 at 12:43 pm
If you invoice some one, one record is added to ARTables and the debit amount increases. The you pay this invoice and one record is added tp ARdetailsTable and one record to ARTables. ARTable.ARID = ARDetailsTable.ARDebit. So when we can know that what payment pays what invoices.
December 19, 2006 at 7:52 am
From the sample data I couldn't figure out why an invoice would have a null transactionID.
But anyways, I ran your script schemas, populated with your data, and
adjusted and ran the query to reference the invoiceID instead:
SELECT
INVOICES.CustomerID, INVOICES.ARDate,INVOICES.InvoiceID,INVOICES.TransactionID,
INVOICES.DebitAmount,PAYMENTS.TotPayAmount,
INVOICES.DebitAmount-PAYMENTS.TotPayAmount as AmountDue
FROM
(select CustomerID, ARDate,InvoiceID,TransactionID,DebitAmount from ARTable where InvoiceID is not null)
AS
INVOICESLEFT
JOIN(
select ARDebit, sum(isnull(amount,0)+isnull(discount,0)) as TotPayAmountfrom
ARDetailsTable group by ARDebit)AS
PAYMENTSON
INVOICES.InvoiceID=PAYMENTS.ArDebitWHERE
INVOICES.DebitAmount>IsNull(PAYMENTS.TotPayAmount,0)
This gave me the following answer:
CustomerID,ARDate,InvoiceID,TransactionID,DebitAmount,TotPayAmount,AmountDue
1 2006-02-03 00:00:00.000 1 NULL 600.00 40.00 560.00
BTW, I understood the one line in the Details table to mean:
Apply $40 of Transaction 2 to InvoiceID 1 (TransactionID 1?)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply