?
Solved

Just a little MS SQL Wizardry please

Posted on 2014-02-18
6
Medium Priority
?
110 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 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

807 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