Dificuldade em realizar join

SQL Server

SQL

10/12/2019

Boa tarde,

Sou novo por aqui e estou buscando ajuda para realizar um join entre duas consultas, não sei se é possível.

Preciso apenas vincular as duas consultas, quero acrescentar as duas últimas colunas da segunda consulta no resultado do join.
Abaixo as duas consultas, e no final parte do resultado da primeira consulta e o resultado da segunda consulta, além do resultado que desejo obter com o JOIN

DECLARE @PAUSA_LANCHE AS INT, @PAUSA_DESCANSO AS INT, @PAUSA_PARTICULAR AS INT
SET @PAUSA_LANCHE = '1200'
SET @PAUSA_DESCANSO = '1200'
SET @PAUSA_PARTICULAR = '600'

SELECT
RTRIM(CONCAT (CAST(ROW_DATE AS DATE), '-' , LOGID)) as DATA_MATRICULA
,CAST(ROW_DATE AS DATE) AS DATA
,[LOGID] AS PIN
,SUM(CAST(TI_AUXTIME AS FLOAT) )/86400 AS TOTAL_PAUSA
,SUM(CAST(TI_AUXTIME0 AS FLOAT) )/86400 AS PAUSA_PADRAO
,SUM(CAST(TI_AUXTIME1 AS FLOAT) )/86400 AS PAUSA_LANCHE
,CAST(IIF(SUM(TI_AUXTIME1) < @PAUSA_LANCHE , 0 , (SUM(TI_AUXTIME1) - @PAUSA_LANCHE)) AS FLOAT) / 86400 AS ESTOURO_PAUSA_LANCHE
,SUM(CAST(TI_AUXTIME2 AS FLOAT) )/86400 AS PAUSA_PARTICULAR
,CAST(IIF(SUM(TI_AUXTIME2) < @PAUSA_PARTICULAR , 0 , (SUM(TI_AUXTIME2) - @PAUSA_PARTICULAR)) AS FLOAT) / 86400 AS EXCEDENTE_PAUSA_PARTICULAR
,SUM(CAST(TI_AUXTIME3 AS FLOAT) )/86400 AS PAUSA_TREINAMENTO
,SUM(CAST(TI_AUXTIME4 AS FLOAT) )/86400 AS PAUSA_DESCANSO
,CAST(IIF(SUM(TI_AUXTIME4) < @PAUSA_DESCANSO , 0 , (SUM(TI_AUXTIME4) - @PAUSA_DESCANSO)) AS FLOAT) / 86400 AS ESTOURO_PAUSA_DESCANSO
,SUM(CAST(TI_AUXTIME5 AS FLOAT) )/86400 AS PAUSA_SUPORTE
,SUM(CAST(TI_AUXTIME6 AS FLOAT) )/86400 AS PAUSA_FEEDBACK
,SUM(CAST(TI_AUXTIME7 AS FLOAT) )/86400 AS PAUSA_SISTEMA
,SUM(CAST(TI_AUXTIME8 AS FLOAT) )/86400 AS PAUSA_SAUDE
,SUM(CAST(TI_AUXTIME9 AS FLOAT) )/86400 AS FUNCAO_ESPECIFICA
,SUM(CAST(I_STAFFTIME AS FLOAT) )/86400 AS TEMPO_LOGADO


FROM [ECH_CNU].[DBO].[HAGENT] AS A (NOLOCK)



WHERE CAST(ROW_DATE AS DATE) between '2019-12-01' and '2019-12-01'
--AND SPLIT IN (1400,1405,1466,1467,1497)
AND ACD= 2
GROUP BY [ROW_DATE]
,[LOGID]
--,[SPLIT]

ORDER BY 1,2,3


-- 2° consulta
DECLARE @DATAI DATETIME,@DATAF DATETIME
SET @DATAI= '2019-12-01'
SET @DATAF= '2019-12-31 23:59:59'

SELECT
CONCAT (CAST(SEGSTART AS DATE), '-' , anslogin) as DATA_MATRICULA
,CAST(SEGSTART AS DATE) AS DATA
,anslogin AS PIN
,count(anslogin) AS QUANTIDADE_PIN
,CAST((sum(talktime) / count(anslogin)) AS FLOAT) /86400 AS TMA

FROM ech_cnu.dbo.tech

WHERE

CAST(SEGSTART AS DATE) BETWEEN '2019-12-01' AND '2019-12-01 23:59:59'
AND talktime <> 0
GROUP BY anslogin, CAST(SEGSTART AS DATE)

consulta 1
DATA_MATRICULA DATA PIN TOTAL_PAUSA PAUSA_PADRAO PAUSA_LANCHE
2019-12-01-5428511 01/12/2019 5428511 0,112233796 0,01193287 0,016296296
2019-12-01-5428516 01/12/2019 5428516 0,136388889 0,005439815 0,016435185
2019-12-01-5428521 01/12/2019 5428521 0,269733796 0,003993056 0,015405093
2019-12-01-5428532 01/12/2019 5428532 0,063726852 0,01224537 0,015092593

consulta 2
DATA_MATRICULA DATA PIN QUANTIDADE_PIN TMA
2019-12-01-5428511 01/12/2019 5428511 15 0,002708333
2019-12-01-5428516 01/12/2019 5428516 2 0,004513889
2019-12-01-5428521 01/12/2019 5428521 8 0,001099537
2019-12-01-5428532 01/12/2019 5428532 3 0,003391204

Resultado esperado
DATA_MATRICULA DATA PIN QUANTIDADE_PIN TMA TOTAL_PAUSA PAUSA_PADRAO PAUSA_LANCHE
2019-12-01-5428511 01/12/2019 5428511 15 0,002708333 0,112233796 0,01193287 0,016296296
2019-12-01-5428516 01/12/2019 5428516 2 0,004513889 0,136388889 0,005439815 0,016435185
2019-12-01-5428521 01/12/2019 5428521 8 0,001099537 0,269733796 0,003993056 0,015405093
2019-12-01-5428532 01/12/2019 5428532 3 0,003391204 0,063726852 0,01224537 0,015092593
Marcos Torres

Marcos Torres

Curtidas 0

Melhor post

Emerson Nascimento

Emerson Nascimento

11/12/2019

tente assim:
DECLARE @PAUSA_LANCHE AS INT, @PAUSA_DESCANSO AS INT, @PAUSA_PARTICULAR AS INT
DECLARE @DATAI DATETIME,@DATAF DATETIME
SET @PAUSA_LANCHE = '1200'
SET @PAUSA_DESCANSO = '1200'
SET @PAUSA_PARTICULAR = '600'
SET @DATAI = '2019-12-01'
SET @DATAF = '2019-12-31 23:59:59'

SELECT 
	A.DATA_MATRICULA,
	A.DATA,
	A.PIN,
	B.QUANTIDADE_PIN,
	B.TMA,
	A.TOTAL_PAUSA,
	A.PAUSA_PADRAO,
	A.PAUSA_LANCHE
FROM
(	SELECT
		RTRIM(CONCAT (CAST(ROW_DATE AS DATE), '-' , LOGID)) as DATA_MATRICULA
		,CAST(ROW_DATE AS DATE) AS DATA
		,[LOGID] AS PIN
		,SUM(CAST(TI_AUXTIME AS FLOAT) )/86400 AS TOTAL_PAUSA
		,SUM(CAST(TI_AUXTIME0 AS FLOAT) )/86400 AS PAUSA_PADRAO
		,SUM(CAST(TI_AUXTIME1 AS FLOAT) )/86400 AS PAUSA_LANCHE
		,CAST(IIF(SUM(TI_AUXTIME1) < @PAUSA_LANCHE , 0 , (SUM(TI_AUXTIME1) - @PAUSA_LANCHE)) AS FLOAT) / 86400 AS ESTOURO_PAUSA_LANCHE
		,SUM(CAST(TI_AUXTIME2 AS FLOAT) )/86400 AS PAUSA_PARTICULAR
		,CAST(IIF(SUM(TI_AUXTIME2) < @PAUSA_PARTICULAR , 0 , (SUM(TI_AUXTIME2) - @PAUSA_PARTICULAR)) AS FLOAT) / 86400 AS EXCEDENTE_PAUSA_PARTICULAR
		,SUM(CAST(TI_AUXTIME3 AS FLOAT) )/86400 AS PAUSA_TREINAMENTO
		,SUM(CAST(TI_AUXTIME4 AS FLOAT) )/86400 AS PAUSA_DESCANSO
		,CAST(IIF(SUM(TI_AUXTIME4) < @PAUSA_DESCANSO , 0 , (SUM(TI_AUXTIME4) - @PAUSA_DESCANSO)) AS FLOAT) / 86400 AS ESTOURO_PAUSA_DESCANSO
		,SUM(CAST(TI_AUXTIME5 AS FLOAT) )/86400 AS PAUSA_SUPORTE
		,SUM(CAST(TI_AUXTIME6 AS FLOAT) )/86400 AS PAUSA_FEEDBACK
		,SUM(CAST(TI_AUXTIME7 AS FLOAT) )/86400 AS PAUSA_SISTEMA
		,SUM(CAST(TI_AUXTIME8 AS FLOAT) )/86400 AS PAUSA_SAUDE
		,SUM(CAST(TI_AUXTIME9 AS FLOAT) )/86400 AS FUNCAO_ESPECIFICA
		,SUM(CAST(I_STAFFTIME AS FLOAT) )/86400 AS TEMPO_LOGADO
	FROM
		[ECH_CNU].[DBO].[HAGENT] (NOLOCK)
	HERE
		CAST(ROW_DATE AS DATE) between @DATAI and @DATAF
	--	AND SPLIT IN (1400,1405,1466,1467,1497)
		AND ACD= 2
	GROUP BY
		[ROW_DATE]
		,[LOGID]
	--	,[SPLIT]
) A
LEFT JOIN
(	SELECT
		CONCAT (CAST(SEGSTART AS DATE), '-' , anslogin) as DATA_MATRICULA
		,CAST(SEGSTART AS DATE) AS DATA
		,anslogin AS PIN
		,count(anslogin) AS QUANTIDADE_PIN
		,CAST((sum(talktime) / count(anslogin)) AS FLOAT) /86400 AS TMA
	FROM
		[ECH_CNU].[DBO].[tech]
	WHERE
		CAST(SEGSTART AS DATE) BETWEEN @DATAI AND @DATAF
		AND talktime <> 0
	GROUP BY
		anslogin, CAST(SEGSTART AS DATE)
) B ON B.DATA_MATRICULA = A.DATA_MATRICULA AND B.PIN = A.PIN
ORDER BY
	A.DATA_MATRICULA
	A.DATA,
	A.PIN

GOSTEI 1

Mais Respostas

Marcos Torres

Marcos Torres

10/12/2019

Muito obrigado, só precisei fazer alguns ajustes, mas a consulta funcionou exatamente como eu precisava, valeu mesmo pela força, ainda me ajudou a entender como realizar o join com duas consultas, eu não estava conseguindo entender como unir as duas.
GOSTEI 0
POSTAR