March 12, 2013 at 5:12 pm
I have a string that needs some serious help...
I need to rewrite many strings with the aggregate value of anything between position 1, 12 and on a per UserID basis. Essentially...I need to GROUP BY UserID.
The formatting needs to stay exactly the same fixed length.
Here's the breakdown of the string:
The [Amount] is anything between position 1, 12. The [UserID] is the final 6.
Thanks in advance...
IF OBJECT_ID('TempDB..#StrungOut','U') IS NOT NULL
DROP TABLE [#StrungOut]
CREATE TABLE [dbo].[#StrungOut](
[Value] varchar (255) NOT NULL
)
INSERT INTO #StrungOut (Value)
SELECT 'D000000206408312335308055102 Joe Blow 1002220200000290357' UNION ALL
SELECT 'D00000248190001233930343216 Sue O''Malley 1004539500000290360' UNION ALL
SELECT 'D00031228690003213965347415 Sue O''Malley 1004539500000290360' UNION ALL
SELECT 'D00000022940010007560323433 Fred Greenface 1002403000000290361' UNION ALL
SELECT 'D000000063100013455348487150327 Bill Guy 9835627000000290363' UNION ALL
SELECT 'D000000063700043445238454350364 Bill Guy 1004695300000290363' UNION ALL
SELECT 'D00000020640010005467116730 billy buck 9836280000000290376' UNION ALL
SELECT 'D00000020640010001334112330 billy buck 9836280000000290376' UNION ALL
SELECT 'D00000350640010001356162670 billy buck 1236280000000290400' UNION ALL
SELECT 'D00000020640001602130103454 dave user 1004253500000290379' UNION ALL
SELECT 'D00001220440002304835606876 dave user 5004253500000290379'
SELECT * FROM #StrungOut
March 12, 2013 at 6:39 pm
Pretty easy, actually.
First you need to extract the actual data from the file rows:
SELECT SUBSTRING(Value, 2,12) Amount, right(Value, 6) UserID
FROM #StrungOut
Then you need to convert the extracted strings to appropriate data types (I assume the last 2 digits in the amount are after decimal point, correct the script if I'm wrong about it):
SELECT CONVERT(decimal(24, 2), amount)/100 Amount, convert(int, UserID) UserID
FROM (
SELECT SUBSTRING(Value, 2,12) Amount, right(Value, 6) UserID
FROM #StrungOut
) E
And then you do your aggregations:
SELECT SUM(Amount) TotalAmount, UserID, COUNT(*) Records
FROM (
SELECT CONVERT(decimal(24, 2), amount)/100 Amount, convert(int, UserID) UserID
FROM (
SELECT SUBSTRING(Value, 2,12) Amount, right(Value, 6) UserID
FROM #StrungOut
) E
) A
GROUP BY A.UserID
_____________
Code for TallyGenerator
March 12, 2013 at 6:55 pm
Thanks Sergiy....
And you're right...that part is/was easy. I pretty much figured out how to do that.
The problem that I'm having is rewriting the string back to it's original and exact format with the aggregated data. e.g...
These two rows...
D00000248190001233930343216 Sue O'Malley 1004539500000290360
D00031228690003213965347415 Sue O'Malley 1004539500000290360
need to become one row all in the same column
D00031476880003213965347415 Sue O'Malley 1004539500000290360
Does that make sense?
March 12, 2013 at 7:44 pm
Should not be so difficult too.
Start with taking all parts of the string:
SELECT VALUE, SUBSTRING(Value, 1,1) Prefix,
SUBSTRING(Value, 2,12) Part1_Amount,
SUBSTRING(Value, 13,20) Part2_WhateverItIs,
SUBSTRING(Value, 33,LEN(Value) -33-18) Part3_Username,
SUBSTRING(Value, LEN(Value) -19, 19 - 6) Part4_WhateverItIs,
right(Value, 6) UserID
FROM #StrungOut
Then you continue with conversions and aggregations, but keep dragging all the parts along.
After it's done - convert Amount and UserID back to strings (following the spacing and padding rules) and concatenate the strings back together.
Ad the last step - delete existing rows and replace them with new ones.
P.S. Would you consider using a relational database? They say it's really an advanced tool for storing and manipulating data.
:hehe:
_____________
Code for TallyGenerator
March 13, 2013 at 12:15 am
OK...OK... Not so "Advanced" after all. For you maybe... 😛
Here's the finished product:
SELECT SUM(CAST(SUBSTRING(col001, 2,10) AS INT)) Amount,
MAX(SUBSTRING(col001, 12,21)) Element1,
MAX(SUBSTRING(col001, 33,29)) AS UserName,
MAX(SUBSTRING(col001, LEN(col001) -19, 19 - 6)) Element2,
RIGHT(col001, 6) UserID
INTO #StrungOut FROM SomeTable
GROUP BY RIGHT(col001, 6)
SELECT
CAST('D'+(CASE
WHEN LEN(CAST(Amount AS VARCHAR)) = 2 THEN '00000000'
WHEN LEN(CAST(Amount AS VARCHAR)) = 3 THEN '0000000'
WHEN LEN(CAST(Amount AS VARCHAR)) = 4 THEN '000000'
WHEN LEN(CAST(Amount AS VARCHAR)) = 5 THEN '00000'
WHEN LEN(CAST(Amount AS VARCHAR)) = 6 THEN '0000'
WHEN LEN(CAST(Amount AS VARCHAR)) = 7 THEN '000'
WHEN LEN(CAST(Amount AS VARCHAR)) = 8 THEN '00'
ELSE 'Unkown'
END) +''+CAST(Amount AS VARCHAR)+''+Element1+''+UserName+''+Element2+''+UserID
AS VARCHAR(80))
AS VALUE
FROM #StrungOut
ORDER BY UserName
DROP TABLE #StrungOut
Thanks again Sergiy...!
March 13, 2013 at 3:36 am
That big CASE statement also can be replaced with a simple function call:
REPLACE(STR(Amount, 10), ' ', '0')
You always welcome!
🙂
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply