Selecting the required contract when using a given MeterID

[ghost]

When joining tables from Meter to Meter_Contract and then on to Contract we need to make sure we are getting the Meter_Contract and Contract information we want. There can be multiple Contracts for a Meter over time. Therefore, for each Meter there can be many Meter_Contracts with a Contract attached. This will mean that looking up a Meter will usually (?) give a set of rows - one row for each contract on the meter.

The easy solution is to make sure that the set of rows is reduced to one with a where clause - using contract_status or the contract_dateStart etc. [selecting the contract_dateStart field using "MAX(contract_dateStart)" is effective is just getting the latest contract.]

When Usage is also linked it can be important to make sure the appropriate contract is used for a given set of usage figures (they may be from a different supplier) - here the where clause will need to have the "usage_dateReading between the contract_dateStart and dateEnd". (Although there is a problem here - the last reading date is often just after the end of contract!)

[ghost]

Shaun - The Meter Search is doing the right thing I think - Paul wants the latest contract information in the listing. Can you check function getMeterDetails($id) please - I assume this is used to get meter information for the Meter Display?. There does not seem to be anything to choose the contract.
Paul - which contract do you want used on the Meter Display (given that we now have a Contracts Tab underneath)?

[ghost]

I think I now have a good solution to this/ The default contract to use with a meter is the latest contract (start_date) that started before or starts today.

i.e. SELECT *, MAX(contract_dateStart) AS A1 FROM meter ......... WHERE ........ AND (contract_dateStart <= DATE(NOW()) OR contract_status IS NULL)

[The contract_status IS NULL is to allow for when there no contract for the meter yet.]

[ghost]

We now have a test case in the production system. MeterID = 344. This is incorrectly showing the old contract in the display of details. We need to get code something similar to the following into the select - it uses a subselect on the meter_contract left join to get the correct meter_contract and contract data. (I can do the SQL and it works - con't see how to create the select in Zend) public function getMeterDetails($id) . .

LEFT JOIN (
    SELECT *, MAX(contract_dateStart) AS aaa 
    FROM meter_contract 
    LEFT JOIN contract ON (
        contract_idContract = meterContract_idContract 
        AND (contract_dateStart <= DATE(NOW())
        OR contract_status IS NULL
    )
)
WHERE  meterContract_idMeter = meter_idMeter)

. . --Watch the () and the max(contract_dateStart) (can't get the display as I want - click edit to see the indentation). This gets the meter_contract and contract data as a table row with the latest contract that started before today. We may need to put this into a v3.3

[shaunfreeman]

It was easier to move this function to the Row Class as the SQL was starting to become unmanagable. I have used your SQL and added it to the meter row class and it is much easier now to manage from here.