2 Replies Latest reply on Apr 27, 2017 1:01 AM by rozvin.marchan

    How Do I Eliminate Double Counting When Joining Tables


      Hi Community


      I have two tables in a SQL Server.  One table contains parts usage information, the other table contains labor hours information.  The only common filed between the two is a Project or Task ID Reference number.  In the Labor table the Task ID Reference number is a unique primary key and contains fields for the total number of ours worked on that task for each individual involved.  In the Parts Usage table, the Task ID number is not a unique primary key.  There is a record for each part type and shipment sent out of the warehouse, so therefore there can be multiple records all with the same Task ID number.


      When I do a JOIN of the two tables, I can get accurate Parts Usage information, but each "joined" record contains the total task hours per person for the overall task.  Thus when I sum hours I get a significant over counting of labor hours spent on that task whenever there is more than one shipment for that task.


      Is there any way to keep the parts details, yet only use a single value per task on the labor hours?


      I have attached an example dataset which kind of simulates the problem in a very simple way.  The actual data tables contain hundreds of fields and many, many millions of records.


      Appreciate any help or direction to explore that folks have to offer.