PS: I know this uses the 'no join predicate', but the query is based on the 3rd party apps workings - I ran a profile trace to capture what...

Thanks. I had read a bit about the 'inserted' pseudo table but interpreted it as being explicitly for inserts, not updates.

Thanks both.

Scott, what's the purpose of FILLFACTOR? Never used it before.

Thanks. Seems to be getting further this time.

Why does this happen? As in in refuses to install in the Native Client is 'still' installed.

Alan.B (9/12/2013)If I understand the original OP correctly (" If something has been credited, the billed item row should be excluded") this would not provide the right answer with this...

It's a 3 party app that only allows you to credit the bill amount.

So say you bill 4X £100 separately, when you raise a credit you have to cherry pick...

Thanks Sean.

They're not real column names. I think you may have solved it anyway. If not, i'll provide some DDL

This works, but for me it has hh:mm:ss.ms, i.e. 17:42:00.0000000

Can I get it to just be hh:mm?

Yes, 17.70 is 17.42.

For those that have 0 hours, ie 0.48 it errors out saying can't convert varchar to int.

EDIT: It's not just 0 hours that it does this with....

Not sure, it's been a...

Thanks guys.

Will have a play over the weekend.

Thanks guys.

That's given me the starting point I need.

Just in response to those who have mentioned time formats; this is hh:mm only.

Thanks both. Although I didn't exactly copy/paste, with the guidance above this is what works for me;

WHEREUser_ID = xxxx

AND MONTH(Date) = xxxx

AND (MONTH(GETDATE()) < 12 AND YEAR(Date) = YEAR(GETDATE())

OR MONTH(GETDATE())...

The thing is I only want it to include the next year if the current month is 12.

So if the current month is Jan-Nov only take dates from this year....