1

I am desperately searching for an efficient way - if there is one - to solve some kind of a recursive task in T-SQL (I could successfully model it in excel and on paper with an iterative solution - as many CMAs would for a small example, re-allocating shares of cost between pairs of support units serving each other in iterations and minimising the balancing unit's unallocated cost leftover to a reasonably small number to stop iterations/recursion).

Now I am trying to find a good scalable solution (or at least a feasible approach to it) how to achieve the same in T-SQL for this typical computational task in the managerial accounting area: when some internal support units service each other (and incur periodic costs, like salary etc) to produce at the end let's say 2 or 3 final products together as a firm, and as a result their respective shares of internally generated support overheads need to be reasonably (according to some physical base distribution, lets say - man hrs spent in each) allocated to these products' cost at the end of the costing exercise.

It would be quite simple if there was no reciprocal services: one support unit providing some service to other support units during the period (and a need to allocate respective costs too alongside this service qty flow) and the second and third support units doing the same thing to other support peers, before all their costs get properly berried into production costs and spread between respective products they jointly serviced (not equally for all support units, I'm using activity-based-costing approach here)... And in a real case there could be many more than just 2-3 units one could manually solve in excel or on paper. So, it really needs some dynamic parameters algorithm (X number of support units servicing X-1 peers and Y products in the period serviced based on some qty-measure/% square matrix allocation table) to spread their periodic cost to one unit of each product at the end. Preferably, somehow natively in SQL without using external .NET or other assembly references.

Some numeric example:

  1. each of 3 support units A,B,C incurred $100, $200, $300 of expenses in the period and worked 50 man hrs each, respectively
  2. A-unit serviced B-unit for 10 hrs and C-unit for 5 hrs, B-unit serviced A-unit for 5 hrs, C-unit serviced A-unit for 3 hrs and B-unit for 10 hrs
  3. The rest of the support units' work time (A-unit 35 hrs: 30% for P1 and 70% for P2, B-unit 45 hrs: 35% for P1 and 65% for P2, C-unit 37 hrs for P2 for 100%) they spent servicing the output of two products (P1 and P2); this portion of their direct time/effort easily allocates to products - but due to reciprocal services to each other some share of support units' cost needs to be shifted to a respective product cost pool unequal to their direct time to product allocation (needs an adjusted mix coefficient for step 2 effects).

I could solve this in excel with iterating algorithm and use of VBA arrays: (a) vector of period costs by each support unit (to finally reallocate to products and leave 0), (b) 2dim array/matrix of coefficients of self-service between support units (based on man hrs - one to another), (c) 2dim array/matrix of direct hrs service for each product by support units, (d) minimal tolerable error of $1 (leftover of unallocated cost in a unit to stop iteration) For just 2 or 3 elements (while still manually provable on paper) it is a feasible approach, but this becomes impossible to manually prove for a correct solution once I have 10-20+ support units and many products in a matrix; and I want to switch from excel and VBA to MS SQL server and t-sql for other reasons.

Since this business case as such is not new at all, I was hoping more experienced colleagues could throw an advise how to best solve this - I believed there must have been a solution to this task before (not in pure programming environment/external code). I am thinking to combine CTE(recursive), table variables and aggregate window functions - but hesitate/struggle how to best/exactly put all puzzle elements together so it is truly scalable for my potentially growing unit/product matrix dimensions. For my current level it's a little mind blowing, so I'd be grateful for an advice.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sergey T
  • 11
  • 1
  • Sample data (preferably DDL and DML statements, if not the **well** formatted tabular `text`), example results *and* your attempts will help us help you. – Thom A Nov 16 '20 at 11:32
  • Have you considered using a CLR function (https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/create-clr-functions?view=sql-server-ver15) The complexity of your requirements would be easier to implement in a language like C#. – Kevin Nov 16 '20 at 11:39
  • @Kevin: CLR function and other external assembly - this is what I was trying to avoid if any SQL integral solutions exist that could suffice – Sergey T Nov 16 '20 at 13:54
  • @SergeyT - Why not use the best tool for the job? You can easily call a CLR function from T-SQL, and I find complex code much easier to maintain outside SQL. A CLR function should also be several times faster. – Kevin Nov 16 '20 at 16:09
  • @Kevin thanks, if no-one suggests some natural SQL solution to this (with CTE recursions, table variables, windows aggregations etc), I shall consider coding it (though I hoped to avoid it after I became a believer in the growing strength of the relational maths and new features of MSSQL in its latest releases) – Sergey T Nov 16 '20 at 18:05

0 Answers0