Xrhrft

Below is my code, which returns billable and non billable total hours for their projects. I want to show in single row, but i am getting two result sets like below.

DECLARE @UserName NVARCHAR(50);



select @UserName = UserName 

from UserRegistration 

where UserID = @UserID;



SET @sqlText = 

    N'SELECT''' + @UserName + ''' AS UserName, isnull(

    SUM(isnull(['+REPLACE(@columns,',','],0)),

    SUM(isnull([') + '],0)), 0)' +' as [Billable Hours] 

    FROM dbo.timesheet 

    where month ='''+ @strMonth+'''

    and [year] =' + Cast(@year1 AS VARCHAR(max))+  ' 

    and [Task Type] = 1 

    and [Task ID] in(

        select TaskID 

        from ManageTasks 

        where TeamMemberUserID ='+  Cast(@UserID AS VARCHAR(max)) +')'



SET @sqlText4 = 

    N'SELECT''' + @UserName + '''AS UserName, 

    isnull(SUM(isnull(['+REPLACE(@columns,',','],0)),

    SUM(isnull([') + '],0)), 0)' +' as [Non Billable Hours] 

    FROM dbo.timesheet 

    where month ='''+ @strMonth+''' 

    and [year] =' + Cast(@year1 AS VARCHAR(max))+  '

    and [Task Type] = 2 

    and [Task ID] in(

        select TaskID 

        from ManageTasks 

        where TeamMemberUserID ='+  Cast(@UserID AS VARCHAR(max)) +')'



set @sqlText = REPLACE(@sqlText,',SUM','+SUM') 



set @sqlText4 = REPLACE(@sqlText4,',SUM','+SUM')

My result is:

UserName   |    BillableHours

------------------------------

myName     |      20 

-----------------------------



UserName   |   NonBillableHours

-------------------------------

myName     |      30

-------------------------------

Expected result should be like this:

UserName   |    BillableHours  |   NonBillableHours

-----------------------------------------------------

myName     |      20           |      30

-----------------------------------------------------

Depending what the colum is for your userID (i've declared it as TeamMemberUserID which you are using later)
Simply Join the timesheet table to the User Registration table on the TeamMemberUserID and select the user.name field

End result looks something like:

SET @sqlText = N'SELECT user.name as [Name], isnull(SUM(isnull(['+REPLACE(@columns,',','],0)),

SUM(isnull([') + '],0)), 0)' +' FROM dbo.timesheet as ts LEFT JOIN UserRegistrations user ON user.TeamMemberUserID = ts.TeamMemberUserID where month ='''+

@strMonth+''' and [year] =' + Cast(@year1 AS VARCHAR(max))+

'and [Task ID] in(select TaskID from ManageTasks where 

TeamMemberUserID ='+  Cast(@UserID AS VARCHAR(max)) +')'

I'm not sure what your dynamic columns are so I cant 100% promise this will work, but if you get any column errors you should just be able to put ts. infront of it to declare its from that table

Ste

I have assumed the ID field that would be used to join the two tables. Let me know what field you would use to join these tables and I will update the answer. You should also specify the correct alias in your @columns variable.

I also assumed the UserRegistration table schema to be dbo.

SET @sqlText = N'   SELECT  ISNULL(SUM(ISNULL(['+REPLACE(@columns,',','],0))

                            ,SUM(isnull([') + '],0)), 0) 

                            ,ur.Name



                    FROM    dbo.timesheet t

                    JOIN    dbo.UserRegistration ur ON t.TimeSheetID = ur.TimeSheetID



                    WHERE   month ='''+ @strMonth +''' 

                            AND [year] =' + Cast(@year1 AS VARCHAR(max))+ '

                            AND [Task ID] IN    (

                                                    SELECT  TaskID 

                                                    FROM    ManageTasks 

                                                    WHERE   TeamMemberUserID ='+ CAST(@UserID AS VARCHAR(MAX)) +'

                                                )'

The question is really unclear. It's not obvious the database structure that you are working with. Your problem looks as though it would be solved with a self-join but am only guessing.

Here's one possible (much simplified to show general principles) solution. In sqlite as I don't have SQL Server.

CREATE TABLE `timesheet` (

    `user_id`   INTEGER PRIMARY KEY AUTOINCREMENT,

    `user_name` TEXT,

    `task_type` INTEGER,

    `hours` INTEGER

);



delete from timesheet;

insert into timesheet (user_name, task_type, hours, user_id) values ('Billy', 1, 10, 1);

insert into timesheet (user_name, task_type, hours, user_id) values ('Billy', 2, 6, 1);

insert into timesheet (user_name, task_type, hours, user_id) values ('Johnny', 1, 7, 2);

insert into timesheet (user_name, task_type, hours, user_id) values ('Johnny', 2, 9, 2););



select a.user_name, a.hours as billable_hours, b.hours as non_billable_hours

from timesheet a 

 join timesheet b

  on a.user_id = b.user_id

  and b.task_type = 2

where a.task_type = 1