SQL Delimited Values

Would like to help on thsi one, but not quite getting the problem.

We can unstring and that type of thing easy enough, but what links "cmd" to "errorfound" ?

Maybe, if you can please post a sample set of data, and what you want to see as expected results ?

That John example, we probably dont care about the 9 unseccessful times, just that the tenth did work, so, would not be considered a candidate as being declined ?

Comment out the 2nd HAVING condition if you want to count visitors even if they eventually was not declined.

declare @transactions table (id int identity, post varchar(max), response varchar(max))
insert @transactions select 'cmd=process&firstname=John&lastname=Smith&ipaddress=123.123.123.123', 'errorfound=1&errormessage=Missing+Email'
insert @transactions select 'cmd=process&firstname=John&lastname=Smith&ipaddress=123.123.123.123', 'errorfound=1&errormessage=Missing+Email'
insert @transactions select 'cmd=process&firstname=John&lastname=Smith&ipaddress=123.123.123.123', 'errorfound=1&errormessage=Missing+Email'
insert @transactions select 'cmd=process&firstname=John&lastname=Smith&ipaddress=123.123.123.123', 'ok'
insert @transactions select 'cmd=process&firstname=Jim&lastname=Public&ipaddress=123.99.123.123', 'ok'
insert @transactions select 'cmd=process&firstname=Jane&lastname=Doe&ipaddress=123.100.123.123', 'ok'
insert @transactions select 'cmd=process&firstname=GI&lastname=JOE&ipaddress=123.101.123.123', 'ok'
insert @transactions select 'cmd=process&firstname=King&lastname=Long&ipaddress=100.100.123.123', 'ok'
insert @transactions select 'cmd=process&firstname=King&lastname=Long&ipaddress=100.100.123.123', 'errorfound=1&errormessage=Already+submitted'
insert @transactions select 'cmd=process&firstname=Jerry&lastname=Brown&ipaddress=101.100.123.123', 'errorfound=1&errormessage=Missing+Email'
insert @transactions select 'cmd=process&firstname=James&lastname=Brown&ipaddress=101.100.123.123', 'errorfound=1&errormessage=Missing+Email'
insert @transactions select 'cmd=process&firstname=Derek&lastname=Brown&ipaddress=101.100.123.123', 'errorfound=1&errormessage=Missing+Email'
insert @transactions select 'cmd=process&firstname=Tom&lastname=Brown&ipaddress=101.100.123.123', 'errorfound=1&errormessage=Missing+Email'
insert @transactions select 'cmd=process&no-params', 'errorfound=2&errormessage=Invalid+Request'
;with TMP as (
select
  ip = substring(post, nullif(patindex('%&ipaddress=%', post),0)+11, 100),
  is_error = case when response like '%errorfound=%' then 1 else 0 end   -- change the pattern to suit
from @transactions),
TMP2 as
(
select ip
from TMP
where ip is not null  -- don't care if we can't get ip
group by ip
having
   sum(is_error) > 0  -- unique visitors who have been declined
   and sum(is_error) = count(is_error)  -- count only where the user has never had a success
)
select count(*) from TMP2

-- result = 1.  IP address 101.100.123.123 is the only one that was declined and never accepted

Select allOpen in new window

@MikeToole - What do I declare @post as?

----
Current SQL
----

SELECT Right(@strpost, len(strpost) - CHARINDEX('&ipaddress', strpost)-10) as ipaddress,
 isnull(count(*),0) as result
 FROM tblLogs
 WHERE api = '1'
 AND strpost like '%crm.boostoffers.com%'
 AND strpost like '%&campaignId=28'
 AND strresponse like 'errorFound=1%'
 AND stamp >= '7/12/2010 12:00:00 AM'
 AND stamp <= '7/12/2010 11:59:59 PM'
 Group By Right(@strpost, len(strpost) - CHARINDEX('&ipaddress', strpost)-10)

----
Current Response
----

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@strpost".

Sorry, I was testing on a string in the variable @post, I missed removing the first @. Just change the two occurances of @strpost to strpost


Basically it looks for the position of the string '&ipaddress' in the field strpost then takes the remaining characters from strpost starting 10 to the right.

Ok, I tried the below.  Came back with no errors, but an empty result, when I know there should be.


=========
SELECT Right(strpost, len(strpost) - CHARINDEX('&ipaddress', strpost)-10) as ipaddress,
 isnull(count(*),0) as result
 FROM tblLogs
 WHERE api = '1'
 AND strpost like '%crm.boostoffers.com%'
 AND strpost like '%&campaignId=28'
 AND strresponse like 'errorFound=1%'
 AND stamp >= '7/5/2010 12:00:00 AM'
 AND stamp <= '7/12/2010 11:59:59 PM'
 Group By Right(strpost, len(strpost) - CHARINDEX('&ipaddress', strpost)-10)
==========

So if you run this you get a count?

=========
SELECT  
 isnull(count(*),0) as result
 FROM tblLogs
 WHERE api = '1'
 AND strpost like '%crm.boostoffers.com%'
 AND strpost like '%&campaignId=28'
 AND strresponse like 'errorFound=1%'
 AND stamp >= '7/5/2010 12:00:00 AM'
 AND stamp <= '7/12/2010 11:59:59 PM'
 ==========

Hi nrking83,

If you have a second would really like to get a couple of answers to those questions I posted above :)

Having the experts understanding more about your problem might make it easier to solve, and quicker to get to the bottom of it all...

Good call on that.  I was missing a character.

Just need one last thing.  See attached image.

There are still a few minor duplicates, due to the string being slightly different on the 2nd attempt
Capture.JPG

If all that we need is ipaddress being split out then please try :


;with ips as
(select substring(strpost, 11+CHARINDEX('&ipaddress=', strpost),CHARINDEX('&', strpost+'&', CHARINDEX('&ipaddress=', strpost)+11) - (11 + CHARINDEX('&ipaddress=', strpost)) ) as ipaddress, * FROM tblLogs)
select ipaddress, strpost
from ips


you can then do group by or whatever using the IPS cte query, e.g.

;with ips as
(select substring(strpost, 11+CHARINDEX('&ipaddress=', strpost),CHARINDEX('&', strpost+'&', CHARINDEX('&ipaddress=', strpost)+11) - (11 + CHARINDEX('&ipaddress=', strpost)) ) as ipaddress, * FROM tblLogs)
select ipaddress, count(*) as num_entries
from ips
group by ipaddress

@mark
Detection of the end of the IP address based on the next & was my next planned refinement.
Does the SELECT from a common table expression make any difference to the performance in this situation? - Logically it's the same as a straight select.

CTE should be fine in this case.

Really it is just a select from a subquery, just easier to use in so much as you define the subquery at the start and it then references back to that. Lot easier to read/use when there are complex definitions to resolve in the subquery first and you want to reference those items.

When it is a simple CTE like the one above, it should be OK. It really is little more than a derived table (ie a subquery).

But there are a lot of mitigating circumstances in any query that can affect performance. Any "where" clauses should of course go inside the bracket area if possible.

But if performance is a problem, then no problems just performing the inside part of the query - it really depends on what you want to do with the results. That is partly the reason for those questions in the first post. If just extracting the ipaddress, then no advantage of a CTE over the straight query.

Goodness gracious me....

Didn't want to respond to any of my questions, didnt want to clarify a few concerns, so I posted in good faith an accurate and working solution, and still no feedback. Ouch...

Well, for what it is worth, my query does actually gives you just the ipaddress (think it is the only one that does) and you can feel free to use it, despite not wanting to acknowledge any post from me (and no, I dont mean points).

Catch ya...