Solved

Just a little MS SQL Wizardry please

Posted on 2014-02-18
6
102 Views
Last Modified: 2014-12-08
Hello Experts!

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!

Fox
0
Comment
Question by:the_b1ackfox
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39868473
Where exactly is the question in here?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39868553
You need to clarify what your question is (as Jim notes above).

Also, keep in mind that SQL Server does not have regexp capabilities, so depending on just what rules apply for picking things out of your Reason column, you may be better off handling that in your C# code.
0
 
LVL 9

Author Comment

by:the_b1ackfox
ID: 39868723
Sorry for the confusing post.  I was mostly wondering if there was a way to extract out the data from the free text column and to use it to do a calculation...   But I think MatthewsPattrick may have nailed to issue on the head...
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39870144
There certainly are ways, but you have not fully spelled out the rules to follow in picking data elements out of that string, nor what to do with those data once found.

Depending on the above, it may be possible to do this in SQL, but if the requirements are complex at all, then I stand by my statement that you may be better off handling that in your C# code.

SQL Server is great at managing data and performing set-wise operations.  It is much less-great when it comes to analyzing/parsing text and applying complex business logic.
0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 total points
ID: 39870156
Assumes day-increment is the first value in the Reason field, with d as a delimiter:
select	CancelDate
,	newDate =	case AddDays
				when 1 then dateadd(dd, cast(substring(Reason, 1, charindex('d', Reason) - 1) as int), CancelDate)
				else dateadd(dd, AddDays, CancelDate)
			end
from	yourTable

Open in new window

0
 
LVL 9

Author Comment

by:the_b1ackfox
ID: 39896144
Sorry for the delay guys.  A week in Vegas followed a week of recovery (sick...who says what happens in Vegas stays in Vegas?  Aparently cold germs aren't cool, nor can they read)

Thank you for the input everyone.

John I will try that code this weekend
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question