If I change the where clause to:

WHERE

invoices.invoicedate BETWEEN '01-apr-2007' AND '31-mar-2008'

or invoices.invoicedate is null

It works 🙂

On a side note - if I remove the Where clause, I get all of the months with blank values for clientname and total.

Hi Thanks again for the reply - I now have the following:

SELECT

months.monthid,

months.month,

months.realmonthid,

isnull(SUM(invoices.cinvoicesubtotal),0) AS Total,

isnull(clients.clientname,'')

FROM ...

lol sorry I'm not too hot on SQL and as soon as I saw temp tables and functions my eyes glazed over...

I have:

SELECT

months.monthid,

months.month,

months.realmonthid,...

Hi - just had a brainwave - I'm nearly there I *think*

I have added a months table with monthid, realmonthid, monthname. April being the first month of the financial...

No probs 🙂 Have pretty much given up and gone back to manually creating the report in excel!

Thanks anyway!

Stephen 🙂

Hi many thanks for that reply - it's gone completely over my head though I'm afraid 🙁 I've never used functions.

When I was developing this app I...

Michael - sorry - my table schema there was a bit wrong - I'll have 3 tables:

invoices

invoiceid

paid

payments

paymentid

amount

invoicepayments

invoicepaymentid

paymentid

invoiceid

clientid

amount

The user can tick off invoices on the asp.net payment page or just put...

Hi Michael - thanks for the prompt reply 🙂

I have sat in accounts for a few days and can see how they operate. They post a payment and then...