Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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
52326 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  
 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