CODEWITHSUNDEEP
sql-serverdatabasesubquery
Jeremy Dodt
Jeremy Dodt

Reputation: 135

How to return multiple rows in sql from same table

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

Answers (2)

Agustin Meriles
Agustin Meriles

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

Edit

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

Tim Lehner
Tim Lehner

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

Related Questions