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
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.
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
You're welcome! Guess how many times I feel like an idiot when I review code that I've written 6 months ago. []
IF you are heavily involved in coding.. its always feels like that. Nevertheless if your code is working then simply don't touch it [].
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.
