SQL Server Performance

Join a temp table with a table

Discussion in 'Getting Started' started by aqweeva, Mar 8, 2011.

  1. aqweeva New Member

    Can you join a temp table with a table?
    What is the most efficient method to use for my SQL SP below?
    I have a table like this:
    EmployeeId, Year, TotalHours, RegularPay

    I want to create a query that will select " EmployeeId, Year, PayDate, sum(TotalHours), sum(RegularPay) "

    Example: (Sample Data)
    =====================================
    EmployeeId Year TotalHours RegularPay
    5568 2011 40 1720.00
    5568 2011 35 1505.00
    5568 2011 42 1806.00
    =====================================

    And the Query needs to return this:
    =====================================
    EmployeeId Year TotalHours RegularPay
    5568 2011 117 5031.00
    =====================================
    My initial though was to use a temp table to select the fields that need sum( ) and then
    Join that with another select of the original table...? is there a better method?
    Thanks much!
    ~Robert
  2. FrankKalis Moderator

    Welcome to the forum!
    This requirement can almost literally be translated into SQL.
    SELECT EmployeeId, Year, SUM(TotalHours), SUM(RegularPay) FROM table GROUP BY EmployeeID, Year.
    Not sure where PayDate comes from and how it fits into this picture, but you certainly can join onto other tables if needs be. As far as I can see there is no need for a temp table.
  3. aqweeva New Member

    Thank very much!, that was too easy... I feel like an idiot.. : )
    Yes, the PayDate is another field of the table along with several others I left out just keep the post simple.
    Kind Regards,
    Thanks!
    ~Robert
  4. FrankKalis Moderator

    You're welcome!
    Guess how many times I feel like an idiot when I review code that I've written 6 months ago. [:)]
  5. satya Moderator

    IF you are heavily involved in coding.. its always feels like that. Nevertheless if your code is working then simply don't touch it [:)].
  6. satya Moderator

    How frequently the query is used?
    It depends on that whether you can use a temp table or not, also for smaller calculations you could take help of TABLE variable in this case to obtain relevant results.

Share This Page