Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

LEFT JOIN and dynamic columns

Posted on 2011-10-20
5
Medium Priority
?
155 Views
Last Modified: 2012-05-12
I have this stored procedure (which has evolved from one asked here recently):
ALTER PROCEDURE [dbo].[usp_MapaSalarios]
	@Emp smallint,
	@AnoMes numeric(6,0),
	@CC char(1)

AS
BEGIN

DECLARE @Columnnames NVARCHAR(MAX), @Columns NVARCHAR(MAX), @Query NVARCHAR(MAX)

SET @Columns = (SELECT STUFF(((SELECT ', ISNULL('+QUOTENAME(CONVERT(VARCHAR,c.strCodigoVenc)) + ', ''0'') AS ' + 
	QUOTENAME(CONVERT(VARCHAR,c.strCodigoVenc))
FROM   
	uv_Cadastro a INNER JOIN
	tblMapaSalarios b ON a.psa_emp_empresa = b.FK_ID_intEmpresa AND a.psa_num_empregado = b.FK_ID_intFuncionario AND b.intAnoMes=@AnoMes INNER JOIN
	tblVencimentos c ON b.FK_ID_intVencimento = c.FK_ID_intVencimento AND strCC=@CC
WHERE psa_emp_empresa=@Emp
GROUP BY c.strCodigoVenc, c.intOrdem
ORDER BY c.intOrdem
                  FOR XML PATH('')
                  )),1,1,''))
                 
SET @Columnnames = (SELECT STUFF(((SELECT ', '+QUOTENAME(CONVERT(VARCHAR,c.strCodigoVenc))
FROM   
	uv_Cadastro a INNER JOIN
	tblMapaSalarios b ON a.psa_emp_empresa = b.FK_ID_intEmpresa AND a.psa_num_empregado = b.FK_ID_intFuncionario AND b.intAnoMes=@AnoMes INNER JOIN
	tblVencimentos c ON b.FK_ID_intVencimento = c.FK_ID_intVencimento AND strCC=@CC
WHERE psa_emp_empresa=@Emp
GROUP BY c.strCodigoVenc, c.intOrdem
ORDER BY c.intOrdem
                  FOR XML PATH('')
                  )),1,1,''))

SET @Query = 'SELECT  psa_num_empregado ''Nº'', psa_nome_abreviado Nome, REPLACE(CONVERT(char(10),psa_dt_admissao_empresa,103),''/'',''-'') ''Admissão'', Dem ''Demissão'', cat_designacao Categoria, cvc_valor_venc Vencimento, Di Dias, ' + @Columns + ' 
FROM 
(SELECT   a.psa_num_empregado, a.psa_nome_abreviado, a.cat_designacao, a.psa_dt_admissao_empresa, '''' Dem, a.cvc_valor_venc, '''' Di, c.strCodigoVenc, ISNULL(CONVERT(VARCHAR, b.dblValorVencimento), '''') dblValorVencimento 
FROM uv_Cadastro a INNER JOIN
	tblMapaSalarios b ON a.psa_emp_empresa = b.FK_ID_intEmpresa AND a.psa_num_empregado = b.FK_ID_intFuncionario AND b.intAnoMes=' + CONVERT(VARCHAR(6),@AnoMes) + ' INNER JOIN
	tblVencimentos c ON b.FK_ID_intVencimento = c.FK_ID_intVencimento AND strCC=''' + @CC + '''
WHERE psa_emp_empresa=' + CONVERT(VARCHAR(3),@Emp) + ' AND Regime = ''' + @CC + ''') AS p 
PIVOT 
(MAX(dblValorVencimento) FOR strCodigoVenc IN (' + @Columnnames + ')) AS v '
+'ORDER BY psa_num_empregado ;'

EXECUTE sp_ExecuteSQL @Query

END

Open in new window

It works fine as long as the employee has at least one code. I need it to show the employee even when there isn't one. Some sort of left join (coming from the view uv_Cadastro), but I can't seem to get it to work. Could someone help me with this, please?
0
Comment
Question by:Cluskitt
  • 3
5 Comments
 
LVL 13

Expert Comment

by:dwkor
ID: 37001065
You can use left join with filter as part of the join predicate instead of using it in where clause. Something like that:

FROM  
      uv_Cadastro a INNER JOIN tblMapaSalarios b ON
            a.psa_emp_empresa = b.FK_ID_intEmpresa AND a.psa_num_empregado = b.FK_ID_intFuncionario AND b.intAnoMes=@AnoMes and psa_emp_empresa=@Emp
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 37001167
I know that. That doesn't really change the query. My problem is that, if an employee doesn't have any value for any code in a certain AnoMes, the procedure won't return anything. Right now, it's returning:

1  Emp1  1  3  56

And it should return:

1  Emp1  1  3  56
2  Emp2  0  0  0
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 37003833
try this

First create a function for spliting your comma delimited string into a table and thus avoid the dynamic SQL alltogether

1. Split String
CREATE FUNCTION dbo.fnSplit(@String VARCHAR(8000), @Delimiter NVARCHAR(1)
) 
RETURNS @Tokens table (Token NVARCHAR(255)) 
AS 
BEGIN 
WHILE (CHARINDEX(@Delimiter,@String)>0) 
   BEGIN 
       INSERT INTO @Tokens (Token) VALUES
       (LTRIM(RTRIM(SUBSTRING(@String,1,CHARINDEX(@Delimiter,@String)-1)))) 
       SET @String = SUBSTRING(@String, CHARINDEX(@Delimiter,@String)+LEN(@Delimiter),LEN(@String)) 
   END 
   INSERT INTO @Tokens (Token) VALUES (LTRIM(RTRIM(@String)))
RETURN 
END
GO

Open in new window



2.
 
ALTER PROCEDURE [dbo].[usp_MapaSalarios]
	@Emp smallint,
	@AnoMes numeric(6,0),
	@CC char(1)

AS
BEGIN

DECLARE @Columnnames NVARCHAR(MAX), @Columns NVARCHAR(MAX), @Query NVARCHAR(MAX)

SET @Columns = (SELECT STUFF(((SELECT ', ISNULL('+QUOTENAME(CONVERT(VARCHAR,c.strCodigoVenc)) + ', ''0'') AS ' + 
	QUOTENAME(CONVERT(VARCHAR,c.strCodigoVenc))
FROM uv_Cadastro a 
INNER JOIN tblMapaSalarios b ON a.psa_emp_empresa = b.FK_ID_intEmpresa AND a.psa_num_empregado = b.FK_ID_intFuncionario AND b.intAnoMes=@AnoMes 
INNER JOIN tblVencimentos c ON b.FK_ID_intVencimento = c.FK_ID_intVencimento AND strCC=@CC
WHERE psa_emp_empresa=@Emp
GROUP BY c.strCodigoVenc, c.intOrdem
ORDER BY c.intOrdem
                  FOR XML PATH('')
                  )),1,1,''))
                 
SET @Columnnames = (SELECT STUFF(((SELECT ', '+QUOTENAME(CONVERT(VARCHAR,c.strCodigoVenc))
FROM uv_Cadastro a 
INNER JOIN tblMapaSalarios b ON a.psa_emp_empresa = b.FK_ID_intEmpresa AND a.psa_num_empregado = b.FK_ID_intFuncionario AND b.intAnoMes=@AnoMes 
INNER JOIN tblVencimentos c ON b.FK_ID_intVencimento = c.FK_ID_intVencimento AND strCC=@CC
WHERE psa_emp_empresa=@Emp
GROUP BY c.strCodigoVenc, c.intOrdem
ORDER BY c.intOrdem
                  FOR XML PATH('')
                  )),1,1,''))

SELECT  psa_num_empregado 'Nº', psa_nome_abreviado Nome, REPLACE(CONVERT(char(10),psa_dt_admissao_empresa,103),'/','-') 'Admissão', Dem 'Demissão', cat_designacao Categoria, cvc_valor_venc Vencimento, Di Dias, @Columns
FROM 
(SELECT   a.psa_num_empregado, a.psa_nome_abreviado, a.cat_designacao, a.psa_dt_admissao_empresa, Dem, a.cvc_valor_venc, Di, c.strCodigoVenc, ISNULL(CONVERT(VARCHAR, b.dblValorVencimento), '') dblValorVencimento 
	FROM uv_Cadastro a 
	LEFT JOIN tblMapaSalarios b ON a.psa_emp_empresa = b.FK_ID_intEmpresa AND a.psa_num_empregado = b.FK_ID_intFuncionario AND b.intAnoMes= CONVERT(VARCHAR(6), @AnoMes) AND a.psa_emp_empresa= CONVERT(VARCHAR(3), @Emp) 
	LEFT JOIN tblVencimentos c ON b.FK_ID_intVencimento = c.FK_ID_intVencimento AND strCC= @CC
	WHERE Regime = @CC
) AS p 
PIVOT 
(MAX(dblValorVencimento) FOR strCodigoVenc IN dbo.fnSplit(@Columnnames, ',')) AS v
 ORDER BY psa_num_empregado ;

EXECUTE sp_ExecuteSQL @Query

END

Open in new window

0
 
LVL 18

Accepted Solution

by:
Cluskitt earned 0 total points
ID: 37005326
I'm not sure why you would be breaking up the columns with a function. Seems to be doing the same thing as before, only now it also uses a function. Also, it generates an error (syntax error near dbo).
I ended up finding a simple solution. Seems my problem was mostly with syntax (and moving some arguments from ON to WHERE, because of the left join). This is how it ended up like:
CREATE PROCEDURE [dbo].[usp_MapaSalarios]
	@Emp smallint,
	@AnoMes numeric(6,0),
	@CC char(1)

AS
BEGIN

DECLARE @Columnnames NVARCHAR(MAX), @Columns NVARCHAR(MAX), @Query NVARCHAR(MAX)

SET @Columns = (SELECT STUFF(((SELECT ', ISNULL('+QUOTENAME(CONVERT(VARCHAR,c.strCodigoVenc)) + ', ''0'') AS ' + 
	QUOTENAME(CONVERT(VARCHAR,c.strCodigoVenc))
FROM   
	tblVencimentos c WHERE FK_ID_intEmpresa=@Emp AND strCC=@CC AND intAnoMes=
		(SELECT MAX(intAnoMes) FROM tblVencimentos WHERE FK_ID_intEmpresa=@Emp AND intAnoMes<=@AnoMes)
GROUP BY c.strCodigoVenc, c.intOrdem
ORDER BY c.intOrdem
                  FOR XML PATH('')
                  )),1,1,''))
                 
SET @Columnnames = (SELECT STUFF(((SELECT ', '+QUOTENAME(CONVERT(VARCHAR,c.strCodigoVenc))
FROM   
	tblVencimentos c WHERE FK_ID_intEmpresa=@Emp AND strCC=@CC AND intAnoMes=
		(SELECT MAX(intAnoMes) FROM tblVencimentos WHERE FK_ID_intEmpresa=@Emp AND intAnoMes<=@AnoMes)
GROUP BY c.strCodigoVenc, c.intOrdem
ORDER BY c.intOrdem
                  FOR XML PATH('')
                  )),1,1,''))

SET @Query = 'SELECT  psa_num_empregado ''Nº'', psa_nome_abreviado Nome, REPLACE(CONVERT(char(10), 
	psa_dt_admissao_empresa,103),''/'',''-'') ''Admissão'', Dem ''Demissão'', cat_designacao Categoria, 
	cvc_valor_venc Vencimento, Di Dias, ' + @Columns + ' 
FROM 
(SELECT   a.psa_num_empregado, a.psa_nome_abreviado, a.cat_designacao, a.psa_dt_admissao_empresa, '''' Dem, 
	a.cvc_valor_venc, '''' Di, c.strCodigoVenc, ISNULL(CONVERT(VARCHAR, b.dblValorVencimento), '''') dblValorVencimento 
FROM uv_Cadastro a LEFT JOIN
	tblMapaSalarios b ON a.psa_emp_empresa = b.FK_ID_intEmpresa AND a.psa_num_empregado = b.FK_ID_intFuncionario 
	AND b.intAnoMes=' + CONVERT(VARCHAR(6),@AnoMes) + ' LEFT JOIN
	tblVencimentos c ON b.FK_ID_intVencimento = c.FK_ID_intVencimento AND strCC=Regime
WHERE psa_emp_empresa=' + CONVERT(VARCHAR(3),@Emp) + ' AND Regime = ''' + @CC + ''') AS p 
PIVOT 
(MAX(dblValorVencimento) FOR strCodigoVenc IN (' + @Columnnames + ')) AS v '
+'ORDER BY psa_num_empregado ;'

EXECUTE sp_ExecuteSQL @Query

END

Open in new window

0
 
LVL 18

Author Closing Comment

by:Cluskitt
ID: 37035234
This works as intended.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question