SQL Joining data when it is grouped and has different frequency
Currently, I have two tables. The data in both is of a time series nature but is in a flat format. In the first table, I currently have a dataset of dates, a company identifier and a value of interest. Both the company identifier and the value are numerical. Another thing to point out is that the companyid/date pairs are unique so there are no duplicates in the dataset. Below is an example of a couple of rows of the data in the first table.
companyid date value1 1111 09/14/1986 1234 1111 10/14/1986 5678 1001 09/14/1986 5675 1001 10/10/1986 5930
The second table is similar since it also has companyid(s), dates, and several columns of values. What I would like to do is write a SQL query to join the two tables. More specifically, for every companyid I would like to add the value columns from the second table but only for those companyid/date pairs that exist in the first table. The first table has approximately 2m+ rows and the second table has approximately 25m+ rows.
Example of intended result
Table #1: See above
companyid date ... several value columns ... 1001 09/14/1986 4275 4447 ... 1001 10/10/1986 5924 7728 ... 1001 11/14/1986 3356 4822 ... 1001 12/14/1986 2539 2849 ... 1111 05/04/1993 2398 1209 ... 1111 09/14/1986 5945 4567 ... 1111 10/14/1986 2039 6797 ...
companyid date value1 ... several value columns ... 1111 09/14/1986 1234 5945 4567 ... 1111 10/14/1986 5678 2039 6797 ... 1001 09/14/1986 5675 4275 4447 ... 1001 10/10/1986 5930 5924 7728 ...
2 Solutions collect form web for “SQL Joining data when it is grouped and has different frequency”
If I understand your question correctly. What you can do is
Select t.companyid, t.date, t.value From table table t inner join table1 t1 on t1.companyid = t.companyid and t.date =t1.date
You have a lot of data. And if you actually want to replace the data in the first table, then I would suggest using the truncate/re-insert method:
Select t1.companyid, t1.date, t2.value, t1.value1, t1.value2, . . . into #temp From table1 t1 inner join table2 t2 on t1.companyid = t2.companyid and t1.date = t2.date; truncate table table1; alter table table1 add value1 int; insert into table1 (companyid, date, value, value1, value2, . . .) select companyid, date, value, value1, value2, . . . from #temp;
You could also put together a
merge command, but the overhead of doing lots of deletes and inserts can be a lot. Not to mention the overhead of adding a new integer column into a very full table.
I’m pretty sure the
inner join does provide the data that you want.