SQL-based, FIFO logic <p>I need to build a query that will aggregate the # of units of blood that has been transfused so it can be compared to the # of units of blood that has been cross-matched. Blood (a precious resource) that is cross-matched, but not transfused is wasted.</p> <p>Providers are supposed to check the system-of-record (Epic) for 'active' cross-match orders before creating new ones. Providers that don't do this are 'penalized' (provider 20). No penalty applies (it seems) for providers that don't transfuse all of the blood that they've cross-matched (provider 10).</p> <p>Cross-match orders:</p> <pre><code>|ENC_ID|PROV_ID|ORDER_ID|ORDER_TIME |UNITS| | 1| 10| 100|26-JUL-12 13:00| 4| | 1| 20| 231|26-JUL-12 15:00| 2| </code></pre> <p>Transfusion orders:</p> <pre><code>|ENC_ID|PROV_ID|ORDER_ID|ORDER_TIME |UNITS| | 1| 10| 500|26-JUL-12 13:05| 1| | 1| 10| 501|26-JUL-12 13:25| 1| | 1| 20| 501|26-JUL-12 15:00| 1| | 1| 20| 501|26-JUL-12 15:21| 2| </code></pre> <p>Rules:</p> <ul> <li>compare transfusions to cross-matches for same encounter (<code>transfusion.END_ID=cross-match.ENC_ID</code>)</li> <li>transfusion orders are applied to cross-match orders in a FIFO manner</li> <li><code>transfusion.ORDER_TIME &gt;= cross-match.ORDER_TIME</code></li> <li>a provider may transfuse more than their cross-match order, as long as all of the 'active' cross-match order still have available units (provider 20's second transfusion order)</li> </ul> <p>Desired result:</p> <pre><code>|ENC_ID|PROV_ID|ORDER_ID|ORDER_TIME |CROSS-MATCHED|TRANSFUSED| | 1| 10| 100|26-JUL-12 13:00| 4| 4| | 1| 20| 231|26-JUL-12 15:00| 2| 1| </code></pre> <p>Provider 10 'credited' with Provider 20's transfusions.</p> <p>Can this logic be implemented without resorting to a procedure?</p>