> Seam

should be Sean of course, sorry for that...

>
> 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
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>
>