Forum Replies Created

Viewing 15 posts - 3,031 through 3,045 (of 5,502 total)

  • RE: need to limit query criteria

    Please provide table def, sample data and expected result together with what you've tried so far. For details on how to post sample data please see the first link in...


    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 10:11 am

    #1203649

  • RE: Can I use pivot on this query?

    But I don't think it's going to change the concept in general 😉

    It just leaves a little more work to do for the OP to modify the solution...


    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 3:55 am

    #1203337

  • RE: Can I use pivot on this query?

    Chris Morris-439714 (8/6/2010)

    ...

    Chris, please take the time to set up the sample data properly. The whole batch should run without errors, and the column names and types must match 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]

    August 6, 2010 at 3:41 am

    #1203325

  • RE: Help Re-writting Query to remove SubQuery

    Matthew VanDerlofske-459596 (8/5/2010)

    Thank you for the explaination, that change has really made the difference, the query now runs on average less then 1 second which is well within acceptable limits.

    Thank...


    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 5, 2010 at 12:15 pm

    #1203030

  • RE: Help Re-writting Query to remove SubQuery

    Continuing my prev. post: You have an index lApprovalId and lItemId. Using the phone book example your index is sorted by last name (=lApprovalId) and first name (=lItemId).

    But your WHERE...


    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 5, 2010 at 11:24 am

    #1202984

  • RE: Help Re-writting Query to remove SubQuery

    Matthew VanDerlofske-459596 (8/5/2010)

    Yeah sorry the sample data did not include any slots that would be picked up the the union, To get a data set that would get data 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]

    August 5, 2010 at 10:27 am

    #1202917

  • RE: Can I use pivot on this query?

    Ok, here's what I came up with so far:

    SELECT

    RP.PERIODNAME,

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


    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 5, 2010 at 10:13 am

    #1202902

  • RE: Help Re-writting Query to remove SubQuery

    Matthew, do you have an example that will actually trigger both parts of the UNION statement? With the data you provided I would suggest to delete the secon UNION part...


    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 5, 2010 at 9:10 am

    #1202835

  • RE: Are the posted questions getting worse?

    GilaMonster (8/4/2010)

    Steve Jones - Editor (8/4/2010)

    We have that clock in the kitchen in our house. People think it's funny.

    My supervisor at the university has a very irritating clock. No numbers...


    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 4, 2010 at 6:10 pm

    #1202445

  • RE: Help Re-writting Query to remove SubQuery

    Hmm.... it seems like the execution plan got lost somewhere in between (2 bytes doesnt sound right...).

    Anyway. Step 1 would be to replace UNION with UNION ALL to eliminate a...


    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 4, 2010 at 5:45 pm

    #1202443

  • RE: Help Re-writting Query to remove SubQuery

    Matthew VanDerlofske-459596 (8/4/2010)

    LutzM, I implemented your suggestion, however running the query the old way and the your way resulted in the same duration (12 seconds with my test case).

    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]

    August 4, 2010 at 10:24 am

    #1202191

  • RE: How to get the null column output

    Please provide table DDL and sample data.

    Based on what you've provided so far, I guess there a re some missing rows in your B cte.

    Hard to tell without sample data...


    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 4, 2010 at 6:53 am

    #1202056

  • RE: Help Re-writting Query to remove SubQuery

    Changing the function into an inline-table valued function would also help, I guess...

    CREATE FUNCTION [DSUSER].[BDP_WPS_Approvals] (@lWPSID AS INT)

    RETURNS TABLE

    AS

    RETURN

    (SELECT

    (SELECT

    a.lUserID ID,

    a.lApprovalState...


    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 4, 2010 at 6:48 am

    #1202048

  • RE: XML Conversion using Cross Apply

    You need to add the namespace declaration, especially the DEFAULT declaration:

    ;WITH XMLNAMESPACES

    ('http://www.w3.org/2001/XMLSchema-instance' as xsi, -- not required for this specific query

    'http://schemas.xmlsoap.org/soap/encoding/' as soapenc, -- not required for this...


    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 4, 2010 at 6:21 am

    #1202020

  • RE: XML Conversion using Cross Apply

    What have you tried so far?

    Please post your current solution.


    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 4, 2010 at 3:13 am

    #1201931

Viewing 15 posts - 3,031 through 3,045 (of 5,502 total)