Sql-server – Retrieve result for all the users

sql serverstored-procedures

I have one query which returns particular user total hours for a month. I want to return for all the users in an org.

UserRegistration table contains UserID.

My code for particular user is,

 SELECT isnull(SUM(isnull([1],0))+SUM(isnull([2],0))+SUM(isnull([3],0))+
               SUM(isnull([4],0))+SUM(isnull([5],0))+SUM(isnull([6],0))+SUM(isnull([7],0))
              +SUM(isnull([8],0))+SUM(isnull([9],0))+SUM(isnull([10],0))+SUM(isnull([11],0))+
               SUM(isnull([12],0))+SUM(isnull([13],0))+SUM(isnull([14],0))+SUM(isnull([15],0))+
               SUM(isnull([16],0))+SUM(isnull([17],0))+SUM(isnull([18],0))+SUM(isnull([19],0))+
        SUM(isnull([20],0))+SUM(isnull([21],0))+SUM(isnull([22],0))+SUM(isnull([23],0))+
        SUM(isnull([24],0))+SUM(isnull([25],0))+SUM(isnull([26],0))+SUM(isnull([27],0))+
        SUM(isnull([28],0))+SUM(isnull([29],0))+SUM(isnull([30],0))+SUM(isnull([31],0)), 0)
     FROM dbo.timesheet where month ='August' and [year] =2015 and [Task ID] in(select
 TaskID from ManageTasks where TeamMemberUserID = 136)

Best Answer

You just need to use GROUP BY:

SELECT mt.TeamMemberUserID ---, <all those crazy SUM expressions>
FROM dbo.timesheet AS t
INNER JOIN dbo.Managetasks AS mt
ON t.[Task ID] = mt.TaskID
-- ... <WHERE clause> ...
GROUP BY mt.TeamMemberUserID;

If you need the user's names, there are several ways to do this, here's one:

SELECT u.UserName --, <all those crazy SUM expressions>
FROM dbo.timesheet AS t
INNER JOIN dbo.Managetasks AS mt
ON t.[Task ID] = mt.TaskID
INNER JOIN dbo.UserRegistration AS u
ON mt.TeamMemberUserID = u.UserID
-- ... <WHERE clause> ...
GROUP BY u.UserName;

Since apparently you want a row for every user, even those that don't have any logged hours for tasks in the selected month(s), try:

SELECT u.UserName --, <all those crazy SUM expressions>
FROM dbo.UserRegistration AS u
LEFT OUTER JOIN dbo.Managetasks AS mt
ON mt.TeamMemberUserID = u.UserID
LEFT OUTER JOIN dbo.timesheet AS t
ON t.[Task ID] = mt.TaskID
-- ... <WHERE clause> ...
GROUP BY u.UserName;

I do recommend more consistency in column names. Why is it Task ID in one table and TaskID in another? Why is UserID expanded to TeamMemberUserID in one table? Is it possible to have a TeamMemberUserID who is not a UserID, or vice versa? Consistency is important - I should be able to identify an entity by its column name regardless of what table it exists in.