How to Find if a Call is a Repeat based on a Serial Number 1

[thermalman]

Hi,
I am using Crystal Reports XI with a SQL Server 2000 BackEnd.

I have a problem with trying to get a report to show me only the records where the SerialNo is the Same and the DateTime Logged is less than 30 days.

We perform between 2000 - 3000 Service Visits on Printers and I want to highlight ALL calls that are repeat visits and the only way to know this is by finding the records where the SerialNo is equal but I only want to see records where the SerialNo and DateTimeLogged is less that 30 days
for Example

CallNo Customer DateLogged Model SerialNo
1234 Joe Bloggs 01/10/06 10:00 4000-000 ABC1234
1645 Joe Bloggs 15/10/06 09:00 4000-000 ABC1234
1856 Joe Bloggs 28/10/06 16:00 4000-000 ABC1234

I have tried grouping by SerialNo but it shows me all of the calls and only groups the SerialNo's together. I ONLY want to See the Calls where the SerialNo is the Same and are logged within 30 days of each other.

Is this possible?

Regards
Thermalman

 

[lbass]

I think you would have to use a section suppression formula like:

(
(
onfirstrecord or
{table.serialno} <> previous({table.serialno})
) and
datediff("d",{table.datelogged},
next({table.datelogged})) > 30
) or
(
{table.serialno} = next({table.serialno}) and
datediff("d",{table.datelogged}, next ({table.datelogged})) > 30
) or
(
{table.serialno} = previous({table.serialno}) and
datediff("d",previous({table.datelogged}),{table.datelogged}) > 30
)

-LB

 

[thermalman]

Hi Lbass,
Thanks for that It has narrowed down the count of records to 25 pages. It is not only showing me calls where there is more than one record with the same SerialNo but it is also showing me just single records by SErialNo.

How can I drill it down further to only show me records where there is more that one record per SerialNo?

Many Thanks for your help on this

Regards
Thermalman

 

[lbass]

Try changing the formula to:

count({table.serialno},{table.serialno}) = 1 or
(
(
onfirstrecord or
{table.serialno} <> previous({table.serialno})
) and
{table.serialno} = next({table.serialno}) and
datediff("d",{table.datelogged},
next({table.datelogged})) > 30
) or
(
{table.serialno} = next({table.serialno}) and
datediff("d",{table.datelogged}, next ({table.datelogged})) > 30
) or
(
{table.serialno} = previous({table.serialno}) and
datediff("d",previous({table.datelogged}),{table.datelogged}) > 30
)

-LB

 

[thermalman]

Hi Lbass,
Many Thanks for that it works a treat after i figured out how to suppress the blanks.

You are an absolute Star!!!!


Many Thanks
Thermalman