Chaps,

Just to let you know, all working properly when integrated into my real database, so thanks for your efforts and you can all feel good about yourselves now !...

OK thanks chaps, I'll just have a play and try to integrate. Really appreciate your time here.

Greg

Yes, thanks. For this test data

SELECT 'Small House', 'A', 2010/02/01', 10, 100 UNION ALL

SELECT 'Big House', 'B','2010/06/01', 2, 200 UNION ALL

SELECT 'Big House', 'A','2010/06/01', 20, 2000 UNION ALL...

OK, I see what's happening here a little more clearly, at least with the tally table.

However, would you believe it (;-)) I am still a bit stuck, as I...

If I wanted to sum up more than 1 metric in this way, how could this be done.

I've tried to amend the code, but I'm not sure I understand...

Right, thanks for this, have submitted the code to 'extreme' stress testing and it's working well. I'll now integrate it and let you know. Thanks very much for you help,...

Ah, thanks. The numbers don't run sequentially, I should have randomised my test data to make it more realistic. Sorry.

Regards, Greg

Right,

I was a bit confused re: the test data. I guess it's an easy way to insert it, as I didn't provide the insert values.

On to the next point, if...

Confirming this:

Month 4 would have metric2 as 9 - cos it would be 4+3+2.

Month 5 would have metric2 as 12 (5+4+3).

The problem would be that (and this is my fault...

gosh, i hope you feel better now, having got that off you chest,

Greg

Thanks for your efforts, that is very useful and much appreciated.

Regards, Greg

Yep thanks - I've started with a list of all locations and dates, then left joined the other metrics in !

Thanks Phil,

I appreciate the help (on a Monday AM in particular) and shall beetle off to give this a try.

Regards, Greg.

Thanks Phil,

Supplementary Question:

If all of these rows were in 1 table - could you think of a solution ? Maybe something like selecting from itself ?

Regards, Greg.

Not really, it wouldn't be a problem when plotting as a visualisation.