May 21, 2008 at 9:49 am
First, I apologize for the appearance of the post, I don't want to change the sample string, or I'd add carriage returns to fix it.
I am attempting to parse a string, extracting dates that are affiliated with a userID, and determine if they fall into a daterange that will end up as a production workload report for some of our business units. When I try to convert to datetime in order to do the comparison, I get an error. Currently have the table variable column set to varchar(19) and the conversion commented out, so you can see what I'm working with prior to converting.
I've already found some ASCII characters in the strings that were giving me troubles, and am replacing them with empty strings. As far as I can tell, the only non-numeric characters left are the slashes within the dates.
Sample data and my code follow, names have been changed to protect the innocent, the guilty one I left in there. 😀
Any help appreciated!
/*
Title: 051608 All Call Trackings Worked.sql
Description:This report generates a count of all call tracking issues worked during a specified timespan.
To do so, it parses the text of each issue, searches for a specified userID and pulls all date stamps
to compare to the timeframe required.
Impact: all production units using call tracking
Author: Jon Crawford
*/
--=============================================================================
-- Create and populate a Tally table --By Jeff Moden, 2008/05/07 http://www.sqlservercentral.com
--=============================================================================
--===== Conditionally drop and create the table/Primary Key
IF OBJECT_ID('Tempdb..#Tally') IS NOT NULL BEGIN DROP TABLE #Tally END
CREATE TABLE #Tally (N INT)
--===== Create and preset a loop counter
DECLARE @Counter INT, @upperLimit INT
SET @Counter = 1
SET @upperLimit = 11000
--===== Populate the table using the loop and counter
WHILE @Counter <= @upperLimit
BEGIN
INSERT INTO #Tally (N) VALUES (@Counter)
SET @Counter = @Counter + 1
END
--===========================================END TALLY TABLE SETUP========================================
-- =================
-- Declare Variables
-- =================
DECLARE @users table (
iRow int identity(1,1),
userID char(8)
) -- table of users we are looking for
DECLARE@fromDate datetime, -- used to limit calls to reasonable list
@toDate datetime, -- used to limit calls to reasonable list
@userNum int, -- which user are we looking up
@who char(8), -- name of the user we are looking up
@issueNum int, -- which issue are we pulling from the universe to analyze
@startFrom int, -- start walking the string from this point
@found int -- flag to say whether occurrence exists
-- ====================
-- Initialize Variables
-- ====================
SET @fromDate = '6/15/2007'
SET @toDate = '7/2/2007'
INSERT INTO @users (userID) VALUES ('CrawforJ') -- for testing, using me
--INSERT INTO @users (userID) VALUES ('SomeUser')
--
SET @userNum = 1 -- start with the first user
SET @issueNum = 1 -- start with the first issue
SET @startFrom = 1 -- start searching from the start of the string
SET @found = 0
-- ================================
-- Loop through users one at a time
-- ================================
WHILE @userNum < (SELECT max(iRow) FROM @users)+1 -- loop until last user
BEGIN
-- ============================================================================
-- create temp table to hold detail data about all call tracking issues that
--contain userID and
--were open during the timeframe
-- this list may not have been an issue updated during the timeframe
-- ============================================================================
IF object_id('tempdb..#issueCount') IS NOT NULL BEGIN DROP TABLE #issueCount END -- drop if it exists already
CREATE TABLE #issueCount (userID char(8),
issueNum int identity(1,1),
location int,
reasonnote varchar(2000))
-- find which user to look for
SELECT @who = userID--+'>'
FROM @users
WHERE iRow = @userNum
-- pull detail for possible issues updated by this user
--===== Insert the test data into the test table
INSERT INTO #issueCount (userID, location, reasonnote) VALUES ('CrawforJ', '21', ' Changed User from Jonathan Crawford to Inquiry Authorization. 6/11/2007 1:36:30 PM SomeUser This call was sitting in ''unassigned'' status because it was routed to wrong bucket. Please see your lead and reroute. 7/2/2007 6:48:23 PM CrawforJ claim subsequently adjusted, closing call ')
INSERT INTO #issueCount (userID, location, reasonnote) VALUES ('CrawforJ', '98', ' Changed User from Jonathan Crawford to Adjustment. 6/15/2007 1:57:01 PM CrawforJ Please adjust claim and manually price to pay - per UM at the health plan, this claim should be approved. Thanks! ')
INSERT INTO #issueCount (userID, location, reasonnote) VALUES ('CrawforJ', '21', ' Monitor date changed from 12/31/2078 to 06/26/2007. 6/26/2007 8:40:29 AM CrawforJ test issue, ignore. Thanks! ')
/*
convert(datetime,substring(reasonnote,charindex(@userID,reasonnote,@startFrom)-19,16)+':00') > @fromDate
AND convert(datetime,substring(reasonnote,charindex(@userID,reasonnote,@startFrom)-19,16)+':00') < DATEADD(dd,1,@toDate)
*/
-- =============================================================================================================
-- find location of userID and extract each timestamp for this user to determine if it should count as an update
-- =============================================================================================================
WHILE @issueNum < (SELECT max(issueNum) FROM #issueCount)+1 -- loop through issues until no more issues for this userID
BEGIN
-- Jeff Moden tally table example to split the string and store results
--===== Simulate a passed parameter
DECLARE @Parameter VARCHAR(8000)-- holds text of the issue note for searching
SELECT @Parameter = reasonnote FROM #issueCount WHERE issueNum = @issueNum AND location > 0
-- only analyzing one issue note at a time
--===== Create a table to store the results in
DECLARE @Elements TABLE
(
Number INT IDENTITY(1,1), --Order it appears in original string
Value varchar(19)--datetime --The string value of the element, or eventually the datetime (when it works)
)
--===== Join the Tally table to the string at the character level and
-- when we find a userID, insert the datetime found into the Elements table
INSERT INTO @Elements (Value)
-- find @who within @Parameter, then move back 19 characters,
-- find the first slash, and back up three from there
-- replace ASCII control characters (tab, line feed, carriage return) with empty strings
-- trim spaces from either side
-- then convert to datetime (currently commented out)
SELECT /*convert(datetime,*/rtrim(ltrim(replace(replace(replace(replace(replace(substring(@Parameter,N-19+(charindex('/',SUBSTRING(@Parameter,N-16,17),1))-3,11),char(9),''),char(10),''),char(13),''),':',''),'<','')))--+'00:00:00')
FROM #Tally
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter,N,8) = @who
--testing
SELECT * FROM @Elements
/* Jon : when I look for the ascii values, I only see char(47), which is the forwardslash - why won't it convert to datetime?
SELECT N,ascii(substring(Value,N,1)) FROM #Tally, @Elements
WHERE N < LEN(Value)
AND substring(Value,N,1) NOT LIKE '[0-9]'
*/
--SELECT @found = count(distinct Value) FROM @Elements WHERE Value BETWEEN @fromDate AND @toDate
DELETE @Elements -- clear out all rows from the table variable
SET @issueNum = @issueNum + 1 -- increment counter to move to the next issue for this userID
END
SET @userNum = @userNum + 1 -- increment @userNum to move to the next user
SELECT * FROM #issueCount -- present for testing only, comment out of final version
END
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
May 21, 2008 at 12:02 pm
The theory goes that it's either a 'fat finger error' or a 'fat head error', this one was both. Needed to either add a space in my final addition of '00:00:00' to make it ' 00:00:00', or just drop that part and close the parentheses since I didn't care about the time anyway.
Couldn't see the trees for the forest.
Thanks anyway.
Jon
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply