LEFT JOIN and dynamic columns

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?
LVL 18
CluskittAsked:
Who is Participating?
 
CluskittAuthor Commented:
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
 
dwkorCommented:
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
 
CluskittAuthor Commented:
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
 
Ephraim WangoyaCommented:
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
 
CluskittAuthor Commented:
This works as intended.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.