June 30, 2011 at 2:31 am
Hi everyone,
I have the SQL statement below which is working fine.
It retreives all (invoice) records from the previous 12 months where payment took place after the due date (InvoiceDate + 30 days) or where there was no payment at all.
SELECT TOP (100) PERCENT idCustomer, NaamCustomer, InvoiceNumber, InvoiceAmount, InvoiceDate, DatePaid, idInvoicePerCustomer, OrderNumber,
MAX(DATEDIFF(d, DATEADD(d, 30, InvoiceDate), CASE WHEN DatePaid IS NULL THEN getdate() ELSE DatePaid END))
AS DaysOverdue
FROM dbo.vwPaymentsOverdue
GROUP BY idCustomer, NaamCustomer, InvoiceNumber, InvoiceAmount, InvoiceDate, DatePaid, idInvoicePerCustomer, OrderNumber
HAVING (DatePaid > DATEADD(d, 30, InvoiceDate)) OR
(DatePaid IS NULL) AND (DATEADD(d, 30, InvoiceDate) < GETDATE()) AND (InvoiceDate >= DATEADD(m, - 12, GETDATE()))
ORDER BY MAX(DATEDIFF(d, DATEADD(d, 30, InvoiceDate), CASE WHEN DatePaid IS NULL THEN getdate() ELSE DatePaid END)) DESC
What I actually need is a query that shows me the Customers where 50% (or more) of all payments were overdue in the past 12 months.
I have searched the internet without success.
Any suggestions?
Thanks in advance,
Berend Storm
June 30, 2011 at 7:54 am
Hi Stewart,
I get the part of the Count's.
But how can I combine my query and your suggestion into one resulting query which give me the DISTINCT names of the customers that are overdue? Can you give an example or a hint as to where to look?
I'm still a newby in the area of 'complex' queries.
The 'vwPaymentsOverdue' query is made out of 3 tables"
Orders, InvoicesPerCustomer en Customers
This View uses the following statement:
SELECT dbo.Customers.idCustomer, dbo.Customers.NameCustomer, dbo.InvoicesPerCustomer.InvoiceNumber, dbo.InvoicesPerCustomer.InvoiceAmount,
dbo.InvoicesPerCustomer.DateInvoice, dbo.InvoicesPerCustomer.DatePaid, dbo.InvoicesPerCustomer.idInvoicePerCustomer,
dbo.Orders.OrderNumber
FROM dbo.InvoicesPerCustomer INNER JOIN
dbo.Orders ON dbo.InvoicesPerCustomer.idOrder = dbo.Orders.idOrder INNER JOIN
dbo.Customers ON dbo.InvoicesPerCustomer.idCustomer = dbo.Customers.idCustomer
I hope this helps.
Berend
June 30, 2011 at 9:15 am
Thank you Stewart,
This looks promissing.
I'll play around with it a bit and let you know the outcome.
B.t.w.: What does CTE stand for?>
I'm always eager to learn.
Berend
June 30, 2011 at 10:41 am
Stewart,
I needed to apply a few minor adaptations (add a comma, reverse the percentage calculations, naming) but this works great now!
Thank you very much for supplying the sample query.
I am sure that I'll need some more exercise before I can do something like this on my own but at least I've got a great example now.
Hopefully other newbies can learn from this example as well.
Thanks again.
Berend
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply