Filtering out duplicate records in a Specified Grouping

[GaryCWL]

Reporting Environment:
BO Crystal XI
SQL Server 2000

Reporting Issue:
I’ve created a detailed sales/quote report that displays the Quote, Project, Discount %, Gross $, Net $. Then grouped the report based on three groupings listed below:
• Group 1: Calendar Month
• Group 2: Sales Rep
• Group 3: Discount Category using specified grouping of (0-4%, 5%, 6-9%, 10%, and >10%

Sample Report Data:
February
John Smith

0-4%
Quote Project Disc % Gross$ Net$
1001 ABC Co 0% 888.00 888.00
1052 NO Hospital 0% 20.25 20.25
1063 NO Hospital 0% 20.25 20.25
1074 NO Hospital 0% 20.25 20.25

5%
Quote Project Disc % Gross$ Net$
1031 LSU 5% 1016.00 965.2
1033 CSS Finance 5% 500.00 475.00
1113 LSU 5% 1016.00 965.2

Question:
How do I create a grouping or formula that will remove the duplicates based on Project and throw them into a separate grouping called Duplicates?

Ultimately, I would like to see a report that looks like the following.

Proposed Report Data:
February
John Smith
0-4%
Quote Project Disc % Gross$ Net$
1001 ABC Co 0% 888.00 888.00
1052 NO Hospital 0% 20.25 20.25

5%
Quote Project Disc % Gross$ Net$
1031 LSU 5% 1016.00 965.2
1033 CSS Finance 5% 500.00 475.00

Duplicates
Quote Project Disc % Gross$ Net$
1063 NO Hospital 0% 20.25 20.25
1074 NO Hospital 0% 20.25 20.25
1113 LSU 5% 1016.00 965.2 50.80

Any suggestions/recommendations is greatly appreciated.

 

[synapsevampire]

How do you know that these are dupes, because the company is the same? Seems like a bad criteria, could you never quote to the same company a second time?

Anyway, Crystal won't toss the dupes into another group for you, you need to handle it yourself.

I would create a UNION query or some such and assign another column called dupes to the dupes.

As in:

select 'nondupe' source, min(quote), project, Disc %, Gross$, Net$
from table
union all
select * from
(
select 'dupe' source, quote, project, Disc %, Gross$, Net$
from table
where quote not in
(
select 'nondupe' source, min(quote), project, Disc %, Gross$, Net$
from table
)
)

Something like that.

You can even use a Command Object and pass this as the query to SQL Server.

-k

 

[lbass]

Do you really want that group for duplicates or is that you don't think there is a way of eliminating those records?

-LB

 

[GaryCWL]

In response to synapsevampire's question, "How do you know that these are dupes, because the company is the same? Seems like a bad criteria, could you never quote to the same company a second time?"

In short, a quote from our company typically expires within 14 days. If a customer calls back and asks to add/remove/change an item or just extend the pricing discount, we can but that means a new quote is generated with the same company.

I will look into your suggestion but if it has to be done in SQL, I may just give the users a check box(flag) for them to select if it's a duplicate quote.

In response to lbass's, "Do you really want that group for duplicates or is that you don't think there is a way of eliminating those records?"

I want to eliminate any duplicate quotes within a given percentage grouping so that the totals within a group are no so inflated. However, I would still like to see the duplicates in another section and that can be labeled exceptions/duplicate.

Hope that answered both questions.

 

[lbass]

The following is similar to what SV suggested, but a bit simpler, I think. Go to database->database expert->your datasource->add command and enter:

Select min(table.`quote`) as minquote,table.`project`
From `table`table
Group by table.`project`

Link the command to your table on the project field. Then in the main report, create a formula {@group}:

if {table.quote} <> {Command.minquote} then
"Others" else
{table.quote}

Insert a group on {@group}. This should return only the first quote per project, with a final group containing all other records.

-LB