Solved

Just a little MS SQL Wizardry please

Posted on 2014-02-18
6
104 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

696 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