Logic Help - Update column in table with data from previous day

[chiplarsen]

I have a SQL 2005 database that I want to use a trigger to update a column in one table base on data from the previous day. The data in this table is inserted via a bulk insert command.

bulk insert BILLING_TABLE from 'c:\ftpfiles\cur_cens2.txt'

There is one column that will be empty upon insert. We can call it "DocOfTheDay". Lets assume that someone entered the "DocOfTheDay" into the previous days columns. After insertion of the current data, I would like the trigger to have logic that would check the previous day against the current day. The links between the two records would be that the patient_id from the previous day would have to equal the patient_id from the current day. Also, the "Census_Date" from the previous day would have to be equal to the current date minus 1. If those two match, update the current days "DocOfTheDay" with the previous days "DocOfTheDay".
I had a trigger that did this base on the information in another table, but this data is going to come from the same table that all the data resides in. Here is what I have for the current trigger.

USE [IPS_PROD]
GO
/****** Object:  Trigger [dbo].[TR_DOCOFDAY_U]    Script Date: 03/09/2011 20:25:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TR_DOCOFDAY_U] ON [dbo].[CURRENT_CENSUS] AFTER INSERT AS 
BEGIN

UPDATE CURRENT_CENSUS 
SET CURRENT_CENSUS.IPS_DOC_OF_DAY = BILLING_TABLE.IPS_DOC_OF_DAY
FROM BILLING_TABLE 
INNER JOIN CURRENT_CENSUS ON CURRENT_CENSUS.PAT_ACCT_NBR = BILLING_TABLE.PAT_ACCT_NBR
AND BILLING_TABLE.CENSUS_DATE= CONVERT(varchar, GETDATE()-1,101) 

END

I really appreciate any help in getting me started. Thank you

 

[PWise]

UPDATE CURRENT_CENSUS 
SET CURRENT_CENSUS.IPS_DOC_OF_DAY = BILLING_TABLE.IPS_DOC_OF_DAY
FROM CURRENT_CENSUS
INNER JOIN (Select PAT_ACCT_NBR,IPS_DOC_OF_DAY
            From CURRENT_CENSUS
            Where CURRENT_CENSUS.CENSUS_DATE= CONVERT(varchar, GETDATE()-1,101) 
            )BILLING_TABLE 
ON CURRENT_CENSUS.PAT_ACCT_NBR = BILLING_TABLE.PAT_ACCT_NBR