Comments by Member 11026392

Member 11026392 22-Aug-14 13:27pm View

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 ]

Member 11026392 22-Aug-14 13:07pm View

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 10:46am View

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

Member 11026392 22-Aug-14 10:32am View

I need the percentage to be all the sites combined for the overall total and the overall first job total

Member 11026392 22-Aug-14 9:53am View

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,

Member 11026392 22-Aug-14 8:27am View

I have never done percentages before so struggling

Member 11026392 22-Aug-14 8:24am View

I am stuck on how to implement the percentage side of things

Comments by Member 11026392 (Top 7 by date)