A really tough SQL query ..... I think

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

A really tough SQL query ..... I think

Author

Topic

DavidD
Yak Posting Veteran

73 Posts

Posted - 2002-03-04 : 01:53:01

Dear all,

Not sure if this query is really tough or I am as bad at query writing as I suspect, but basically I have a table that contains 4 territories and therefore 4 targets against each client in the following format.
(tables look crap because I can't get this @#*#%$ software to show spaces, so I've used commas in this first one)

ClientTargetID ClientID TargetID Territory DateEntered
101, 21, 4, 300, 1/1/02
102, 21, 4, 301, 1/1/02
103, 21, 3, 302, 2/2/02
104, 21, 4, 303, 1/1/02

The problem is I need to update the targetIDs of all territories for a client to the most recently updated value, so in the example above all targetIDs for ClientID 21 would become 3.

Anyone wishing to give it a whirl, here is some dummy data to copy paste:

create table ClientTargets
(ClientTargetID int CONSTRAINT PK_ClientTargetID PRIMARY KEY CLUSTERED,
ClientID int,
Targetid tinyint,
Territory varchar(10),
DateEntered datetime)
set dateformat dmy

insert into ClientTargets values(3,101,0,'612307','5/6/2001')
insert into ClientTargets values(5,102,0,'610202','5/6/2001')
insert into ClientTargets values(11,103,1,'612603','5/6/2001')
insert into ClientTargets values(15,104,5,'612201','5/6/2001')
insert into ClientTargets values(6,102,3,'611202','21/6/2001')
insert into ClientTargets values(7,102,3,'612202','6/6/2001')
insert into ClientTargets values(8,102,0,'613202','6/6/2001')
insert into ClientTargets values(13,104,3,'610201','6/6/2001')
insert into ClientTargets values(14,104,3,'611201','6/6/2001')
insert into ClientTargets values(16,104,3,'613201','6/6/2001')
insert into ClientTargets values(2,101,0,'611307','6/6/2001')
insert into ClientTargets values(1,101,0,'610307','6/6/2001')
insert into ClientTargets values(4,101,5,'613307','20/6/2001')
insert into ClientTargets values(10,103,1,'611603','6/6/2001')
insert into ClientTargets values(9,103,1,'610603','6/6/2001')
insert into ClientTargets values(12,103,1,'613603','6/6/2001')

And here is how the table should look after the update query (Headers changed to save space) :

ClientTargetID ClientID TargetID Territory DateEntered
1 101 5 610307 6/06/2001
2 101 5 611307 6/06/2001
3 101 5 612307 5/06/2001
4 101 5 613307 20/06/2001
5 102 3 610202 5/06/2001
6 102 3 611202 21/06/2001
7 102 3 612202 6/06/2001
8 102 3 613202 6/06/2001
9 103 1 610603 6/06/2001
10 103 1 611603 6/06/2001
11 103 1 612603 5/06/2001
12 103 1 613603 6/06/2001
13 104 3 610201 6/06/2001
14 104 3 611201 6/06/2001
15 104 3 612201 5/06/2001
16 104 3 613201 6/06/2001

Any ideas would be appreciated
Regards
David

Nazim
A custom title

1408 Posts

Posted - 2002-03-04 : 03:07:00

Try this

update s set targetid=t.tar from clienttargets s
inner join
(
select c.clientid,min(targetid) as tar
from clienttargets i,(
select clientid,max(dateentered) as MDate from clienttargets
group by clientid ) c
where i.dateentered=mdate and i.clientid=c.clientid
group by c.clientid ) t
on s.clientid=t.clientid

HTH

--------------------------------------------------------------

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-04 : 06:32:01

If you use the code tags then you will maintain tabs and spaces - blank lines get suppressed though. Click on "forum code" when posting for help on this.

============
The Dabbler!

Edited by - davidpardoe on 03/04/2002 06:39:13

DavidD
Yak Posting Veteran

73 Posts

Posted - 2002-03-04 : 18:45:29

Thanks Nazim, it works beautifully..

Regards
David

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources