Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Aug 07, 2007 08:39 AM by abhishek2981
Aug 06, 2007 03:48 AM|LINK
Also I have tested the parameters (which i pass from my application) so many times and they work just fine...
Aug 06, 2007 05:26 AM|LINK
I forgot the Employee table join while popluating the variables, include them and the obove query will be fine, try this and i am sure there wont be problem in executing the stored procedure
Aug 06, 2007 07:08 AM|LINK
Well i got the join in the variables but now the problem is that although the result set (number of rows) is correct but the calculated fields are equal for all the employees
e.g. for @TotalHoursWorked the SUM is equal for all employees. Let us see at one of the query after editing:
Set @TotalHoursWorked = (SELECT
SUM (P.HoursWorked) FROM Presence P
INNER JOIN Employee E ON P.EmpID_fk = E.EmpID // newly added join
WHERE P.EmpID_fk = E.EmpID
AND P.DateWorked Between @JoinDate AND @CurrentDate)
In the above case, the SUM is being calculated from the entire table. Thus the results returned are euqal for all the employees.
What should be done to compute values for individual employees. By the way, i see a dramatic improvement in the result set being returned within SQL Server. From 6 seconds
it went to 1 second, cool...
Thanks for your patience...
Aug 06, 2007 07:16 AM|LINK
use group by on employees
Aug 06, 2007 07:38 AM|LINK
Create 3 table variables for each @TotalHoursWorked etc. having columns EmpID and the TotalHoursWorked and insert like this
Insert into @TotalHoursWorked
AND P.DateWorked Between @JoinDate AND @CurrentDate
group By E.EmpID)
This will give the Employees and thier Sum
and then in the main query you join with these tables on empid to get the result.
Aug 06, 2007 07:45 AM|LINK
Let me give it a try, thanks alot for your help and patience, im obliged...
Aug 07, 2007 08:03 AM|LINK
Awesom, my problem got solved. I used table variables and Joined those tables with my query and that gave me my result with exactly the same Parameters and Where Clause. Also
the performance was great. I cant believe it. Greate job Abhishek. Really wonder job done. Thumbs upto you and i specially admire your patience in this whole process.
I also learnt a wonder technique of writing better queries with SQL Server. I still have my point unanswered as why not the same query (leaving apart the table variables)
didnt work at the first place. Can you guess what could be going wrong without the table variables. Also why the application hung indefintely ???
Anyways, once again thanks alot for your time and patience. Wonderful job done man...
Aug 07, 2007 08:39 AM|LINK
Thanks for the same By the way u know me I am Abhishek(aptech)