Trouble with contradicting where clause
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
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
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
精彩评论