Hello, I'm working on a report, I reached the penultimate step but I'm
not able to go on.

Here's the output of the penultimate step. I added a dashed line to
separate the sections of the output that I have to elaborate further.
After the table I'm trying to explain what I have to do.

CODE DATE MEASURE CLOSING
------ ---------- ---------- ----------
033161 19-08-2010 412.918569 23-11-2010
033161 01-09-2010 403.445429 15-12-2010
---------------------------------------
033274 20-09-2010 426.729098 28-12-2010
---------------------------------------
033398 11-01-2010 411.157853 12-05-2010
033398 14-01-2010 436.719516 12-05-2010
033398 21-01-2010 400.76608 12-05-2010
033398 25-01-2010 414.005238 12-05-2010
033398 01-02-2010 410.820988 12-05-2010
033398 08-02-2010 422.621691 12-05-2010
033398 17-02-2010 455.28461 19-05-2010
033398 03-03-2010 465.202856 10-06-2010
033398 17-03-2010 455.724659 21-06-2010
033398 24-03-2010 443.773019 21-06-2010
033398 01-04-2010 458.203104 06-07-2010
033398 22-04-2010 435.892846 20-10-2010
033398 26-04-2010 403.97195 20-10-2010
033398 05-05-2010 407.338933 20-10-2010
---------------------------------------
033398 21-07-2010 421.607262 20-10-2010
033398 26-07-2010 447.006644 08-11-2010
033398 04-08-2010 464.043172 08-11-2010
033398 12-08-2010 492.558158 10-11-2010
033398 19-08-2010 484.288424 22-11-2010
033398 23-08-2010 503.180673 22-11-2010
033398 01-09-2010 499.069113 10-12-2010
033398 08-09-2010 481.387512 10-12-2010
033398 20-09-2010 460.156675 22-12-2010
033398 28-09-2010 467.459055 28-12-2010
033398 06-10-2010 476.595229
033398 12-10-2010 453.558728

The very first line says that item 033161 had a non-compliant measure on
19-08-2010. This event inhibits other measures 'till 23-11-2010. This
means that the second line must be excluded from the final report.

Item 033274 appears only once in the report so no problem.

Item 033398 has two series of non-compliant measures and they must be
treated separately.

The final report must be:

CODE DATE MEASURE CLOSING
------ ---------- ---------- ----------
033161 19-08-2010 412.918569 23-11-2010
033274 20-09-2010 426.729098 28-12-2010
033398 11-01-2010 411.157853 12-05-2010
033398 21-07-2010 421.607262 20-10-2010

I used analytical functions in previous steps, I guess I have to use
analytical functions also in this last step but I'm stuck. May you help me?

Thanks in advance.

Y.

On Nov 2, 9:22*am, yossarian <yossaria... (AT) operamail (DOT) com> wrote:
It would be easier to help if you posted commands that would create
tables, load with test data, and show what sql you tried.

jg
--
@home.com is bogus.
"They're making me uncomfortable. Nobody is going to shoot me, so tell
them to relax." - JFK

joel garry wrote:

I didn't, because this would add useless complication. This output is
produced by a very complex query. This output is OK, I only have to
remove some lines according to the rules that I tried to describe in my
previous post. You can take this table as test data.

Thank you.

Kind regards,

Y.

On Nov 3, 4:03*am, yossarian <yossaria... (AT) operamail (DOT) com> wrote:
What piece of the row indicates the measure is non-compliant?

Brian

bhonaker wrote:

All the measures are non-compliant. I only have to remove all the rows
in a group except the first one because they are included in the time
period defined by the first row. I separed groups with dashed lines in
my first post.

I solved the problem procedurally by writing a pipelined table function
but I'm almost sure that this can be solved with SQL and analytic
functions too.

Thank you.

Kind regards, Y.

(sorry, I wrote to you a personal e-mail reply by mistake)

On Nov 4, 9:33*am, yossarian <yossaria... (AT) operamail (DOT) com> wrote:
What part of the data indicates item 033398 had two series of non-
compliant measures other than the dashes you inserted in the output
manually?

bhonaker wrote:

The first line of item 033398 has column CLOSING=12-05-2010. This means
that all the subsequents measures of item 033398 must be ignored until
DATE becomes greater than 12-05-2010.

Actually, the second series of item 033398 begins with a row with
DATE=21-07-2010, the first DATE value greater then 12-05-2010.

Thank you.

Kind regards, Y.