Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: getting number ranges with start and end numbers Post Reply Post New Topic
Author Message
PeterH
Newbie
Newbie


Joined: 13 Dec 2012
Location: United Kingdom
Online Status: Offline
Posts: 5
Quote PeterH Replybullet Topic: getting number ranges with start and end numbers
    Posted: 29 Oct 2014 at 6:03am
Hi
I have a database with thousands of serial numbers which are not necessarily continuous as they are withdrawn for use e.g. 1-50, then 55-456, then 769-999

What I'm trying to do is produce a report showing, line by line the serial numbers in the various ranges. So, using the data above as an example I need the result to look like this

Row No      Start Serial           End Serial
1             1                     50
2            55                    456
3           769                    999
etc.......

Has anyone any ideas on the best approach?

Thanks
Peter
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 Oct 2014 at 6:13am
what is your source data?
seems like you can just use split on the '-' but I am not sure what your true starting point is
 
split (field,'-')[1]
split (field,'-')[2]
IP IP Logged
praveeng
Senior Member
Senior Member
Avatar

Joined: 11 Jul 2011
Online Status: Offline
Posts: 165
Quote praveeng Replybullet Posted: 29 Oct 2014 at 6:15am
Hi Peter,
 
If you have Start Serial and End Serial as separate database fields then place those filelds in details section and sort on Start serial field.
IF your data is like
1-50
55-456
769-999
Then split the data as below
//Start Serial formula
Split(database_field,'-')[1]
//End Serial formula
Split(Database_filed,'-')[2]
Then place these fields in details section and sort on Start field.
 
--Praveen G


Edited by praveeng - 29 Oct 2014 at 6:15am
Praveen Guntuka,
praveen_guntuka@yahoo.com
IP IP Logged
PeterH
Newbie
Newbie


Joined: 13 Dec 2012
Location: United Kingdom
Online Status: Offline
Posts: 5
Quote PeterH Replybullet Posted: 29 Oct 2014 at 6:23am
The source table is a serial number table containing many thousands of records in serial number sequence. As these serial numbers get used (not necessarily in sequence) they are allocated a code (dozens of different codes depending on type of use) to show that they are in use, leaving the remaining records which have not yet been issued.

The trouble is the unused serial numbers won't always be in sequence so there could be unused (serial) numbers 1-50 in sequence, then a range of numbers that have been used, followed again by another range of unused numbers.

It's the blocks of unused numbers that I need to identify by getting the first number of the block and the last number of the block and presenting those two numbers in one row on the report etc. Hope that helps to clarify

Thanks

Peter
IP IP Logged
PeterH
Newbie
Newbie


Joined: 13 Dec 2012
Location: United Kingdom
Online Status: Offline
Posts: 5
Quote PeterH Replybullet Posted: 29 Oct 2014 at 6:26am
Praveen

Thanks for your reply

Unfortunately the serial numbers are only in one field (see my other reply), so I need to find a way of identifying the start and end numbers of blocks of numbers that haven't been used. The only thing I can think of that may help is that all of the unused records have a control key field of either null or zero

Peter
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 Oct 2014 at 6:34am

do not filter on the table

order by the asc number
use a suppression
if next(field)=field+1
should leave you with only the rows where a gap exists
two display formulas should give your gap begin and end
//start
field+1
//end
next(field)-1
 
EDIT: looks like i inverted what you want to see but it gives you an idea of a process


Edited by DBlank - 29 Oct 2014 at 6:35am
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.