Query for outstanding invoice amount report

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query for outstanding invoice amount report

Author

Topic

steve_c
Yak Posting Veteran

54 Posts

Posted - 2008-04-03 : 07:37:08

Hi all - struggling a bit with this one... I have made an accounts application and need to make a report called "Aged Debtors". It lists all of the clients with outstanding invoices and breaks them down into periods:

current (this month)
period 1 (last month)
period 2 (2 months ago)
period 4 (invoices raised 3 months ago or more)

My tables are as follows (simplified)

Clients
clientid int
clientname varchar(100)

invoices
invoiceid int
clientid int
invoicedate datetime
cinvoiceamount decimal(19,2)
paid int (1 = paid, 0 = unpaid)
paidamount decimal(19,2) (how much of this invoice has been paid)

I need the following columns in the report

clientname invoiceid current period1 period2 period3+
test client 1 £100 - - -
2 £50 - - -
3 - £100 - -
test client2

I can format all the report using a matrix in SSRS so just need the stored proc to get me going - any help greatly appreciated!
Thanks guys,
Stephen.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-04-03 : 07:41:38

So, how do you link client to invoice?

steve_c
Yak Posting Veteran

54 Posts

Posted - 2008-04-03 : 07:50:24

Sorry Rick - simplified it too much! Edited :)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-04-03 : 08:57:07

How do you know which period they paid an amount in, or does it not matter?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-04-03 : 09:17:26

You want something like this.

I am using calendar months as you didn't state whether it was months from a certain date of the month.

I also haven't tested it.


select 
	clientname, 
	invoiceid, 
	sum(case when invoicedate >= DATEADD(mm,DATEDIFF(mm,0,getdate()),0) and <= dateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1, 0)) then cinvoiceamount - paidamount else 0 end) as [Current],
	sum(case when invoicedate >= DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-1,getdate())),0) and <= dateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()), 0)) then cinvoiceamount - paidamount else 0 end) as Period1,
	sum(case when invoicedate >= DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-2,getdate())),0) and <= dateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-1,getdate())), 0)) then cinvoiceamount - paidamount else 0 end) as Period2,
	sum(case when invoicedate < DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-2,getdate())),0) then cinvoiceamount - paidamount else 0 end) as Period3
from invoice i
inner join clients c
	on i.clientid = c.clientid
group by
	clientname, 
	invoiceid

steve_c
Yak Posting Veteran

54 Posts

Posted - 2008-04-03 : 09:24:21

That's great thanks Rick - definitely gives me something to get my teeth into :) Am useless when it comes to date functions in SQL :(

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources