sql query to with insert select

Question

I have a table with the below format:

ID  curr    Date                    Bid     Ask
1   AUD/NZD 20090501 00:00:00.833   1.2866  1.28733
2   AUD/NZD 20090501 01:01:01.582   1.28667 1.2874
3   AUD/NZD 20090501 02:01:01.582   1.28667 1.28747

Now I need to select the change of Bid and Ask column and store into a different table...The result should be like the following

Bid Change  Ask Change  

0.0000700   0.0000700   
0.0000000   0.0000700   


select 
    t1.id, 
    t1.curr, 
    t1.date,
    t1.bid, 
    t1.bid - t2.bid [Bid Change],  
    t1.ask, 
    t1.ask - t2.ask [Ask Change]

from tbltest t1
left join tbltest t2 on t1.ID = t2.ID + 1
order by date

This query returns everything correct except the format of Bid Change and Ask Change...like the following

 BID Change            ASK CHANGE
7.00000000000145E-05  7.00000000000145E-05

Am really clueless on what to do with this situation...any little help will work.

Thanks in advance!

Self-join on the condition that will compare the two rows you want to compare. — Tab Alleman, Feb 27 '15 at 13:16

GarethD · Accepted Answer · 2015-02-27T13:37:44.343

1

It doesn't seem necessary to me to store them in a different table, you can just calculate them on the fly using APPLY, this way any changes to the underlying data will not cause your change data to be stale:

SELECT  T.*,
        BidChange = t.Bid - prev.Bid,
        AskChange = t.Ask - prev.Ask
FROM    T
        OUTER APPLY
        (   SELECT  TOP 1 T2.Bid, T2.Ask
            FROM    T AS T2
            WHERE   T2.Curr = T.Curr
            AND     T2.Date < T.Date
            ORDER BY T2.Date DESC
        ) AS prev;

If this is something you will need regularly then you may want to consider a view, rather than storing it in a table.

enter image description here

edited Feb 27 '15 at 13:37

answered Feb 27 '15 at 13:16

GarethD

68,045
10
83
123

@GarethD...what is the T2 here? – Joyeeta Sinharay Feb 27 '15 at 13:20
`T2` is a [table alias](https://technet.microsoft.com/en-us/library/ms187455%28v=sql.105%29.aspx) - I didn't know your table name so I just used `T` – GarethD Feb 27 '15 at 13:21
I am getting following error...Msg 208, Level 16, State 1, Line 1 Invalid object name 'T'. – Joyeeta Sinharay Feb 27 '15 at 13:21
As above. I don't know what your actual table is called, so I just used `T`. If you substitute your table name in it should work fine. – GarethD Feb 27 '15 at 13:22
@garethD...for BID change and Ask Change am getting the following value 7.00000000000145E-05 7.00000000000145E-05 – Joyeeta Sinharay Feb 27 '15 at 13:30
I think this is an issue with your data then - I have posted an image of the query working correctly with your sample data. – GarethD Feb 27 '15 at 13:40
@garethD...I have done some other query which returns me everything only the format of Bid change and Ask change are weird..have posted that query in the post...can you please check that once...please – Joyeeta Sinharay Feb 27 '15 at 13:49
@GarethD...have you checked the edited version of post with the query...please help me on the formatting please – Joyeeta Sinharay Feb 27 '15 at 14:01
Formatting is a job for the presentation layer, not SQL.. What are you displaying the results in? – GarethD Feb 27 '15 at 14:11
(t1.bid - t2.bid) and (t1.ask - t2.ask) returning weird format...whereas if i try to add value it returns the exact result!!! – Joyeeta Sinharay Feb 27 '15 at 14:15
@GarethD...is it because of the TYPE I have used is FLOAT for the Bid and Ask column? – Joyeeta Sinharay Feb 27 '15 at 14:24
Probably. [Float is not always accurate](http://stackoverflow.com/q/2100490/1048425) - You may want to cast to decimal. – GarethD Feb 27 '15 at 14:32

sql query to with insert select

1 Answers1