I have code that is inserting records into a temp table, Then using that those records in a second query:
select distinct(CASE_NO) into #Temp_Case_Attach_nlto
from Case_Attachments
where CASE_NO in
(select CASE_NO from Case_Actions
where CASE_NO in (select CASE_NO from Case_Main
where STARTED >='2015-06-01' and STARTED <= '2015-06-30'))
and DESCRIPTION like '%nlto%' --(43 row(s) affected)
select * from #Temp_Case_Attach_nlto
where CASE_NO not in
(select distinct(CASE_NO) from Case_Attachments
where CASE_NO in
(select CASE_NO from Case_Actions
where CASE_NO in (select CASE_NO from Case_Main
where STARTED >='2015-06-01' and STARTED <= '2015-06-30'))
and DESCRIPTION like '%nov%') -- 41 include this first section with the and
and CASE_NO not in
(select CASE_NO from Case_Main
where STATUS = 'UNFOUNDED') --41 ---Use this number for cases resolved voluntarily
drop table #Temp_Case_Attach_nlto
I want to create a query that will allow me to do the second query getting a count without using a temp table something like:
SELECT Distinct COUNT(*)
from
(SELECT Distinct CASE_NO
FROM Case_Attachments
Where DESCRIPTION like '%nlto%'
and CASE_NO in
(Select CASE_NO from Case_Actions
Where CASE_NO in (Select CASE_NO from Case_Main
Where STARTED >= '2015-06-01' and STARTED <= '2015-06-30')))
Where CASE_NO not in
(select distinct(CASE_NO) from Case_Attachments
where CASE_NO in
(select CASE_NO from Case_Actions
where CASE_NO in (select CASE_NO from Case_Main
where STARTED >='2015-06-01' and STARTED <= '2015-06-30'))
and DESCRIPTION like '%nov%')
and CASE_NO not in
(select CASE_NO from Case_Main
where STATUS = 'UNFOUNDED')
HOWEVER, It does not like the where clause after the select from.