Insert data into temp table, multiple columns from one Table

Question

I have a table with columns: ID (Int), Date (Date) and Price (Decimal). Date column is in format 2013-04-14:

Table Example

ID      Date        Price
    1       2012/05/02  23.5
    1       2012/05/03  25.2
    1       2012/05/04  22.5
    1       2012/05/05  22.2
    1       2012/05/06  26.5
    2       2012/05/02  143.5
    2       2012/05/03  145.2
    2       2012/05/04  142.2
    2       2012/05/05  146.5
    3       2012/05/02  83.5
    3       2012/05/03  85.2
    3       2012/05/04  80.5

Query Example:

I want to be able to select all ID1 and ID3's data between a date range from the table and have this in a table with three columns, ordered by Date column. Also I would want to insert this into a temporary table to perform mathematical calculations on the data. Please comment if there is a better way.

Correct Result Example

Date        ID1     ID3
2012-05-02  23.5    83.5    
2012-05-03  25.2    85.2    
2012-05-04  22.5    80.2

Any help and advice will be appreciated,

Thanks

I don't understand where your “correct results” are coming from. Neither your sample data nor query have rows for 2012-05-02, but your results have them. Your query selects two columns, but your results have three. Price for ID 1 on 2012/05/03 is 23.5, but your “correct results” show 318.69. — Dour High Arch
– Dour High Arch, Commented Aug 2, 2013 at 16:32
What if you had more than one row with the same ID and Date? Should the Prices be added? Only one chosen? If you think there shouldn't be, what should happen if there were? — Dour High Arch
– Dour High Arch, Commented Aug 2, 2013 at 22:57

David Nhim · Accepted Answer · 2013-08-17 14:18:53Z

2

Try the following.

CREATE TABLE #temp (
  Date date,
  x money,
  y money
  )
;

SELECT
 Date,
 MAX(CASE WHEN id=1 THEN price END) AS x,
 MAX(CASE WHEN id=3 THEN price END) AS y
FROM Top40
WHERE Date BETWEEN '2012-05-02' AND '2012-05-04'
GROUP BY 
 Date
;

See SQL Fiddle for working example

EDIT: To use the LAG window function on the x and y columns, you'll have to use a common table expression or CTE first.

WITH prices AS(
SELECT
 Date as myDate,
 MAX(CASE WHEN id=1 THEN price END) AS x,
 MAX(CASE WHEN id=3 THEN price END) AS y
FROM Top40
WHERE Date BETWEEN '2012-05-02' AND '2012-05-04'
GROUP BY 
 Date
 )
SELECT
 myDate,
 p.x,
 (p.x/(LAG(p.x) OVER (ORDER BY MyDate))-1) as x_return,
 p.y,
 (p.y/(LAG(p.y) OVER (ORDER BY MyDate))-1) as y_return
FROM prices p
ORDER BY
 myDate
;

See new SQL Fiddle for example.

edited Aug 17, 2013 at 14:18

answered Aug 2, 2013 at 19:34

David Nhim

4693 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stuart Ainsworth · Accepted Answer · 2013-08-02 19:19:28Z

0

The simplest way to do it in code (although it may not perform well with large data sets) is to do something like:

SELECT [Date], x = MAX(CASE WHEN ID = 1 THEN PRICE END)
, y = MAX(CASE WHEN ID = 3 THEN PRICE END)
INTO #tmp
FROM Top40
GROUP BY [Date]

answered Aug 2, 2013 at 19:19

Stuart Ainsworth

12.9k44 silver badges46 bronze badges

Comments

Nicholas Carey · Accepted Answer · 2013-08-02 19:47:54Z

Or...

select Date , t1.Price as Stock_1_Price , t2.Price as Stock_3_price
from      ( select "Date" , max(Price) as Price from myData where ID = 1 group by "Date" ) t1
full join ( select "Date" , max(Price) as Price from myData where ID = 3 group by "Date" ) t2 on t2.Date = t1.Date

As far as populating a temp table, any of the usual ways works:

Table variable:

declare @work table
(
  yyyymmdd      varchar(32) not null ,
  stock_1_price money null ,
  stock_3_price money null
)

insert @work ( yyyymmdd , stock_1_price , stock_3_price )
select Date , t1.Price as Stock_1_Price , t2.Price as Stock_3_price
from      ( select "Date" , max(Price) as Price from myData where ID = 1 group by "Date" ) t1
full join ( select "Date" , max(Price) as Price from myData where ID = 3 group by "Date" ) t2 on t2.Date = t1.Date

Declared temp table in tempdb

create table #work
(
  yyyymmdd      varchar(32) not null primary key clustered ,
  stock_1_price money null ,
  stock_3_price money null
)

insert #work ( yyyymmdd , stock_1_price , stock_3_price )
select Date , t1.Price as Stock_1_Price , t2.Price as Stock_3_price
from      ( select "Date" , max(Price) as Price from myData where ID = 1 group by "Date" ) t1
full join ( select "Date" , max(Price) as Price from myData where ID = 3 group by "Date" ) t2 on t2.Date = t1.Date

non-declare temp table in tempdb via select into:

select Date , t1.Price as Stock_1_Price , t2.Price as Stock_3_price
into #work
from      ( select "Date" , max(Price) as Price from myData where ID = 1 group by "Date" ) t1
full join ( select "Date" , max(Price) as Price from myData where ID = 3 group by "Date" ) t2 on t2.Date = t1.Date

It works thanks, though no date Where clause to specify range

Collectives™ on Stack Overflow

Insert data into temp table, multiple columns from one Table

Table Example

Query Example:

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Table Example

Query Example:

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related