Trying to optimize this T-SQL query

Question

I have this T-SQL query that I am trying to optimize.

I am not sure how to make all rest of query starting from "where DateLoad...." should be modified to.

It appears that I could possibly make it simpler, but I am not sure how.

select Employee_Number, 
       DateLoad, 
       min(Rate_1_Pay_Rate) as Rate_1_Pay_Rate,
       min(Hourly_Rate) as Hourly_Rate, 
       min(FLSA_Status) as FLSA_Status,  
       min(Hire_Date) as Hire_Date, 
       min(Employee_Type) as Employee_Type,
       min(Status_Type) as Status_Type
from dbo.TableMain as hist
where DateLoad = (select min(DateLoad)
                  from dbo.TableMain as hist2 
                  where hist.Employee_Number = hist2.Employee_Number)
  and Employee_Type = (select min(Employee_Type)
                       from dbo.TableMain as hist2 
                       where hist.Employee_Number = hist2.Employee_Number)
  and Status_Type = (select min(Status_Type)
                     from dbo.TableMain as hist2 
                     where hist.Employee_Number = hist2.Employee_Number)
  and Hire_Date = (select min(Hire_Date) 
                   from dbo.TableMain as hist2 
                   where hist.Employee_Number = hist2.Employee_Number) 
group by Employee_Number, DateLoad

What is the goal of this query? You are referencing the table `TableMain` 5 separate times, and I doubt you you need to reference it more than once. — Thom A, Jun 30 '22 at 17:14
You use table alias `hist2` frequently, but not in a coordinated way. Is it your intention that all of the subqueries in the outer `where` clause should refer to _one_ row, or is picking values from multiple rows intentional? Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. — HABO, Jun 30 '22 at 18:27
@HABO Thank you for your feedback. This was written by one of developers (who is a novice to SQL), and I was wondering what the intention was. It appears that she was just copying the original query where we only had one where condition. I was trying to see if there is a better way/ simplifying the all the where etc. — Java, Jun 30 '22 at 19:45
Looks like it's a poor attempt at a top-1-per-group query, better solutions here https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group — Charlieface, Jul 01 '22 at 00:00

score 1 · Accepted Answer · answered Jun 30 '22 at 17:39

Without knowing more context to the data, I dont think you might really be getting expected output based on the minimum of each of the 5 individual columns. Lets try to demostrate with some data

TableMain
Employee_Number   DateLoad  Employee_Type  Status_Type  Hire_Date
1                 1/25      Z              D            1/21
1                 2/15      Y              A            1/21
1                 6/21      X              B            1/21

By looking at this sample data for a single employee (as it appears your table WILL have multiple entries for a single person), if I took the MIN() of each of the respective columns, I would get

Minimums          MINIMUM   MINIMUM        MINIMUM      MINIMUM
Employee_Number   DateLoad  Employee_Type  Status_Type  Hire_Date
1                 1/25      X              A            1/21

By joining back to find the "single" record that has these 5 elements does not exist.

Please edit your post, try to provide sample data to better illustrate what you HAVE with data and what you are looking TO GET from it. Simple English descriptions to get clarification across, but EDIT your original post and provide additional data and context.

Trying to optimize this T-SQL query

1 Answers1