SQL Addition of two Temp Tables

I have two temp tables that are counting a number of IDs. I want to combine those tables to give the count for each and then add those together. This is what I have so far.

if object_id('tempdb..#order') is not null drop table #order
select count (a.patientSID) as 'Order Count'

into #order
from CPRSOrder.CPRSOrder a
join sstaff.SStaff b on b.staffSID = a.EnteredbyStaffSID
join spatient.spatient c on c.patientSID = a.patientSID
where b.staffName = xxxxxxxx
and a.enteredDateTime >= '20180801' and a.enteredDateTime <= '20180828'

if object_id('tempdb..#note') is not null drop table #note
select count (a.patientSID) as 'Note Count'

into #note
from tiu.tiudocument a
join sstaff.SStaff b on b.staffSID = a.EnteredbyStaffSID
--join spatient.spatient c on c.patientSID = a.patientSID
where b.staffName = xxxxxxxx
and a.episodeBeginDateTime >= '20180801' and a.episodeBeginDateTime <= '20180828'

select (select [Note Count] from #note) as 'Note Count',
(select [Order Count] from #order) as 'Order Count',
sum((select [Order Count] from #order) + (select [Note Count] from #note)) as Total


2 Answers
2

Remove the sum(), unless you want to aggregate. Also, since the tables contain only one row each, this could be simplified a little by using a cross join.

sum()

SELECT n.[Note Count],
o.[Order Count],
n.[Note Count] + o.[Order Count] [Total]
FROM #note n
CROSS JOIN #order o;


While there's nothing syntactically wrong with selecting a single column from a temp table, what is clear is you are using an entire temp table to save a single value, an aggregated sum. An integer variable can also hold a count. For example:

DECLARE @order int =
(
select count (a.patientSID)
from CPRSOrder.CPRSOrder a
join sstaff.SStaff b on b.staffSID = a.EnteredbyStaffSID
join spatient.spatient c on c.patientSID = a.patientSID
where b.staffName = xxxxxxxx
and a.enteredDateTime >= '20180801' and a.enteredDateTime <= '20180828'
)

DECLARE @note int = (
select count (a.patientSID)
from tiu.tiudocument a
join sstaff.SStaff b on b.staffSID = a.EnteredbyStaffSID
--join spatient.spatient c on c.patientSID = a.patientSID
where b.staffName = xxxxxxxx
and a.episodeBeginDateTime >= '20180801' and a.episodeBeginDateTime <= '20180828'
)

SELECT @note AS [note count]
,@order AS [order count]
,@order + @note AS [total]


Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.