?
Solved

Just a little MS SQL Wizardry please

Posted on 2014-02-18
6
Medium Priority
?
108 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 66

Expert Comment

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

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 93

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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

650 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