August 9, 2012 at 12:14 pm
I have a very challenging TSQL situation. I am dealing with a few million rows and have to modify a few thousand that meet the following criteria.
I have to compare a date column that has complete dates (mm/dd/yyyy) and partial dates (mm/yyyy). If I have "hits", I must compare another column and see if one was before or after the other and update the day portion of the date to either before or after the value with the complete date.
EDIT : I do want to try and do this with UPDATE statement(s) if possible, I am trying to avoid LOOPs as much as possible because of the amount of rows I have to deal with.
The following is an example, but the concept is the same:
CREATE TABLE Test1
([ID] INT IDENTITY(1,1) NOT NULL,
[Trans] BIGINT NOT NULL,
[SubTrans] BIGINT NOT NULL,
[Date] VARCHAR(20) NOT NULL
)
INSERT INTO Test1([Trans],[SubTrans],[Date])
SELECT 1,2,'09/2012'
UNION ALL
SELECT 1,3,'09/2012'
UNION ALL
SELECT 1,4,'09/15/2012'
UNION ALL
SELECT 1,5,'09/2012'
UNION ALL
SELECT 2,1,'09/15/2012'
UNION ALL
SELECT 2,2,'09/2012'
UNION ALL
SELECT 2,3,'08/2012'
SELECT * FROM Test1
/*
IDTransSubTransDate
11209/2012
21309/2012
31409/15/2012
41509/2012
52109/15/2012
62209/2012
72308/2012
*/
-- I need to perform a compare on any "Trans" that have the same month and year and if a complete date exists,
-- I need to look at the "SubTrans" and based on it being lower or higher, i have to update the "day" portion
-- of the date with a date that is earlier or later to the original date...
-- so for "Trans 1", the result should look like this:
/*
IDTransSubTransDate
11209/13/2012
21309/14/2012
31409/15/2012
41509/16/2012
*/
-- If the month and year DO NOT MATCH, I must update the "day" portion of the "Date" with "01",
-- "Trans 2" would look like the following:
/*
IDTransSubTransDate
52109/15/2012
62209/16/2012
72308/01/2012
Thanks In Advance.
JT
August 9, 2012 at 7:13 pm
This might work for you:
CREATE TABLE #Test1
( [ID] INT IDENTITY(1,1) NOT NULL,
[Trans] BIGINT NOT NULL,
[SubTrans] BIGINT NOT NULL,
[Date] VARCHAR(20) NOT NULL
)
INSERT INTO #Test1([Trans],[SubTrans],[Date])
SELECT 1,2,'09/2012'
UNION ALL SELECT 1,3,'09/2012'
UNION ALL SELECT 1,4,'09/15/2012'
UNION ALL SELECT 1,5,'09/2012'
UNION ALL SELECT 2,1,'09/15/2012'
UNION ALL SELECT 2,2,'09/2012'
UNION ALL SELECT 2,3,'08/2012'
UNION ALL SELECT 3,1,'09/15/2012'
UNION ALL SELECT 3,2,'01/2013'
UNION ALL SELECT 3,3,'02/2013'
SELECT * FROM #Test1
;WITH Test1 AS (
SELECT [Trans],[SubTrans],[Date]
FROM #Test1
WHERE LEN([Date]) <= 7
)
UPDATE a
SET [Date] = STUFF(a.[Date], 3, 1, '/' +
CASE WHEN LEFT(a.[Date], 2) = LEFT(b.[Date], 2) AND RIGHT(a.[Date], 4) = RIGHT(b.[Date], 4)
THEN RIGHT('00' + CAST(SUBSTRING(b.[Date], 4, 2) + a.[SubTrans] - b.[SubTrans] AS VARCHAR(2)), 2)
ELSE '01' END + '/')
FROM Test1 a
INNER JOIN #Test1 b
ON a.[Trans] = b.[Trans] AND LEN(b.[Date]) > 7
SELECT * FROM #Test1
DROP TABLE #Test1
But you need to check the results for the 3 transactions I added and highlighted in bold to make sure they represent your expected output for that case.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 13, 2012 at 10:21 am
Thank you very much dwain.c-
I need to run through this a little bit and modify to my "exact" environment, but prelim tests look great! Thank You very much for pointing me in the right direction.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply