Forum Replies Created

Viewing 15 posts - 736 through 750 (of 1,228 total)

  • RE: NTILE help

    DECLARE @GroupPartitionSize INT

    SET @GroupPartitionSize = 6 -- 700 in your actual data

    SELECT

    grp_no,

    cnt, -- workings, not part of solution

    ...

    [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]

    December 22, 2011 at 1:15 am

    #1424612

  • RE: Really Odd and Seemingly Difficult Update, One Piece of Data at a Time.

    drew.allen (12/21/2011)

    ChrisM@home (12/21/2011)

    Secondly, use COALESCE(ExistingColumn, StagingColumn) like this to handle the NULL issue:

    ExistingColumn = COALESCE(ExistingColumn, StagingColumn)

    Actually it sounds like he wants to overwrite the existing column if both the existing...

    [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]

    December 22, 2011 at 12:43 am

    #1424602

  • RE: I want No Duplication and some extra future.

    This is very very basic TSQL and looks like homework. Please read the link in my sig which will show you how best to ask questions and post sample data....

    [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]

    December 21, 2011 at 5:37 am

    #1424158

  • RE: Really Odd and Seemingly Difficult Update, One Piece of Data at a Time.

    Hi Andrew

    Firstly, try the MERGE statement for upserts, that's what it's for and it makes for clear and performant code.

    Secondly, use COALESCE(ExistingColumn, StagingColumn) like this to handle the NULL issue:

    ExistingColumn...

    [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]

    December 21, 2011 at 5:32 am

    #1424154

  • RE: No repeatetion of names?

    alishaik001 (12/21/2011)

    My actual query is:

    here Name is the fieldName and Employee is the TableName;

    SELECT Name FROM Employee GROUP BY Name HAVING ( COUNT(Name) >= 1 );

    From this query I got...

    [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]

    December 21, 2011 at 5:23 am

    #1424152

  • RE: No repeatetion of names?

    alishaik001 (12/21/2011)

    I get this by the following query:

    SELECT Column_Name FROM TableName GROUP BY Column_Name HAVING ( COUNT(Column_Name) >= 1 );

    Can you post the actual query you are using? The query...

    [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]

    December 21, 2011 at 5:11 am

    #1424144

  • RE: NTILE help

    VIG (12/21/2011)

    2 ChrisM@home

    even more simple 🙂declare @subgroupsize int

    set @subgroupsize=3

    select dense_rank() over(order by act,grp desc) grp_no,1+(rn-1)/@subgroupsize grp_sub_no

    ,grp,member,act

    from

    (select * ,ROW_NUMBER() over(partition by act,grp order by (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]

    December 21, 2011 at 3:14 am

    #1424105

  • RE: NTILE help

    mikes84 (12/20/2011)

    Thanks for the response, Chris. Would you mind explaining this line a little bit more?

    grp_sub_no = ((rn+2)/3*3)/3

    The actual number I'd like to split the groups up by is 700....

    [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]

    December 21, 2011 at 12:57 am

    #1424064

  • RE: NTILE help

    Hi Mike

    NTILE() is great for splitting tables into equal partitions for e.g. marketing, I think this better suits your requirement:

    SELECT

    grp_no,

    grp_sub_no = ((rn+2)/3*3)/3,

    grp,

    member,

    act

    FROM (

    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]

    December 20, 2011 at 2:53 pm

    #1423935

  • RE: Prioritize a JOIN - get just one option

    Left-join table2 twice, once for each condition. Resolve the dispute in the WHERE clause and the output using CASE.

    [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]

    December 20, 2011 at 8:02 am

    #1423650

  • RE: Select specific days and hours from a date and make that a new column

    APPLY is explained in Paul White's papers - links are in my sig. It's somewhat similar to putting a correlated subquery in the FROM list. Read the papers - APPLY...

    [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]

    December 20, 2011 at 1:34 am

    #1423453

  • RE: Help with query

    ramadesai108 (12/19/2011)

    I have this query:

    SELECT COUNT(*)

    FROM Departments INNER JOIN

    ...

    [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]

    December 19, 2011 at 2:00 pm

    #1423302

  • RE: Look Back Through Records to Apply Funds Approriately

    Hi Doug, welcome to the forum. This is a Running Total project, and there are several ways of getting the output you're expecting. The fastest is the Quirky Update (QU),...

    [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]

    December 19, 2011 at 9:56 am

    #1423191

  • RE: Are the posted questions getting worse?

    Revenant (12/18/2011)

    L' Eomot Inversé (12/18/2011)

    . . . Perhaps it would be good to get together, preferably somewhere with a good range of whiskies. . . . But I'm based in...

    [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]

    December 19, 2011 at 9:04 am

    #1423154

  • RE: Are the posted questions getting worse?

    bitbucket-25253 (12/19/2011)

    LutzM Posted Today @ 9:07 AM

    Hmmm.... interesting....

    Based on the (almost public available) age of the two fellows you're somewhat between 14 and 16 years old. Hard to believe that...

    [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]

    December 19, 2011 at 8:53 am

    #1423145

Viewing 15 posts - 736 through 750 (of 1,228 total)