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.

  • MS SQL Server How to truncate all table's rows by length?
  • How to run SQL Server stored procedure query for each value of a CSV
  • How to separate string into 3 columns
  • What does a query return in SQL Server?
  • How can I list all foreign keys referencing a given table in SQL Server?
  • Sql Server Full Text: Human names which sound alike
  • Example of intended result
    Table #1: See above
    Table #2:

    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 ...

    Merged Table:

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.