Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

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
0
jtrapat1
Asked:
jtrapat1
  • 3
  • 2
  • 2
  • +1
1 Solution
 
p_nutsCommented:
Did you try select distinct .....
0
 
Alpesh PatelAssistant ConsultantCommented:
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.
0
 
jtrapat1Author Commented:
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
GhunaimaCommented:
Try the query

 cod1.sql
0
 
jtrapat1Author Commented:
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
0
 
GhunaimaCommented:
Add distinct clause in the outermost query as well
0
 
p_nutsCommented:
Just use it in reporting services and hide details and only show the aggregates ...

0
 
jtrapat1Author Commented:
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
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now