• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

help grouping in query

I have an sp with a query to preselect translators who have already worked with us in a specific language combination. As long as there is a source and target language this works fine. I now need to expand this query to allow for a NULL value in the source and also the same source and target value. We use this to preselect proofreaders who do not necessarily need to have the source language in their profile.

The code below renders the list including the NULL values but does not group properly. I only need each translator mentioned once (even if he has several source languages in his profile).

How can I properly group this?

USE [araxiSQLsite]
GO
/****** Object:  StoredProcedure [dbo].[sp_TranslatorPreselection2]    Script Date: 09/11/2012 15:07:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_TranslatorPreselection2]
	 @trad_commandesID INT,
	 @IndustryID INT,
	 @IndustrySubCategoryID INT,
	 @SourceLanguage INT,
	 @TargetLanguage INT,
	 @ClientCode NVARCHAR(10)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	-- use this for testing
--	DECLARE @trad_commandesID INT;
--	DECLARE @IndustryID INT;
--	DECLARE @IndustrySubCategoryID INT;
--	DECLARE @SourceLanguage INT;
--	DECLARE @TargetLanguage INT;
--	DECLARE @ClientCode NVARCHAR(10);
--
--	SET @trad_commandesID = 7990;
--	SET @IndustryID = 5;
--	SET @IndustrySubCategoryID = 5;
--	SET @SourceLanguage = 2;
--	SET @TargetLanguage = 1;
--	SET @ClientCode = 'CTR101'

DECLARE @LanguageCombination NVARCHAR(100)
If COALESCE(@SourceLanguage, @TargetLanguage)  = @TargetLanguage  
SET @LanguageCombination = '(T.TargetLanguage = @TargetLanguage)'
Else
SET @LanguageCombination = '(T.SourceLanguage = @SourceLanguage) AND (T.TargetLanguage = @TargetLanguage)'

DECLARE @LanguageCombination2 NVARCHAR(100)
If COALESCE(@SourceLanguage, @TargetLanguage)  = @TargetLanguage  
SET @LanguageCombination2 = '(D.langue_cible = @TargetLanguage)'
Else
SET @LanguageCombination2 = '(D.langue_source = @SourceLanguage)AND (D.langue_cible = @TargetLanguage)'

declare @nSQL nvarchar(max)
SET  @nSQL = '

	INSERT INTO tblTranslationPreSelection (trad_commandesID, TranslatorLanguageCombinationsID
										  , ContactID
										  , SourceLanguage
										  , TargetLanguage
										  , pIndustry
										  , pSubIndustry
										  , LastJob
										  , cProjects
										  , cIndustry
										  , cSubIndustry
										  , cClient)
	SELECT @trad_commandesID , TranslatorLanguageCombinationsID
		 , T1.ContactID
		 , T1.SourceLanguage
		 , T1.TargetLanguage
		 , T1.pIndustry
		 , T1.pSubIndustry
		
		 , T2.LastJob
		 , T2.cProjects
		 , T2.cIndustry
		 , T2.cSubIndustry
		 , T2.cClient
	FROM (SELECT TR.ContactID, TranslatorLanguageCombinationsID
			   , T.SourceLanguage
			   , T.TargetLanguage
			   , max(CASE WHEN tblIndustry.IndustryID = @IndustryID THEN 1 ELSE 0 END) AS pIndustry
			   , sum(CASE WHEN tblIndustrySubCategory.IndustrySubCategoryID = @IndustrySubCategoryID THEN 1 ELSE 0 END) AS pSubIndustry
		  FROM tblTranslatorIndustries
			  INNER JOIN tblIndustryMapper
				  ON tblTranslatorIndustries.IndustryMapperID = tblIndustryMapper.IndustryMapperID
			  LEFT OUTER JOIN tblIndustrySubCategory
				  ON tblIndustryMapper.IndustrySubCategoryID = tblIndustrySubCategory.IndustrySubCategoryID
			  LEFT OUTER JOIN tblIndustry
				  ON tblIndustryMapper.IndustryID = tblIndustry.IndustryID
			  RIGHT OUTER JOIN tbltraducteurs AS TR
			  LEFT OUTER JOIN tblTranslatorLanguageCombinations AS T
				  ON TR.ContactID = T.ContactID
				  ON tblTranslatorIndustries.ContactID = TR.ContactID
		  WHERE ' + @LanguageCombination + ' AND (convert(INT, TR.BlackList) <> 1) AND TR.Translator = 1
		  GROUP BY T.SourceLanguage, TranslatorLanguageCombinationsID
				 , T.TargetLanguage
				 , TR.ContactID) AS T1
		LEFT OUTER JOIN (SELECT D.contactID_traducteur
							  , max(C.date_commande) AS LastJob
							  , count(C.ClientProjectRequestID) AS cProjects
							  , sum(CASE WHEN C.ClientCode = @ClientCode THEN 1 ELSE 0 END) AS cClient
							  , sum(CASE WHEN D.IndustryID = @IndustryID THEN 1 ELSE 0 END) AS cIndustry
							  , sum(CASE WHEN D.IndustrySubCategoryID = @IndustrySubCategoryID THEN 1 ELSE 0 END) AS cSubIndustry
						 FROM tblTranslatorLanguageCombinations AS T
							 INNER JOIN tbltraducteurs AS TR
								 ON T.ContactID = TR.ContactID
							 RIGHT OUTER JOIN tbltrad_commandes AS C
							 INNER JOIN tbltrad_commandes_details AS D
								 ON C.trad_commandesID = D.trad_commandesID
								 ON TR.ContactID = D.contactID_traducteur
						 WHERE ' + @LanguageCombination + ' AND ' + @LanguageCombination2 +
							 'AND (convert(INT, TR.BlackList) <> 1)
						 GROUP BY D.contactID_traducteur
								, D.langue_source
								, D.langue_cible
								, convert(INT, TR.BlackList)) AS T2
			ON T1.ContactID = T2.contactID_traducteur
	WHERE NOT EXISTS (SELECT *
					  FROM tblTranslationPreSelection
					  WHERE @trad_commandesID = trad_commandesID
						  AND T1.ContactID = tblTranslationPreSelection.ContactID)
	ORDER BY T1.ContactID'

exec sp_ExecuteSQL @nSQL , N'@trad_commandesID INT, @IndustryID INT,@IndustrySubCategoryID INT,@SourceLanguage INT, @TargetLanguage INT,@ClientCode NVARCHAR(10)'
, @trad_commandesID, @IndustryID,@IndustrySubCategoryID,@SourceLanguage, @TargetLanguage,@ClientCode 

	 

END 

Open in new window

0
Shawn
Asked:
Shawn
1 Solution
 
David ToddSenior DBACommented:
Hi

Can you post sample data?

Regards
  David
0
 
GhunaimaCommented:
remove "sourcelanguage" from group by clause & from select list
Sol1.txt
0
 
namethisCommented:
Remove the columns D.langue_source, D.langue_cible from the grouping clause in the second subquery of the join. They are not necessary and they may produce more than one row for each D.contactID_traducteur .
0
 
ShawnAuthor Commented:
thx. that was part of the answer. I ended up restructuring it quite a bit
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now