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
gvamsimbaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
yawkey13Connect With a Mentor Commented:
update myTable
set myTable.full_date = temp.full_date
from
      myTable join
      (
            select custid, max(startdate) as full_date
            from myTable
            where typeid = 1
            group by custid
      ) as temp
            on myTable.custid = temp.custid
0
 
yawkey13Commented:
select custid, min(startdate) as full_date
from myTable
where typeid = 1
group by custid
0
 
gvamsimbaAuthor Commented:
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
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
yawkey13Commented:
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;
0
 
yawkey13Commented:
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.
0
 
gvamsimbaAuthor Commented:
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
0
All Courses

From novice to tech pro — start learning today.