FULL OUTER JOIN?

Is it possible to have more than one of the same item in a given invoice? And if so, does that count as 1 item or more than 1?

I would also recommend getting into the habit of indenting your code so that you can quickly tell at a glance where the beginning and end of each nested statement...

But isn't the MONEY type a float? If so, you will have to be careful when multiplying/dividing against it. Right Luis? I got bitten by that once many moons ago...

I would also suggest you Google CTEs (Common Table Expressions), table variables, and the EXISTS / NOT EXISTS operator. Those may be very helpful concepts as you do more tasks...

If what I posted isn't sufficient, then yes, you need a calendar table as the above guys have suggested.

You haven't really defined your data well enough for me to determine what's going on. For example, what is the point of Column A? Is that the week number? Or...

Maybe you should consider setting a specific transaction isolation level outside of your transaction instead of at the inner query level? There are multiple options which will, for example, prevent...

1) What's for lunch?
2) Who's buying?

You are correct, thank you! That was frustrating, really appreciate your help!

I don't need added precision, in fact I will be using a precision of 0, we don't need milliseconds. But it will be helpful to have the larger range of...

Got it, thanks for the explanation, guys!

@trvlbabie, he WAS patient and helpful. what an ungrateful and rude person you are. luckily people like you are very rare on these forums.

If you don't need DateLastTrans in your resultset, and if the other 3 columns are unique identifiers, then why are you even looking at the 4th column?

select DISTINCT contractno,...

Ah okay, yes, I should have figured out on my own that it was a recursive CTE. Thanks.