October 18, 2006 at 1:15 pm
I have a table called trackingpixels (schema and example of data below)
I need to find out which adid's have the auto pixel, but not the realestate pixel and vice versa.
I'm having trouble when trying to compare the pixel as it is in text format.
Any Ideas?
This give me the list of ADID's, but I can't seem to pull in the pixel
select count(adid), adid from trackingpixels
group by adid
having count(adid) = 1
ADID (Numeric)
Pixel (Text)
Type (nvarchar)
Subtype (nvarchar)
Clientname (varchar)
Here's a few rows of data
10045 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos[IDENTIFIER]"> A L Reply
10045 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate_%5BIDENTIFIER]"> N L Reply
10046 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos_%5BIDENTIFIER]"> A L Reply
10046 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate_%5BIDENTIFIER]"> N L Reply
10047 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos_%5BIDENTIFIER]"> A L Reply
10047 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate_%5BIDENTIFIER]"> N L Reply
10048 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos_%5BIDENTIFIER]"> A L Reply
10048 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate_%5BIDENTIFIER]"> N L Reply
10049 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos_%5BIDENTIFIER]"> A L Reply
10049 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate_%5BIDENTIFIER]"> N L Reply
10050 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos_%5BIDENTIFIER]"> A L Reply
10050 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate_%5BIDENTIFIER]"> N L Reply
10051 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos_%5BIDENTIFIER]"> A L Reply
10051 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate_%5BIDENTIFIER]"> N L Reply
10052 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos_%5BIDENTIFIER]"> A L Reply
10052 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate_%5BIDENTIFIER]"> N L Reply
10053 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos_%5BIDENTIFIER]"> A L Reply
10053 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate_%5BIDENTIFIER]"> N L Reply
10054 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos_%5BIDENTIFIER]"> A L Reply
10054 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate_%5BIDENTIFIER]"> N L Reply
10055 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos_%5BIDENTIFIER]"> A L Reply
10055 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate_%5BIDENTIFIER]"> N L Reply
10056 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos_%5BIDENTIFIER]"> A L Reply
10056 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate_%5BIDENTIFIER]"> N L Reply
10057 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos_%5BIDENTIFIER]"> A L Reply
10057 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate_%5BIDENTIFIER]"> N L Reply
10058 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos_%5BIDENTIFIER]"> A L Reply
10058 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate_%5BIDENTIFIER]"> N L Reply
10059 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos_%5BIDENTIFIER]"> A L Reply
10059 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate_%5BIDENTIFIER]"> N L Reply
10060 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos_%5BIDENTIFIER]"> A L Reply
10060 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate_%5BIDENTIFIER]"> N L Reply
10061 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos_%5BIDENTIFIER]"> A L Reply
10061 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate_%5BIDENTIFIER]"> N L Reply
10062 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos_%5BIDENTIFIER]"> A L Reply
10062 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate_%5BIDENTIFIER]"> N L Reply
10063 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos_%5BIDENTIFIER]"> A L Reply
10063 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate_%5BIDENTIFIER]"> N L Reply
10064 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos_%5BIDENTIFIER]"> A L Reply
10064 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate_%5BIDENTIFIER]"> N L Reply
10065 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos_%5BIDENTIFIER]"> A L Reply
10065 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate_%5BIDENTIFIER]"> N L Reply
10066 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos_%5BIDENTIFIER]"> A L Reply
10066 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate_%5BIDENTIFIER]"> N L Reply
10067 <img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=autos_%5BIDENTIFIER]"> A L Reply
October 18, 2006 at 2:26 pm
figured it out..
There maybe a better way, but this is what I ended up with
insert into trackingpixels
select adid,'<img src="','N','L','Reply'">https://reporting.cpcmanager.com/ct/146/x.gif?cid=auto%5BIDENTIFIER%5D">','N','L','Reply'
from trackingpixels
where adid in
(select adid from trackingpixels
group by adid
having count(adid) = 1)
and pixel like '<img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate%'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
