What is START_DATE and END_DATE in the DUDETAILSUMMARY table?

[Naomi Stringer]

Hi Nick,

I'm trying to check the FCAS dispatch data by summing over generators in each region, and as a first step, need to get the DUIDs data by region. 

To do so, I've merged data from DISPATCHLOAD with DUDETAILSUMMARY, using DUID as the key for both left and right ('NEMOSIS_merge' image attached). 

However, for some DUIDs there are multiple entries per time period ('merged_data' image attached), which I think corresponds to multiple START_DATE and END_DATE values for each DUID. Does this sound correct? And if so, what is START_DATE and END_DATE? 

(have checked the MMS data model, which doesn't make it any clearer, 'MMS_data_model' image attached)

Thank you!

Naomi

[Nicholas Gorman]

Hi Naomi,

Just thought I'd summarise the chat we had in person, a while back, in case anyone else had the same question.

You are correct that there will be mutiple enteries for each DUID in the table DUDETAILSUMMARY and that this corresponds to mutiple START_DATE and END_DATE values. This details when different versions of the DUDERTAILSUMMARY applied to each DUID in the past, i.e AEMO doesn't just override values when it updates the table but creates a new entry with a new START_DATE and END_DATE, and updates the END_DATE of the previous record. Working out which records in the DUDERTAILSUMMARY table applied during which settlementdates could be done by finding the record where START_DATE is less than settlementdate and the END_DATE is greater than the settlementdate. Note that other tables have a similair column to START_DATE called EFFECTIVEDATE, however this has no corresponding end date, rather it stops applying when a record with the same primary key (except for EFFECTIVEDATE) and a newer EFFECTIVEDATE is added to the table.

It would be nice to add a feature to the tool that maps START_DATEs, END_DATEs and EFFECTIVEDATEs to particular settlement dates to make joins like this easier. Hopefully soon :)

Cheers,

Nick