Help in writing a query - 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)

Help in writing a query

Author

Topic

michaelgr
Starting Member

9 Posts

Posted - 2009-08-26 : 04:36:37

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.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-26 : 05:35:23

You should give us:
- table structure
- sample data
- wanted output

No, you're never too old to Yak'n'Roll if you're too young to die.

michaelgr
Starting Member

9 Posts

Posted - 2009-08-26 : 06:12:33

Hi,
I already write the wanted output in the first message.
WHat do you mean table structure and how can i write it here? and also the sample data?

The table has column of operator names and a column of units names and a column saying if the work succeed or not for each unit (for each user the query is:
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

But how can i do a loop thorough all the operators and count their success rate?

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-08-26 : 08:00:52

Hi,
Like this u can specify the problem and reqd output
Hi,
I want to have union of two tables like this without using UNION operator directly and without using my following query!
Please post other method (query).

--My Query
SELECT ID=ISNULL(a.ID,b.ID),
[Value]=ISNULL(a.[value],b.[value])
FROM @A a
FULL OUTER JOIN @B b ON a.id=b.id

/*
ID Value
----------- -------------------------
1 v1
2 v2
3 v3
4 v4
5 v5
*/
DECLARE @A TABLE
(
ID Int Primary Key,
[Value] NVarChar(25) NOT NULL
)

DECLARE @B TABLE
(
ID Int Primary Key,
[Value] NVarChar(25) NOT NULL
)

INSERT INTO @A VALUES (1,'v1')
INSERT INTO @A VALUES (2,'v2')
INSERT INTO @A VALUES (3,'v3')
INSERT INTO @A VALUES (4,'v4')

SELECT * FROM @A
/*
ID Value
----------- --------
1 v1
2 v2
3 v3
4 v4
*/

INSERT INTO @B VALUES (1,'v1')
INSERT INTO @B VALUES (2,'v2')
INSERT INTO @B VALUES (5,'v5')

SELECT * FROM @B
/*
ID Value
----------- --------
1 v1
2 v2
5 v5
*/

Kunal

michaelgr
Starting Member

9 Posts

Posted - 2009-08-26 : 08:25:12

I didn't understand what do you mean.
The table is very big (with manu columns). How can i write a sample data for it here?

ABout the wanted output i already wrote what i want. 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 (i wrote before the example how i calculate the success rate )

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-08-26 : 12:44:53

You can write your query like this...
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]

if the above query didn't give you proper result, understand the below sample table, some sample data and query which resembles your situation

declare @mydata table
(
Id int,
Marks int
)
insert into @Mydata
select 1,0 union all
select 2,20 union all
select 3,30 union all
select 1,50 union all
select 2,0

select convert(float,sum(case when Marks <> 0 then 1 else 0 end))/count(distinct Marks) * 100 from @Mydata
group by ID

michaelgr
Starting Member

9 Posts

Posted - 2009-08-27 : 07:46:18

I understood the query,
but it doesn't work as expected (not calculate the success rate right)

michaelgr
Starting Member

9 Posts

Posted - 2009-08-27 : 07:49:50

I understood the problem-
in the sum(case when jobresult='success' then 1 else 0 end)
i need it to do the sum on (distinct jobresult) . how can i do it in sum?

michaelgr
Starting Member

9 Posts

Posted - 2009-08-27 : 12:43:49

Does anybody know how can i do it? maybe use distinct in case when?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources