SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Add row if missing
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lucsky8
Posting Yak Master

105 Posts

Posted - 06/06/2008 :  08:54:17  Show Profile  Reply with Quote
Hi i have a sorted procedure that create a @temp table that return strNomObjet,strNomDescripteur,strReponse,Percentage

I need to calculate the Percentage for strReponse for each strNomDescripteur. strReponse can be = to A,B,C, or D
To calculate the Percentage i have no problem my is that something it possible that a certain strNomDescripteur that will have only A,B and D and there no C i need to put 0%.

Here what i have

DECLARE @Temp table
	(
		strLastName varchar(50),
		strFirstName varchar(50),
		strReponse char(10),
		strNomObjet nvarchar(200),
		strDescripteurOrdre char(10),
		strNomDescripteur nvarchar(200),
		strObjetOrdre char(10)
	)


INSERT INTO @Temp
	Select te.stdlastn,te.stdfirstn,COALESCE(NULLIF(REPLACE(tr.T3,'*',''),' '),REPLACE(tr.T2,'*','')),tob.Nom,tob.Affichage,td.Nom,td.Affichage
	from	Bulletin.dbo.tblEleves as te
			INNER JOIN Bulletin.dbo.tblBulletin as tb ON te.stdnumber=tb.NoEtud
			INNER JOIN Bulletin.dbo.tblNomBull as tnb ON tb.NoBull=tnb.ID
			INNER JOIN Bulletin.dbo.tblSection as ts ON ts.ID_Bull = tnb.ID
			INNER JOIN Bulletin.dbo.tblObjet	as tob ON tob.ID_Sec = ts.ID	
			INNER JOIN Bulletin.dbo.tblDescripteur as td ON td.ID_Obj = tob.ID
			INNER JOIN Bulletin.dbo.tblReponse as tr ON tr.ID = tb.ID 
	where	intActif = 1 and 
			tb.NoBull not like '%EdPhy%' 
			and tnb.Nom not like '%EdPhy%' 
			and tnb.Date = '2006-2007'
			and te.STDGRADeID = 3
			and te.schoolid = '1202'
			and ts.Nom = 'MATHÉMATIQUES'
			and tr.[Desc] = td.ID
			and tr.T2 <> 'NULL'
			and tr.T3 <> 'NULL' 
			and (tr.T2 <> '' OR tr.T3 <> '')
	ORDER BY tob.Affichage,td.Affichage

	
	SELECT t1.strNomObjet,t1.strNomDescripteur,t1.strReponse,
	t1.ReponseCount * 100.0/t2.PersonCount AS Percentage
	FROM
	(
	SELECT strNomObjet,strNomDescripteur,strReponse,count(*) AS ReponseCount FROM @Temp
	GROUP BY strNomObjet,strNomDescripteur,strReponse)t1
	INNER JOIN (SELECT strNomObjet,strNomDescripteur,count(*) AS PersonCount FROM @Temp
	GROUP BY strNomObjet,strNomDescripteur)t2
	ON t2.strNomObjet=t1.strNomObjet
	AND t2.strNomDescripteur=t1.strNomDescripteur


This will give this result :
As you can see it always A,B,C,D A,B,C,D

Except for "FORMES utilise les transformations géométriques pour" it only A,B,C there no D.
So is there a way that i could insert D = 0
So if i don't find any A,B,C or D for certain strNomDescripteur put that letter = 0

Thanks for your help in advance!!

FORMES utilise la mesure pour résoudre des problèmes A 47.222222222222
FORMES utilise la mesure pour résoudre des problèmes B 38.888888888888
FORMES utilise la mesure pour résoudre des problèmes C 8.333333333333
FORMES utilise la mesure pour résoudre des problèmes D 5.555555555555
FORMES utilise les figures géométriques pour résoudre A 61.111111111111
FORMES utilise les figures géométriques pour résoudre B 25.000000000000
FORMES utilise les figures géométriques pour résoudre C 11.111111111111
FORMES utilise les figures géométriques pour résoudre D 2.777777777777
FORMES utilise les transformations géométriques pour A 62.857142857142
FORMES utilise les transformations géométriques pour B 28.571428571428
FORMES ET ESPACE (géométrie) utilise les transformat C 8.571428571428
NOMBRE ET OPÉRATIONS démontre une compréhension A 69.444444444444
NOMBRE ET OPÉRATIONS démontre une compréhension B 16.666666666666
NOMBRE ET OPÉRATIONS démontre une compréhension C 5.555555555555
NOMBRE ET OPÉRATIONS démontre une compréhension D 8.333333333333

lucsky8
Posting Yak Master

105 Posts

Posted - 06/06/2008 :  09:24:17  Show Profile  Reply with Quote
Any suggestion would be appreciate i don't if i explain my self correctly!
Tks in advance!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/06/2008 :  10:10:20  Show Profile  Reply with Quote
SELECT main1.strNomObjet,main1.strNomDescripteur,main1.strReponse,
ISNULL(main2.Percentage,0) AS Percentage
FROM
(
SELECT tmp.strNomObjet,tmp.strNomDescripteur,tmp1.strReponse
FROM 
(SELECT  DISTINCT t1.strNomObjet,t1.strNomDescripteur FROM @Temp) tmp
CROSS JOIN (SELECT DISTINCT strReponse FROM @Temp) tmp1
)main1
LEFT JOIN
(     SELECT t1.strNomObjet,t1.strNomDescripteur,t1.strReponse,
	t1.ReponseCount * 100.0/t2.PersonCount AS Percentage
	FROM
	(
	SELECT strNomObjet,strNomDescripteur,strReponse,count(*) AS ReponseCount FROM @Temp
	GROUP BY strNomObjet,strNomDescripteur,strReponse)t1
	INNER JOIN (SELECT strNomObjet,strNomDescripteur,count(*) AS PersonCount FROM @Temp
	GROUP BY strNomObjet,strNomDescripteur)t2
	ON t2.strNomObjet=t1.strNomObjet
	AND t2.strNomDescripteur=t1.strNomDescripteur
)main2
ON main2.strNomObjet = main1.strNomObjet
AND main2.strNomDescripteur = main1.strNomDescripteur
AND main2.strReponse=main1.strReponse
Go to Top of Page

lucsky8
Posting Yak Master

105 Posts

Posted - 06/06/2008 :  10:26:53  Show Profile  Reply with Quote
WOW thanks man!! thats exacly what i need!
Tks again you made my day!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/06/2008 :  10:32:29  Show Profile  Reply with Quote
quote:
Originally posted by lucsky8

WOW thanks man!! thats exacly what i need!
Tks again you made my day!


No worries. You're always welcome. Glad that i could help you to sort it out
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000