Grouping in batches 1

[marckssg]

Hi,

I've got a list of 15,000 contract numbers I'd like to order them and then group them in batches as follows;

1-100
101-200
201-300
301-400 etc.

The contract number tends to be in the following format;
CN/000000001
Although there are some anomolies every now and again such as;
CH/H5105

Once I've grouped the contracts I would then like to calculate what percentage are Active, Suspended or Terminated. I've done a search of existing threads and the solutions I've found so far give the impression of a group without actually creating one via supression etc. But the % are critical to what I am trying to achieve.

I am using Crystal XI coming off an Oracle database.

Cheers

Marc

 

[marckssg]

Typically come up with something like a working solution just after posting!

I've done a SQL Command taking the Contract Number and adding the "ROWNUM". Ordered by Contract Number, then in the report grouped on the following;
truncate(({Command.ROWNUM}-1)/100)

Which was lifted from one of LBass's previous replies. So thanks for that!

Cheers

Marc

 

[lbass]

Hmmm. I don't remember this. Do you have the thread number you looked at? Thanks.

-LB

 

[marckssg]

It was one of many! But here you go;

http://www.tek-tips.com/viewthread.cfm?qid=1319215

Cheers

Marc

 

[lbass]

I see--I was thinking about the command. Can you share the command that you set up using rownum? Thanks.

-LB

 

[marckssg]

Sure, its;

SELECT RB.Contract_Number,Contract_Status, row_number() over (order by RB.Contract_Number asc)
FROM LFM.RBCONTHM RB
order by RB.Contract_Number

Just playing about it with it now to get the row count to go over a union.

Cheers

Marc

 

[marckssg]

Gone with this is the end to generate the row numbers over the union, probably not the prettiest solution, but its done what I needed to do!

select ROW_NUMBER() OVER(Order by Contract_Number ASC), t1.Contract_Number,t1.Contract_Status
from (
(SELECT Contract_Number,Contract_Status from LFM.RBCONTHM )
UNION
(SELECT Contract_Number,Contract_Status from Caretex.RBCONTHM)
) t1
Order by Contract_Number


Cheers

Marc