>Let's say that I have a table containing:
>Machine_Name varchar(20)
>Config_Name varchar(20)
>Work_Date date
>
>The data for one machine may look like this:
>
>Machine_1, Config_1, 01.11.2012
>Machine_1, Config_1, 02.11.2012
>Machine_1, Config_1, 03.11.2012
>Machine_1, New_Config, 04.11.2012 <- we have set up a new configuration
>Machine_1, New_Config, 05.11.2012
>Machine_1, Config_1, 06.11.2012 <- back to old config
>Machine_1, Conifg_1, 07.11.2012
>
>I'd like to create a query that returns the 'history' of configurations
>for one machine, like this:
>Config_Name, Date_From, Date_To
>Config_1, 01.11.2012, 03.11.2012
>New_Config, 04.11.2012, 05.11.2012
>Config_1, 06.11.2012, 07.11.2012
>
>Is it possible without iterating each entry for Machine_1 ?

Hi Marcin!

To get the result you want, I'd think of this as something that should satisfy the following criteria:

For Date_From: There should be no record with the same configuration the day before.
For Date_To: There should be no record with the same configuration the day after.
Generally: There should be no record with a different configuration between Date_From and Date_To.

I think the following SQL will satisfy those criteria:

select M1.Config_name, M1.Work_Date as Date_From, M2.Work_Date as Date_To
from MyTableName M1
join MyTableName M2 on M1.Machine_Name = M2.Machine_Name
and M1.Config_Name = M2.Config_Name
and M1.Work_Date <= M2.Work_Date /*There may be just one day with a configuration */
left join MyTableName M3 on M1.Machine_Name = M3.Machine_Name
and (M1.Config_Name = M3.Config_Name
and (M1.Work_Date-1 = M3.Work_Date /*Date_From criterion*/
or M2.Work_Date+1 = M3.Work_Date)) /*Date_To criterion*/
or (M1.Config_Name <> M3.Config_Name /*General criterion*/
and M3.Work_Date between M1.Work_Date and M2.Work_Date)
where M1.Machine_Name = 'Machine_1'
and M3.Machine_Name is null

Note that this will only work as long as there's no gap in Work_Date (e.g if there's a holiday 25th December and you don't want that to mean another row in your table if 24th and 26th December had the same configuration, then you would have to add a bit to the query).

If your table contained lots of rows for each Machine_Name, this query could be a bit slow. However, if you only have one configuration per day per machine and an index for Machine_Name, then it should require many years of data to become a show stopper.

Alternatively, on recent Firebird versions you could use EXECUTE BLOCK and iterate (iterating will be quicker if you have lots of rows per machine). The difference between my above and below suggestion regarding output, is that EXECUTE BLOCK will not return two rows if there's missing dates in between (confer 25th December above):

EXECUTE BLOCK RETURNS (CONFIG_NAME VARCHAR(100), DATE_FROM DATE, DATE_TO DATE)
AS
DECLARE VARIABLE NEXT_CONFIG VARCHAR(100);
BEGIN
FOR SELECT CONFIG_NAME, WORK_DATE
FROM MYTABLENAME
WHERE MACHINE_NAME = 'Machine_1'
ORDER BY WORK_DATE
ROWS 1
INTO :CONFIG_NAME, :DATE_FROM DO
BEGIN
FOR SELECT CONFIG_NAME, WORK_DATE
FROM MYTABLENAME
WHERE MACHINE_NAME = 'Machine_1'
ORDER BY WORK_DATE
INTO :NEXT_CONFIG, :DATE_TO DO
IF (CONFIG_NAME <> NEXT_CONFIG) THEN
BEGIN
SUSPEND;
CONFIG_NAME = NEXT_CONFIG;
DATE_FROM = DATE_TO;
END
SUSPEND;
END
END

HTH,
Set