return 1 row for each toolnumber

Rather than perform the Max(uploadtime) in the WHERE clause, you could put it as an inline SELECT and see how that works for you.  This would select the Max Uploadtime for the toolnumber in the row returned in the joined rowset.

Also, the first WHERE clause looks a little redundant.  toolassemblies.toolnumber will always be in (SELECT DISTINCT toolnumber FROM toolassemblies).  If it were a SELECT DISTINCT on a different table, say toolassemblyhistory.toolnumber, it would make more sense.  As written, it will always return every toolnumber in toolassemblies, which is what you had to begin with.  It is not reducing the rowset any.

SELECT (SELECT Max(uploadtime) 
          FROM   toolassemblies b 
          WHERE toolassemblies.toolnumber = b.toolnumber 
          )                         AS 'BuildDate', 
       toolassemblies.toolnumber, 
       l.location                                        AS 'Location', 
       'Tool Communication'                              AS 'Event', 
       batterygaugesn, 
       ( 'Tool Size: ' + Cast(toolsize AS VARCHAR) 
         + '; ' + 'Controller: ' 
         + Cast(controllersn AS VARCHAR) + '; ' 
         + 'Battery Gauge: ' 
         + Cast(batterygaugesn AS VARCHAR) + '; ' 
         + 'Battery: ' + Cast(batterysn AS VARCHAR) 
         + '; ' + 'Magnetics: ' 
         + Cast(magneticssn AS VARCHAR) + ' Used: ' 
         + Cast(Round(bd.batteryused, 2)AS VARCHAR) 
         + ';' + ' Voltage: ' 
         + Cast(Round(bd.batteryvoltage, 2)AS VARCHAR) ) AS 'Event Notes' 
FROM   toolassemblies 
       LEFT JOIN locations AS l 
              ON l.locationindex = toolassemblies.defaultlocationindex 
       LEFT JOIN batterydata AS bd 
              ON bd.defaultlocationindex = toolassemblies.defaultlocationindex 
WHERE  toolassemblies.toolnumber IN (SELECT DISTINCT toolnumber 
                                     FROM   toolassemblies) 
       AND bd.gaugeserialnumber = batterygaugesn  
       AND toolassemblies.uploadtime BETWEEN 
           '2012-01-31 11:59:59 PM' AND '2015-03-31 12:00:00 AM' 
ORDER  BY toolassemblies.toolnumber   

Select allOpen in new window

@Talok good suggestions but the max upload in the select clause slowed it down to a 4 1/2 minute query - I do agree that the toolassemblies.toolnumber IN (SELECT DISTINCT toolnumber
                                     FROM   toolassemblies)  
was redundant so I took it out and put the max back in the where clause and it runs at 25 seconds but still has multiple rows

Are you only wanting to return the last batterygaugesn used for a particular toolnumber with the last uploadtime?

now it is less than a second but still multiple rows for each toolnumber

SELECT DISTINCT  toolassemblies.toolnumber,
toolassemblies.uploadtime                         AS 'BuildDate', 
       
       l.location                                        AS 'Location', 
       'Tool Communication'                              AS 'Event', 
       batterygaugesn, 
       ( 'Tool Size: ' + Cast(toolsize AS VARCHAR) 
         + '; ' + 'Controller: ' 
         + Cast(controllersn AS VARCHAR) + '; ' 
         + 'Battery Gauge: ' 
         + Cast(batterygaugesn AS VARCHAR) + '; ' 
         + 'Battery: ' + Cast(batterysn AS VARCHAR) 
         + '; ' + 'Magnetics: ' 
         + Cast(magneticssn AS VARCHAR) + ' Used: ' 
         + Cast(Round(bd.batteryused, 2)AS VARCHAR) 
         + ';' + ' Voltage: ' 
         + Cast(Round(bd.batteryvoltage, 2)AS VARCHAR) ) AS 'Event Notes' 
FROM   toolassemblies 
       LEFT JOIN locations AS l 
              ON l.locationindex = toolassemblies.defaultlocationindex 
       LEFT JOIN batterydata AS bd 
              ON bd.defaultlocationindex = toolassemblies.defaultlocationindex 
WHERE  
       bd.gaugeserialnumber = batterygaugesn  
        AND toolassemblies.uploadtime = (SELECT Max(uploadtime) 
                                        FROM   toolassemblies b 
                                        WHERE 
           toolassemblies.toolnumber = b.toolnumber 
                                       ) 
       AND toolassemblies.uploadtime BETWEEN 
           '2012-01-31 11:59:59 PM' AND '2015-03-31 12:00:00 AM' 
           AND l.LocationIndex = 0
ORDER  BY toolassemblies.toolnumber 

Select allOpen in new window

basically everything in the event notes for each toolnumber

used CTE thanks Talok