I wasn't sure if this was an app question or a database question, so I'll start here first ...
Desired End State:
I'd like a process/app/database/whatever that converts daily inventory snapshots into one or two tables for easy analysis over time. Ideally, I'd like the solution to be efficient, flexible, and resource-sensitive.
What is the best way to go about this?
I'm working with a vendor who sends me daily Excel sheets with assets under management. The reports are based a snapshot of master records from the inventory system. For discussion, let's say a sample looks like this:
AssetID, Serial#, Status
123456, ABC123, Deployed
365494, D2-F39, Retired
B63489, 123GR2, Pending
The inventory is mostly static. Of the 40K rows in the spreadsheet, only 20 or so change from day-to-day. Further, once an asset is retired, its record never changes again.
Because they're complete snapshots of the master data, the spreadsheets are massive (over 40K rows, currently around 40 MB each). This makes them awkward to work with.
I have two main business requirements:
1) From today's spreadsheet, I need an easy way to identify the 20 rows that changed yesterday.
2) For a specific asset, I need to trac
View Complete Post