Dificuldade em realizar join
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
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
Curtidas 0
Melhor post
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
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