Forum Replies Created

Viewing 15 posts - 38,461 through 38,475 (of 59,098 total)

  • RE: Retrieve rows only if the difference in two rows is less than 2

    Yes... Self Joined CTE with ROW_NUMBER() so you can join the rows with an offset of 1. Since you're brand new, you might want to take a look at...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 11, 2010 at 8:17 pm

    #1165919

  • RE: While Loop Much Slower than LOCAL FAST_FORWARD READ_ONLY Cursor

    Robb Melancon (5/11/2010)

    Thanks for the replies, I will look into the "quirky update" method as a possible solution. The calculations are more involved than simple summing but I think...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 11, 2010 at 8:11 pm

    #1165918

  • RE: How to obtain week by week plus YTD totals

    simflex-897410 (5/11/2010)

    Once again, my apologies Lutz.

    I have fixed them up and have been able to generate data.

    Please see entire scripts from Createtable, to insert, to query.

    If (OBJECT_ID('dbo.HHSDataTest', 'Table') Is Not...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 11, 2010 at 8:06 pm

    #1165916

  • RE: High record/calculation query performance help

    The problems are many here. You're simply trying to do too much in a single GROUP BY which is why you have to duplicate so much of the SELECT...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 11, 2010 at 7:53 pm

    #1165913

  • RE: Monthly Data

    shjaffer (5/11/2010)

    I have this query that I want it to give me data for the 12 months.

    Thanks for the sample data but... WHICH 12 months? Current Calendar year...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 11, 2010 at 7:45 pm

    #1165912

  • RE: While Loop Much Slower than LOCAL FAST_FORWARD READ_ONLY Cursor

    Robb Melancon (5/11/2010)

    basically each row has to be compared to the previous row in order to do some calculations to get a daily "performance" of Assets/Accounts by some grouping

    Unless the...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 11, 2010 at 7:27 pm

    #1165910

  • RE: Selecting row data into columns

    rfranklin-741429 (5/11/2010)

    Thanks for the link. I actually did read it earlier but will go thru it again. The version of SQL Server we are on does not support...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 11, 2010 at 7:19 pm

    #1165907

  • RE: Selecting row data into columns

    bitbucket-25253 (5/11/2010)

    From Jeff's article:

    Last but not least, I currently only have SQL Server 2000 and 2005 installed. I indicate which rev each section of code will run on in parenthesis

    Emphasis...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 11, 2010 at 7:18 pm

    #1165906

  • RE: Code generation

    Magoo... here's what I'm talking about... if I take out the partitioning that supports the dupe check, then you probably get more like what you're expecting...

    WITH

    cteFirstGen AS

    ( --=== Gen enough...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 11, 2010 at 6:35 pm

    #1165902

  • RE: Code generation

    mister.magoo (5/11/2010)

    Jeff, that is an interesting take on Random..... :w00t:

    The first ten codes in the sample I tried...

    10000180

    10000F52

    10001875

    10001D17

    1000221F

    10003478

    100035E7

    10003763

    10006415

    100081B2

    Is there some bias towards groups of characters in that solution ....

    Or am...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 11, 2010 at 6:26 pm

    #1165900

  • RE: Code generation

    scziege (5/11/2010)

    Absolutly Great job

    I love a satisfied customer. Thanks for the feedback. Most folks on this thread had the right idea to begin with...

    Now... a favor from...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 11, 2010 at 2:47 pm

    #1165843

  • RE: Code generation

    Lowell (5/11/2010)

    Jeff Moden (5/11/2010)

    1.6 million random 8 character codes that no one can guess and they're guaranteed to be unique within the set... takes about 25 seconds on my 8...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 11, 2010 at 2:41 pm

    #1165841

  • RE: problem importing data from a huge xml file 1g

    sohairzaki (5/11/2010)

    I used ssis xml task xpath the original file was of the following form

    ______________

    My input file has the following format

    <enterprise>

    <person>

    <sourcedid>

    <source>111111</source>

    <id>22222</id>

    </sourcedid>

    <name>

    <fn>xxxxxxx</fn>

    <n>

    <family>yyyyy</family>

    <given>zzzzzz</given>

    </n>

    </name>

    <demographics>

    <gender>2</gender>

    </demographics>

    <email>xxxxxx@fffff.edu</email>

    <adr>

    <street>cccccc</street>

    <locality>ccccc</locality>

    <region>cccc</region>

    <pcode>ccccccc</pcode>

    </adr>

    <academics>

    <academicmajor>gggggg</academicmajor>

    <customrole>hhhhhh</customrole>

    <customrole>dddddd</customrole>

    <customrole>cccccc</customrole>

    </academics>

    </person>

    <person>

    …

    </person>

    </enterprise>

    ______________________

    out put was in the form of...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 11, 2010 at 2:35 pm

    #1165836

  • RE: CTE performance

    kazim.raza (5/11/2010)

    Yo nailed it.. here's what I did

    I have joined EntityType with FullyQualifiedName. The script would treat it as another extension to levels. My last select remains intact and I...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 11, 2010 at 2:26 pm

    #1165831

  • RE: Code generation

    1.6 million random 8 character codes that no one can guess and they're guaranteed to be unique within the set... takes about 25 seconds on my 8 year old desktop...

    WITH

    cteFirstGen...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 11, 2010 at 7:41 am

    #1165534

Viewing 15 posts - 38,461 through 38,475 (of 59,098 total)