Forum Replies Created

Viewing 15 posts - 676 through 690 (of 1,228 total)

  • RE: How to filter the output clause from Merge

    Stephanie Giovannini (1/3/2012)

    ...I also wish there was a way to declare a table variable and have its column structure exactly match another table's structure without writing out all the columns...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 3, 2012 at 1:56 pm

    #1427984

  • RE: How to filter the output clause from Merge

    I've always used a local temp table to capture OUTPUT.

    Scanning BOL, you can't OUTPUT to a remote view - but what about a 'local' one? Use $action and filter...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 3, 2012 at 11:29 am

    #1427913

  • RE: update 1 col with values of col2 and 3

    Uripedes Pants (1/3/2012)

    my point was that, sure, there COULD be a buisness need, but it's really a formatting issue and should be done at display time rather than stored. :hehe:

    As...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 3, 2012 at 11:21 am

    #1427907

  • RE: Error converting data type varchar to datetime in stored Procedure

    You're welcome.

    Did you resolve the problem? Would you like to share with us the resolution?

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 3, 2012 at 10:33 am

    #1427883

  • RE: Help with Stored Procedure

    Look up GROUP BY in books on line (BOL).

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 3, 2012 at 9:31 am

    #1427806

  • RE: exclude certain entries but not all when something is found

    You can use CASE in a WHERE clause.

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 3, 2012 at 8:35 am

    #1427771

  • RE: Group By Date loses records but Date required for join

    mic.con87 (1/3/2012)

    @ ChrisM@Home: I'm extremely sorry for the late reply! I actually completely forgot we were busy with this, used your previous code and moved on to the other problems...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 3, 2012 at 8:20 am

    #1427762

  • RE: Recusion on Group and their subGroups- how can I solve this problem?

    Shamshad Ali (1/3/2012)

    ...why we are limiting it to lr.Level < 8?

    does that means we are drilling down to 7th level? and NOT till nth Level?...

    Where A is a member of...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 3, 2012 at 7:45 am

    #1427734

  • RE: Recusion on Group and their subGroups- how can I solve this problem?

    Try this query, using different values of Groupname. If it works, then we'll pivot the results using FOR XML PATH to put them into a comma-delimited list:

    ;WITH rCTE AS (

    SELECT...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 3, 2012 at 6:06 am

    #1427675

  • RE: Error converting data type varchar to datetime in stored Procedure

    Run the query outside the stored procedure - always much easier to debug - and chop out some unnecessary bits.

    DECLARE

    @LNK_File_ID numeric(18, 0),

    @LNK_Client_ID numeric(18, 0),

    @ExpanseDate datetime

    SET @ExpanseDate = '29/12/2011 12:00:00...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 3, 2012 at 5:53 am

    #1427665

  • RE: Loop through data to find consecutive results prior to a certain date within a 6 month period

    Thanks for the feedback - I won't hold my breath while you're still testing 😉

    DENSE_RANK() and ROW_NUMBER() are used together like this to resolve subsets of the same partition, where...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 3, 2012 at 1:56 am

    #1427579

  • RE: Error converting data type varchar to datetime in stored Procedure

    What datatype is E.[ExpanseDate]?

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 3, 2012 at 1:36 am

    #1427573

  • RE: Error converting data type varchar to datetime in stored Procedure

    ,CONVERT(Datetime,E.[LNK_CreatedBy_ID],103) as [LNK_CreatedBy_ID]

    E.[LNK_CreatedBy_ID]= Case when @LNK_Client_ID IS NOT NULL THEN @LNK_Client_ID

    ...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 3, 2012 at 12:54 am

    #1427567

  • RE: Loop through data to find consecutive results prior to a certain date within a 6 month period

    mic.con87 (1/3/2012)

    ...@ChrisM@home: Your Query is very close to my desired solution. The only issue is that it selects the max DueDate for a particular month regardless of whether this is...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 3, 2012 at 12:47 am

    #1427566

  • RE: Quirky Update query...works on server ...fails on PC.

    3 successful runs on home lappy, 2K8, Vista.

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 2, 2012 at 4:26 pm

    #1427509

Viewing 15 posts - 676 through 690 (of 1,228 total)