Forum Replies Created

Viewing 15 posts - 4,606 through 4,620 (of 5,502 total)

  • RE: Running totals problem

    The major issue I can see so far is using varchar(max).

    If possible for your solution, change it to varchar(8000) or less.

    It seems like the dowside of varchar(max) (being too long...


    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]

    January 4, 2010 at 4:56 pm

    #1098703

  • RE: Join / union 2 tables with SOME distinct columns

    Please post table def and sample data as described in the first link in my signature.


    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]

    January 4, 2010 at 3:45 pm

    #1098684

  • RE: Date Query

    lmu92 (1/4/2010)

    select * from wce_history where recordedtime >= dateadd(mm,-2,getdate())

    dateadd is used to calculate date offsets based on a given date. Getdate() is current date, current time. Please see BOL (Books...


    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]

    January 4, 2010 at 3:27 pm

    #1098678

  • RE: Normalize data using Pivot WITHOUT using Aggregate function

    Well, since we just have learned that you're looking for a dynamic solution, here it is:

    Step 1: running the code from before excluding the PIVOT part, but storing the result...


    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]

    January 4, 2010 at 3:12 pm

    #1098667

  • RE: looking for alternative way to write this query

    You're very welcome!

    Would you mind sharing the results for both versions when applied to your "real data" for both solutions? (including total number of rows in table and indexes on...


    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]

    January 4, 2010 at 1:58 pm

    #1098630

  • RE: Normalize data using Pivot WITHOUT using Aggregate function

    Justin James (1/4/2010)

    ... There has to be a way around using ISNULL. ...

    YES, there is: Try a different solution!

    I have to ask again: What is your reason for refusing to...


    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]

    January 4, 2010 at 1:50 pm

    #1098626

  • RE: Get data from last 3 months

    Basically, to explain the concept of an index the easiest way I've seen so far is to compare an indexed table with a phone book, where the table index equals...


    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]

    January 4, 2010 at 11:10 am

    #1098539

  • RE: looking for alternative way to write this query

    Here's another version using a CTE to check that both billing types exist.

    Assuming the required indexes are available, it is slightly faster than your solution (about 20% as per execution...


    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]

    January 4, 2010 at 10:53 am

    #1098536

  • RE: Running Totals in SQL

    You're very welcome and thank you for the feedback! 🙂

    I still recommend reading the article I referred to. It's helpful to know how it works. Just in case... 😉


    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]

    January 4, 2010 at 9:35 am

    #1098509

  • RE: looking for alternative way to write this query

    Something like this?

    SELECT ol.orderid,ol.billingType,ol.BillingFlag,count(ol.billingType) as 'Num order Lines'

    FROM orderlines ol

    WHERE OrderLineStatus<>'HIS'

    AND (

    (ol.billingtype = 'INI'

    and ol.BillingFlag = 'F'

    )

    OR

    (

    ol.billingType = 'SCH'

    and ol.BillingFlag = 'T'

    )

    )

    GROUP BY ol.orderid,ol.billingType,ol.BillingFlag


    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]

    January 4, 2010 at 9:02 am

    #1098488

  • RE: Get data from last 3 months

    There are a couple issues with the way your query is written:

    1) the query will not benefit from any index since you applied a function to the column you query...


    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]

    January 4, 2010 at 8:45 am

    #1098471

  • RE: Running Totals in SQL

    If you can add a column to your table to store the running total in AND create a CLUSTERED index on startup_Time, then you could use the "quirky update" method...


    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]

    January 4, 2010 at 7:54 am

    #1098428

  • RE: Turning multiple rows into a view of multiple columns, calculating a value

    If you want/need to do it without SSRS you might want to read the two articles I referenced below regarding CrossTab and DynamicCrossTab.


    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]

    January 4, 2010 at 7:16 am

    #1098404

  • RE: Need help with a SQL Query

    Additionally to the test data it would be nice to how you'd provide those multiple reportids (e.g. comma separated, table, xml aso).

    Basically, being a little more specific what you mean...


    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]

    January 4, 2010 at 6:53 am

    #1098388

  • RE: Normalize data using Pivot WITHOUT using Aggregate function

    Justin James (1/4/2010)

    mister.magoo (12/30/2009)

    For the sake of it, a solution which copes with new "ColumnName" values by using dynamic sql (go ahead - pork chop me or whatever you do...


    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]

    January 4, 2010 at 6:43 am

    #1098379

Viewing 15 posts - 4,606 through 4,620 (of 5,502 total)