Sorry for the messy query, I'm very new to writing these complex queries. I'll try and make it easier to read by using WITH clauses. However, just to clarify, the WITH clauses only increase readability and not performance in any way, right?
On Thu, May 23, 2013 at 4:22 PM, james <james@xxxxxxxxxxxxxxxxxxxxxx> wrote:
Perhaps you could use triggers to help somewhat? At least for the lifetime part.On 23/05/2013 22:57, Jonathan Morra wrote:
I'm not sure I understand your proposed solution. There is also the case to consider where the same patient can be assigned the same device multiple times. In this case, the value may be reset at each assignment (hence the line value - issued_value AS value from the original query).
For a given assignment of a device to a patient, only the last value is useful, so you can maintain that easily enough (a bit like a materialised view but before 9.3 I guess).
But, that might fix 'lifetime' but not some arbitrary windowed view. I can see why an 'as at' end time is useful, but not why a start time is so useful: if a device has readings before the window but not in the window, is that 'no reading' or should the last reading prior to the window apply?
It also seems to me that the solution you have is hard to reason about. Its like a Haskell program done in one big inline fold rather than a bunch of 'where' clauses, and I find these cause significant brain overload.
Perhaps you could break it out into identifiable chunks that work out (both for lifetime if not using triggers, and for your date range otherwise) the readings that are not superceded (ie the last in the date bounds for a device assignment), and then work with those. Consider the CTE 'WITH queries' for doing this?
It seems to me that if you can do this, then the problem might be easier to express.
Failing that, I'd be looking at using temporary tables, and forcing a series of reduce steps using them, but then I'm a nasty old Sybase hacker at heart. ;-)
