• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

Add another SUM column to a PIVOT table ?

This is my pivot query. Is is possible to add another sum column ??

SET @sql='SELECT jobID,taskid,' + @cols  + ',Description,TenderID FROM
(SELECT jobID,taskid,CONVERT(NVARCHAR,StartTime,103) as StartTime,Hours,Description,TenderId FROM Timesheet
WHERE CONVERT(NVARCHAR,StartTime,101) BETWEEN ''' + CONVERT(NVARCHAR,@dateFromReset,101) + ''' AND '''
+  CONVERT(NVARCHAR,@dateTo,101) + ''')o  PIVOT(SUM(Hours) FOR StartTime IN (' + @cols+  ')) as PIVOTTABLE'
0
arcross
Asked:
arcross
  • 5
  • 3
1 Solution
 
tigin44Commented:
what do you want to sum?
0
 
arcrossAuthor Commented:
another field called overtime for the same columns that HOURS is being summarized
0
 
tigin44Commented:
I think this may guide you
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
tigin44Commented:
here is the code
SET @sql='SELECT jobID,taskid,' + @cols  + ',Description,TenderID 
FROM 
(SELECT jobID,taskid,CONVERT(NVARCHAR,StartTime,103) as StartTime,Hours,Description,TenderId 
FROM Timesheet
WHERE CONVERT(NVARCHAR,StartTime,101) BETWEEN ''' + CONVERT(NVARCHAR,@dateFromReset,101) + ''' AND ''' +  CONVERT(NVARCHAR,@dateTo,101) + ''')o  
PIVOT(SUM(Hours) FOR StartTime IN (' + @cols+  ')) as PIVOTTABLE' +
' UNION ' +
'SELECT jobID,taskid,' + @cols  + ',Description,TenderID 
FROM 
(SELECT jobID,taskid,CONVERT(NVARCHAR,StartTime,103) as StartTime,Hours,Description,TenderId 
FROM Timesheet
WHERE CONVERT(NVARCHAR,StartTime,101) BETWEEN ''' + CONVERT(NVARCHAR,@dateFromReset,101) + ''' AND ''' +  CONVERT(NVARCHAR,@dateTo,101) + ''')o  
PIVOT(SUM(overtime) FOR StartTime IN (' + @cols+  ')) as PIVOTTABLE'

Open in new window

0
 
arcrossAuthor Commented:
thanks for that...but
Is it possible to show it in the same row? even duplicating the columns...
showing them in two rows would give lots of issues later on...
0
 
tigin44Commented:
h1mmm this may help you..
SET @sql=' SELECT * FROM ' +
'(SELECT jobID,taskid,' + @cols  + ',Description,TenderID 
FROM 
(SELECT jobID,taskid,CONVERT(NVARCHAR,StartTime,103) as StartTime,Hours,Description,TenderId 
FROM Timesheet
WHERE CONVERT(NVARCHAR,StartTime,101) BETWEEN ''' + CONVERT(NVARCHAR,@dateFromReset,101) + ''' AND ''' +  CONVERT(NVARCHAR,@dateTo,101) + ''')o  
PIVOT(SUM(Hours) FOR StartTime IN (' + @cols+  ')) as PIVOTTABLE ) AS P1' +
' INNER JOIN '
'(SELECT jobID,taskid,' + @cols  + ',Description,TenderID 
FROM 
(SELECT jobID,taskid,CONVERT(NVARCHAR,StartTime,103) as StartTime,Hours,Description,TenderId 
FROM Timesheet
WHERE CONVERT(NVARCHAR,StartTime,101) BETWEEN ''' + CONVERT(NVARCHAR,@dateFromReset,101) + ''' AND ''' +  CONVERT(NVARCHAR,@dateTo,101) + ''')o  
PIVOT(SUM(overtime) FOR StartTime IN (' + @cols+  ')) as PIVOTTABLE) AS P2 ON P1.jobID = P2.jobID AND P1.taskid = P2.taskid '

Open in new window

0
 
arcrossAuthor Commented:
i get
Incorrect syntax near '(SELECT jobID,taskid,'...
the right after the INNER JOIN
0
 
tigin44Commented:
I missed  a + after the
' INNER JOIN '
should be
' INNER JOIN ' +

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now