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
Post a Comment