Max Forecast Date per Settlement Date

[twoplustwo]

Hi guys,

Potentially an easy one!

I have a select query as follows:

SettDate ForcastDate

02/07/08 31/07/08 + Timestamp
02/07/08 01/08/08 + Timestamp
02/07/08 01/08/08 + Timestamp
02/07/08 02/07/08 + Timestamp

As we can see from the sample, there are two records for the 1st August. I need to select the last forecast date rather than return two. The max function is not quite working correctly.

Thanks for any help offered.

 

[Brianwarnock]

Since the MAX function always works correctly but people don't always use it correctly perhaps you could clarify by posting your SQL and illustrating the results.

Brian

 

[twoplustwo]

Haha, touche. That is ofc what I meant.

SELECT PUBLIC_RTU_RETAIL_UNIT.RETAIL_UNIQUE_ID, IIf([Settlement_date]=[forecast_date],"WD",DateDiff("d",[settlement_date],[forecast_date])) AS LmDay, PUBLIC_FSH_SETTLEMENT_HISTORY.SETTLEMENT_DATE AS SettDate, Format([FORECAST_DATE],"dd/mm/yyyy") AS ForDate, PUBLIC_FSH_SETTLEMENT_HISTORY.CONSUMPTION01
FROM PUBLIC_RTU_RETAIL_UNIT INNER JOIN PUBLIC_FSH_SETTLEMENT_HISTORY ON PUBLIC_RTU_RETAIL_UNIT.RTU_ID = PUBLIC_FSH_SETTLEMENT_HISTORY.RTU_RETAIL_UNIT
WHERE (((PUBLIC_RTU_RETAIL_UNIT.RETAIL_UNIQUE_ID)="~00007869") AND ((PUBLIC_FSH_SETTLEMENT_HISTORY.SETTLEMENT_DATE)=#8/2/2007#))
ORDER BY PUBLIC_FSH_SETTLEMENT_HISTORY.SETTLEMENT_DATE;

 

[twoplustwo]

Sample data

 

[Brianwarnock]

SELECT PUBLIC_RTU_RETAIL_UNIT.RETAIL_UNIQUE_ID, IIf([Settlement_date]=[forecast_date],"WD",DateDiff("d",[settlement_date],[forecast_date])) AS LmDay, PUBLIC_FSH_SETTLEMENT_HISTORY.SETTLEMENT_DATE AS SettDate, Format([FORECAST_DATE],"dd/mm/yyyy") AS ForDate, PUBLIC_FSH_SETTLEMENT_HISTORY.CONSUMPTION01
FROM PUBLIC_RTU_RETAIL_UNIT INNER JOIN PUBLIC_FSH_SETTLEMENT_HISTORY ON PUBLIC_RTU_RETAIL_UNIT.RTU_ID = PUBLIC_FSH_SETTLEMENT_HISTORY.RTU_RETAIL_UNIT
WHERE (((PUBLIC_RTU_RETAIL_UNIT.RETAIL_UNIQUE_ID)="~00007869") AND ((PUBLIC_FSH_SETTLEMENT_HISTORY.SETTLEMENT_DATE)=#8/2/2007#))
ORDER BY PUBLIC_FSH_SETTLEMENT_HISTORY.SETTLEMENT_DATE;

Where is the MAX, and what has the sample.xls got to do with this?

 

[twoplustwo]

That's what it returns.

I removed the max as it only returns one date.

 

[Brianwarnock]

Max is an aggregate function and is used in a Totals query, I'm going to make some assumptions since I have little to go on but I think that you need to run a first query to get your Max dates, something like , with syntax corrected , then join this in your main query.

Select RTU_RETAIL_UNIT, Settlementdate, Max(forecastdate) as Maxofforcastdate
From PUBLIC_FSH_SETTLEMENT_HISTORY
Group by RTU_RETAIL_UNIT, Settlementdate


Max is a field operator and gives the max value of a field in the selected group, your group was probably the whole file.

 

[twoplustwo]

Thanks for the reply Brian.

The query still only returns the max date for the range of settlement days. This is only a problem when I have two forecasts for a given day i.e.

02/07/08 31/07/08 + Timestamp
02/07/08 01/08/08 + Timestamp
02/07/08 01/08/08 + Timestamp
02/07/08 02/07/08 + Timestamp

So on the 1st, two forecasts were made. I need the query to return:

02/07/08 31/07/08 + Timestamp
02/07/08 01/08/08 + Timestamp - where this is the last forecast
02/07/08 02/07/08 + Timestamp

HTH.

Thanks again.

 

[Brianwarnock]

Have you run an aggregate query against that table if so post the SQL, it cannot return both values if you have grouped correctly.

Brian

 

[twoplustwo]

This returns the first set of data as per the example.

SELECT PUBLIC_RTU_RETAIL_UNIT.RETAIL_UNIQUE_ID, IIf([Settlement_date]=[forecast_date],"WD",DateDiff("d",[settlement_date],[forecast_date])) AS LmDay, PUBLIC_FSH_SETTLEMENT_HISTORY.SETTLEMENT_DATE AS SettDate, PUBLIC_FSH_SETTLEMENT_HISTORY.CONSUMPTION01, PUBLIC_FSH_SETTLEMENT_HISTORY.CONSUMPTION02
FROM PUBLIC_RTU_RETAIL_UNIT INNER JOIN PUBLIC_FSH_SETTLEMENT_HISTORY ON PUBLIC_RTU_RETAIL_UNIT.RTU_ID = PUBLIC_FSH_SETTLEMENT_HISTORY.RTU_RETAIL_UNIT
WHERE (((PUBLIC_RTU_RETAIL_UNIT.RETAIL_UNIQUE_ID)="~00007869") AND ((PUBLIC_FSH_SETTLEMENT_HISTORY.SETTLEMENT_DATE)=#8/2/2007#))
ORDER BY PUBLIC_FSH_SETTLEMENT_HISTORY.SETTLEMENT_DATE;

This currently returns the very last forecast date, which isn't quite right.

SELECT PUBLIC_RTU_RETAIL_UNIT.RETAIL_UNIQUE_ID, PUBLIC_FSH_SETTLEMENT_HISTORY.SETTLEMENT_DATE AS S_DATE, Max(PUBLIC_FSH_SETTLEMENT_HISTORY.FORECAST_DATE) AS Maxofforcastdate
FROM PUBLIC_FSH_SETTLEMENT_HISTORY INNER JOIN PUBLIC_RTU_RETAIL_UNIT ON PUBLIC_FSH_SETTLEMENT_HISTORY.RTU_RETAIL_UNIT = PUBLIC_RTU_RETAIL_UNIT.RTU_ID
GROUP BY PUBLIC_RTU_RETAIL_UNIT.RETAIL_UNIQUE_ID, PUBLIC_FSH_SETTLEMENT_HISTORY.SETTLEMENT_DATE
HAVING (((PUBLIC_RTU_RETAIL_UNIT.RETAIL_UNIQUE_ID)="~00007869") AND ((PUBLIC_FSH_SETTLEMENT_HISTORY.SETTLEMENT_DATE)=#8/2/2007#));

 

[Brianwarnock]

I don't get it, unless in here PUBLIC_FSH_SETTLEMENT_HISTORY

each RTU_RETAIL_UNIT has multiple forecastdates per settlementdate, which it wont as that would be nonsense.

Brian

 

[twoplustwo]

Hi Brian,

That *IS* the case.

Say I pass one forecast for a given site for a given settlement date a week before belivery. Then, I get some information like a new weather forecast or something, so I adjust my forecast based on my new data.

Each unit can have many forecast dates (the date a forecast was entered for a given settlement date).

 

[Brianwarnock]

From post 7,

Max is a field operator and gives the max value of a field in the selected group, your group was probably the whole file.

you have to have a unique group to Max over, sorry but Max is not going to work for you.

BTW I still don't understand what you are doing but it appears that you need some way of tying forecasts to a specific "event".

Brian

 

[Brianwarnock]

How about you use Datevalue and timevalue to break forecastdate into two fields group on the date but max on the time.

Brian

 

[twoplustwo]

Haha very sick, thanks so much Brian.

Much appreciated.