[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 172
  • Last Modified:

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.
0
Cluskitt
Asked:
Cluskitt
  • 3
  • 2
1 Solution
 
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
 
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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