I have a column where there is free form text in it. We like to pull some codes out of there to make some calculations and either update another table OR make the calculations in realtime to be consumed by a C# Application for presentation and printing.
Typically I have used C# do all of this, but then I thought that if I can get SQL to do the heavy lifting, then there is a lot less data manipulation for me to do.
Anyways the table is laid out as follows
(PK)pkid (Datetime)CancelDate (int) AddDays (String)Reason
1 2014-02-14 08:00:00 1 10d Reschedule in 10 days
2 2014-02-14 08:00:00 1 180d Snowstorm
3 2014-02-14 08:00:00 30
In the cases when the AddDays value is greater than 1 (the default) then calculate canldate + AddDays. In cases where AddDays value is greater than 1 then get the day value from the reason (10d =10 days, 180d = 180 days) and calulate the canceldate + the day value.
I am most interested in the extraction of the day value from the string, at which point I can play with the case scenarios, but the whole shootin match is great too! I absolutely love learning new techniques, so please lay it out!