Sql-server – SQL: Merge rows when value doesnt change in continuous time

This sounds like your customer does not entirely understand what is possible with SQL Server. It is also possible that he is confusing SQL Server with Oracle. With Oracle, you can spin up one database and have two database instances running independantly and operating of the same data set.

With SQL Server, the best you are going to probably do is some sort of Merge replication with two application servers and a distribution database. I would re-visit the customer requirements, determine exactly what he wants, not what he thinks he wants, and not how he wants to do it, and then come up with a solution to provide to the customer.

Unless you or your customer are willing to buy a third party product, some form of SQL Server replication is going to have to be used. Here are some links (2008 R2):

Merge replication

Replication overview

As first step I merge the two product tables. I need an full outer join on product1 and product2 tables. To obtain on outer join in mysql we can union a left join and a right join. Start with a left join. In the following query IFNULL(p1.key1, p2.key1) is useful to merge two key column coming from the two product tables and IFNULL(, 'NaN') is useful to obtain 'NaN' in the output.

select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
left join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial =     p2.serial;

To obtain a full outer join I need to repeat the previous query with 'right join' and union the result with the 'left join'

select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
left join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial
union
select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
right join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial;

For convenience, I create a view with the previous query:

create or replace view p12 as
select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
left join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial
union
select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
right join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial;

To check we can try with:

mysql> select * from p12 where key1 = 1 and key2 =1 ;
+------+------+--------+---------------+---------------+
| key1 | key2 | serial | product_data1 | product_data2 |
+------+------+--------+---------------+---------------+
| 1    | 1    | 0      | 15.556        | 5.556         |
| 1    | 1    | 1      | 14.996        | 4.996         |
| 1    | 1    | 2      | 12.556        | NaN           |
| 1    | 1    | 3      | 15.669        | NaN           |
+------+------+--------+---------------+---------------+

So the he final query is:

select mi.key1, mi.key2, p12.serial, mi.info1, mi.info2, mi.date, p12.product_data1, p12.product_data2 
from main_info mi 
inner join p12 on mi.key1 = p12.key1 and mi.key2 = p12.key2
order by mi.key1, mi.key2, p12.serial;


+------+------+--------+-------+-------+----------+---------------+---------------+
| key1 | key2 | serial | info1 | info2 | date     | product_data1 | product_data2 |
+------+------+--------+-------+-------+----------+---------------+---------------+
| 1    | 1    | 0      | 15    | 90    | 20120501 | 15.556        | 5.556         |
| 1    | 1    | 1      | 15    | 90    | 20120501 | 14.996        | 4.996         |
| 1    | 1    | 2      | 15    | 90    | 20120501 | 12.556        | NaN           |
| 1    | 1    | 3      | 15    | 90    | 20120501 | 15.669        | NaN           |
| 1    | 2    | 0      | 14    | 92    | 20120601 | 12.556        | 2.556         |
| 1    | 2    | 1      | 14    | 92    | 20120601 | 13.335        | 3.335         |
| 1    | 2    | 2      | 14    | 92    | 20120601 | NaN           | 2.56          |
| 1    | 2    | 3      | 14    | 92    | 20120601 | NaN           | 3.556         |
| 1    | 3    | 1      | 15    | 82    | 20120801 | 12.225        | 2.225         |
| 1    | 3    | 2      | 15    | 82    | 20120801 | 13.556        | 3.556         |
| 1    | 3    | 3      | 15    | 82    | 20120801 | 14.556        | NaN           |
| 2    | 1    | 0      | 17    | 90    | 20130302 | 12.556        | NaN           |
| 2    | 1    | 1      | 17    | 90    | 20130302 | 13.553        | NaN           |
| 2    | 1    | 2      | 17    | 90    | 20130302 | 12.335        | NaN           |
| 2    | 2    | 0      | 16    | 88    | 20130601 | NaN           | 2.556         |
| 2    | 2    | 1      | 16    | 88    | 20130601 | NaN           | 3.553         |
+------+------+--------+-------+-------+----------+---------------+---------------+