?
Solved

Just a little MS SQL Wizardry please

Posted on 2014-02-18
6
Medium Priority
?
107 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

752 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