[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • 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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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