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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-02-14 : 08:11:38
|
| Rohit writes "I am having trouble creating a query to get a result set I want. Please help me on this. I have a table "customerHistory" as follows. customerId varchar(10) not null primary key,modifiedColomnName varchar(10) not null,newValue varchar(10),dateModified datetime.Below are the rows in the table. 1) 1111111, customerName, Hector, 200501012) 2222222, customerName, Mike , 200501043) 1111111, customerAge, 45, , 200501034) 1111111, customerName, Michael, 200501065) 1111111, customerAge, 55, 20050106From the data you will come to know that, for the customerId 1111111 , the customerName was modified to Hector on 20050101. For the same customerId 1111111, the customerName was again modified on 20050106. I want a query such that if I enter the dateModified = '20050106' and modifiedColomnName='customerName' it should give me the lastest customerId , newValue before that day. one record per customerId. So in the above case it will return2222222, customerName, Mike , 200501041111111, customerName, Michael, 20050106I am trying to track the lastest information of a certian colomn on a given day for all the customers." |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-02-14 : 08:22:15
|
| Select * from Table T where datemodified in (Select Max(DateModified) from Table where CustomerId=T.CustomerId group by CustomerId)Madhivanan |
 |
|
|
|
|
|
|
|
subquery - question