[thelist] MS SQl help ASAP

[Brian Cummiskey]

Matt Warden wrote:
> 
> Well then it is not clear what you are trying to do. 

the personalIdentifier field contains either a numeric code 01 through 
52 and denotes a positive response, or a text string such as "refused", 
"not given", etc, but is entirely data-entry fill in, so it can be any 
combination there in, and typos galore, making it impossible to query on 
a set set of words

Do you wish for
> strings like 'abc123' to be included or excluded? Because it seems
> like you are not actually looking for the opposite, can you give a
> clearer idea of what you mean by the opposite of the original query?
> 


the original code was:
and personalidentifier in ('refused', 'rpq')

OR, for the opposite,
and personalidentifier not in ('refused', 'rpq')

and then they changed it to a fill-in against my opinion, and sh*@ hit 
the fan.  lol



its unclear because my query is so huge.


this is an abridged version of the actual query, 02 being the one that 
needs alphas, and 07 being the one that needs numerics:

@startdate and @endaate are passed in via the stored procedure/asp


//start

declare @uCnt 	float


select @uCnt = count(*) from IK4342A
where i3_rowid in (select i3_rowid from IK4342A_callhistory where reason 
in ('success', 'failure')
group by i3_rowid having max(calldate) between @startdate + ' 00:00:00' 
and @enddate + ' 23:59:59' )
and status = 'u'


select * from
(

	select '02' as 'NO', 'LABEL - W/ FAX' AS 'ACTIVITY',
		cast(count(*) as int) as 'DISPOSITION',
		case when @uCnt = 0 then 'Sorry, no records'
			else		
		cast((convert(decimal(10,2), ((count(*)/@uCnt) * 100))) as varchar) + 
'%' end as 'PERCENT'
	from IK4342A
		where i3_rowid in (select i3_rowid from IK4342A_callhistory where 
reason in ('success', 'failure')
		group by i3_rowid having max(calldate) between @startdate + ' 
00:00:00' and @enddate + ' 23:59:59' )
		and resultcode = 'SUB'
			and companyfax_new NOT IN('', 'NONE')		
	        and ReplacementNameIndicator <> 'Y'
			and i3_rowid not like 'x%'
			and PersonalIdentifier NOT IN( select distinct(personalidentifier)
				from IK4342A
				where isnumeric(personalidentifier) = 1)
				
			and email_new IN('', 'NONE')

	union 
-------------------------------------------------------------------------------------------------------
	select 	'07' as 'NO', 'RPQ - W/ E-MAIL' AS 'ACTIVITY',
		cast(count(*) as int) as 'DISPOSITION', 	
		case when @uCnt = 0 then 'Sorry, no records'
			else		
		cast((convert(decimal(10,2), ((count(*)/@uCnt) * 100))) as varchar) + 
'%' end as 'PERCENT'
	from IK4342A
		where i3_rowid in (select i3_rowid from IK4342A_callhistory where 
reason in ('success', 'failure')
		group by i3_rowid having max(calldate) between @startdate + ' 
00:00:00' and @enddate + ' 23:59:59' )
		and resultcode = 'SUB'
			and email_new NOT IN('', 'NONE')		
			and ReplacementNameIndicator <> 'Y'
			and isnumeric(PersonalIdentifier) = 1
             and i3_rowid not like 'x%'
) as t
order by [NO] asc


// end

and did i mention there's 52 unions?  lol
the program was made horribly.  and i'm not the one who made it.  But, 
the accountants are basing their billing off this report, so it needs to 
be dead on, and i just can't get it to match my total resultcode = 'SUB' 
quantity.  This is basically a split-out of that resultcode based on 
certain parameters, like email given, fax not given, email not given, 
fax given, if a secretary signed off for them or not, and so forth.