How to return min and max only records for multiple rows 1

[beckji]

I currently have a report which has data laid out as shown below

part monthend period start end
fred 2004-01 2004-01-1 100 150
fred 2004-01 2004-01-2 150 350
fred 2004-01 2004-01-3 350 250
fred 2004-02 2004-02-1 0 50
fred 2004-02 2004-02-2 50 100
fred 2004-02 2004-02-3 100 150
fred 2004-02 2004-02-4 150 200

The user wants to have the 'start' for the start period and
the 'end' for the last period of each monthend, so the report should look like this:-

part monthend start end
fred 2004-01 100 250
fred 2004-02 0 200

all the data is held in the same table, so no worries about cross table links, etc... any starters for 10 ?
I have tried to use sub-queries but I only return a single value when I use MIN or MAX.. (obviously)


Thanks,

Jim Beck

 

[Sridharan]

Going with your already done stuff... You can have two DP's one fetching MIN value and other MAX value and then you can show these in the report along with Part and MonthEnd... Now if you don't like two DP approach drop a note I will provide an alternative solution...

Sri

 

[blom0344]

Sri, is that correct?

I think he needs the first and the last value out of every
combination of part and monthend, not min and max values.

As I see it it will require some heavy SQL to get the job done, but I may be too pessimistic

T. Blom
Information analyst
tbl@shimano-eu.com

 

[Sridharan]

Well may be I was not clear blom... When I said Min and Max its bit misleading... What he needs to do is take the Minimum of Period for each (Part, MonthEnd) and take the Start Value in One DP... In the second one he needs to take the Max Period for each (Part, MonthEnd) and take the corresponding End Value...

That should do the thing... The thing is we are using two DP's... This can be done in One DP provided he is willing to add some objects at the Universe Side... I've seen lot of people hesitant to add objects at the Universe Side... So if Jim is happy with this 2 DP approach then its fine...

Sri

 

[BrianTyler]

Yes, it is fairly heavy.

Breaking it down, I feel that a Union of two queries will be required.

Each of the two queries will require a Subquery, one to identify the minimum part/date and one to find the maximum.

This is achievable in B.O. if there is a unique key on each row so that the outer query can be matched to the subquery.

 

[beckji]

Thanks to everyone who has helped out.

I have been able to get a 2 SQL solution, and merging the results, and to prove it I have attached the SQL for the MIN of Start value, in case anyone else has this problem.

select 
TABLE.PART,
TABLE.START,
substr(TABLE.PERIOD,1,7),  
TABLE.PERIOD
from TABLE
WHERE
  substr(TABLE.PERIOD,1,7)  BETWEEN  @variable('1. Start Monthend') AND @variable('2. Finish Monthend') 
 AND TABLE.PERIOD = 
 (
  select min( a.PERIOD) from TABLE a
  WHERE
    AND  ( substr(a.PERIOD,1,7) = substr(TABLE.PERIOD,1,7)  ) 
 )
group by TABLE.PERIOD

Thanks,

Jim Beck