Building a complicated query or universe-object

building reports desktopintelligence
1

Please can someone help me to build a reportquery or an universe object for getting the following results.

My data is about this:

Projectid / Activitycode / Act.DS Earlystart / ActualStart
P001 / 110M / blabla / 1-1-08 / 1-1-08
P001 / 230M / fdsfds / 31-1-09 / null
P001 / 450M / fsfsd / 1-4-09 / null
P002 / null / null / 1-12-08 / 1-12-08
P002 / 110M / nhggh / 5-2-09 / null
P002 / 750M / wdwdwd / 5-2-09 / null
P002 / 210M / plplplp / 31-12-08 / 31-12-08
P002 / 550M / ewdwd / 5-1-09 / null

I’d like to get one row for each Projectid with the first next Early- or Actualstart after today and one row with the latest early-/actualstart before today. When there are two or mare rows with the same date then I want the highest Activitycode. This last condition makes it insoluble for me! :oops:

I’ve tryed free SQL, with nested Select-statements, but I got still 2 rows per projectid, because I select the min(nvl(Earlystart ,ActualStart ) but the !!! I’ve tryed object in the universe with Partion BY, but the same problem.

Can someone show me the right way to solve my problem?

Wittk (BOB member since 2009-01-23)

2

What database are you using ?

Did you use LAG and LEAD analytical functions… with ROWS BETWEEN 1 PRECEDING and 1 FOLLOWING syntax…? And also explain which rows should be displayed … from your sample data… for each project?

Post your SQL here…

BO_Chief :us: (BOB member since 2004-06-06)

3

Thank you for your reaction! :roll_eyes:

I work with Qracle 9. My last query with free SQL was like this (Attachement in Word)

With this SQL I get the message ORA-01427 single row subquery returns more than one row.

Can you help me on the road with a simple example how to use LAG or LEAD?

Thanks, Els
SQL met geneste subselect.doc (25.0 KB)

Wittk (BOB member since 2009-01-23)