Forum Replies Created

Viewing 15 posts - 3,916 through 3,930 (of 5,502 total)

  • RE: Retrieving cut-off time relevant data with date change

    Glad I could help 🙂

    And I'd like to thank you for taking the time to set up sample data in a ready to use format. Made it easy to work...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

    March 24, 2010 at 11:39 am

    #1139116

  • RE: Assign value to another fieldname using CASE statement

    Is it something like the following that you're looking for?

    Please note that I used the STR() function to convert the numeric value to to a string data type:

    DECLARE @tbl TABLE

    (

    ...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

    March 24, 2010 at 10:59 am

    #1139074

  • RE: How to write query for summing duplicate records and insert this summed records into my table after deleting the old duplicate records

    Please help us help you and provide ready to use sample data.

    The most efficient way how to do that is described in the first link in my signature.

    Providing some Excel...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

    March 23, 2010 at 5:47 pm

    #1138619

  • RE: how to strip out all non alpha numeric characters?

    @peter-2:

    BTW, where did you get [0-Z] from. I was surprised to see that ':' or ';' do not fall within that range when using a collation like Latin1_General_CI_Ax. I scanned...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

    March 23, 2010 at 5:38 pm

    #1138613

  • RE: Assign value to another fieldname using CASE statement

    You should use equal comparison rather than not equal.

    CASE isComments WHEN 1 THEN rd.responseComments

    WHEN 0 THEN ''

    ...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

    March 23, 2010 at 1:52 pm

    #1138533

  • RE: how to strip out all non alpha numeric characters?

    Thanx for clarification, Peter!

    I knew there must have been a reason Lowell provided a method that seemed to leave room for improvement... :blush:


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

    March 23, 2010 at 1:46 pm

    #1138526

  • RE: help in avoiding loop

    I would look into a Tally table solution with CROSS APPLY or a split string function together with an intermediate table and a quirky update or any combination of those...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

    March 23, 2010 at 1:40 pm

    #1138523

  • RE: Need Help Creating T-SQL Function from VBA

    The main question is: what logic do you use to define the week number?

    Are you using ISO week, SQL standard (January 1st = week 1), DATEFIRST dependent or not or...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

    March 23, 2010 at 1:26 pm

    #1138513

  • RE: Need help in preparing code review check list for SPs

    What's on your list so far?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

    March 23, 2010 at 1:18 pm

    #1138511

  • RE: need help in writing query

    Lynn Pettis (3/23/2010)

    lmu92 (3/23/2010)

    @Lynn:

    Why would you use two functions for this task rather than doing it all at once?

    My wild guess would be that the pure UNPIVOT solution should be...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

    March 23, 2010 at 1:12 pm

    #1138507

  • RE: need help in writing query

    @Lynn:

    Why would you use two functions for this task rather than doing it all at once?

    My wild guess would be that the pure UNPIVOT solution should be more efficient. What...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

    March 23, 2010 at 1:01 pm

    #1138501

  • RE: how to strip out all non alpha numeric characters?

    Maybe you could make the CASE statement of Lowells nice function a little shorter:

    CASE

    WHEN (SUBSTRING(@OriginalText,Tally.N,1)) like '[0-Z]'

    THEN SUBSTRING(@OriginalText,Tally.N,1)

    ELSE '' END

    It seems...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

    March 23, 2010 at 12:52 pm

    #1138488

  • RE: need help in writing query

    Would something like this help?

    DECLARE @tbl TABLE

    (id INT,

    c1 DATETIME,

    c2 DATETIME,

    c3 DATETIME,

    c4 DATETIME

    )

    INSERT INTO @tbl

    SELECT 1,GETDATE(), GETDATE()-1,GETDATE()-2,GETDATE()+3 UNION ALL

    SELECT 2,GETDATE()-5, GETDATE()-6,GETDATE()-4,GETDATE()-3

    ;WITH cte AS

    (

    SELECT id,DateVal

    FROM...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

    March 23, 2010 at 12:35 pm

    #1138482

  • RE: Retrieving cut-off time relevant data with date change

    Hi Marin,

    I'm not sure if it's overengineered but I tried the divide'n'conquer method, causing the numerous CTEs... 😉

    DECLARE @date DATETIME

    SET @date='2010-03-24 10:20:00'

    ;WITH

    cte0 AS

    -- find the latest order per customer...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

    March 23, 2010 at 12:22 pm

    #1138476

  • RE: Retrieving cut-off time relevant data with date change

    So, what have you tried so far?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

    March 23, 2010 at 10:18 am

    #1138368

Viewing 15 posts - 3,916 through 3,930 (of 5,502 total)