Trying to optimize this T-SQL query-CodePudding

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

CodePudding user response：

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.