Click here to Skip to main content
15,072,209 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have posted some script below which works out the totals

SQL
SELECT *
FROM (
SELECT cast([Id] AS VARCHAR(20)) AS ID
,[Site]
,convert(VARCHAR(20), [jobdate], 101) AS [job date]
,[submittime]
,[totalfirstjobs]
,[totalSecondJobs]
,[totalThirdjobs]
,[nightwork]
,[totalfirstjobs] + [totalsecondjobs] + [totalthirdjobs] + [nightwork] AS [Total all Jobs]
FROM site_total
WHERE convert(VARCHAR(20), [jobdate], 101) = convert(VARCHAR(20), getdate(), 101)

UNION

SELECT 'Totals'
,NULL
,convert(VARCHAR(20), [jobdate], 101)
,NULL
,sum(totalfirstjobs)
,sum(totalsecondjobs)
,sum(totalthirdjobs)
,sum(nightwork)
,sum(totalfirstjobs) + sum(totalsecondjobs) + sum(totalthirdjobs) + sum(nightwork)
FROM site_total
GROUP BY [jobdate]
) tbla

where cast([job Date] as date) =cast(getdate() as date)
ORDER BY [job Date ]

What i need to do is get a percentage for the first jobs, this needs to be an extra colum in the table below is how it needs to be worked out

I have made an error its got to be
total overall 1st Jobs divided by total overall jobs times by 100 = %

I just need some way of getting the percentage using the above method not working with percentage so hence why asking for help on implementing this.
Posted
Updated 22-Aug-14 4:47am
v4
Comments
OriginalGriff 22-Aug-14 7:54am
   
And?
What have you tried?
Where are you stuck?
What help do you need?
Member 11026392 22-Aug-14 8:24am
   
I am stuck on how to implement the percentage side of things
Member 11026392 22-Aug-14 8:27am
   
I have never done percentages before so struggling
PhilLenoir 22-Aug-14 9:45am
   
It's still npt clear to me what you exactly want want: Percentages on each side of the union? (so percentage of "firstjobs" against jobs for that "jobdate" and percentage by all sites) or percentage of jobs at all sites (harder). What are the data types of your "jobs" columns?
Member 11026392 22-Aug-14 9:53am
   
What i need is the percentage of the total number of first job for all the sites using the below format.
Take the total number of jobs divide by 100 the multiply by the number of first jobs this will give you the first job percentage.

This is how the sql is setup
[Id] INT IDENTITY (1, 1) NOT NULL,
[site] VARCHAR (50) NULL,
[jobdate] DATETIME NULL,
[submittime] VARCHAR (50) NULL,
[totalFirstJobs] INT NULL,
[totalSecondJobs] INT NULL,
[totalThirdJobs] INT NULL,
[nightwork] INT NULL,
PhilLenoir 22-Aug-14 10:01am
   
Sorry if I seem dense, but you say "for all sites", do you mean "for each site". Let's give an example: lets say there are three sites and on a given date each site has one of each type of job. Do you wnat an answer of 33% or 11%?
Member 11026392 22-Aug-14 10:32am
   
I need the percentage to be all the sites combined for the overall total and the overall first job total
PhilLenoir 22-Aug-14 10:47am
   
I'm sorry, the language you are using is not precise enough. If you can indicate 11 or 33, give a concrete example of your own or show your attempts I can't give you an exact answer.
Member 11026392 22-Aug-14 10:46am
   
I think i have done an error, its got to be total overall 1st Jobs divided by total overall jobs times by 100 = %

1 solution

I think I understand you now. The big issues here are that the data types are ints and nullable, so if there are nulls, the results returned will be null and without using casts (to float) the expression is not commutative.

I'll assume that you don't need to any decimal places (if you do, cast all expressions to floats) and the sums are never null (if they are, use ISNULL function):

SELECT *
FROM (
SELECT cast([Id] AS VARCHAR(20)) AS ID
,[Site]
,convert(VARCHAR(20), [jobdate], 101) AS [job date]
,[submittime]
,[totalfirstjobs]
,[totalSecondJobs]
,[totalThirdjobs]
,[nightwork]
,[totalfirstjobs] + [totalsecondjobs] + [totalthirdjobs] + [nightwork] AS [Total all Jobs]
,(ISNULL([totalfirstjobs],0)*100)/(ISNULL([totalfirstjobs],0) + ISNULL([totalsecondjobs],0) + ISNULL([totalthirdjobs],0) + [nightwork]) AS percentfirstjobs
FROM site_total
WHERE convert(VARCHAR(20), [jobdate], 101) = convert(VARCHAR(20), getdate(), 101)

UNION

SELECT 'Totals'
,NULL
,convert(VARCHAR(20), [jobdate], 101)
,NULL
,sum(totalfirstjobs)
,sum(totalsecondjobs)
,sum(totalthirdjobs)
,sum(nightwork)
,sum(totalfirstjobs) + sum(totalsecondjobs) + sum(totalthirdjobs) + sum(nightwork)
,(sum([totalfirstjobs])*100)/(sum([totalfirstjobs]) + sum([totalsecondjobs]) + sum([totalthirdjobs]) + [nightwork]) AS percentfirstjobs
FROM site_total
GROUP BY [jobdate]
) tbla

where cast([job Date] as date) =cast(getdate() as date)
ORDER BY [job Date ]

Please note that I've not dealt with a divide by zero problem if that's a possibility.
   
Comments
Member 11026392 22-Aug-14 13:07pm
   
I am getting this error

Msg 8120, Level 16, State 1, Line 27
Column 'site_total.nightwork' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8155, Level 16, State 2, Line 32
No column name was specified for column 1 of 'tbla'.
Msg 8155, Level 16, State 2, Line 32
No column name was specified for column 2 of 'tbla'.
Msg 8155, Level 16, State 2, Line 32
No column name was specified for column 3 of 'tbla'.
Msg 8155, Level 16, State 2, Line 32
No column name was specified for column 4 of 'tbla'.
Msg 8155, Level 16, State 2, Line 32
No column name was specified for column 5 of 'tbla'.
Msg 8155, Level 16, State 2, Line 32
No column name was specified for column 6 of 'tbla'.
Msg 8155, Level 16, State 2, Line 32
No column name was specified for column 7 of 'tbla'.
Msg 8155, Level 16, State 2, Line 32
No column name was specified for column 8 of 'tbla'.
Msg 8155, Level 16, State 2, Line 32
No column name was specified for column 9 of 'tbla'.
Msg 207, Level 16, State 1, Line 32
Invalid column name 'job Date'.
Msg 207, Level 16, State 1, Line 33
Invalid column name 'job Date '.
Member 11026392 22-Aug-14 13:27pm
   
Changed the code to the following and it works
SELECT *
FROM (
SELECT cast([Id] AS VARCHAR(20)) AS ID
,[Site]
,convert(VARCHAR(20), [jobdate], 101) AS [job date]
,[submittime]
,[totalfirstjobs]
,[totalSecondJobs]
,[totalThirdjobs]
,[nightwork]
,[totalfirstjobs] + [totalsecondjobs] + [totalthirdjobs] + [nightwork] AS [Total all Jobs]
,(ISNULL([totalfirstjobs],0)*100)/(ISNULL([totalfirstjobs],0) + ISNULL([totalsecondjobs],0) + ISNULL([totalthirdjobs],0) + ISNULL([nightwork],0)) AS percentfirstjobs
FROM site_total
WHERE convert(VARCHAR(20), [jobdate], 101) = convert(VARCHAR(20), getdate(), 101)

UNION

SELECT 'Totals'
,NULL
,convert(VARCHAR(20), [jobdate], 101)
,NULL
,sum(totalfirstjobs)
,sum(totalsecondjobs)
,sum(totalthirdjobs)
,sum(nightwork)
,sum(totalfirstjobs) + sum(totalsecondjobs) + sum(totalthirdjobs) + sum(nightwork)
,(sum([totalfirstjobs])*100)/(sum([totalfirstjobs]) + sum([totalsecondjobs]) + sum([totalthirdjobs]) + sum([nightwork])) AS percentfirstjobs
FROM site_total
GROUP BY [jobdate]
) tbla

where cast([job Date] as date) =cast(getdate() as date)
ORDER BY [job Date ]

PhilLenoir 25-Aug-14 9:21am
   
Great, I had assumed that your original query was OK without the percentages. The important thing to understand here is that the math is not commutative because SQL Server casrs as it goes: Let's suppose we have 10 out of 100 = 10%. If we divide 10 by 100, cast to an integer the answer is zero. If we then multiply by 100 we get zero. However, if we multiply 10 by 100 we get 1000. Finally dividing by 100, we get 10 (percent) = the answer we're after!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900