Updating Dates Within A Date Range 1

[TJLearn]

Hi Everyone,

Can anyone please help me with some coding for my update problem. I am in a rush and not so good about it. Thanks!

I have 2 tables: tblLoans and tblStates as below:

tblLoans:
LoanNum FundingDate State PlannedDate
1 1/1/02 CA
2 10/5/02 CA
3 12/17/03 CA
4 10/10/03 TX


tblStates:
State Days StartDate EndDate
CA 5 1/1/1995 12/31/2000
CA 10 1/1/2002 12/31/2002
CA 11 1/1/2003 12/31/2003
TX 10 1/1/1995 12/31/2003

What I need to do is to fill out the PlannedDate field in the tblLoans table. The criteria are:
1. tblLoans.State = tblStates.State
2. tblLoans.FundingDate between tblStates.StartDate AND tblStates.EndDate
3. Add Days to the tblLoans.FundingDate from the record that has tblStates.StartDate and tblStates.EndDate the tblLoans.FundingDate falls in between. To summarize the 3 criteria, I need to match the state columns in both tblLoans and tblStates tables. Then take the FundingDate of each loan in the tblLoans table and see if it falls in betwwen a date range of StartDate and EndDate in the tblStates table. If it falls in between a date range, then take the FundingDate and add the # of days from the Days column in the tblStates table.

For example, the tblLoans table should look like this after getting updated:

LoanNum FundingDate State PlannedDate
1 1/1/02 CA 1/11/02
2 10/5/02 CA 10/15/02
3 12/17/03 CA 12/28/03
4 10/10/03 TX 10/20/03

Any help will be very appreciated!

 

[Mdavis123]

Hi TJ-
Here is the short answer:

select a.LoanNum,a.FundingDate,a.State,
dateadd(dd,b.Days,a.FundingDate) as PlannedDate
from tblloans A
left join tblStates b
on b.State=a.State and (a.FundingDate between b.StartDate and b.EndDate)

The Longer answer handles things like a time mixed in so that Between function may cause a fence error on dates. Also some times the enddate is null because nobody knows when the next change comes about.

select a.LoanNum,a.FundingDate,a.State,
dateadd(dd,b.Days,a.FundingDate) as PlannedDate
from tblloans A
left join tblStates b
on b.State=a.State and (convert(datetime,Convert(varchar(10),a.FundingDate,101))
between b.StartDate and isnull(b.EndDate,a.FundingDate))

Happy Hollidays
MikeD

 

[TJLearn]

Hi Mike,

Thank you so much for your quick help. I really appreciated and happy Hollidays to you, too!!!. Your answer works great.

I just found out 1 more case that we also have cancelled loans. In the tblLoans table, I have a field called CancelDate and another field to identifies if Funded loans or cancelled loans called Status. The tblLoans now looks like this:

tblLoans:
LoanNum FundingDate State Status CancelDate PlannedDate
1 1/1/02 CA 1
2 10/5/02 CA 1
3 12/17/03 CA 1
4 10/10/03 TX 1
5. TX 2 12/15/03

I treat the CancelDate the same as FundingDate. The different is I just need to read the code from the Status column (1 for FundingDate, and 2 for CancelDate). I am currently using 2 update statements in the same stored procedure. I am wondering if I can use CASE to combine 2 update statements to make it become 1 statement. I have tried today, but it didn't work. If Mike or anyone has a some time or a chance, please let me know because I am not sure if the stored procedure always executes the 2 update statements when I run it. If it's ok, then I will leave it like that. Thanks a lot!!!

Below is the stored procedure:

Alter procedure "UpdatePlannedDate"
AS

Update tblloans
set PlannedDate = dateadd(dd,b.Days,a.FundingDate)
from tblloans A
left join tblStates b
on b.State=a.State and (convert(datetime,Convert(varchar(10),a.FundingDate,101))
between b.StartDate and isnull(b.EndDate,a.FundingDate))

Update tblloans
set PlannedDate = dateadd(dd,b.Days,a.CancelDate)
from tblloans A
left join tblStates b
on b.State=a.State and (convert(datetime,Convert(varchar(10),a.CancelDate,101))
between b.StartDate and isnull(b.EndDate,a.CancelDate))

 

[Mdavis123]

TJ,

You only want to run one or the other procedure depending on the value of Status. But we may be able to use the isnull function to supply either the Funding or the Cancel
Date.

You will have to test this, I dropped your original data I set up to test. If it works, it is really cool.


This only requires one update statement. Everplace where a.FundingDate is used, replace it with
isnull(a.FundingDate,a.CancelDate)

--
Update tblloans
set PlannedDate = dateadd(dd,b.Days,isnull(a.FundingDate,a.CancelDate))
from tblloans A
left join tblStates b
on b.State=a.State and (convert(datetime,Convert(varchar(10),isnull(a.FundingDate,a.CancelDate),101))
between b.StartDate and isnull(b.EndDate,isnull(a.FundingDate,a.CancelDate)))

 

[TJLearn]

Hi Mike,

I only want to run once. If Status = 1, pick up FundingDate, and if status = 2, then pick up CancelDate.
Your stored procedure works, but sometime it happens that other programmers go into the tblLoans table in the backend and mess it up. They will fill out both the FundingDate and CancelDate. For this reason, I have the field named Status as an identifier.

Thanks!

 

[Mdavis123]

If you have to use Status, you need to add if/then logic to the stored procedure based on the value of Status.

I think a trigger might be of use to enforce the correct entry.

MikeD