asked on

Eliminate duplicates from aggregate join SQL Query

I need to rewrite this query to eliminate duplicates;
I've probably constructed it wrong
I need to use data from these 2 tables on one SSRS report;
They are only related by three fields month, year and class.
Attached is a sample image of the dupes i have in my results.

I have a detail table with vehicle mileage info and a stats table with table i need to display comparison data from a date range of start and end date.
the stats table has the counts for vehicles and the detail table has the mileage breakdown with vehicle ids by year.
My query is based on two separate reporting periods since i prompt for start and end date.
attached is my table layout and the results i am currently getting.
Should the subquery contain the result set with the multiple vehicle ids?
Can i use a group by to help?
I need to do a lot of aggregation on different fields from each table.

thanks
john
code.txt
sample-results.jpg
dbTables.docx

p_nuts

Did you try select distinct .....

Alpesh Patel

Distinct is no possible. Because for that all columns value should be distinct for dupes rows. If any of field has not matching value then it will not be eliminated by distinct.

jtrapat1

ASKER

These two result sets will be used as the dataset for my one report in Reporting Services-
The top half of the report consists of three matrices to do month/year comparison of values
and the bottom half of the report involves vehicle mileage aggregate data.
If anyone could tell me how to merge the two into one sp for my report - that would help.
I think I should be able to write this as a subquery OR link it to my main report as a subreport -

thanks
john

ASKER CERTIFIED SOLUTION

G Trurab Khan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jtrapat1

ASKER

Ghunaima:
Thanks - but i tried your query and I am still getting the duplicates;
Its not your fault - its my database table design;
I think if I select the dupes in the outer most query and then bring in the non-dupes table,
i may be able to eliminate the dupes on the ssrs report side-

thanks
john

G Trurab Khan

Add distinct clause in the outermost query as well

p_nuts

Just use it in reporting services and hide details and only show the aggregates ...

jtrapat1

ASKER

p_nuts -
can you expand on that answer?
How can I hide the details and only show the aggregates?
If I go into design view for the matrices, I can "Hide Duplicates" for the details row but this doesnt give me the correct results.
please help.

thanks
John