quote:

---

Originally posted by theboyholty

--CREATE TABLE tblPO (
--pono int,
--firstsr INT,
--lastsr INT,
--qty INT)

--INSERT INTO tblPO (pono,firstsr,lastsr,qty) 
--SELECT 1001,5000,5050,51 UNION ALL
--SELECT 1002,5100,5200,101 UNION ALL
--SELECT 1003,5201,5250,50 UNION ALL


--CREATE TABLE tblAS (
--sr INT,
--run INT,
--result VARCHAR(5))

--INSERT INTO tblAS (sr,run,result)
--SELECT 5000,1,'fail' UNION ALL
--SELECT 5000,2,'pass' UNION ALL
--SELECT 5001,1,'pass' UNION ALL
--SELECT 5110,1,'pass' UNION ALL
--SELECT 5112,1,'fail' UNION ALL
--SELECT 5112,2,'pass'


--SELECT * FROM tblPO
--SELECT * FROM tblAS

SELECT 
	 pono
	,qty
	,SUM(CASE WHEN result = 'pass' THEN 1 ELSE 0 END) AS Pass
	,SUM(CASE WHEN result = 'fail' THEN 1 ELSE 0 END) AS Fail
	,(100/COUNT(*)) * SUM(CASE WHEN result = 'pass' THEN 1 ELSE 0 END) as Perc
FROM tblPO p
LEFT JOIN tblAS a ON a.sr>=p.FirstSR AND a.sr<=p.LastSR
GROUP BY pono,qty
ORDER BY pono,qty

Hi. First of all, my results don't quite match yours. That may be because of the definition of 'between'. I've done it inclusive but have just realised that this may cause some replication. You can ammend the query to fix this, its the >= and <= on the join that does it.

I've included all my create tables code, but its just the bit at the bottom you'd need.

Also, you're example of what you expect in your %age column 2/51 is a little odd I think. I've put in an actual percentage but again, this is easily fixable.

Have a look and let us know what you think.


---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

---

quote:

---

Originally posted by rohit04413


Thanks for your reply but i don't need to create table. I am facing problem only while generating report from the existing data. I want only those records for which "run" is maximum. please think again on this.

---

quote:

---

Originally posted by theboyholty
Hi. First of all, my results don't quite match yours. That may be because of the definition of 'between'. I've done it inclusive but have just realised that this may cause some replication. You can ammend the query to fix this, its the >= and <= on the join that does it.

I've included all my create tables code, but its just the bit at the bottom you'd need.

---

quote:

---

Originally posted by khtan

quote:

---

Originally posted by rohit04413


Thanks for your reply but i don't need to create table. I am facing problem only while generating report from the existing data. I want only those records for which "run" is maximum. please think again on this.

---

quote:

---

Originally posted by theboyholty
Hi. First of all, my results don't quite match yours. That may be because of the definition of 'between'. I've done it inclusive but have just realised that this may cause some replication. You can ammend the query to fix this, its the >= and <= on the join that does it.

I've included all my create tables code, but its just the bit at the bottom you'd need.

---

---

KH
[spoiler]Time is always against us[/spoiler]

---

quote:

---

Originally posted by khtan

starting from the SELECT . . .

---

KH
[spoiler]Time is always against us[/spoiler]

---

quote:

---

Originally posted by khtan

; with tblASmax as
(
	select	sr, run, result
	from	(
			select	*, row_no = row_number() over (partition by sr order by run desc)
			from	tblAS
		) m
	where	m.row_no	= 1
)
select	po.pono, po.qty,
	passed	= count(case when result = 'pass' then result end),
	failed  = count(case when result = 'fail' then result end),
	[%age]	= count(case when result = 'pass' then result end) * 100.0 / po.qty
from	tblPO po
	inner join tblASmax rs	on	po.firstsr	<= rs.sr
				and	po.lastsr	>= rs.sr
group by po.pono, po.qty

---

KH
[spoiler]Time is always against us[/spoiler]

---

quote:

---

Originally posted by khtan

post the exact query that you used

---

KH
[spoiler]Time is always against us[/spoiler]

---

quote:

---

Originally posted by khtan

; with tblASmax as
(
	select	sr, run, result
	from	(
			select	*, row_no = row_number() over (partition by sr order by run desc)
			from	tblAS
		) m
	where	m.row_no	= 1
)
select	po.pono, po.qty,
	passed	= count(case when result = 'pass' then result end),
	failed  = count(case when result = 'fail' then result end),
	[%age]	= count(case when result = 'pass' then result end) * 100.0 / po.qty
from	tblPO po
	inner join tblASmax rs	on	po.firstsr	<= rs.sr
				and	po.lastsr	>= rs.sr
group by po.pono, po.qty

---

KH
[spoiler]Time is always against us[/spoiler]

---

quote:

---

Originally posted by rohit04413

Is it right?

quote:

---

Originally posted by khtan

; with tblASmax as
(
	select	sr, run, result
	from	(
			select	*, row_no = row_number() over (partition by sr order by run desc)
			from	tblAS
		) m
	where	m.row_no	= 1
)
select	po.pono, po.qty,
	passed	= count(case when result = 'pass' then result end),
	failed  = count(case when result = 'fail' then result end),
	[%age]	= count(case when result = 'pass' then result end) * 100.0 / po.qty
from	tblPO po
	inner join tblASmax rs	on	po.firstsr	<= rs.sr
				and	po.lastsr	>= rs.sr
group by po.pono, po.qty

---

KH
[spoiler]Time is always against us[/spoiler]

---

---

pono        qty         passed      failed      %age                         
----------- ----------- ----------- ----------- ---------------------------- 
1001        51          2           0           3.921568627450
1002        101         2           0           1.980198019801

(2 row(s) affected)

quote:

---

with tblASmax as
(
select sr, run, result
from (
select *, row_no = row_number() over (partition by sr order by run desc)
from tblAS where process = 'fpt'
) m
where m.row_no = 1
)
select po.pono, po.qty,
passed = count(case when result = 'pass' then result end),
failed = count(case when result = 'fail' then result end),
[%age] = count(case when result = 'pass' then result end) * 100.0 / po.qty
from tblPO po
inner join tblASmax rs on po.firstsr <= rs.sr
and po.lastsr >= rs.sr
group by po.pono, po.qty

---

quote:

---

Originally posted by khtan

tblASmax is the Common Table Expressions.

It gives the latest result (pass or fail) based on run no for each sr.

the row_number() will return a sequence no for each sr starting from the highest run as 1 and increment there after. So filter for row_no = 1 will give you the latest run no for each sr

based on the sample data provided, the result of the query i posted

pono        qty         passed-A   passed-B      failed      %age                         
----------- ----------- ----------- ----------- ---------------------------- 
1001        51          2           0           3.921568627450
1002        101         2           0           1.980198019801

(2 row(s) affected)

---

KH
[spoiler]Time is always against us[/spoiler]

---

passed_A	= count(case when process = 'Process A' and result = 'pass' then result end),
passed_B	= count(case when process = 'Process B' and result = 'pass' then result end),

quote:

---

Originally posted by khtan

just add another condition to the case when . .

passed_A	= count(case when process = 'Process A' and result = 'pass' then result end),
passed_B	= count(case when process = 'Process B' and result = 'pass' then result end),

---

KH
[spoiler]Time is always against us[/spoiler]

---

quote:

---

Originally posted by khtan

yes. you have to include the process column in CTE tblASmax

---

KH
[spoiler]Time is always against us[/spoiler]

---