sql - Create a table based on a query -


the title may misleading, i'm unsure of way of phrasing it. query below works intended if little slow. however, when try wrapping insert into around query, locks database. table1 contains less 1,000 records , table2 less 1,500 records.

the objective of query match records based on date/time groups contained in columns tables 1 , 2. crux of problem getting result set unique table can exported.

this query variation of find here , may clarify intentions: https://social.msdn.microsoft.com/forums/office/en-us/1305b3a9-94c9-4e7c-a5fe-7b64a79600ca/select-closest-earlier-date. change rather looking closest date after, i'm retrieving closest day prior.

select table1.*, (     select table2.column1 table2 table2.column1 = (         select max(t.column1) table2 t t.column1 <= table1.column1)) tempcol table1 

are there alternatives can attempt break sql or option let run until it's complete?

example of output:

table1.id     table1.column1     t.column1 1             2/1/2015 12:00     2/1/2015 08:30 2             2/1/2015 12:15     2/1/2015 08:30 3             2/1/2015 12:30     2/1/2015 08:30 4             2/1/2015 12:45     2/1/2015 08:30 5             2/1/2015 13:00     2/1/2015 12:55 

you try insert result of query new table:

select * newtable (   select table1.*, (     select table2.column1 table2 table2.column1 = (         select max(t.column1) table2 t t.column1 <= table1.column1))     tempcol table1 ) 

then run insert using select * newtable query.

but based on said need, why don't run this:

select table1.* , (select max(t2.column1) table2 t2 t2.column1 < table1.column1) t2col1 table1  

or

select table1.id, table1.column1, max(table2.column1) table1  left join table2 on  table2.column1 < table1.column1 group table1.id,  table1.column1 

that gets records table1, along maximum date table2 table2's column1 less table1's column1.


Comments

Popular posts from this blog

c++ - Difference between pre and post decrement in recursive function argument -

php - Nothing but 'run(); ' when browsing to my local project, how do I fix this? -

php - How can I echo out this array? -