Forum Replies Created

Viewing 15 posts - 4,576 through 4,590 (of 5,502 total)

  • RE: select data from 3 tables (join) REGARDLESS of one table not containing a row

    I would use a UNION to get all possible CompanyID as the left side of the joins and join all three other tables to that:

    ;WITH cte as

    (

    SELECT CompanyID

    from Widgets

    UNION

    SELECT...


    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 6, 2010 at 8:45 am

    #1099648

  • RE: Help between dates

    You should use variable with datetime type. It makes it a lot easier than dealing with the separate parts of a date value. It also helps performance since you don't...


    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 6, 2010 at 8:35 am

    #1099637

  • RE: Best way for reading huge XML data

    I keep forgetting to check the forum a question is posted in... Sorry!! :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]

    January 6, 2010 at 7:56 am

    #1099588

  • RE: Best way for reading huge XML data

    Beside your sample data are not wellformed xml data (name tag with spaces), I'd use XQuery instead of openxml:

    SELECT

    c.value('column1[1]','varchar(30)') as c1,

    c.value('column2[1]','varchar(30)') as c2,

    c.value('column1500[1]','varchar(30)') as c1500

    FROM @xml.nodes('NewDataSet') a(b)

    CROSS apply b.nodes('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]

    January 6, 2010 at 7:20 am

    #1099560

  • RE: Compare values of rows in a single column

    Or are you looking for a reference to the previons date with value=1000

    DECLARE @t TABLE (reading_timestamp datetime,value int)

    INSERT INTO @t

    SELECT '06/09/2005', 1000 UNION ALL

    SELECT '07/09/2005', 990 UNION ALL...


    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 6, 2010 at 5:55 am

    #1099490

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

    sqlservercentral 53898 (1/6/2010)

    That looks excellent. Having a permanent calendar table makes absolute sense to me and your solution looks so much cleaner than mine.

    I'll give it a try when I...


    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 6, 2010 at 5:38 am

    #1099485

  • RE: Convert to decimal

    You need to divide by 5.0 instead of 5.

    Integer divided by integer will result in an integer again...


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

    #1099459

  • RE: Are the posted questions getting worse?

    BrainDonor (1/6/2010)

    He reminds me of one of my kids - he'll put more effort into avoiding doing something than if he just did it and got it out of 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]

    January 6, 2010 at 4:13 am

    #1099457

  • RE: How to get data for whole month

    Please have a look at the two articles in my signature regarding CrossTab and DynamicCrossTab. I guess you can't use PIVOT since your column names and number of columns will...


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

    #1099454

  • RE: BULK INSERT : need to load into selected columns of the table

    I used the following format file and it worked without any problem:

    Please note the way I defined the dob column to be ignored.

    9.0

    4

    1 SQLCHAR...


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

    #1099451

  • RE: BULK INSERT : need to load into selected columns of the table

    You would need a format file to exclude the dob column.

    See BOL for details.


    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 6, 2010 at 3:17 am

    #1099431

  • RE: Are the posted questions getting worse?

    Dave Ballantyne (1/5/2010)

    ...

    And another

    http://www.dotnetspider.com/forum/236299-Urgently-needed-Stored-Procedure-Please-Dont-Ignore-It.aspx

    One thing he deserves credit for is the number of forums he knows to ask for help. :hehe:


    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 6, 2010 at 2:40 am

    #1099414

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

    After looking at your solution I found that I need to include the source_ID.

    The basic concept of the join is pretty much the same you used. I just have 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]

    January 6, 2010 at 2:26 am

    #1099405

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

    Here's what I would use.

    ;with cteTally AS

    (

    SELECT row_number() OVER(order BY v1.number) AS N FROM master..spt_values v1

    WHERE type ='P' AND number<365

    ),

    cteCalendar AS

    (

    SELECT dateadd(dd,N-1,'20100101') AS day_

    FROM cteTally

    )

    ,cteHrPerDay AS

    (

    SELECT dateadd(hh,N-1,day_)...


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

    #1099318

  • RE: Date Query

    I didn't question Phils solution as "having a problem"...

    I might have misinterpreted / overreacted though.

    Probably caused by some bad private news I received shortly before... Shouldn't have posted in that...


    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 5, 2010 at 2:30 pm

    #1099251

Viewing 15 posts - 4,576 through 4,590 (of 5,502 total)