Want a query for for finding total number of non defected items.

VishwaKL

Hi guys, I have 2 tables Table1,Table2 Table1 having products and serial number information, and Table2 having set of 32 check points to say that product having defect or not, i want to get only total number of non defected products for each month, say example i have 3 products in march month and only 2 products satisfy set of 32 check points and having non defect, so for this problem result will be Total Number of non defected product will be 2. i want this in query. I tried it but it giving each product defect count, i want only total number of non defect product here is my query

WITH months (num) as(
SELECT 1
UNION ALL
SELECT num+1
FROM months
WHERE num<12)SELECT m.num as Month,
--isnull(t.count,0) as Total,
isnull(t.resolved,0) as Totalresolved,
t.YearValue as YearData FROM months m
left join (SELECT month(ProgramStartedDate) as Month,YEAR(ProgramStartedDate) as YearValue,
--count(*) as count,

							sum(case when (ProgrammerStatus = 1 OR ReviewerStatus = 1)  THEN 1 ELSE 0 END)as resolved  
							FROM tb\_ProgramCheckList 
							LEFT JOIN dbo.tb\_ProgramDetails on 
							tb\_ProgramDetails.ProgramID =tb\_ProgramCheckList.ProgramID AND tb\_ProgramDetails.Version=tb\_ProgramCheckList.VersionID
						   WHERE ProgramStartedDate >='10/1/2012' 
							and ProgramStartedDate <'10/01/2013' --AND month(ProgramStartedDate)=m.num  
							GROUP BY month(ProgramStartedDate),YEAR(ProgramStartedDate)

If any body help me to solve this issue will be appreciated, Thanks and regards VISHWA

Corporal Agarn

Does table 2 have 32 bit columns or two columns with one record per check?

VishwaKL

each program having 32 bit checks to prove the item is defect or not

Mycroft Holmes

Great design - what happens when you want to add a new check! You need to add the values of the 32 checks (presumably booleans where 1= positive) if it = 32 then the program passes andything els the program fails.

Select *
From (Select C1 + C2+C3+... as checktotal, Program from table) as SubQuery
where checktotal = 32

Never underestimate the power of human stupidity RAH