How do I add columns to a table to calculate workdays, etc...

[lennym]

I currently transfer data from one SQL7 database to another using DTS and I would like to set up an automated procedure to add columns which will contain data that calculates the working days between two date fields in the original table.&nbsp;&nbsp;I am using a time_dimension table and formula from a previous post in a VIEW however I would like to place the same information directly into a table in the database.&nbsp;&nbsp;There are several related date fields I would like to calculate the working days between and have them all in one database table.&nbsp;&nbsp;The REASON for all this...so it's easier to create reports which access this data. <br><br>SO...I want to copy all the data from one table to another, then add several columns, then using a link to another table(time_dimension), calculate the working days between several date fields and enter the result into the newly added columns.&nbsp;&nbsp;DOING ALL THIS AUTOMATICALLY...<br><br>PS: I currently truncate the table entirely before each DTS transaction to copy the table.&nbsp;&nbsp;This is done once a day.<br><br>Thanks in advance for any assistance.<br><br>LennyM

 

[ColinM]

You could do it with triggers and the DATEDIFF function, although I'm not sure that you can make triggers fire during a DTS import.<br>You might also try a transformation script in DTS.