0

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.

  • Hmm - sounds to me like you need a 'Visited' table that contains a row for every store 'visit' regardless of whether they 'scanned' anything or not - what would you do if the single scanned item at Target hadn't been there to be scanned? Did they even visit Target? – Scott Hodgin Nov 23 '16 at 15:04
  • That's definitely a valid concern, it's unlikely as we have quite a few products at each store, so the odds of there being none are extremely low (though anything is possible), that's something I figured I would worry about at a later date, I can have them "check in" with the app so a record appears with a time stamp....my biggest hurdle at the moment is displaying what materials are missing for each store visit :( – Neostim Nov 23 '16 at 15:18
2

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

enter image description here

  • You sir, are THE MAN! Thanks a lot! I altered the query and used it with my data and it worked perfectly, I spent more time then I would like to admit trying to figure out a way to do this, so I really appreciate it! :) – Neostim Nov 23 '16 at 16:02
  • You're welcome - glad to hear it worked for you! – Scott Hodgin Nov 24 '16 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.