Left join with nulls if more than one row exists

I have this stored procedure. It was created with the help of EE members.
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

This will generate the following output:
Number   Name   [...]   Code1 [...]  CodeN
1             Name1 [...]   12.5     [...]  0
2             Name2 [...]   2          [...]  6
3             Nam31 [...]   0          [...]  0
Now, I have a query:
SELECT FK_ID_intFuncionario, datHoraEntradaTrabalho, datHoraSaidaTrabalho
FROM tblTrabalho
WHERE FK_ID_intEmpresa=41 AND strNumHorario='0'
AND FK_ID_intTipoTrabalho IN (SELECT ID_intTipoTrabalho FROM tblTiposTrabalho WHERE strCategoria IN ('I','A'))

Open in new window

This will return something like:
Number   StartDate      EndDate
1             01-10-2011  03-10-2011
1             06-10-2011  06-10-2011
3             05-10-2011  12-10-2011

Now, what I need is some sort of LEFT JOIN that will generate this:
Number   Name   [...]   Code1 [...]  CodeN  Start              End
1             Name1 [...]   12.5     [...]  0           01-10-2011  03-10-2011
1                         [...]               [...]               06-10-2011  06-10-2011
2             Name2 [...]   2          [...]  6
3             Nam31 [...]   0          [...]  0           05-10-2011  12-10-2011

Creating 0 (and '' for strings) on the left part of the repeated rows is also acceptable, though empty would be better.
LVL 18
CluskittAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Is this MS SQL 2005 or higher?
If so, then you can do something like this:

SELECT FK_ID_intFuncionario, datHoraEntradaTrabalho, datHoraSaidaTrabalho
     , ROW_NUMBER() OVER(PARITITION BY FK_ID_intFuncionario ORDER BY datHoraEntradaTrabalho) RN
FROM tblTrabalho
WHERE FK_ID_intEmpresa=41 AND strNumHorario='0'
AND FK_ID_intTipoTrabalho IN (SELECT ID_intTipoTrabalho FROM tblTiposTrabalho WHERE strCategoria IN ('I','A'))

This will give you a rank for each of the rows in the start and end dates table.

Then you can derive that to your original query using LEFT OUTER JOIN ( query above ).

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 ', CASE RN WHEN 1 THEN ISNULL('+QUOTENAME(CONVERT(VARCHAR,c.strCodigoVenc)) + ', ''0'') ELSE '''' END 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 + ', d.datHoraEntradaTrabalho AS "Start", d.datHoraSaidaTrabalho AS "End"
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 '
+'LEFT OUTER JOIN (SELECT FK_ID_intFuncionario, datHoraEntradaTrabalho, datHoraSaidaTrabalho
     , ROW_NUMBER() OVER(PARITITION BY FK_ID_intFuncionario ORDER BY datHoraEntradaTrabalho) RN
FROM tblTrabalho
WHERE FK_ID_intEmpresa=41 AND strNumHorario=''0''
AND FK_ID_intTipoTrabalho IN (SELECT ID_intTipoTrabalho FROM tblTiposTrabalho WHERE strCategoria IN (''I'',''A''))) AS d ON d.FK_ID_intFuncionario = v.psa_num_empregado'
+'ORDER BY psa_num_empregado ;'

EXECUTE sp_ExecuteSQL @Query

END

Open in new window


If I have not made any type-o's you should see the RN used in determining if the PIVOT column is shown or not. Hope that helps!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CluskittAuthor Commented:
Works like a charm (and you did make a typo, but it was easy to replace PARITITION with PARTITION ;)
0
CluskittAuthor Commented:
Actually, there is a small problem with it. If there is no record on the partition subquery, the whole query returns nulls, even though there are values for the codes.
0
Kevin CrossChief Technology OfficerCommented:
Change the CASE RN to CASE ISNULL(RN, 1).
0
CluskittAuthor Commented:
Excellent, thank a lot!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.