«

I have a table which has a datetime field for when a row in this one table
(Ticket) was created. This is for trouble tickets for a help desk. After a
ticket has been created, any additions to that ticket are recorded in
another table called History. There could be several additions, thus the
splitting up into a one-to-many relationship.


In order to meet the boss's request, I recently added a LastModified field
to the Ticket table. I have included code in my ASP which will fill that
field with the date when a new ticket is created, and then when new data is
added later, will update that field. The problem is that for all existing
tickets, that field is empty. I'd like to run a SQL statement which will
copy the entry date (but only the date, not the time) from the original
timestamp field of each existing ticket, and then go back and see if there
are any updates in the History table for each ticket, and if so, take the
MAX value for the Timestamp field in that table, and copy it over, again,
just the date, not the whole timestamp.

I realize that you may wonder why I don't just use the MAX(TimeStamp) from
my history table as my lastmodified, but I had lots of trouble with this.
This way works, and I just need to find a way to keep from having nulls in
existing tickets.

Should add I'm using SQL Server 2000.


"middletree" <> wrote in message
news:...
> I have a table which has a datetime field for when a row in this one table
> (Ticket) was created. This is for trouble tickets for a help desk. After a
> ticket has been created, any additions to that ticket are recorded in
> another table called History. There could be several additions, thus the
> splitting up into a one-to-many relationship.
>
>
> In order to meet the boss's request, I recently added a LastModified field
> to the Ticket table. I have included code in my ASP which will fill that
> field with the date when a new ticket is created, and then when new data
is
> added later, will update that field. The problem is that for all existing
> tickets, that field is empty. I'd like to run a SQL statement which will
> copy the entry date (but only the date, not the time) from the original
> timestamp field of each existing ticket, and then go back and see if there
> are any updates in the History table for each ticket, and if so, take the
> MAX value for the Timestamp field in that table, and copy it over, again,
> just the date, not the whole timestamp.
>
> I realize that you may wonder why I don't just use the MAX(TimeStamp) from
> my history table as my lastmodified, but I had lots of trouble with this.
> This way works, and I just need to find a way to keep from having nulls in
> existing tickets.
>
>