TSQL getting first date
Hi, below is my sample data from a table.I just want to create a new column
named 'Full_Date' which is nothing but the startdate from the below table
where typeid has been First changed to typeid 1 from typeid 2.
So, for the below date my new date from custid 1 should be 10/11/2002
and custid 2 should be 09/08/2004 as the column typeid was first changed to
typeid 1 on these dates.
Custid Startdate typeid
1 01/01/2001 2
1 09/10/2001 2
1 10/11/2002 1
2 09/10/2001 2
2 09/08/2004 1
Many Thanks
named 'Full_Date' which is nothing but the startdate from the below table
where typeid has been First changed to typeid 1 from typeid 2.
So, for the below date my new date from custid 1 should be 10/11/2002
and custid 2 should be 09/08/2004 as the column typeid was first changed to
typeid 1 on these dates.
Custid Startdate typeid
1 01/01/2001 2
1 09/10/2001 2
1 10/11/2002 1
2 09/10/2001 2
2 09/08/2004 1
Many Thanks
ASKER
hi yawkey13,
but I want another column to be added to my table with all the data...but
i just want the min(startdate) when it was changed to 1 per custid...as per below-
Custid Startdate typeid Full_Date
1 01/01/2001 2 10/11/2002
1 09/10/2001 2 10/11/2002
1 10/11/2002 1 10/11/2002
2 09/10/2001 2 09/08/2004
2 09/08/2004 1 09/08/2004
Thanks
but I want another column to be added to my table with all the data...but
i just want the min(startdate) when it was changed to 1 per custid...as per below-
Custid Startdate typeid Full_Date
1 01/01/2001 2 10/11/2002
1 09/10/2001 2 10/11/2002
1 10/11/2002 1 10/11/2002
2 09/10/2001 2 09/08/2004
2 09/08/2004 1 09/08/2004
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The above query is assuming that you have already added the full_date column to your table. If you have not already added the column you will need to run the following first.
alter table myTable
add full_date DATETIME;
alter table myTable
add full_date DATETIME;
Just out of curiosity, why are you wanting to add this data to your table? In my opinion, for what it's worth, I would rather compute this data on output instead of saving the redundant data in the database.
Also, is this a one time update or do you have some mechanism for keeping this updated? If you need to keep it updated and you don't already have code to do it, I would probably create a trigger if it needs to be updated in real time. If it doesn't need to be updated real time, I'd create a job to run each night and update the column using the query above.
Also, is this a one time update or do you have some mechanism for keeping this updated? If you need to keep it updated and you don't already have code to do it, I would probably create a trigger if it needs to be updated in real time. If it doesn't need to be updated real time, I'd create a job to run each night and update the column using the query above.
ASKER
update myTable
set myTable.full_date = temp.full_date
from
myTable join
(
select custid, min(startdate) as full_date
from myTable
where typeid = 1
group by custid
) as temp
on myTable.custid = temp.custid
set myTable.full_date = temp.full_date
from
myTable join
(
select custid, min(startdate) as full_date
from myTable
where typeid = 1
group by custid
) as temp
on myTable.custid = temp.custid
from myTable
where typeid = 1
group by custid