Trouble with contradicting where clause

Question

Hi,

I am trying to display what each user has spend their time doing for the week(either internal or external work) but the time is all on the same column on the table, is it possible to split it into 2 different columns and still have it so that it only shows each user once not each time they entered time which could be multiple times throughout the week.

The SQL below gives me each users tracked time for the week but internal and external on different rows.

SELECT SUM(FilteredMag_Time.mag_hoursspent) AS Time, 
       FilteredSystemUser.fullname, 
       FilteredMag_project.mag_typename
  FROM FilteredSystemUser 
INNER JOIN FilteredMag_Task 
INNER JOIN FilteredMag_project ON FilteredMag_Task.mag_projectid = FilteredMag_project.mag_projectid 
INNER JOIN FilteredMag_Time ON FilteredMag_Task.mag_taskid = FilteredMag_Time.mag_taskid 
                            ON FilteredSystemUser.systemuserid = FilteredMag_Time.createdby
     WHERE (FilteredMag_Time.mag_starttime BETWEEN DATEADD(dd, - (DATEPART(dw, GETDATE()) - 1), GETDATE()) 
                                               AND DATEADD(dd, - (DATEPART(dw, GETDATE()) - 7), GETDATE()))
GROUP BY FilteredSystemUser.fullname, FilteredMag_project.mag_typename
ORDER BY FilteredSystemUser.fullname

Here is an example of the current output.

Time                fullname             mag_typename
------------------ --------------------- -------------------------
1.2500000000        David Sutton        External
8.2500000000        Gayan Perera        External
9.0000000000        Paul Nieuwelaar     Internal
14.8700000000       Roshan Mehta        External
6.0000000000        Roshan Mehta        Internal
2.7800000000        Simon Phillips      External
4.6600000000        Simon Phillips      Internal

Answer 1

You can make use of SQL Server PIVOT.

Something like

DECLARE @Table TABLE(
        userID INT,
        typeID VARCHAR(20),
        TimeSpent FLOAT
)

INSERT INTO @Table SELECT 1, 'INTERNAL', 1
INSERT INTO @Table SELECT 2, 'INTERNAL', 1
INSERT INTO @Table SELECT 1, 'INTERNAL', 1
INSERT INTO @Table SELECT 1, 'INTERNAL', 1
INSERT INTO @Table SELECT 2, 'EXTERNAL', 3
INSERT INTO @Table SELECT 1, 'EXTERNAL', 3

SELECT  *
FROM 
(
    SELECT  userID, typeID, TimeSpent
    FROM    @Table
) s
PIVOT   (SUM(TimeSpent) FOR typeID IN ([INTERNAL],[EXTERNAL])) pvt

Output:

userID      INTERNAL               EXTERNAL
----------- ---------------------- ----------------------
1           3                      3
2           1                      3

Answer 2

Assuming FilteredMag_project.mag_typename is 'INTERNAL' or 'EXTERNAL', try the following:

SELECT SUM(CASE FilteredMag_project.mag_typename 
                WHEN 'INTERNAL' THEN FilteredMag_Time.mag_hoursspent
                ELSE 0 END) AS InternalTime, 
       SUM(CASE FilteredMag_project.mag_typename 
                WHEN 'EXTERNAL' THEN FilteredMag_Time.mag_hoursspent
                ELSE 0 END) AS ExternalTime, 
       FilteredSystemUser.fullname
  FROM FilteredSystemUser 
INNER JOIN FilteredMag_Task 
INNER JOIN FilteredMag_project ON FilteredMag_Task.mag_projectid = FilteredMag_project.mag_projectid 
INNER JOIN FilteredMag_Time ON FilteredMag_Task.mag_taskid = FilteredMag_Time.mag_taskid 
                            ON FilteredSystemUser.systemuserid = FilteredMag_Time.createdby
     WHERE (FilteredMag_Time.mag_starttime BETWEEN DATEADD(dd, - (DATEPART(dw, GETDATE()) - 1), GETDATE()) 
                                               AND DATEADD(dd, - (DATEPART(dw, GETDATE()) - 7), GETDATE()))
GROUP BY FilteredSystemUser.fullname
ORDER BY FilteredSystemUser.fullname