Results 1 to 2 of 2

Thread: Update query (Urgent please)

  1. #1
    Join Date
    Oct 2002
    Location
    US
    Posts
    21

    Angry Update query (Urgent please)

    Hi Guys,
    I have a table created like
    (cname,perfweek,orderstaken,callsmade).
    the application give a report of perticular salespersons(cname) performance by ordertaken and callsmade.
    somedays back, they have entered different cname for the same person. What I wanted to do is, when they give me correct cname and the wrong cname, I will have to findout in a perticular week, is there are duplication of cname and if then I have to add orderstaken and callsmade to the correct Cname for that particular week and after that delete the wrong Cname detail for that particular week(because it is added to the correct cname rows for that week).
    And then I have to change the wrong Cname to the correct Cname for all the rows, if there is no data found for right cname matching for that week.
    cname perfweek orderstaken callsmade
    ----- ------- --------- --------
    abc 1 3 4
    bbb 1 5 6
    abc 3 3 1
    bbb 3 2 3
    bbb 2 4 5

    in this eg: abc is the right cname and bbb is the wrong cname. Here what I have to do is I have to combine rows for the correct and wrong cname for that particular week ie: now the table looks like :
    cname perfweek orderstaken callsmade
    ---- ------- ---------- ----------
    abc 1 8 10
    bbb 1 5 6
    abc 3 5 4
    bbb 3 2 3
    bbb 2 4 5
    (note: after combine, I have to delete the bbb for the perfweek of 1 and 3)
    and then I have to update the rest of the wrong cname to the correct cname.
    Then the firnal table looks like

    cname perfweek orderstaken callsmade
    ----- ------- --------- --------
    abc 1 8 10
    abc 3 5 4
    abc 2 4 5

    It is an urgent requirment and I will really appreciate your valuable inputs.
    Thanks very very much.
    Last edited by ragul; 04-12-2005 at 04:05 PM.

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    use tempdb
    go
    create table x1(cname varchar(10),perfweek int,orderstaken int, callsmade int)
    go
    insert into x1 select 'abc', 1, 3, 4
    insert into x1 select 'bbb', 1, 5, 6
    insert into x1 select 'zzz', 1, 5, 6
    insert into x1 select 'abc', 3, 3, 1
    insert into x1 select 'bbb', 3, 2, 3
    insert into x1 select 'bbb', 2, 4, 5
    insert into x1 select 'aaa', 2, 4, 5
    go
    select cname=case when cname='bbb' then 'abc' else cname end,perfweek,sum(orderstaken) as orderstaken,
    sum(callsmade) as callsmade
    from x1
    group by case when cname='bbb' then 'abc' else cname end ,perfweek
    order by cname
    go


    You can use this query to export the result to a staging table.

    Delete both abc and bbb from the original table. Then move all the date from staging table to original table.

    drop the staging table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •