At my company we have a project where we write select SQL queries on different financial software databases to insert into a pre-designed database table we created.
An example query would be:
INSERT INTO Mut_Sample SELECT SUM(amnt) AS Mut_Amount, ledger AS Mut_Ledger, person AS Mut_Relation FROM random_financial_package GROUP BY ledger, person
However, we will do this for different users of each software database. Therefore, it could happen that user A will use the column 'amnt' in his software and lets say user B will use the column 'amnt_lcy'. This would mean the queries would not be the same for the two users.
As this is the case we want to document the queries. So in our example: How is Mut_Amount created? What field is it from? What joins/aggregations/grouping is done to get to the alias Mut_Amount which is inserted into our table.
Even better, building on the previous question: If we are building queries on lets say financial package SMPLE1 for 10 users, we want to see how many times SUM(amnt) was used to generate Mut_Amount and how many times was for example SUM(amnt_lcy) used.
Any suggestions for a workflow, documentation model or tool is welcome.