Help With Update Query

  • Hello I have the following table where I would like to set the value of PersonCount field to 1 if the record is equal the minimum date for that particular AssocaiteName(will Have multiple entries just want to mark first record with 1) else set the value to 0

    Table name = #test

    Fields = ID,

    Site,

    ManagerName,

    AssociateName,

    PesronNumber,

    Startdate,

    Enddate,

    EntryDate,

    PersonCount

    Here is what I have but is not working. Any help would be greatly appreciated.

    Update #test

    Set PersonCount = 1

    From #test

    Join #test B ON #test.PesronNumber = B.PesronNumber

    and b.EntryDate = (SELECT PesronNumber,MIN(EntryDate)

    FROM #test

    where #test.EntryDate = EntryDate

    Group By PesronNumber)

  • Try this code. It is untested as you did not provide the DDL or any test data.

    with TestDate (

    PersonNumber,

    EntryDate

    ) as (

    select

    PersonNumber,

    min(EntryDate)

    from

    #test

    group by

    PersonNumber

    )

    update #test set

    PersonCount = 1

    from

    #test t

    inner join TestDate td

    on (t.PersonNumber = td.PersonNumber

    and t.EntryDate = td.EntryDate);

    😎

  • I am sorry i do not understand with TestDate ( PersonNumber, EntryDate) as piece.

    Mayby I can clarify what I need. I want to update The PersonCount field to = 1 onlyt for 1 record per person. I would like to set the value to 1 for the first record related to an associate. I also attached some sample data.

    Thank you for your help.

    IDSiteManagerNameAssociateNamePesronNumberStartDateEnddateEntryDatePersonCount

    1ATLANTADoe, JohnBrown, Charlie1234567812/17/0705/15/0812/19/070

    2ATLANTADoe, JohnBrown, Charlie1234567812/17/0705/15/0812/28/070

    3ATLANTADoe, JohnBrown, Charlie1234567812/17/0705/15/0801/07/080

    4ATLANTADoe, JohnBrown, Charlie1234567812/17/0705/15/0801/08/080

    5ATLANTADoe, JohnBrown, Charlie1234567812/17/0705/15/0801/09/080

    6ATLANTADoe, JohnBrown, Charlie1234567812/17/0705/15/0801/11/080

    7ATLANTADoe, JohnBrown, Charlie1234567812/17/0705/15/0801/14/080

    8ATLANTADoe, JohnBrown, Charlie1234567812/17/0705/15/0801/15/080

    9ATLANTADoe, JohnBrown, Charlie1234567812/17/0705/15/0801/16/080

    10ATLANTADoe, JohnBrown, Charlie1234567812/17/0705/15/0801/17/080

    11ATLANTADoe, JohnBrown, Charlie1234567812/17/0705/15/0801/18/080

    12ATLANTADoe, JohnBrown, Charlie1234567812/17/0705/15/0801/22/080

    13ATLANTADoe, JohnBrown, Charlie1234567812/17/0705/15/0801/23/080

    14ATLANTADoe, JohnBrown, Charlie1234567812/17/0705/15/0801/24/080

    15ATLANTADoe, JohnBrown, Charlie1234567812/17/0705/15/0801/25/080

    16ATLANTADoe, JohnFully, Dave8765432112/17/0705/15/0801/07/080

    17ATLANTADoe, JohnFully, Dave8765432112/17/0705/15/0801/08/080

  • David -

    Lynn is using what is called a CTE (common table expression). BOL will give you lots of examples of how it can be used. It's pretty nice once you can wrap your head around how its syntax works.

    I've reformatted your test data so that it can be used, and compaensated for the typo in the table field name.

    create table #test(ID int, [Site] varchar(20),

    ManagerName varchar(20),

    AssociateName varchar(20),

    PersonNumber int,

    StartDate datetime,

    Enddate datetime,

    EntryDate datetime,

    PersonCount int)

    Insert #test

    SELECT 1,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','12/19/07', 0 UNION ALL

    SELECT 2,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','12/28/07', 0 UNION ALL

    SELECT 3,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/07/08', 0 UNION ALL

    SELECT 4,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/08/08', 0 UNION ALL

    SELECT 5,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/09/08', 0 UNION ALL

    SELECT 6,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/11/08', 0 UNION ALL

    SELECT 7,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/14/08', 0 UNION ALL

    SELECT 8,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/15/08', 0 UNION ALL

    SELECT 9,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/16/08', 0 UNION ALL

    SELECT 10,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/17/08', 0 UNION ALL

    SELECT 11,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/18/08', 0 UNION ALL

    SELECT 12,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/22/08', 0 UNION ALL

    SELECT 13,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/23/08', 0 UNION ALL

    SELECT 14,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/24/08', 0 UNION ALL

    SELECT 15,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/25/08', 0 UNION ALL

    SELECT 16,'ATLANTA','Doe, John','Fully, Dave','87654321','12/17/07','05/15/08','01/07/08', 0 UNION ALL

    SELECT 17,'ATLANTA','Doe, John','Fully, Dave','87654321','12/17/07','05/15/08','01/08/08', 0

    ;with TestDate (

    PersonNumber ,

    EntryDate) as

    (

    select

    PersonNumber ,

    min(EntryDate)

    from #test

    group by PersonNumber

    )

    update #test

    set PersonCount = 1

    from #test t

    inner join TestDate td

    on (t.PersonNumber = td.PersonNumber

    and t.EntryDate = td.EntryDate);

    select * from #test where personcount=1

    Really - give it a whirl - you'll see it's right.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Thank you so much SSCrazy and Lynn I really don't yet understand how it works but it does. I am going to try and wrap my brain around this for a while and see what I can learn!

    Thank You for the help!

    PS - Sorry for the typo's.

  • Nicely done folks...

    --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)

  • Hello I am a newbie as indicated next to my name. I spent the last 24 hours wrapping my brain around CTE's and feel I learned quite a bit. I have everything working as intended now I would like to open my methodology up to constuctive criticism in order to learn perhaps a better way of doing things. Please read the posts above if want to know what I am attempting to and or send me any questions you may have.

    The stored procedure below runs a little slow but it does work. Is there a better way to go about solving this problem.

    I am not limited as to how I can do this I just want to learn the best way to go about it.

    ALTER procedure [dbo].[Card_OnBoardingDaily_History_sp] AS

    Begin

    Create table #CardDaily(

    ID int constraint pkid Primary Key,

    Site nvarchar(100),

    ManagerName nvarchar(100),

    AssociateName nvarchar(100),

    PersonNumber nvarchar(8),

    Startdate datetime,

    Enddate datetime,

    EntryDate datetime,

    [30_60_90] nvarchar(100),

    PersonCount int,

    PersonCount306090 int,

    DaysSinceStart int,

    PercentOnbording float,

    AverageHandleTime Float,

    AverageHandleTimeSeconds Float,

    AveragehandleTimeCalls int,

    TotalBTSales nvarchar(50),

    TotalBTDollars money,

    TotalProducts int,

    IncomingRetentionCalls int,

    CB int,

    ICR float)

    Insert #CardDaily

    Select

    ID,

    Site,

    ManagerName,

    AssociateName,

    PesronNumber As PersonNumber,

    Startdate,

    Enddate,

    EntryDate,

    Case

    WHEN DateDiff(Day,StartDate,EntryDate) <= 42 THEN '30'

    WHEN DateDiff(Day,StartDate,EntryDate) between 43 and 72 THEN '60'

    WHEN DateDiff(Day,StartDate,EntryDate) between 73 and 102 THEN '90'

    WHEN DateDiff(Day,StartDate,EntryDate) > 102 THEN '90+'

    End AS [30_60_90],

    PersonCount = 0,

    PersonCount306090 = 0,

    DateDiff(Day,StartDate,EntryDate) DaysSinceStart,

    Case

    WHEN DateDiff(Day,StartDate,EntryDate) <= 102 THEN Cast(DateDiff(Day,StartDate,EntryDate) As Float)/102 * 100

    else 100

    end AS PercentOnbording,

    AverageHandleTime,

    AverageHandleTimeSeconds,

    AveragehandleTimeCalls,

    TotalBTSales,

    TotalBTDollars,

    TotalProducts,

    IncomingRetentionCalls,

    CB,

    ICR

    From dbo.Card_OnBoardingDaily_History ;

    with TestDate ( PersonNumber , EntryDate) as

    (selectPersonNumber ,

    min(EntryDate)

    from #CardDaily

    group by PersonNumber)

    update #CardDaily set PersonCount = 1

    from #CardDaily t

    inner join TestDate td

    on (t.PersonNumber = td.PersonNumber

    and t.EntryDate = td.EntryDate);

    with TestDate ( PersonNumber ,[30_60_90], EntryDate) as

    (selectPersonNumber ,

    [30_60_90],

    min(EntryDate)

    from #CardDaily

    group by PersonNumber,[30_60_90] )

    update #CardDaily set PersonCount306090 = 1

    from #CardDaily t

    inner join TestDate td

    on (t.PersonNumber = td.PersonNumber

    and t.EntryDate = td.EntryDate

    and t.[30_60_90] = td.[30_60_90] );

    Create index pn_idx on #CardDaily(PersonNumber);

    select

    ID,

    Site,

    ManagerName,

    AssociateName,

    PersonNumber,

    Startdate,

    Enddate,

    EntryDate,

    [30_60_90],

    PersonCount,

    PersonCount306090,

    DaysSinceStart,

    PercentOnbording,

    AverageHandleTime,

    AverageHandleTimeSeconds,

    AveragehandleTimeCalls,

    TotalBTSales,

    TotalBTDollars,

    TotalProducts,

    IncomingRetentionCalls,

    CB,

    ICR

    from #CardDaily

    End

    Also thank you all for helping me get to this point!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply