0

I'm working on a SP that takes a lot of data, separates it into two temp tables and unions those two together. It works, but right now it's giving me a different line for each of temp tables, but I need it to grab it into one line.

USE CallDB
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[LDS_ALL_CALLS_120s_PLUS]
AS
BEGIN
end end 



--Pulls all unique calls from CallDB into temp table #tmpTotCalls

IF OBJECT_ID('tempdb..#tmpTotCalls') IS NOT NULL DROP TABLE #tmpTotCalls

SELECT DISTINCT [user],count(*) as [TotCall]
  into #tmpTotCalls
  FROM CallDB
  WHERE status <> 'ALTNUM' --and length_in_sec >= '120'
  GROUP by [user]

--Pulls all unique calls OVER 120 seconds from 
CallDB into temp table #tmp120s

IF OBJECT_ID('tempdb..#tmp120s') IS NOT NULL DROP TABLE #tmp120s

SELECT DISTINCT [user], count(*) as [120calls]
  into #tmp120s
  FROM CallDB
  WHERE status <> 'ALTNUM' and length_in_sec >= '120'
  GROUP by [user]

delete CallDB.[dbo].[120s_calls]
insert into CallDB.[dbo].[120s_calls]

Select [user], TotCall, '0' as [120calls] from #tmpTotCalls 

UNION ALL

select [USER], '0' as [Totcall], [120calls] from #tmp120s

Order by [user]

DROP TABLE #tmp120s
DROP TABLE #tmpTotCalls

select * from CallDB.[dbo].[120s_calls]

END

What I get, as a solution is:

User | totCalls | 120Calls
fernandezv | 0 | 23
fernandezv | 2147 | 0
1

Seems to me like you need a JOIN instead of a UNION:

SELECT  ISNULL(T1.[user],T2.[user]) [user],
        ISNULL(T1.TotCall,0) TotCall,
        ISNULL(T2.[120Calls],0) [120Calls]
FROM #tmpTotCalls T1
FULL JOIN #tmp120s T2
      ON T1.[user] = T2.[user]
;
| improve this answer | |
  • I'll accept when I can, this worked, thank you so much! – dragos_kai May 4 '18 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.