Total Pivot does not work - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Total Pivot does not work

Author

Topic

bearswent
Starting Member

20 Posts

Posted - 2009-06-29 : 19:39:41

SELECT CompanyName, [38] as B, [36] as R, [39] as P, [37] as B,
[40] as M, [41] as C, [42] as L, [45] as J, [53] as D,
[54] as I, TotalHours
FROM
(select SUM (Manhours) as TotalHours, Manhours,CompanyName, ServiceCEO.dbo.tblSchedules.TeamID
from ServiceCEO.dbo.tblSchedules, ServiceCEO.dbo.tblTeams, ServiceCEO.dbo.tblCustomers
where ServiceCEO.dbo.tblSchedules.TeamID = ServiceCEO.dbo.tblTeams.TeamID
and ServiceCEO.dbo.tblSchedules.CustomerID = ServiceCEO.dbo.tblCustomers.CustomerID
group by Manhours, ServiceCEO.dbo.tblSchedules.TeamID, ServiceCEO.dbo.tblCustomers.CompanyName ) p
PIVOT
(
SUM(Manhours)
FOR TeamID IN
([38], [36], [39], [37], [40], [41], [42], [45], [53], [54])
) AS pvt
order BY pvt.CompanyName

Problem:

Total hours column should show me total for every company across all employees, instead it shows time but it is not total for all employees. it almost randomly chooses time to put into total column.
Do not know what is the problem. Please help

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-30 : 03:19:30

[code]
try this once
SELECT CompanyName, [38] as B, [36] as R, [39] as P, [37] as B,
[40] as M, [41] as C, [42] as L, [45] as J, [53] as D,
[54] as I, TotalHours
FROM
(select SUM (Manhours)OVER (PARTITION BY s.TeamID, c.CompanyName ) as TotalHours,
Manhours,CompanyName, s.TeamID
from
ServiceCEO.dbo.tblSchedules s,
ServiceCEO.dbo.tblTeams t,
ServiceCEO.dbo.tblCustomers c
where S.TeamID = t.TeamID
and s.CustomerID = c.CustomerID ) p
PIVOT
(
SUM(Manhours)
FOR TeamID IN
([38], [36], [39], [37], [40], [41], [42], [45], [53], [54])
) AS pvt
order BY CompanyName
[/code]

bearswent
Starting Member

20 Posts

Posted - 2009-06-30 : 11:25:42

Thank you for an idea. I still end up having multiple entries for the same company, all jobs for one company should be combined into single row. Total for every employee work, but TotalHours is still not working
Please help

bearswent
Starting Member

20 Posts

Posted - 2009-06-30 : 11:58:08

This will add all jobs into single row for the same companies. Now, how would I get total for all employees for the same company?

SELECT CompanyName, [38] as B, [36] as R, [39] as P, [37] as B,
[40] as M, [41] as C, [42] as L, [45] as J, [53] as D,
[54] as I
FROM
(SELECT CompanyName, ServiceCEO.dbo.tblSchedules.TeamID, Manhours
FROM ServiceCEO.dbo.tblSchedules, ServiceCEO.dbo.tblTeams, ServiceCEO.dbo.tblCustomers
where ServiceCEO.dbo.tblSchedules.TeamID = ServiceCEO.dbo.tblTeams.TeamID
and ServiceCEO.dbo.tblSchedules.CustomerID = ServiceCEO.dbo.tblCustomers.CustomerID) p
PIVOT
(
SUM(Manhours)
FOR TeamID IN
([38], [36], [39], [37], [40], [41], [42], [45], [53], [54])
) AS pvt
ORDER BY pvt.CompanyName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-30 : 13:20:13

[code]
select CompanyName,
SUM(CASE WHEN ServiceCEO.dbo.tblSchedules.TeamID = 38 THEN Manhours ELSE 0 END) AS [38],
SUM(CASE WHEN ServiceCEO.dbo.tblSchedules.TeamID = 36 THEN Manhours ELSE 0 END) AS [36],
SUM(CASE WHEN ServiceCEO.dbo.tblSchedules.TeamID = 39 THEN Manhours ELSE 0 END) AS [39],
SUM(CASE WHEN ServiceCEO.dbo.tblSchedules.TeamID = 37 THEN Manhours ELSE 0 END) AS [37],
SUM(CASE WHEN ServiceCEO.dbo.tblSchedules.TeamID = 40 THEN Manhours ELSE 0 END) AS [40],
SUM(CASE WHEN ServiceCEO.dbo.tblSchedules.TeamID = 41 THEN Manhours ELSE 0 END) AS [41],
SUM(CASE WHEN ServiceCEO.dbo.tblSchedules.TeamID = 42 THEN Manhours ELSE 0 END) AS [42],
SUM(CASE WHEN ServiceCEO.dbo.tblSchedules.TeamID = 45 THEN Manhours ELSE 0 END) AS [45],
SUM(CASE WHEN ServiceCEO.dbo.tblSchedules.TeamID = 53 THEN Manhours ELSE 0 END) AS [53],
SUM(CASE WHEN ServiceCEO.dbo.tblSchedules.TeamID = 54 THEN Manhours ELSE 0 END) AS [54],
SUM(Manhours) as TotalHours
from ServiceCEO.dbo.tblSchedules, ServiceCEO.dbo.tblTeams, ServiceCEO.dbo.tblCustomers
where ServiceCEO.dbo.tblSchedules.TeamID = ServiceCEO.dbo.tblTeams.TeamID
and ServiceCEO.dbo.tblSchedules.CustomerID = ServiceCEO.dbo.tblCustomers.CustomerID
group by ServiceCEO.dbo.tblCustomers.CompanyName
[/code]

bearswent
Starting Member

20 Posts

Posted - 2009-06-30 : 13:27:36

Great thank you. it works great!!!
two more questions:

how do I sort by company (A-Z)
how can I put total hours for all companies for each employee, so total will be present at the end of companies list?

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-30 : 13:32:06

bearswent
Starting Member

20 Posts

Posted - 2009-06-30 : 13:46:30

second questions relates to this:

Compnayname [38],[36],[39],[37],[40],[41],[42],[45],[53],[54] Total
Comp 1
Comp 2
Comp 3
Comp 4
etc
CompTotal Total Total Total Total etc

so total per each employee but for all companies

thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-30 : 14:00:54

bearswent
Starting Member

20 Posts

Posted - 2009-06-30 : 14:13:22

great thank you
is there a way to control where total shows up?
It shows at the top, can it show at the bottom?
How would I replace Null value under companyname
with rollup it shows as:
Compnayname [38],[36],[39],[37],[40],[41],[42],[45],[53],[54] Total
NULL total total etc total

I want to replace that NULL with say Grand_Total

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-30 : 14:16:03

use COALESCE() or ISNULL() for replacing null

bearswent
Starting Member

20 Posts

Posted - 2009-06-30 : 14:35:22

should not this work?
CompanyName = case CompanyName when '<NULL>' then 'grandtotal' else [Companyname] end

but it does not
thank you

bearswent
Starting Member

20 Posts

Posted - 2009-06-30 : 16:21:19

I have this store procedure. When I give it begging date and end date in Crystal reports it does not update totals. It seems like it look at time range provided and pulls every company that had any work during that period but except limiting output to only that time range, it gives total for all work ever done for that company? Any ideas

ALTER procedure [dbo].[M]

(
@startDateBegin varchar(10)= 'ALL',
@startDateEnd varchar(10) = 'ALL'
)
AS
select CompanyName,
SUM(CASE WHEN ServiceCEO.dbo.tblSchedules.TeamID = 38 THEN Manhours ELSE 0 END) AS [B],
SUM(CASE WHEN ServiceCEO.dbo.tblSchedules.TeamID = 36 THEN Manhours ELSE 0 END) AS [R],
SUM(CASE WHEN ServiceCEO.dbo.tblSchedules.TeamID = 39 THEN Manhours ELSE 0 END) AS [P],
SUM(CASE WHEN ServiceCEO.dbo.tblSchedules.TeamID = 37 THEN Manhours ELSE 0 END) AS [B],
SUM(CASE WHEN ServiceCEO.dbo.tblSchedules.TeamID = 40 THEN Manhours ELSE 0 END) AS [M],
SUM(CASE WHEN ServiceCEO.dbo.tblSchedules.TeamID = 41 THEN Manhours ELSE 0 END) AS [C],
SUM(CASE WHEN ServiceCEO.dbo.tblSchedules.TeamID = 42 THEN Manhours ELSE 0 END) AS [L],
SUM(CASE WHEN ServiceCEO.dbo.tblSchedules.TeamID = 45 THEN Manhours ELSE 0 END) AS [J],
SUM(CASE WHEN ServiceCEO.dbo.tblSchedules.TeamID = 53 THEN Manhours ELSE 0 END) AS [D],
SUM(CASE WHEN ServiceCEO.dbo.tblSchedules.TeamID = 54 THEN Manhours ELSE 0 END) AS [I],
SUM(Manhours) as TotalHours
from ServiceCEO.dbo.tblSchedules, ServiceCEO.dbo.tblTeams, ServiceCEO.dbo.tblCustomers
where ServiceCEO.dbo.tblSchedules.TeamID = ServiceCEO.dbo.tblTeams.TeamID
and ServiceCEO.dbo.tblSchedules.CustomerID = ServiceCEO.dbo.tblCustomers.CustomerID
and (ServiceCEO.dbo.tblSchedules.EventTypeID = '12'
or ServiceCEO.dbo.tblSchedules.EventTypeID = '11'
and ServiceCEO.dbo.tblSchedules.StartDate >= @startDateBegin
and ServiceCEO.dbo.tblSchedules.StartDate <= @startDateEnd)
group by ServiceCEO.dbo.tblCustomers.CompanyName with rollup
order by ServiceCEO.dbo.tblCustomers.CompanyName

bearswent
Starting Member

20 Posts

Posted - 2009-06-30 : 16:28:41

disregard store procedure, I found the error but still need help with

CompanyName = case CompanyName when '<NULL>' then 'grandtotal' else [Companyname] end

Thank you

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-01 : 04:52:50

try any one of these
select case when companyname is null then grandtotal else companyname end as companyname,
............................

select isnull(companyname,grandtotal) or coalesce(companyname,grandtotal)

bearswent
Starting Member

20 Posts

Posted - 2009-07-01 : 12:28:57

select case when companyname is null then grandtotal else companyname end as companyname,

worked

thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 12:33:08

<NULL> is not same as NULL. first one is stored as a string value whereas second one is not regarded as a value under default conditions. It just represents condition lack of defined value.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources