Re: Complex query

On 2014-03-31 11:46:28 -0700, David Johnston wrote:
> Leonardo M. Ramé-2 wrote
> > Hi, I'm looking for help with this query.
> >
> > Table Tasks:
> >
> > IdTask StatusCode StatusName
> > ----------------------------------
> > 1 R Registered
> > 1 S Started
> > 1 D Dictated
> > 1 F Finished
> > 1 T Transcribed
> > ----------------------------------
> > 2 R Registered
> > 2 S Started
> > 2 T Transcribed
> > 2 F Finished
> >
> > As you can see, I have a table containing tasks and statuses. What I
> > would like to get is the list of tasks, including all of its steps, for
> > only those tasks where the StatusCode sequence was S followed by T.
> >
> > In this example, the query should only return task Nº 2:
> >
> > 2 R Registered
> > 2 S Started
> > 2 T Transcribed
> > 2 F Finished
> >
> > Can anybody help me with this?.
>
> First you need to decide how tell the database that R-S-T-F is ordered and
> then maybe you can use window functions, specifically "lag(col, -1) over
> (...)", to determine what the prior row's code is and act accordingly.
>
> Put that into a sub-query and return the "IdTask" to the outer query's where
> clause.
>
> David J.
>
>
Thanks David, I hope I understood what you mean.

After adding the Id column, I came up with this query:

ris=# select lag.id, lag.idtask, lag.code, lag.lg from (select idtask, code, id, lag(code, -1) over () as lg from tasks_test) as lag;
id | idtask | code | lg
----+--------+------+----
1 | 1 | R | S
2 | 1 | S | D
3 | 1 | D | F
4 | 1 | F | T
5 | 1 | T | R
6 | 2 | R | S
7 | 2 | S | T
8 | 2 | T | F
9 | 2 | F |
(9 rows)

Row nº 7 meets the condition, but I don't want to show only that row, I
would like to show this:

6 | 2 | R | S
7 | 2 | S | T
8 | 2 | T | F
9 | 2 | F |

Any hint?.

--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877