a tale of two tables ...
-
People,
I have two tables A and B
Table A has ID, DateCreated, CurrentStatus, Custid
Table B has ID, DateCreated, Table_A_Status, Custid
Table A updates it's status field whenever it changes while a trigger on Table A inserts into B the update so we know exactly when the update happened.
My question is: how do I retreive back at any one time the last Update for each customer? Put another way for each custid in Table A find the latest date/time of the the status changed to that of the value in Table A?
i.e. an output like this
A.id A.Custid A.CurrentStatus B.DateCreated
=======================================================
1 12 Waiting 12-Jan-05 14:16
2 13 In Transit 14-Jan-05 11:10
3 16 Delivered 05-Jan-05 09:30
I will forever be indebited to your kindness if some kinf sould can help me?
Thanks
Ahsen
-
To find for each customer in table B the latest update, regardless of which status..?
select custid, max(datecreated) as maxDate
from tableB
group by custid
..should answer that
Though, I'm not entirely clear as to exactly what you want - ie what data you want to see and based on what criteria. Could you expand a little on the problem, perhaps with a small example of the contents in tables A and B and the desired output based on that?
/Kenneth
-
In the future, please post create table statements including all primary key and foreign key constraints such as:
Create table Customer
(ID integer not null
, DateCreated datetime not null
, CurrentStatus varchar(40) not null
, Custid integer not null
, constraint Customer_P primary key (Custid )
)
go
Create table CustomerStatus
( DateCreated datetime not null
, CurrentStatus varchar(40) not null
, Custid integer not null
, constraint CustomerStatus_P primary key (Custid , DateCreated )
, constraint Customer_F_CustomerStatus foreign key (Custid ) references Customer
)
go
-- Latest Status
select Customer.Custid
, CustomerStatus.CurrentStatus
, CustomerStatus.DateCreated
from Customer
join CustomerStatus
on Customer.Custid = CustomerStatus.Custid
join
(select CustomerStatus.Custid
, MAX(CustomerStatus.DateCreated )
from CustomerStatus
group by CustomerStatus.Custid
 
as CustomerStatus_Latest ( Custid , DateCreated)on CustomerStatus_Latest.Custid = CustomerStatus.Custid
and CustomerStatus_Latest.DateCreated = CustomerStatus.DateCreated
Order by Customer.Custid
-- Status as of a specified date:
Declare @AsOfDate datetime
set @AsOfDate = '2004-12-31 23.59.59.997'
select Customer.Custid
, CustomerStatus.CurrentStatus
, CustomerStatus.DateCreated
from Customer
join CustomerStatus
on Customer.Custid = CustomerStatus.Custid
join (select CustomerStatus.Custid
, MAX(CustomerStatus.DateCreated )
from CustomerStatus
WHERE CustomerStatus.DateCreated <= @AsOfDate
group by CustomerStatus.Custid
 
as CustomerStatus_Latest ( Custid , DateCreated)on CustomerStatus_Latest.Custid = CustomerStatus.Custid
and CustomerStatus_Latest.DateCreated = CustomerStatus.DateCreated
Order by Customer.Custid
SQL = Scarcely Qualifies as a Language
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply