Forum Replies Created

Viewing 15 posts - 781 through 795 (of 1,228 total)

  • RE: Using Full-Text to "match" rows in two different tables

    Can you set up a sample table with a few rows of data? The link in my sig will show you how to do this.

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    December 8, 2011 at 3:10 pm

    #1418991

  • RE: need help with query to select with subquery

    Have you tried normalising out the first column into two, say in a CTE? If the numeric component is always the first four characters, you could do this easily with...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    November 29, 2011 at 11:25 pm

    #1414448

  • RE: CTEs

    dva2007 (11/2/2011)

    Just to clarify something for my knowledge, i thought CTE can be replacement of temp tables. I've started using CTEs and it is useful in terms of code readability,...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    November 24, 2011 at 3:02 pm

    #1412804

  • RE: Starting with "WITH"

    Same as the original, Jeff - CLR. I use T1 at work because the output of Split() is unexpected with delimiters such as ' of '.

    FWIW I tried a number...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    November 17, 2011 at 11:26 pm

    #1409688

  • RE: Starting with "WITH"

    Paul's suggestion of piling tons of rows into the recursive part to speed up counting gave me an idea. Well, two ideas. The first was to minimise the activity of...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    November 16, 2011 at 2:26 pm

    #1408893

  • RE: Starting with "WITH"

    The Dixie Flatline (11/7/2011)

    I agree with Chris.

    Because I inadvertantly started (this very entertaining and informative discussion) by making a "suckish" comment, I feel compelled to admit that I have...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    November 8, 2011 at 1:07 pm

    #1404810

  • RE: converting to INT from varchar

    MyDoggieJessie (11/7/2011)

    Hey Sean, does Jeff send you some sort of royalty checks in the mail from time-to-time? 😉

    (heh, heh)

    Read a few of Jeff's articles mate, see for yourself why...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    November 7, 2011 at 3:46 pm

    #1404202

  • RE: How can I improve this query? Need to Create Aggregates in One Step - Currently Create a Query and Subsequently Use a #Temp Table to Perform Summing and Grouping

    Do you see any difference if you change this...

    WHERE (Quote.SaleTransferredTS >= '2010-01-01')

    GROUP BY LEFT(Comp.CompanyNumber, 6), Comp.CompanyName, Quote.QuoteID,SaleTransferredTS

    HAVING LEFT(Comp.CompanyNumber, 6) = '240002')

    to...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    November 6, 2011 at 1:59 pm

    #1403735

  • RE: How can I improve this query? Need to Create Aggregates in One Step - Currently Create a Query and Subsequently Use a #Temp Table to Perform Summing and Grouping

    What's wrong with a derived table?

    SELECT CompanyName, [Account Number], MIN(Phone) AS Phone,

    MIN( Affinity) AS Affinity,MIN(Territory) AS Territory,

    MIN(County) AS County,SUM(PriorYear)AS PriorYear, SUM(CurrentYear)AS CurrentYear,

    ...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    November 6, 2011 at 1:55 pm

    #1403734

  • RE: Comparing strings

    This should get you started:

    -- Create some sample data to play with

    DROP TABLE #Fullname

    CREATE TABLE #Fullname (ID int identity(1,1), Fullname VARCHAR(50))

    INSERT INTO #Fullname (fullname)

    SELECT 'Phill R Williams' UNION ALL

    SELECT 'P...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    November 6, 2011 at 4:48 am

    #1403695

  • RE: Comparing strings

    Beginner_2008 (11/5/2011)

    how to compare a column from one table with two columns in other table keeping the primary key same in both the tables?

    After comparing if the two columns...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    November 5, 2011 at 4:08 pm

    #1403672

  • RE: Union query displays multiple records after Group By

    Have you tested the solution I posted yet? There's a reason for choosing SUM() over COUNT(*) which I'm expecting you will have to use.

    Oh, and it's all an Illusion...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    November 5, 2011 at 1:59 pm

    #1403659

  • RE: Union query displays multiple records after Group By

    Drop the UNIONs and read the source table only once:

    SELECT d.district_nm,

    urban_appr = SUM(COUNT_urban_appr),

    urban_in = SUM(COUNT_urban_inprogress),

    rural_appr = SUM(COUNT_rural_appr),

    rural_in = SUM(COUNT_rural_inprogress),

    esthal_appr = SUM(COUNT_esthal_appr),

    ground_in = SUM(COUNT_esthal_inprogress)

    FROM (

    SELECT

    district_nm,

    scheme_type,

    COUNT_urban_appr= SUM(CASE WHEN urban_appr IS...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    November 5, 2011 at 8:32 am

    #1403623

  • RE: Help with performance - aggregating a lot of data

    Here's a few preliminary results from queries posted so far, and another which I'm guessing is Jeff's preaggregate. The preaggregate works fastest but not by a fat lot. I've not...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    October 30, 2011 at 3:54 pm

    #1400900

  • RE: Help with performance - aggregating a lot of data

    J Livingston SQL (10/28/2011)

    Hi Ki

    I will try again with your test rig....earlier on I thought it was my PC playing up...until I saw Jeff's post.

    think I will reboot and start...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    October 28, 2011 at 12:30 pm

    #1400758

Viewing 15 posts - 781 through 795 (of 1,228 total)