Has anyone setup Temporal Tables for PartBin or PartWarehouse tables in MSSQL 2016 or later?
This looks very interesting? How could you implement this in the ERP schema? Would it be in a different database collecting the data? Very curious.
I think it just extends the tables that are there but I’m not sure. This would definitely replace a lot of audit and control reports and BPM’s though.
1 Like
Doesnt the PartTran table kind of provide the history the Temporal tables would?
The PartBin table records hold the instantaneous value, while the PartTran holds the records of the transactions that affected the PartBin.
Although a table that simply lists all the trans of a part bin, would be nicer than having to construct it from the PartTran.
It wouldn’t show you the transactions. From my understanding of the Temporal tables it would allow you to say what were the values of the table at a specific date. For example, if you extended the SysUsrFile you could run a query like:
SELECT * FROM Ice.SysUsrFile
FOR SYSTEM_TIME
BETWEEN ‘2018-01-01 00:00:00.0000000’ AND ‘2018-01-31 00:00:00.0000000’
WHERE CompList Like ‘%MfgSys%’
For auditing you would simply you could simply put your dates in and get a report of what the table records actually were at that point in time.