?
Solved

need help with sql code

Posted on 2012-09-07
25
Medium Priority
?
492 Views
Last Modified: 2012-10-04
Hi experts I need a sql code written for this

(1)      Has either ‘%Google%’ or ‘%MSN%’ in their sub_source code before the first tilde
(2)      AND doesn’t have ‘http%’ in their sub_source code before the first tilde
(3)             AND has an date added between 91-2008 and 9-1-2011
Please help
0
Comment
Question by:sqlcurious
  • 11
  • 8
  • 4
23 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38377589
SELECT *
FROM SomeTable
WHERE (CHARINDEX('Google', sub_source) < CHARINDEX('~', sub_source) OR
    CHARINDEX('MSN', sub_source) < CHARINDEX('~', sub_source)) AND
    sub_source NOT LIKE 'http%~' AND date_added >= '2008-09-01' AND
    date_added < '2011-09-02'

Open in new window

0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38378200
>> 9-1-2011 <<  I'm assuming that's Sept 1 not Jan  9.


SELECT *
FROM (
    SELECT
        *,
        CHARINDEX('~', sub_source_code) AS first_tilde,
        CHARINDEX('Google', sub_source_code) AS Google,
        CHARINDEX('MSN', sub_source_code) AS MSN,
        CHARINDEX('http', sub_source_code) AS http
    FROM dbo.tablename
    WHERE
        date_added >= '20080901' AND
        date_added < '20110902' AND
        CHARINDEX('~', sub_source_code) > 0 AND
        (CHARINDEX('Google', sub_source_code) > 0 OR CHARINDEX('MSN', sub_source_code) > 0)
) AS derived
WHERE
    (Google < first_tilde OR MSN < first_tilde) AND
    (http = 0 OR http > first_tilde)
0
 

Author Comment

by:sqlcurious
ID: 38383987
sorry its not working coz I am getting few cases where the google or msn is not before the first tilda

None~D15569 ~ http://www.google.com/search?sourceid=navclient&ie=UTF-8&rlz=1T4DS_enUS297US297&q=d+forever
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38384314
Did you try my suggestion?
0
 

Author Comment

by:sqlcurious
ID: 38384360
no thats not working either, thanks
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38384438
>> I am getting few cases where the google or msn is not before the first tilde <<

If you don't want that restriction, drop it from the WHERE conditions:



SELECT *
FROM (
    SELECT
        *,
        CHARINDEX('~', sub_source_code) AS first_tilde,
        CHARINDEX('Google', sub_source_code) AS Google,
        CHARINDEX('MSN', sub_source_code) AS MSN,
        CHARINDEX('http', sub_source_code) AS http
    FROM dbo.tablename
    WHERE
        date_added >= '20080901' AND
        date_added < '20110902' AND
        CHARINDEX('~', sub_source_code) > 0 AND
        (CHARINDEX('Google', sub_source_code) > 0 OR CHARINDEX('MSN', sub_source_code) > 0)
) AS derived
WHERE
    --(Google < first_tilde OR MSN < first_tilde) AND  --<<--COMMENTED THIS OUT
    (http = 0 OR http > first_tilde)
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 total points
ID: 38384642
This seems to work:

CREATE TABLE SomeTable (sub_source varchar(500), date_added datetime)

INSERT INTO SomeTable (sub_source, date_added) VALUES
('xxxGooglexx~xx', '2005-09-01'),
('xxxGooglexx~xx', '2007-09-01'),
('xxxGooglexx~xx', '2009-09-01'),
('xxxGooglexx~xx', '2011-09-01'),
('xxxGooglexx~xx', '2013-09-01'),
('xxxMSNxx~xx', '2005-09-01'),
('xxxMSNxx~xx', '2007-09-01'),
('xxxMSNxx~xx', '2009-09-01'),
('xxxMSNxx~xx', '2011-09-01'),
('xxxMSNxx~xx', '2013-09-01'),
('xxxFooxx~xx', '2005-09-01'),
('xxxFooxx~xx', '2007-09-01'),
('xxxFooxx~xx', '2009-09-01'),
('xxxFooxx~xx', '2011-09-01'),
('xxxFooxx~xx', '2013-09-01'),
('httpxxxGooglexx~xx', '2005-09-01'),
('httpxxxGooglexx~xx', '2007-09-01'),
('httpxxxGooglexx~xx', '2009-09-01'),
('httpxxxGooglexx~xx', '2011-09-01'),
('httpxxxGooglexx~xx', '2013-09-01'),
('httpxxxMSNxx~xx', '2005-09-01'),
('httpxxxMSNxx~xx', '2007-09-01'),
('httpxxxMSNxx~xx', '2009-09-01'),
('httpxxxMSNxx~xx', '2011-09-01'),
('httpxxxMSNxx~xx', '2013-09-01'),
('httpxxxFooxx~xx', '2005-09-01'),
('httpxxxFooxx~xx', '2007-09-01'),
('httpxxxFooxx~xx', '2009-09-01'),
('httpxxxFooxx~xx', '2011-09-01'),
('httpxxxFooxx~xx', '2013-09-01'),
('xxx~Google', '2008-09-01'),
('xxx~MSN', '2008-09-01'),
('None~D15569 ~ http://www.google.com/search?sourceid=navclient&ie=UTF-8&rlz=1T4DS_enUS297US297&q=d+forever', '2008-09-01')

SELECT *
FROM SomeTable
WHERE ((sub_source LIKE '%Google%' AND CHARINDEX('Google', sub_source) < CHARINDEX('~', sub_source)) OR
        (sub_source LIKE '%MSN%' AND CHARINDEX('MSN', sub_source) < CHARINDEX('~', sub_source))) AND
    NOT (sub_source LIKE '%http%' AND CHARINDEX('http', sub_source) < CHARINDEX('~', sub_source)) AND
    date_added >= '2008-09-01' AND
    date_added < '2011-09-02' 

DROP TABLE SomeTable

/* Results:

sub_source       date_added
----------------------------------------
xxxGooglexx~xx   2009-09-01 00:00:00.000
xxxGooglexx~xx   2011-09-01 00:00:00.000
xxxMSNxx~xx      2009-09-01 00:00:00.000
xxxMSNxx~xx      2011-09-01 00:00:00.000
*/

Open in new window

0
 

Author Comment

by:sqlcurious
ID: 38399598
This is great! Mathew thanks but I am missing out on a case where http is after the first tilda for ex:
('xxxGooglexx~httpxx', '2005-09-01'),
this should show up
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38450675
From your question:

(3)     AND has an date added between 91-2008 and 9-1-2011

In your last comment:

('xxxGooglexx~httpxx', '2005-09-01')

That value is outside the date range you specified :)

When I add that record to the sample data set, but use a date within the specified range, it shows up in the results.
0
 

Author Comment

by:sqlcurious
ID: 38455354
thanks
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38458627
sqlcurious,

Please explain your choice here.  The code I posted in http:#a38384642 meets the criteria you posted in the question, while the code Scott posted in http:#a38384438 does not handle some of the test cases correctly.

I'd have been happy with a split.

CREATE TABLE SomeTable (sub_source varchar(500), date_added datetime)

INSERT INTO SomeTable (sub_source, date_added) VALUES
('xxxGooglexx~xx', '2005-09-01'),
('xxxGooglexx~xx', '2007-09-01'),
('xxxGooglexx~xx', '2009-09-01'),
('xxxGooglexx~xx', '2011-09-01'),
('xxxGooglexx~xx', '2013-09-01'),
('xxxMSNxx~xx', '2005-09-01'),
('xxxMSNxx~xx', '2007-09-01'),
('xxxMSNxx~xx', '2009-09-01'),
('xxxMSNxx~xx', '2011-09-01'),
('xxxMSNxx~xx', '2013-09-01'),
('xxxFooxx~xx', '2005-09-01'),
('xxxFooxx~xx', '2007-09-01'),
('xxxFooxx~xx', '2009-09-01'),
('xxxFooxx~xx', '2011-09-01'),
('xxxFooxx~xx', '2013-09-01'),
('httpxxxGooglexx~xx', '2005-09-01'),
('httpxxxGooglexx~xx', '2007-09-01'),
('httpxxxGooglexx~xx', '2009-09-01'),
('httpxxxGooglexx~xx', '2011-09-01'),
('httpxxxGooglexx~xx', '2013-09-01'),
('httpxxxMSNxx~xx', '2005-09-01'),
('httpxxxMSNxx~xx', '2007-09-01'),
('httpxxxMSNxx~xx', '2009-09-01'),
('httpxxxMSNxx~xx', '2011-09-01'),
('httpxxxMSNxx~xx', '2013-09-01'),
('httpxxxFooxx~xx', '2005-09-01'),
('httpxxxFooxx~xx', '2007-09-01'),
('httpxxxFooxx~xx', '2009-09-01'),
('httpxxxFooxx~xx', '2011-09-01'),
('httpxxxFooxx~xx', '2013-09-01'),
('xxx~Google', '2008-09-01'),
('xxx~MSN', '2008-09-01'),
('None~D15569 ~ http://www.google.com/search?sourceid=navclient&ie=UTF-8&rlz=1T4DS_enUS297US297&q=d+forever', '2008-09-01'),
('xxxGooglexx~httpxx', '2005-09-01')

--Patrick's

SELECT *
FROM SomeTable
WHERE ((sub_source LIKE '%Google%' AND CHARINDEX('Google', sub_source) < CHARINDEX('~', sub_source)) OR
        (sub_source LIKE '%MSN%' AND CHARINDEX('MSN', sub_source) < CHARINDEX('~', sub_source))) AND
    NOT (sub_source LIKE '%http%' AND CHARINDEX('http', sub_source) < CHARINDEX('~', sub_source)) AND
    date_added >= '2008-09-01' AND
    date_added < '2011-09-02' 

/* Results:

sub_source       date_added
----------------------------------------
xxxGooglexx~xx   2009-09-01 00:00:00.000
xxxGooglexx~xx   2011-09-01 00:00:00.000
xxxMSNxx~xx      2009-09-01 00:00:00.000
xxxMSNxx~xx      2011-09-01 00:00:00.000
*/

--Scott's

SELECT sub_source, date_added 
FROM (
    SELECT 
        sub_source,
        date_added, 
        CHARINDEX('~', sub_source) AS first_tilde,
        CHARINDEX('Google', sub_source) AS Google,
        CHARINDEX('MSN', sub_source) AS MSN,
        CHARINDEX('http', sub_source) AS http
    FROM SomeTable
    WHERE
        date_added >= '20080901' AND
        date_added < '20110902' AND
        CHARINDEX('~', sub_source) > 0 AND
        (CHARINDEX('Google', sub_source) > 0 OR CHARINDEX('MSN', sub_source) > 0)
) AS derived
WHERE
    --(Google < first_tilde OR MSN < first_tilde) AND  --<<--COMMENTED THIS OUT
    (http = 0 OR http > first_tilde)
    
/* Results:

sub_source       date_added
----------------------------------------
xxxGooglexx~xx   2009-09-01 00:00:00.000
xxxGooglexx~xx   2011-09-01 00:00:00.000
xxxMSNxx~xx      2009-09-01 00:00:00.000
xxxMSNxx~xx      2011-09-01 00:00:00.000
xxx~Google       2008-09-01 00:00:00.000
xxx~MSN          2008-09-01 00:00:00.000
None~D15569 ~ http://www.google.com/search?sourceid=navclient&ie=UTF-8&rlz=1T4DS_enUS297US297&q=d+forever	2008-09-01 00:00:00.000
*/

DROP TABLE SomeTable

Open in new window


Patrick
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38459381
That's a bit unfair matthews.  I adjusted my query in response to his comment, which appeared to adjust the original conditions.

In fact, your first query does NOT meet his initial conditions:

original conditions:
"AND doesn’t have ‘http%’ in their sub_source code before the first tilde"

your code:
"sub_source NOT LIKE 'http%~'"

You did not check where http was in relation to the first tilde, you just wholely excluded it.

I likely wouldn't have posted a query at all if yours had done everything requested, as requested.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38459548
Scott,

Please be assured that I meant no offense.

You wrote:

your code:
"sub_source NOT LIKE 'http%~'"

You did not check where http was in relation to the first tilde, you just wholely excluded it.

Not true, actually.  The full condition I tested was:

NOT (sub_source LIKE '%http%' AND CHARINDEX('http', sub_source) < CHARINDEX('~', sub_source))

Open in new window


So as you can see, I did explicitly look at where the first tilde was in relation to the http.

Now, consider the following two test cases I used:

xxx~Google
xxx~MSN

Those cases should both be excluded according to the original criteria, but your query will let them pass.  (FWIW, my query excludes them.)

That may in fact have been what sqlcurious wanted, but that's not what s/he asked for :)

Patrick
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38459888
Your first/original query (the only one before I did my first post), in toto, was this:

"
SELECT *
FROM SomeTable
WHERE (CHARINDEX('Google', sub_source) < CHARINDEX('~', sub_source) OR
    CHARINDEX('MSN', sub_source) < CHARINDEX('~', sub_source)) AND
    sub_source NOT LIKE 'http%~' AND date_added >= '2008-09-01' AND
    date_added < '2011-09-02'
"

I don't see the code you posted above in that query.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38459937
Scott,

Please see http:#a38384642

You'll have to scroll down to see it, after the INSERT to populate my test data set.

:)

Patrick
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38460220
That was NOT your first post.  That was AFTER TWO OF MY POSTS.

I made clear in my comments that I was talking about your FIRST post, not your post AFTER my posts.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38460420
Yes, Scott, but why exactly does it matter that you had two posts in there before that?  Your comment http:#a38384438 fails at least two test cases that my revised code handles correctly, as I pointed out.

Now, if you can find a test case that my revised code does not handle correctly--which is possible, for all I know--I would argue that neither of us really answered the question :)

Looking at the last communication from the Asker, which came after your comments:

This is great! Mathew [sic]thanks but I am missing out on a case where http is after the first tilda for ex:
('xxxGooglexx~httpxx', '2005-09-01'),
this should show up

That indicates to me that your last post did not completely resolve the matter, and further the Asker was bringing up a "failure" that was not really a failure: as I pointed out, my code correctly rejected that item because it fell out of the date range.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38461290
>> while the code Scott posted in http:#a38384438 does not handle some of the test cases correctly. <<

I'm not clear on specifically what you mean ... rather vague.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38461385
Scott,

As I indicated in http:#a38459548, I can think of at least two test cases that your code will say are OK, but which do not meet the criteria specified in the question:

xxx~Google
xxx~MSN

Try running this script, which is based on your code:

CREATE TABLE tablename (sub_source_code varchar(500), date_added datetime)

INSERT INTO tablename (sub_source_code, date_added) VALUES
('xxx~Google', '2008-09-01'),
('xxx~MSN', '2008-09-01')

SELECT * 
FROM (
    SELECT 
        *, 
        CHARINDEX('~', sub_source_code) AS first_tilde,
        CHARINDEX('Google', sub_source_code) AS Google,
        CHARINDEX('MSN', sub_source_code) AS MSN,
        CHARINDEX('http', sub_source_code) AS http
    FROM dbo.tablename
    WHERE
        date_added >= '20080901' AND
        date_added < '20110902' AND
        CHARINDEX('~', sub_source_code) > 0 AND
        (CHARINDEX('Google', sub_source_code) > 0 OR CHARINDEX('MSN', sub_source_code) > 0)
) AS derived
WHERE
    --(Google < first_tilde OR MSN < first_tilde) AND  --<<--COMMENTED THIS OUT
    (http = 0 OR http > first_tilde)
    
DROP TABLE tablename

Open in new window


Your script will return both of those results, even though Google/MSN appear after the first tilde.  The question specifies that these appear before the first tilde.

Patrick
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38463357
LOL, so you mean the code where I CLEARLY COMMENTED OUT THAT CHECK IN RESPONSE TO A REQUEST FROM THE ORIGINAL POSTER?

The line where I added "COMMENTED THIS OUT"?

THAT'S your objection?  Seriously????
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38463447
Scott,

Using the test set I provided in http:#a38384642, I tried running your query two different ways.  First I ran it leaving that line commented out.  I then uncommented that line, and ran it again.

Both runs yielded the same result.

Patrick
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 38463485
I should have used the style I used for the last comparison. [maybe the OP did :-)]


WHERE
    (Google = 0 OR Google < first_tilde) AND
    (MSN = 0 OR MSN < first_tilde) AND
    (http = 0 OR http > first_tilde)
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38463766
That last construction seems to work against my test set :)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question