Percentage Calculation
-
I need to calculate the percentage of a specific value from a table. The layout is below:
Facility
District
Division
EntryType
Series
Totals
1
2
3
DCM
2005-06
9
1
2
3
Group Edit
2005-06
5
2
5
6
DCM'
2005-07
8
Basically I need to get the percent of the specific EntryTypes for the specific Series. So instead of a whole numbers in Totals, i would return a percentage.
The current query is listed below:
SELECT
cast(C.LABORLEV3DSC + '(' + C.LABORLEV3NM + ')' as varchar(255))AS Facility,
cast(C.LABORLEV2DSC + '(' + C.LABORLEV2NM + ')' as varchar(255))AS District,
cast(C.LABORLEV1DSC + '(' + C.LABORLEV1NM + ')' as varchar(255))AS Division,
CASE(B.FUNCTSTR)
WHEN 'A' THEN 'Smart Scheduler'
WHEN 'B' THEN 'People Editor'
WHEN 'C' THEN 'DCM'
WHEN 'E' THEN 'Time Card Editor'
WHEN 'F' THEN 'Mobile Time'
WHEN 'G' THEN 'Group Edit'
WHEN 'I' THEN 'System Import'
WHEN 'L' THEN 'Labor Manager'
WHEN 'M' THEN 'Employee Maintenance'
WHEN 'P' THEN 'Time Clock'
WHEN 'S' THEN 'Schedule'
WHEN 'T' THEN 'Teletime (IVR)'
WHEN 'U' THEN 'Systems Upgrade'
WHEN 'W' THEN 'Workforce Web entry'
ELSE 'No value'
END AS EntryType,
CONVERT(CHAR(7),A.PUNCHDTM,120) As Series,
SUM(CASE WHEN A.PUNCHDTM is not null THEN 1 ELSE 0 END) AS EntryTypePercent
FROM PUNCHEVENT A
INNER JOIN LABORACCT C ON A.LABORACCTID=C.LABORACCTID
INNER JOIN DATASOURCE B ON A.DATASOURCEID=B.DATASOURCEID
WHERE A.DELETEDSW=0
AND C.LABORLEV3DSC IS NOT NULL
GROUP BY
C.LABORLEV3DSC, C.LABORLEV3NM,
C.LABORLEV2DSC, C.LABORLEV2NM,
C.LABORLEV1DSC, C.LABORLEV1NM,
(CASE(B.FUNCTSTR)
WHEN 'A' THEN 'Smart Scheduler'
WHEN 'B' THEN 'People Editor'
WHEN 'C' THEN 'DCM'
WHEN 'E' THEN 'Time Card Editor'
WHEN 'F' THEN 'Mobile Time'
WHEN 'G' THEN 'Group Edit'
WHEN 'I' THEN 'System Import'
WHEN 'L' THEN 'Labor Manager'
WHEN 'M' THEN 'Employee Maintenance'
WHEN 'P' THEN 'Time Clock'
WHEN 'S' THEN 'Schedule'
WHEN 'T' THEN 'Teletime (IVR)'
WHEN 'U' THEN 'Systems Upgrade'
WHEN 'W' THEN 'Workforce Web entry'
ELSE 'No value'
END ),
CONVERT(CHAR(7),A.PUNCHDTM,120)
ORDER BY Facility, Series
Any assistance is appreciated
-
Can you post a Table definition, and some sample data?
http://www.aspfaq.com/etiquette.asp?id=5006
When you say percentage, Percentage of what?
Is this what you mean?
Value, Percentage
9 %40
5 %23
8 %37
-
Here's the table design
CREATE TABLE [dbo].[PunchTrend] (
[Facility] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[District] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Division] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EntryType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Series] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Totals] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
The sampl data is listed below
Facility
District
Division
EntryType
Series
Totals
1
2
3
DCM
2005-06
9
1
2
3
Group Edit
2005-06
5
2
5
6
DCM'
2005-07
8
I would like to do the percentage as you suggested.
9 %40
5 %23
8 %37
Ex. %40 would be the percent of DCM (EntryTypes) for 2005-06 (Series).
Thanks for your help
-
Actually I think we need table defs, and sample data for tables you are querying.
PUNCHEVENT, LABORACCT, DATASOURCE
-
drop table PunchTrend
CREATE TABLE [dbo].[PunchTrend] (
[Facility] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[District] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Division] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EntryType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Series] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Totals] [int])
insert into PunchTrend
select 1,2,3,'DCM','2005-06',9
Union
select 1,2,3,'Group Edit','2005-06', 5
Union
select 2,5,6,'DCM','2005-07', 8
Union
select 1,2,3,'Group Edit','2005-07', 4
select Facility,District,Division,EntryType,Series,Totals, Cast(cast(Totals as decimal(6,4))/(Select cast(Sum(Totals) as decimal(6,4)) from PunchTrend b where a.Series = b.series) as Decimal (5,4)) as average.
from PunchTrend a
Order by Facility,District,Division,EntryType,Series
-
Thanks for pointing me in the right direction. Utilizing your logic, I modified my query as shown below:
SELECT
cast(C.LABORLEV3DSC + '(' + C.LABORLEV3NM + ')' as varchar(255))AS Facility,
cast(C.LABORLEV2DSC + '(' + C.LABORLEV2NM + ')' as varchar(255))AS District,
cast(C.LABORLEV1DSC + '(' + C.LABORLEV1NM + ')' as varchar(255))AS Division,
CASE(B.FUNCTSTR)
WHEN 'A' THEN 'Smart Scheduler'
WHEN 'B' THEN 'People Editor'
WHEN 'C' THEN 'DCM'
WHEN 'E' THEN 'Time Card Editor'
WHEN 'F' THEN 'Mobile Time'
WHEN 'G' THEN 'Group Edit'
WHEN 'I' THEN 'System Import'
WHEN 'L' THEN 'Labor Manager'
WHEN 'M' THEN 'Employee Maintenance'
WHEN 'P' THEN 'Time Clock'
WHEN 'S' THEN 'Schedule'
WHEN 'T' THEN 'Teletime (IVR)'
WHEN 'U' THEN 'Systems Upgrade'
WHEN 'W' THEN 'Workforce Web entry'
ELSE 'No value'
END AS EntryType,
CONVERT(CHAR(7),A.PUNCHDTM,120) As Series,
--SUM (CASE WHEN A.PUNCHDTM IS NOT NULL THEN 1 ELSE 0 END)
ROUND(
CAST(
CAST(SUM (CASE WHEN A.PUNCHDTM IS NOT NULL THEN 1 ELSE 0 END) AS DECIMAL(6,2))/
CAST( (select count(A.PUNCHDTM) from PUNCHEVENT A
INNER JOIN LABORACCT C ON A.LABORACCTID=C.LABORACCTID
INNER JOIN DATASOURCE B ON A.DATASOURCEID=B.DATASOURCEID
WHERE A.DELETEDSW=0
AND C.LABORLEV3DSC IS NOT NULL
)
AS DECIMAL(6,2))
AS DECIMAL(6,2)),2)as Average
FROM PUNCHEVENT A
INNER JOIN LABORACCT C ON A.LABORACCTID=C.LABORACCTID
INNER JOIN DATASOURCE B ON A.DATASOURCEID=B.DATASOURCEID
WHERE A.DELETEDSW=0
AND C.LABORLEV3DSC IS NOT NULL
GROUP BY
C.LABORLEV3DSC, C.LABORLEV3NM,
C.LABORLEV2DSC, C.LABORLEV2NM,
C.LABORLEV1DSC, C.LABORLEV1NM,
(CASE(B.FUNCTSTR)
WHEN 'A' THEN 'Smart Scheduler'
WHEN 'B' THEN 'People Editor'
WHEN 'C' THEN 'DCM'
WHEN 'E' THEN 'Time Card Editor'
WHEN 'F' THEN 'Mobile Time'
WHEN 'G' THEN 'Group Edit'
WHEN 'I' THEN 'System Import'
WHEN 'L' THEN 'Labor Manager'
WHEN 'M' THEN 'Employee Maintenance'
WHEN 'P' THEN 'Time Clock'
WHEN 'S' THEN 'Schedule'
WHEN 'T' THEN 'Teletime (IVR)'
WHEN 'U' THEN 'Systems Upgrade'
WHEN 'W' THEN 'Workforce Web entry'
ELSE 'No value'
END ),
CONVERT(CHAR(7),A.PUNCHDTM,120)
ORDER BY Facility, Series
-
Galahad,
It is ok if you want to leave it like that but I think it may be easier if you create a view with the thefinition of the original query and then perform the aggregate on the view. It will be simpler to read and mainatin (mind the people that may come after you or maybe it will be for your own good in the long run
)BTW the "average" column name should be Percentage
* Noel -
Good point, I would create a view, however w/ the amount of data that will be contained in the table (millions of rows) the requirement is aggregate all data first, insert into the table and then connect this table to a chart in Crystal.
Basically, this query will become a stored proc which will be run on a monthly basis to update the table's data for the current month.
Unfortunately, I can't reference a stored proc from Crystal due to the current Time Management System (Kronos) that the reports are generated from...if I could I would have used your sproc example from my earlier post.
Thanks for your advice,
Galahad
-
I am not a Crystal Reports expert but I am positive that you can use Stored procedures with it!
* Noel -
select Facility,District,Division,EntryType,Series,Totals, (Cast(cast(Totals as numeric(6,4))/(Select cast(Sum(Totals) as numeric(6,4)) from PunchTrend b) as numeric (5,4)) * 100.000) as Percentage
from PunchTrend a
Order by Facility,District,Division,EntryType,Series
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply