May 2, 2012 at 4:51 pm
I recently uploaded data from a CSV file into a SQL Server Table. The data is formatted with a vchar field that needed to be sorted. Since the data could not be sorted with this format, I proceeded to create a substring of the relevant parts of the vchar field and completed the following script and creating another table:
USE EURUSD
GO
INSERT Tick#6
SELECT [Time] ,[Ask] ,[Bid] ,[AskVolume] ,[BidVolume]
FROM [EURUSD].[dbo].[Tick#4]
ORDER BY Substring(Time,1,4), Substring(Time,6,2) , Substring(Time,9,2), Substring(Time,12,2) , Substring(Time,15,2), Substring(Time,18,2), Substring(Time,21,3)
This though did not work. Next I created a concatenated (Concat) field basically taking the substring and pasting them together. Thinking that it was possible to index a vchar field, I placed an index on the concatenate (Concat) field thinking that it would sort out all of the records in the table. This though did not work either. Next I changed the Concat field from a vchar to a decimal formated field and placed an index on this as well. This did not work either. Lastly I performed the following script to create another table. This did not work either as there is a significant gap in my records when you perform a simple select top 1000 query. All of the record though are present in the table.
USE EURUSD
GO
INSERT Tick#7
SELECT (Substring(Time,1,4) + SUBSTRING(Time,6,2) + SUBSTRING(Time,9,2) + Substring(Time,12,2) + SUBSTRING(Time,15,2) + SUBSTRING(Time,18,2) + Substring(Time,21,3)) AS 'CONCAT', [Time], [Ask] ,[Bid] ,[AskVolume] ,[BidVolume]
FROM [EURUSD].[dbo].[Tick#6]
ORDER BY CONCAT
Can someone help me to do this properly?
May 2, 2012 at 5:14 pm
Your png attachment is very nice, however it does not allow me to copy and paste any information from it, so that I can use same to create your table and populate it with data. Something that those who attempt to answer your question prefer to do so that they can assist you by giving you and tested answer.
To make it easier, to help those who wish to help you please click on the first link in my signature block, read the article which contains the T-SQL code to allow you to post table definition and sample data in an readily consumable format and post the table definition, sample data and the results you require.
May 2, 2012 at 5:19 pm
Well, now, that's interesting. These are ISO dates with milliseconds, but the don't conform to actual standards. Instead of dissassembling the data, I'd recommend we adjust the data in transit.
So, original test bed:
DECLARE @blah TABLE ( vcDT VARCHAR(40))
INSERT INTO @blah ( vcDT)
SELECT '20090101230701249' UNION ALL
SELECT '20100216212606518'
SELECT
*,
CAST( vcDT AS DATETIME) AS dt
FROM
@blah
That will fail. We need to adjust it slightly. I used a calculated column for your staging table interpretation I created here.
DECLARE @blah TABLE
( vcDT VARCHAR(40),
-- Always work STUFF() from the back forwards
-- Intention is to get to ODBC Canonical with milliseconds
-- yyyy-mm-dd hh:mi:ss.mmm
vcDT_frmt AS STUFF( STUFF( STUFF( STUFF( STUFF( STUFF(vcDT, 15,0,'.' ), 13, 0, ':') , 11, 0, ':'), 9, 0, ' '), 7, 0, '-'), 5, 0, '-')
)
INSERT INTO @blah ( vcDT)
SELECT '20090101230701249' UNION ALL
SELECT '20100216212606518'
SELECT * FROM @Blah
SELECT
*,
CAST( vcDT_frmt AS DATETIME) AS vcDT_dt
FROM
@blah
ORDER BY
CAST( vcDT_frmt AS DATETIME)
I believe that's what you're looking for it to do?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 2, 2012 at 6:02 pm
bitbucket,
Thank you for your reply. I will go read through this so that my postings are more efficient and revise what I posted.
Mike
May 2, 2012 at 6:04 pm
Evil Kraig F,
Thank you for your reply. I will code this and see how it works. Aparently my solutions are too simple and as a result not working.
Mike
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply