add a totals column to a dynamic crosstab query

Mykal73
Mykal73 used Ask the Experts™
on
I have 2 queries, one lists out the employees by name and the total of things they have accomplished for a month(just a simple count). the second query is a dynamic crosstab that lists the same employees as the first query, but lists out what they did by day. I want to add the first query and the crosstab query together so I have a totals column in my crosstab. Is there a way to do this in SQL?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
Hello Mykal73,

Please post the SQL for both queries.

Regards,

Patrick

Author

Commented:
Here's the whole script.

/***************************************************************************************
*                                                MASTER QUERY
**************************************************************************************/

SELECT accountNR, transDate, settlementDate, oi_cat_code, transAmt, si.CaseID, tranDescription,
      tranCode, comments, employeename, substring(accountNR, 1,6) as BINS, regionID
into #master
FROM starITEMS si
      LEFT JOIN Starusers u on si.employeeID = u.employeeID
inner JOIN (SELECT caseID FROM cancelUnworked WHERE region = 'ms' AND cancelUnworked = 'unworked') as t
ON si.caseID = t.caseID
WHERE regionID = 'mssn'





CREATE TABLE #ColTemp (settlement varchar(20))

INSERT INTO #ColTemp (settlement)
SELECT DISTINCT t.settlementDAte
FROM #master t
ORDER BY t.settlementDate


DECLARE @cols2 NVARCHAR(2000)
SELECT  @cols2 = COALESCE(@cols2 + ',','')+ '[' + settlement +']'
FROM    #colTemp



/*
SELECT AssignedTo, count(caseID) as totals
FROM(
SELECT employeeName as assignedTo,      t1.settlement,caseID  
            FROM #colTemp as t1
                        JOIN #master as t2 on t1.settlement = t2.settlementDate)p
GROUP by assignedTo
ORDER BY aSSIGNEDtO
*/


declare @query nvarchar(4000)
SET @query = ''
SET @query = N' SELECT employeeName, ' + @cols2
                        + 'FROM (SELECT employeeName,
                        t1.settlement,caseID  FROM #colTemp as t1
                        JOIN #master as t2 on t1.settlement = t2.settlementDate
                        ) p
            PIVOT
            (
                  count([caseID])
                  FOR settlement IN
                  ( '+ @cols2 +' )
            ) AS pvt order by employeeName'
exec(@query)

drop table #master
drop table #colTemp
Hamed NasrRetired IT Professional

Commented:
Use the cross tab
Union
the totals query

Make shre to have the same column numbers in both. You may specify the column headers in the crosstab query.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

SharathData Engineer

Commented:
Post the result of both your queries and the expected result.
Senior Database Architect
Commented:
Try this, I put the two queries together joined on EmployeeName/AssignedTo.
/***************************************************************************************
*                                                MASTER QUERY
**************************************************************************************/

SELECT accountNR, transDate, settlementDate, oi_cat_code, transAmt, si.CaseID, tranDescription, 
      tranCode, comments, employeename, substring(accountNR, 1,6) as BINS, regionID
into #master
FROM starITEMS si
      LEFT JOIN Starusers u on si.employeeID = u.employeeID
inner JOIN (SELECT caseID FROM cancelUnworked WHERE region = 'ms' AND cancelUnworked = 'unworked') as t
ON si.caseID = t.caseID
WHERE regionID = 'mssn'





CREATE TABLE #ColTemp (settlement varchar(20))

INSERT INTO #ColTemp (settlement)
SELECT DISTINCT t.settlementDAte
FROM #master t
ORDER BY t.settlementDate


DECLARE @cols2 NVARCHAR(2000)
SELECT  @cols2 = COALESCE(@cols2 + ',','')+ '[' + settlement +']'
FROM    #colTemp



/*
SELECT AssignedTo, count(caseID) as totals 
FROM(
SELECT employeeName as assignedTo,      t1.settlement,caseID  
            FROM #colTemp as t1
                        JOIN #master as t2 on t1.settlement = t2.settlementDate)p
GROUP by assignedTo
ORDER BY aSSIGNEDtO
*/


declare @query nvarchar(4000)
SET @query = ''
SET @query = N'  SELECT employeeName, ' + @cols2
                        + ', q2.Totals 
                        FROM (SELECT employeeName, ' + @cols2
                        + 'FROM (SELECT employeeName, 
                        t1.settlement,caseID  FROM #colTemp as t1
                        JOIN #master as t2 on t1.settlement = t2.settlementDate
                        ) p
            PIVOT 
            (
                  count([caseID])
                  FOR settlement IN
                  ( '+ @cols2 +' )
            ) AS pvt ) q1
INNER JOIN (SELECT AssignedTo, count(caseID) as totals 
FROM(
SELECT employeeName as assignedTo,      t1.settlement,caseID  
            FROM #colTemp as t1
                        JOIN #master as t2 on t1.settlement = t2.settlementDate)p
GROUP by assignedTo
) q2 on q1.employeeName = q2.AssignedTo
order by employeeName'
exec(@query)

drop table #master
drop table #colTemp

Open in new window

Kevin CrossChief Technology Officer
Most Valuable Expert 2011
Commented:
(Sharath, sorry if this is duplicate of your post, but was created earlier but I had difficulty posting)

I agree with that solution as it is what I would propose. For a little more direction given you are dealing with dynamic query here, I would use a common table expression and then you can do something like this:

declare @query nvarchar(4000)
SET @query = ''
SET @query = N';WITH t AS (SELECT employeeName, ' + @cols2
+ ' FROM (SELECT employeeName,
t1.settlement,caseID FROM #colTemp as t1
JOIN #master as t2 on t1.settlement = t2.settlementDate
) p
PIVOT
(
count([caseID])
FOR settlement IN
( '+ @cols2 +' )
) AS pvt order by employeeName)' +
' SELECT employeeName, ' + @cols2
+ ' FROM t UNION SELECT ''Totals'', ' + @cols2SUM
+ ' FROM t;'
exec(@query)

As yosu can see, you will need an additional collection of the columns called @col2SUM -- this variable will have the same column names as @cols2 but with SUM(...) function surrounding them.

DECLARE @cols2 NVARCHAR(2000), @cols2SUM NVARCHAR(2000);
SELECT @cols2 = COALESCE(@cols2 + ',','')+ '[' + settlement +']'
, @cols2SUM = COALESCE(@cols2SUM + ',','')+ 'SUM([' + settlement +'])'
FROM #colTemp

Hope that helps!
SharathData Engineer

Commented:
Kevin - I am not sure which "solution" you are referring? I have not yet posted any "solution" to this question so far :)

I think you are referring CGLuttrell's post.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
I sure am.  :) Having problems with my eye sight as well as my internet connection, apparently.

Author

Commented:
thank you so much, I knew it was possible but I couldn't get my head wrapped around it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial