Click here to Skip to main content
14,928,959 members
Home / Discussions / Database
   

Database

 
GeneralRe: Logging Pin
cdpace28-Aug-09 11:25
Membercdpace28-Aug-09 11:25 
GeneralRe: Logging Pin
Ashfield29-Aug-09 3:57
MemberAshfield29-Aug-09 3:57 
GeneralRe: Logging Pin
εїзεїзεїз30-Aug-09 20:37
Memberεїзεїзεїз30-Aug-09 20:37 
GeneralRe: Logging Pin
Ashfield31-Aug-09 2:42
MemberAshfield31-Aug-09 2:42 
GeneralRe: Logging Pin
εїзεїзεїз5-Sep-09 22:18
Memberεїзεїзεїз5-Sep-09 22:18 
GeneralRe: Logging Pin
Ashfield6-Sep-09 21:19
MemberAshfield6-Sep-09 21:19 
AnswerRe: Logging Pin
εїзεїзεїз30-Aug-09 20:36
Memberεїзεїзεїз30-Aug-09 20:36 
QuestionHelp writing query Pin
michaelgr128-Aug-09 2:04
Membermichaelgr128-Aug-09 2:04 
Hello,

I need to write a query.
I have a table with many operators (can take them using a query) and for each of them i need to calculate it's success rate (count fail units query/count all units query). I can do the calculation of the success rate for every operator,
but how can i write a query that will loop through all the operators and show the success rate of each operator (in one table).

The operators and the units (count for fail and all the units) are located in one table in the DB.

The results will be as follows:
there will be an operator columns and success rate column. In the operator column it will write the operators from the table and in the second column it will write the success rate of each operator.

How can i do it?

I use the following query for the success rate (of one operator):
select (convert(float,(select count(distinct jobid)
from v_equipattrrep where ([assigndate] > '01/01/2009' or jobcompletiontime>'01/01/2009') and
[operator] like 'mgroiser' and
equipname like '%fib%' and equipname not like '%test%' and
iscabinet = 0 and utilcatdesc = 'Debug' and labid = '37' and jobresult='success')))/(select count(distinct jobid)
from v_equipattrrep where ([assigndate] > '01/01/2009' or jobcompletiontime>'01/01/2009') and
[operator] like 'mgroiser1' and
equipname like '%fib%' and equipname not like '%test%' and
iscabinet = 0 and utilcatdesc = 'Debug' and labid = '37' and jobresult is not null) *100

I tried to write as follows:
select (convert(float,sum(case when jobresult='success' then 1 else 0 end))/ count(distinct jobid)) * 100
from v_equipattrrep where ([assigndate] > '01/01/2009' or jobcompletiontime>'01/01/2009') and
equipname like '%fib%' and equipname not like '%test%' and
iscabinet = 0 and utilcatdesc = 'Debug' and labid = '37' and jobresult is not null
group by [operator]

But it doesn't work well because i muse use a distinct in the sum(case...) too.
AnswerRe: Help writing query Pin
Mycroft Holmes29-Aug-09 15:22
professionalMycroft Holmes29-Aug-09 15:22 
QuestionSQL:Problem in dynamic sql query [modified] Pin
xodeblack27-Aug-09 22:37
Memberxodeblack27-Aug-09 22:37 
AnswerRe: SQL:Problem in dynamic sql query Pin
Mycroft Holmes28-Aug-09 0:45
professionalMycroft Holmes28-Aug-09 0:45 
QuestionPlease suggest Pin
ME_Learner27-Aug-09 20:25
MemberME_Learner27-Aug-09 20:25 
AnswerRe: Please suggest Pin
Mycroft Holmes28-Aug-09 0:50
professionalMycroft Holmes28-Aug-09 0:50 
AnswerConsider Data Partioning Pin
David Mujica28-Aug-09 2:48
MemberDavid Mujica28-Aug-09 2:48 
Question## in sql server..? Pin
DIPAK@EMSYS27-Aug-09 19:57
MemberDIPAK@EMSYS27-Aug-09 19:57 
AnswerRe: ## in sql server..? Pin
Ashfield27-Aug-09 20:56
MemberAshfield27-Aug-09 20:56 
AnswerRe: ## in sql server..? Pin
Coding C#28-Aug-09 1:58
MemberCoding C#28-Aug-09 1:58 
Questiondesign questions - tables to support users Pin
Kimmmmy27-Aug-09 9:58
MemberKimmmmy27-Aug-09 9:58 
AnswerRe: design questions - tables to support users Pin
Luc Pattyn27-Aug-09 10:57
sitebuilderLuc Pattyn27-Aug-09 10:57 
GeneralRe: design questions - tables to support users Pin
Kimmmmy27-Aug-09 12:04
MemberKimmmmy27-Aug-09 12:04 
GeneralRe: design questions - tables to support users Pin
Luc Pattyn27-Aug-09 12:29
sitebuilderLuc Pattyn27-Aug-09 12:29 
GeneralRe: design questions - tables to support users Pin
Kimmmmy27-Aug-09 12:38
MemberKimmmmy27-Aug-09 12:38 
GeneralRe: design questions - tables to support users Pin
Mycroft Holmes27-Aug-09 14:34
professionalMycroft Holmes27-Aug-09 14:34 
GeneralRe: design questions - tables to support users Pin
Luc Pattyn27-Aug-09 15:25
sitebuilderLuc Pattyn27-Aug-09 15:25 
GeneralRe: design questions - tables to support users Pin
Mycroft Holmes27-Aug-09 16:04
professionalMycroft Holmes27-Aug-09 16:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.