running sql2008. i have the following two temp tables. the first is just all web service errors for a date range. the second is simply 20 rows that contain values of types of web service errors.
--temp tbl #1 select [date],[err],[srv],[e1],[e2] into #tblExcept from netlog where [err] like '%web service%' and convert(char(10),[date],120) >= @startDate and convert(char(10),[date],120) <= @endDate order by [date]
--temp tbl #2 create table #tblWSerr (errName nvarchar(255)); insert into #tblWSerr values ('Error connecting'), ('server down'), ('mainframe'), ('invalid call'), . . . . ('request aborted');
i need to execute 3 queries: 1) return a count for each occurrence of records in tbl #2 as in tbl1. 2) do the opposite of query #1, and 3) count total number of records returns by #2.
1)the first is below which returns a count of each web service error in tbl #1 and that works perfectly:
select t2.errName as 'Err Msg',count(t1.[message]) as 'Count' from #tblExcept t1,#tblWSerr t2 where t1.[message] like '%' + t2.errName + '%' group by t2.errName
2) have tried many different ways by using other operators and not exists, in, joins, or just flipping the above query by using "not like" (see below), but results are of course wrong:
select t1.[Message],t2.errName as 'Err Msg',count(t1.[message]) as 'Count' from #tblWebSvcExcept t1,#tblWSerr t2 where t1.[message] not like '%' + t2.errName + '% group by t1.[message]
questions: what query can i use to return a count of all occurences NOT in tbl2 (opposite of #1)?
thanks in advance.
August 17, 2011 10:02 PM
March 31, 2012 6:12 PM