June 18, 2013 at 12:27 am
Can someone help me with determining why my TestTable data does not roll over into my ResultTable data? I have reviewed all of the data types but cannot figure out why I am not getting any data in the ResultTable. I have tried to put together input data for this posting into the #mytable but cannot figure out how to completely populate this table. Anyhow here is what I have:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
Yearvarchar(4),
Monthvarchar(2),
Dayvarchar(2),
Hourvarchar(2),
Minutevarchar(2),
Secondvarchar(2),
Milisecondvarchar(3),
Askfloat,
Bidfloat,
AskVolumefloat,
BidVolumefloat,
Itemchar(2)
)
INSERT INTO #mytable
(Year, Month, Day, Hour, Minute, Second, Milisecond, Ask, Bid, AskVolume, BidVolume, Item)
SELECT'2013','05','04','10','12','45','200','1.23054', '1.23052', '1','3','N'
SELECT'2013','05','04','10','12','46','400','1.23055', '1.23052', '1','3','N'
SELECT'2013','05','04','10','12','46','500','1.23055', '1.23052', '1','3','N'
SELECT'2013','05','04','10','12','47','400','1.23055', '1.23052', '1','3','N'
SELECT'2013','05','04','10','12','49','400','1.23055', '1.23052', '1','3','N'
DECLARE @ResultTable TABLE (curRN bigint, prevRN bigint, curBid Decimal(6,5), prevBid Decimal(6,5), Item char, Concat Varchar(20));
DECLARE @TestTable TABLE (Year Varchar(4), Month Varchar(2), Day Varchar(2), Hour Varchar(2), Minute Varchar(2), Second Varchar(2), Milisecond Varchar(3), Bid Decimal(6,5), Item Char(2), Concat Varchar(20));
INSERT INTO @TestTable (Year, Month, Day, Hour, Minute, Second, Milisecond, Bid, Item, Concat)
SELECT Year, Month, Day, Hour, Minute, Second, Milisecond, Bid, Item, (Year+Month+Day+Hour+Minute+Second+Milisecond) AS Concat
From #mytable
;WITH cte AS
(
SELECT Year, Month, Day, Hour, Minute, Second, Milisecond, Bid, Item, Concat,
RN = ROW_NUMBER()
OVER (ORDER BY Year, Month, Day, Hour, Minute, Second, Milisecond, Bid, Item, Concat)
FROM @TestTable
)
INSERT INTO @ResultTable (curRN, prevRN, curBid, prevBid, Item, Concat)
SELECT cur.RN AS 'Cur-RN', prev.RN AS 'Prev-RN', cur.Bid AS 'Cur-Bid', prev.Bid AS 'Prev-Bid', cur.Concat,
CASE
WHEN (cur.Bid - prev.Bid) > 0 THEN 'U'
WHEN (cur.Bid - prev.Bid) = 0 THEN 'F'
WHEN (cur.Bid - prev.Bid) < 0 THEN 'D'
ELSE 'N'
END
FROM cte cur JOIN cte prev
ON cur.RN = prev.RN + 1
SELECT *
FROM @ResultTable
(249616 row(s) affected)
Msg 8152, Level 16, State 14, Line 7
String or binary data would be truncated.
The statement has been terminated.
(0 row(s) affected)
June 18, 2013 at 2:13 am
You have probably interchanged the order of columns in your SELECT statement
INSERT INTO @ResultTable (curRN, prevRN, curBid, prevBid, Item, Concat)
SELECT cur.RN AS 'Cur-RN', prev.RN AS 'Prev-RN', cur.Bid AS 'Cur-Bid', prev.Bid AS 'Prev-Bid',
CASE
WHEN (cur.Bid - prev.Bid) > 0 THEN 'U'
WHEN (cur.Bid - prev.Bid) = 0 THEN 'F'
WHEN (cur.Bid - prev.Bid) < 0 THEN 'D'
ELSE 'N'
END, cur.Concat -- Concat should be the last column as per your INSERT
FROM cte cur JOIN cte prev
ON cur.RN = prev.RN + 1
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply