CODEWITHSUNDEEP
sqlsql-serversql-server-2008pivot-table
dynamicuser
dynamicuser

Reputation: 1552

SQL SUM in PIVOT that uses MAX

I am trying to get the SUM of 2 or more TIME fields within my PIVOT table, however because the SUM does not work with characters (converted so I can show EstimatedTime / ActualTime), I'm having difficulty.

The code below - 

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + ID + ',' + QUOTENAME(Name)
                  FROM JobPhases
                  FOR XML PATH(''), TYPE
                 ).value('.', 'NVARCHAR(MAX)') 
                    , 1, 1, '');

SELECT @query = 'SELECT * FROM    
(   
    SELECT j.JobID, c.Registration as ''Reg.'', p.Name, 
    CAST(MAX(j.EstimatedTime) as VARCHAR(MAX)) + ''/'' +
    CAST(MAX(j.ActualTime) as VARCHAR(MAX)) as [x]
    FROM    JobDetails  AS j
    INNER JOIN JobPhases p ON p.ID = j.PhaseId
    INNER JOIN Jobs job on job.ID = j.JobID
    INNER JOIN Cars c on job.CarID = c.ID
    WHERE (job.Status = 1 or job.Status = 0)
    GROUP BY c.Registration, p.Name, j.JobID
) JobDetails
PIVOT
(   MAX(x)
    FOR Name IN (' + @cols + ')
) pvt'

execute(@query);

Generates - 

JobID | Reg.     | P13$ | Repair and Reshape                | P15$ | Refit Stripped Parts
1065  | BJ11 2VT | NULL | 01:00:00.0000000/01:54:10.5387526 | NULL | NULL

Tables - 

**JobDetails**
ID - PK Auto increment
JobID - Int (Joined to Jobs table)
PhaseID - String (joined to JobPhases table)
EstimatedTime - time(7)
ActualTime time(7)

**JobPhases****
ID - PK String
Name - VarChar(150)

The problem in this example is there are 2 JobDetails for JobID 1065 - 

ID | JobID | PhaseID | EstimatedTime    | ActualTime 
25 | 1065  | P13$    | 01:00:00.0000000 | 01:54:10.5387526
26 | 1065  | P13$    | 00:30:00.0000000 | 00:00:00.0000000

So, the correct result should be (Note the 1:30 in the Repair & Reshape) - 

JobID | Reg.     | P13$ | Repair and Reshape                | P15$ | Refit Stripped Parts
1065  | BJ11 2VT | NULL | 01:30:00.0000000/01:54:10.5387526 | NULL | NULL

Any ideas how I can get the total EstimatedTime for all rows for each phase id?

Thanks!

Upvotes: 1

Views: 919

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180947

Summing TIME columns isn't straight forward, what you need is to rewrite your line;

CAST(MAX(j.EstimatedTime) as VARCHAR(MAX)) + ''/'' +

to something like (the untested)

CAST(CAST(DATEADD(ms, SUM(DATEDIFF(ms, '0:00:00', j.EstimatedTime)), 
                 '00:00:00') AS TIME) AS VARCHAR(MAX)) + ''/'' +

Upvotes: 1

Related Questions