Reputation: 135
Pretty new here and could really use some help. I have a table with
TimeID(pk), DateEntry(date), EntryTiem(time),
ProjID(int), ProjName(varchar), Phone(bit),
Research(bit), Notes(varchar), ProjActive(bit),
TimeDateStamp(date).
I'm trying to return multiple rows of data into each ProjName but am not having any luck. Here is what I've come up with so far...
SELECT ProjName AS 'Project Name',
(SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
+ ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2)
FROM WorkTime
WHERE Phone = 0 AND Research = 0 AND EntryTime IS NOT NULL
) AS 'Total Time No PMRE',
(SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
+ ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2)
FROM WorkTime
WHERE Phone = 1 AND RESEARCH = 0 AND EntryTime IS NOT NULL
) AS 'Total Time Phone',
(SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
+ ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2)
FROM WorkTime
WHERE Phone = 0 AND RESEARCH = 1 AND EntryTime IS NOT NULL
) AS 'Total Time Research',
(SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
+ ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2)
FROM WorkTime
WHERE EntryTime IS NOT NULL
) AS 'TotalTime'
FROM WorkTime
WHERE EntryTime IS NOT NULL
GROUP BY ProjName
Which returns the following...
Project Name Total Time No PMRE Total Time Phone Total Time Research Total Time
AAAA 19:06:15 2:00:00 1:00:06 22:06:21
BBBB 19:06:15 2:00:00 1:00:06 22:06:21
CCCC 19:06:15 2:00:00 1:00:06 22:06:21
DDDD 19:06:15 2:00:00 1:00:06 22:06:21
Which is wrong. The amount in each column is the total for that condition with all of the ProjName added together instead of separated out individually.
This is what the output should be...
Project Name Total Time No PMRE Total Time Phone Total Time Research Total Time
AAAA 00:00:19 0:00:00 0:00:00 00:00:19
BBBB 00:00:04 0:00:00 0:00:00 00:00:04
CCCC 03:00:00 2:00:00 1:00:06 06:06:06
DDDD 16:05:52 0:00:00 0:00:00 16:05:52
Does anyone have any suggestions or could help point me in the right direction? Thanks!
Upvotes: 1
Views: 277
Reputation: 4854
I think that what you are looking for is a conditional SUM:
SELECT ProjName AS 'Project Name',
SUM(CASE WHEN (Phone = 0 AND Research = 0 AND EntryTime IS NOT NULL) THEN DATEDIFF(second, 0, EntryTime) ELSE NULL END) AS 'Total Time No PMRE',
SUM(CASE WHEN (Phone = 1 AND RESEARCH = 0 AND EntryTime IS NOT NULL) THEN DATEDIFF(second, 0, EntryTime) ELSE NULL END) AS 'Total Time Phone',
SUM(CASE WHEN (Phone = 0 AND RESEARCH = 1 AND EntryTime IS NOT NULL) THEN DATEDIFF(second, 0, EntryTime) ELSE NULL END) AS 'Total Time Research',
SUM(DATEDIFF(second, 0, EntryTime)) AS 'TotalTime'
FROM WorkTime
WHERE EntryTime IS NOT NULL
GROUP BY ProjName
Add your formatting code to the result of SUM and you will be fine
Answering your comment for the format, take your format "template":
CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
+ ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2)
and replace every occurence of SUM(DATEDIFF(second, 0, EntryTime)
for SUM(CASE WHEN (Phone = 0 AND Research = 0 AND EntryTime IS NOT NULL) THEN DATEDIFF(second, 0, EntryTime) ELSE NULL END)
Or, you can add the formats in a query that wraps the source query, like this:
SELECT CAST([Total Time No PMRE] / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST([Total Time No PMRE] / 60 % 60 AS varchar(2)),2)
+ ':' + RIGHT('0' + CAST(([Total Time No PMRE] % 60 AS varchar(2)), 2)
... -- The other columns
FROM (
SELECT ProjName AS 'Project Name',
SUM(CASE WHEN (Phone = 0 AND Research = 0 AND EntryTime IS NOT NULL) THEN DATEDIFF(second, 0, EntryTime) ELSE NULL END) AS 'Total Time No PMRE',
SUM(CASE WHEN (Phone = 1 AND RESEARCH = 0 AND EntryTime IS NOT NULL) THEN DATEDIFF(second, 0, EntryTime) ELSE NULL END) AS 'Total Time Phone',
SUM(CASE WHEN (Phone = 0 AND RESEARCH = 1 AND EntryTime IS NOT NULL) THEN DATEDIFF(second, 0, EntryTime) ELSE NULL END) AS 'Total Time Research',
SUM(DATEDIFF(second, 0, EntryTime)) AS 'TotalTime'
FROM WorkTime
WHERE EntryTime IS NOT NULL
GROUP BY ProjName
) AS ds
Hope this helps you
Upvotes: 1
Reputation: 15261
You need to correlate these subqueries to something in the outer query, probably the same thing you're grouping on:
SELECT ProjName AS 'Project Name',
(
SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
+ ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2)
FROM WorkTime
WHERE Phone = 0 AND Research = 0 AND EntryTime IS NOT NULL
AND ProjName = a.ProjName -- Correlate to outer query
) AS 'Total Time No PMRE',
(
SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
+ ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2)
FROM WorkTime
WHERE Phone = 1 AND RESEARCH = 0 AND EntryTime IS NOT NULL
AND ProjName = a.ProjName -- Correlate to outer query
) AS 'Total Time Phone',
(
SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
+ ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2)
FROM WorkTime
WHERE Phone = 0 AND RESEARCH = 1 AND EntryTime IS NOT NULL
AND ProjName = a.ProjName -- Correlate to outer query
) AS 'Total Time Research',
(
SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
+ ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2)
FROM WorkTime
WHERE EntryTime IS NOT NULL
AND ProjName = a.ProjName -- Correlate to outer query
) AS 'TotalTime'
FROM WorkTime a -- Add alias
WHERE EntryTime IS NOT NULL
GROUP BY ProjName
Upvotes: 0