Displaying range values

[CrazyManiac]

In MSSQLServer i have a table of sales:
customer dateofsale checknumber
---------------------------------
Jon ....... 1
Jon ....... 2
Bill ....... 7
Bill ....... 8
Jon ....... 3
Jon ....... 22
Bill ....... 10

In the report I need to show checknumbers as range,i.e:

customer rangeofcheknumbers
Jon 1-3
Jon 22
Bill 7-8
Bill 10

Is it possible?
Thanks for help!

 

[Thadeus]

My simplistic approach would be to group by customer and then show the output in the Group Footer as the formula:

{customer} + " " + ToText(Min({checknumber}) + " - " + ToText(Max({checknumber})

 

[lbass]

Something like the following formula (placed in the details section) should work:

whileprintingrecords;
stringvar chkx := totext({table.chkno},"0000" //for 4-digit check no
stringvar seq;

if onfirstrecord or
{table.custID} <> previous({table.custID}) then
seq := chkx else

if not onlastrecord and
{table.custID} = next({table.custID}) and
{table.chkno} = previous({table.chkno})+1 and
{table.chkno} = next({table.chkno})-1 then
seq := seq else

if {table.custID} = previous({table.custID}) and
{table.chkno} = previous({table.chkno})+1 then
seq := left(seq,4)+ &quot; - &quot;+ chkx else //for 4-digit check no
seq := chkx;
seq;

Then select the formula->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
stringvar seq;

{table.custID} = next({table.custID}) and
(instr(seq,totext({table.chkno},0,&quot;&quot) = 0 or
{table.chkno} = next({table.chkno})-1)

-LB

 

[Ngolem]

Your sampple data is a bit confusing since you do't specify dates at all....

So I ASSUME that

Jon ....... 1
Jon ....... 2

both have the same date...otherwise why isn't 22 included in your number range.

you haven't told us key information to solving your problem...

do these have to be consecutive check numbers????

It makes a big difference in the approach to the problem.

A really simple way of doing this (assuming non-consecutive check number) .... why do I feel this is a waste of time {sigh} is to suppress the detail section and have as your grouping something like this

Group 1 header - customer
Group 2 header - Check ID (Here your grab the first Check ID)
Detail (Suppress)
Group 2 footer (here you grab the last Check-ID )
Group 1 footer ... here you display the numbers

BUT this is obviously too simple since there is a lot to this report that you have not told us about...

So define your problem better...please

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!