Forum Replies Created

Viewing 15 posts - 3,016 through 3,030 (of 5,502 total)

  • RE: problem reading a xml field: xmlns causing null result

    please provide the sample xml data in total, not only the first line.

    You'll need to use WITH XMLNAMESPACES() together with your select statement, and maybe you'd need to add the...


    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]

    August 11, 2010 at 5:17 am

    #1205278

  • RE: Can I use pivot on this query?

    Glad I could help 😀

    Would you mind sharing the performance difference for the solution you used before vs. the one I provided? Just being curious...


    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]

    August 11, 2010 at 5:06 am

    #1205271

  • RE: Help needed to generate an XML Structured output file

    Please provide table def and sample data so we have something to test against.

    Also, please show us what you've 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]

    August 11, 2010 at 5:04 am

    #1205270

  • RE: Increment 1 min when matching start and end time

    ColdCoffee (8/10/2010)

    LutzM (8/10/2010)

    @ColdCoffee:

    Sorry for sending you in the wrong direction!

    Not at all, Lutz. I already coded the sample data with IDENTITY column , so that i will get a ROW...


    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]

    August 11, 2010 at 5:00 am

    #1205269

  • RE: Sorting with multiple columns using CASE

    With your current design it's close to impossible (unless you're going to use some delimited strings): you have two variables (@SortBy and @SortDirection) and you want to have more than...


    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]

    August 10, 2010 at 3:51 pm

    #1205074

  • RE: Updating 2 rows in table B based on 1 row from table A

    Even though I have no idea what value you want to store in the DescriptionID column nor do I have a clue as how you'd calculate the number to 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]

    August 10, 2010 at 3:42 pm

    #1205068

  • RE: Are the posted questions getting worse?

    GilaMonster (8/10/2010)

    ...Love to, but it wouldn't be allowed through customs on this side. Same with honey, fruit and meat products etc. Otherwise I'd bring biltong when I visit (as...


    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]

    August 10, 2010 at 2:42 pm

    #1205050

  • RE: Increment 1 min when matching start and end time

    Drew,

    I guess the Row_Number approach I was hitting for was overdesigned for the task...

    You're absolutely right, for the given task a simple join on the time columns would be enough.

    That's...


    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]

    August 10, 2010 at 1:06 pm

    #1204995

  • RE: Increment 1 min when matching start and end time

    You would need to use ROW_NUMBER() to put your data in sequence and do a self join on this subquery (or better, CTE) based on the row number column with...


    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]

    August 10, 2010 at 11:22 am

    #1204925

  • RE: Can I use pivot on this query?

    I would use CrossTab over PIVOT due to more flexibility:

    ;

    WITH cte AS

    (

    SELECT

    RP.PERIODNAME,

    [1.1] = COUNT(CASE WHEN C.CASETYPE = 'F' AND C.PROPERTYTYPE = 'P' AND RIGHT(C.IRN, 2) = 'PA' AND CE.EVENTNO =...


    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]

    August 10, 2010 at 11:14 am

    #1204921

  • RE: Creating Pivot table

    PIVOT isn't really that great of a tool if you either have to pivot by more than one column or if your target columns aren't fixed.

    You should have a look...


    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]

    August 9, 2010 at 1:46 pm

    #1204371

  • RE: Are the posted questions getting worse?

    bitbucket-25253 (8/9/2010)

    ... teaching him you catch more flies with honey than vinegar?

    :-D;-):-D

    I don't think that's actually true: That's how we get rid of fruit flies:

    1/2 shot glass cider vinegar,

    1/2 shot...


    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]

    August 9, 2010 at 1:20 pm

    #1204357

  • RE: how generate series?

    Please have a look at the TallyTable article referenced in my signature.

    Once you have such a table it's as easy as

    SELECT @start + N

    FROM Tally

    WHERE N <= (@end - @start)

    (You...


    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]

    August 9, 2010 at 1:00 pm

    #1204344

  • RE: sorting

    I would probably use PATINDEX(). Something like

    DECLARE @search CHAR(2)

    SET @search='sm';

    WITH tab AS

    (

    SELECT 'Smith' AS lastname, 'John' AS firstname UNION ALL

    SELECT 'GoldSmith', 'Vincent' UNION ALL

    SELECT 'Smith', 'Patrick' UNION ALL

    SELECT 'Blacksmith',...


    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]

    August 9, 2010 at 12:53 pm

    #1204341

  • RE: Find the min and max effective date for when a value changed

    Would something along the following lines help you? (the "trick" is to build subgroups by contract_type as shown with the subgrp aliased column)

    ;

    WITH cte AS

    (

    SELECT

    *,

    ROW_NUMBER() OVER(ORDER BY...


    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]

    August 6, 2010 at 4:25 pm

    #1203815

Viewing 15 posts - 3,016 through 3,030 (of 5,502 total)