Insert missing records from another table and update timestamp? I made an app for the sales team at my company, they visit the stores that our product is sold at and scan all the retail boxes that are physically there. Now I'm building the reporting side of things, and I'm a bit stuck. I'm running SQL Server 2008.

I've got a "Stores" table which lists all of the stores we sell to as well as each product that SHOULD be there, for example:


+---------+-------------+---------+
|  Store  |   Address   | Product |
+---------+-------------+---------+
| Walmart | 123 Fake St |  702640 |
| Walmart | 123 Fake St |  704651 |
| Walmart | 123 Fake St |  709999 |
| Target  | 456 Main St |  789562 |
| Target  | 456 Main St |  777898 |
| Target  | 456 Main St |  723456 |
+---------+-------------+---------+


I've also got a "Scanned" table which lists all of the products that our sales team has scanned with their phone while being at that physical location:


+-------------+---------+-------------+---------+
| Timestamp   |  Store  |   Address   | Product |
+-------------+---------+-------------+---------+
| 2016-10-06  | Walmart | 123 Fake St |  702640 |
| 2016-10-06  | Walmart | 123 Fake St |  704651 |
| 2016-10-06  | Target  | 456 Main St |  789562 |
+-------------+---------+-------------+---------+


The problem I'm running in to is showing what products were missing on any one visit, for example on 2016-10-06 at Target only 1 product was scanned, 789562, the other 2 from the "Stores" table (777898, 723456) are missing. 

I've tried doing a right join:

SELECT * FROM Scanned RIGHT OUTER JOIN Stores on Store, Address, Product

The problem with this is for any missing records in the Scanned table that exist in the Stores table it's returning a NULL for the Timestamp column, so I get something like this:


+------------+---------+-------------+---------+---------+-------------+---------+
| Timestamp  |  Store  |   Address   | Product |  Store  |   Address   | Product |
+------------+---------+-------------+---------+---------+-------------+---------+
| 2016-10-06 | Walmart | 123 fake St | 702640  | Walmart | 123 Fake St |  702640 |
| 2016-10-06 | Walmart | 123 Fake St | 709999  | Walmart | 123 Fake St |  704651 |
| NULL       | NULL    | NULL        | NULL    | Walmart | 123 Fake St |  709999 |
| 2016-10-06 | Target  | 456 Main St | 789562  | Target  | 456 Main St |  789562 |
| NULL       | NULL    | NULL        | NULL    | Target  | 456 Main St |  777898 |
| NULL       | NULL    | NULL        | NULL    | Target  | 456 Main St |  723456 |
+------------+---------+-------------+---------+---------+-------------+---------+


I need the Timestamp column to be able to do reporting on this data.

I'm not sure if there is a better way to go about this, I'm open to anything, I've tried looking into updating the table so the NULL timestamp will match the timestamp of the other entries (we don't visit a store more then once a day) but I didn't get very far.
Well - I dreamed up the following - now it's up to yo to try and 'break' it with your real data :)

set nocount on
declare @Stores table (Store varchar(100),Address varchar(100), Product varchar(100));
insert into @Stores values ('Walmart','123 Fake St','702640');
insert into @Stores values ('Walmart','123 Fake St','704651');
insert into @Stores values ('Walmart','123 Fake St','709999');
insert into @Stores values ('Target','456 Main St','789562');
insert into @Stores values ('Target','456 Main St','777898');
insert into @Stores values ('Target','456 Main St','723456');

declare @Scanned table (Timestamp Date,Store varchar(100), Address varchar(100), Product varchar(100));
insert into @Scanned values ('2016-10-06','Walmart','123 Fake St','702640');
insert into @Scanned values ('2016-10-06','Walmart','123 Fake St','704651');
insert into @Scanned values ('2016-10-06','Target','456 Main St','789562');

WITH StoreVisits
AS (
    SELECT DISTINCT TIMESTAMP
        ,Store
    FROM @Scanned
    )
SELECT a.TIMESTAMP
    ,b.Store
    ,b.Address
    ,b.product
    ,CASE 
        WHEN c.Product IS NULL
            THEN 'not-scanned'
        ELSE 'scanned'
        END
FROM Storevisits a
JOIN @Stores b ON b.store = a.store
LEFT JOIN @Scanned c ON c.Store = b.store
    AND c.Address = b.Address
    AND c.Product = b.Product
    AND C.TIMESTAMP = A.TIMESTAMP
WHERE c.product IS NULL


It produces the following result set