Seam

W dniu 20.11.2012 16:40, Leyne, Sean pisze:

> Marcin,
>
>> 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 ?
>
> This should do the trick:
>
> SELECT
> M.Config_name
> ,M.Work_Date as Date_From
> ,(SELECT FIRST 1 N.Work_Date FROM Table N WHERE N.Config_name = M.Config_name AND N.Work_Date < M.Work_Date ORDER BY DESC N.Work_Date) as Date To
> FROM Table M
> WHERE
> M.Config_name = :Machine_Name
>
>
> Sean

Thank you for your effort, but I'm afraid that it won't do the trick,
because this query returns all entries for particular machine along with
max date for particular configuration:
Config_1, 01.11.2012, 07.11.2012
Config_1, 02.11.2012, 07.11.2012
Config_1, 03.11.2012, 07.11.2012
New_Config, 04.11.2012, 05.11.2012
New_Config, 05.11.2012, 05.11.2012
Config_1, 06.11.2012, 07.11.2012
Conifg_1, 07.11.2012, 07.11.2012

What I need is that if certain config lasts more than one day, the query
returns one record for whole period.

Marcin