Forum Replies Created

Viewing 15 posts - 30,886 through 30,900 (of 59,101 total)

  • RE: Week Starts On Saturday?

    I believe the following simple formula will do the same thing for you. No need for a function here at all.

    DATEADD(dd,DATEDIFF(dd,-2,SomeDateColumn)/7*7,-2)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    March 4, 2012 at 12:46 pm

    #1454597

  • RE: How to display months across like crosstab?

    siva 20997 (3/4/2012)

    Sorry. I have been looking at the wrong code

    I should have been looking at Abishalt code

    Ah, got it. Your questions were conerning Abishalt's code.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    March 4, 2012 at 12:24 pm

    #1454596

  • RE: How to display months across like crosstab?

    siva 20997 (3/4/2012)

    SELECT Customer,

    Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec

    FROM

    (

    Select

    left(datename(month,SalesDate ),3) as mn

    ,Sales,Customer

    from

    #Sales

    ) AS SourceTable

    PIVOT

    (

    sum(Sales)

    FOR mn IN (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)

    ) AS PivotTable;

    1) The Data mn,Sales,Cusomer is being Pivoted here. Is there a some other command called PIVOT...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    March 4, 2012 at 12:07 pm

    #1454593

  • RE: Generate consecutive numbers

    Oh, be careful, now...

    That's why I use sys.all_columns. Even on a brand new instance, it contains more than 4,000 rows when all features are installed correctly and it...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    March 4, 2012 at 11:53 am

    #1454591

  • RE: How to display months across like crosstab?

    siva 20997 (3/4/2012)

    I am not sure where you can use variables in the Pivot Statment so as to move the12 month period forward month by month

    Livingston's code didn't use a...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    March 4, 2012 at 11:45 am

    #1454590

  • RE: Select columns dynamically

    assefam (3/4/2012)

    Really thank you very much for your best explanation. I am satisfied using temp table.

    how to solve this problem using dynamic SQL? Thank U

    You're welcome.

    Before I set to...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    March 4, 2012 at 11:40 am

    #1454589

  • RE: Column Name Restrictions / Description Keyword

    david.holley (3/3/2012)

    I'm looking at adding a table, one of the 'common sense' names for a column in that table is 'Description', however its my understanding that 'Description' is a keyword....

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    March 4, 2012 at 11:35 am

    #1454586

  • RE: Column Name Restrictions / Description Keyword

    abhilasht (3/4/2012)

    Hello,

    see the defference..

    declare @tbl TABLE(create smalldatetime,view int,declare decimal(20,4))

    --it will rise error

    go

    declare @tbl TABLE([create] smalldatetime,[view] int,[declare] decimal(20,4))

    -- this wont

    The OP did say that he preferred to not further...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    March 4, 2012 at 11:26 am

    #1454584

  • RE: How to display months across like crosstab?

    siva 20997 (3/4/2012)

    I agree livingstones method works.

    However you have write the script everytime you want to Execute it becaus...

    Nope, you don't. If you make the minor tweak of using...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    March 4, 2012 at 11:21 am

    #1454583

  • RE: search mdb files in specific path

    Vahid.Ch (3/3/2012)

    hi there,I want to search mdb files in specific path.

    following query select all of files in "..\Microsoft SQL Server\MSSQL10_50.SPR2\MSSQL\DATA\.." path.

    SELECT name, physical_name AS current_file_location

    FROM sys.master_files

    but how can...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    March 4, 2012 at 11:04 am

    #1454580

  • RE: Slight mystery....

    Check your AssignedUsers table with the following code. I'm betting that the AssignedUsers_UserID has duplicate values in it. If there are any dupes, the following code will find...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    March 4, 2012 at 8:54 am

    #1454567

  • RE: Select columns dynamically

    abhilasht (3/4/2012)

    Hello, its can do.

    Forget what it's for. Let's see it. 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    March 4, 2012 at 8:50 am

    #1454565

  • RE: Select columns dynamically

    Since we're not allow to use Dynamic SQL, we need to do it in a fairly odd way. I originally got this idea from Jonathan Livingston SQL on a...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    March 4, 2012 at 8:34 am

    #1454563

  • RE: How to display months across like crosstab?

    siva 20997 (3/4/2012)

    However putting the correct title in the TSQL itself with in the Pivot Command I am not if that can be done

    Actually, I missed what Jonathan Livingston SQL...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    March 4, 2012 at 7:42 am

    #1454560

  • RE: How to display months across like crosstab?

    abhilasht (3/4/2012)

    So we must have the details on the Periods.

    Here i created one sample periods #period table. and come one more issue to find the available periods. So it...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    March 4, 2012 at 7:39 am

    #1454558

Viewing 15 posts - 30,886 through 30,900 (of 59,101 total)