SHARE
TWEET

Rolling Sum SQL CLR Function Source

SQL_Kiwi Sep 14th, 2015 (edited) 156 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. using Microsoft.SqlServer.Server;
  2. using System;
  3. using System.Collections;
  4. using System.Collections.Generic;
  5. using System.Data;
  6. using System.Data.SqlClient;
  7. using System.Data.SqlTypes;
  8. using System.Threading;
  9.  
  10. public partial class UserDefinedFunctions
  11. {
  12.     [SqlFunction(
  13.         DataAccess = DataAccessKind.Read,
  14.         SystemDataAccess = SystemDataAccessKind.None,
  15.         FillRowMethodName = "RollingSum_FillRow",
  16.         TableDefinition = @"
  17.            ProductID integer NULL,
  18.            TransactionDate date NULL,
  19.            ReferenceOrderID integer NULL,
  20.            ActualCost money NULL,
  21.            RollingSum45 money NULL")
  22.     ]
  23.     public static IEnumerable RollingSum(
  24.         [SqlFacet(MaxSize = 128)] string InstanceName,
  25.         [SqlFacet(MaxSize = 128)] string DatabaseName)
  26.     {
  27.         // Construct connection string for loopback connection
  28.         var csb = new SqlConnectionStringBuilder();
  29.         csb.DataSource = InstanceName;
  30.         csb.InitialCatalog = DatabaseName;
  31.         csb.Enlist = false;
  32.         csb.IntegratedSecurity = true;
  33.  
  34.         using (var conn = new SqlConnection(csb.ConnectionString))
  35.         {
  36.             conn.Open();
  37.  
  38.             using (var cmd = conn.CreateCommand())
  39.             {
  40.                 // The source data
  41.                 cmd.CommandText = @"
  42.                    SELECT
  43.                        TH.ProductID,
  44.                        TH.TransactionDate,
  45.                        TH.ReferenceOrderID,
  46.                        TH.ActualCost
  47.                    FROM Production.TransactionHistory AS TH
  48.                    ORDER BY
  49.                        TH.ProductID,
  50.                        TH.TransactionDate,
  51.                        TH.ReferenceOrderID;";
  52.  
  53.                 using (var rdr = cmd.ExecuteReader(CommandBehavior.SingleResult))
  54.                 {
  55.                     // The source data row we're currently working with
  56.                     var currentRow = new RowInfo();
  57.  
  58.                     // Rows with the same product and date, waiting
  59.                     // for the running sum for that date to be finalized
  60.                     var sameDateRows = new Queue<RowInfo>(45);
  61.  
  62.                     // Up to 45 days of date & cost information
  63.                     // used to adjust the running sum when the
  64.                     // sliding window moves past rows
  65.                     var cachedCostInfo = new Queue<DateCostInfo>(45);
  66.  
  67.                     // Current product, date, and running sum
  68.                     int currentProduct;
  69.                     DateTime currentDate;
  70.                     decimal currentSum = 0m;
  71.  
  72.                     if (rdr.Read())
  73.                     {
  74.                         // Initialize from the first row
  75.                         currentRow.ProductID = rdr.GetInt32(0);
  76.                         currentRow.TransactionDate = rdr.GetDateTime(1);
  77.                         currentRow.ReferenceOrderID = rdr.GetInt32(2);
  78.                         currentRow.RollingSum45 = currentRow.ActualCost = rdr.GetDecimal(3);
  79.  
  80.                         currentProduct = currentRow.ProductID;
  81.                         currentDate = currentRow.TransactionDate;
  82.                         currentSum = currentRow.RollingSum45;
  83.  
  84.                         cachedCostInfo.Enqueue(
  85.                             new DateCostInfo
  86.                             {
  87.                                 TransactionDate = currentRow.TransactionDate,
  88.                                 ActualCost = currentRow.ActualCost
  89.                             });
  90.  
  91.                         sameDateRows.Enqueue(
  92.                             new RowInfo
  93.                             {
  94.                                 ProductID = currentProduct,
  95.                                 TransactionDate = currentRow.TransactionDate,
  96.                                 ReferenceOrderID = currentRow.ReferenceOrderID,
  97.                                 ActualCost = currentRow.ActualCost
  98.                             });
  99.  
  100.                         // Main reading loop
  101.                         while (rdr.Read())
  102.                         {
  103.                             // New current row values
  104.                             currentRow.ProductID = rdr.GetInt32(0);
  105.                             currentRow.TransactionDate = rdr.GetDateTime(1);
  106.                             currentRow.ReferenceOrderID = rdr.GetInt32(2);
  107.                             currentRow.ActualCost = rdr.GetDecimal(3);
  108.  
  109.                             // On a change of date or product
  110.                             if (currentRow.ProductID != currentProduct || currentRow.TransactionDate != currentDate)
  111.                             {
  112.                                 // Some rows that contributed to the current running sum may now be
  113.                                 // outside the 45 day frame, so we need to remove those contributions
  114.                                 while (
  115.                                     cachedCostInfo.Count > 0 &&
  116.                                     currentDate > cachedCostInfo.Peek().TransactionDate.AddDays(45))
  117.                                 {
  118.                                     // Remove the row from the queue and subtract its contribution from the running sum
  119.                                     currentSum -= cachedCostInfo.Dequeue().ActualCost;
  120.                                 }
  121.  
  122.                                 // Return finalized row(s) for the single date in the sameDateRows queue
  123.                                 while (sameDateRows.Count > 0)
  124.                                 {
  125.                                     // Dequeue and set the rolling sum
  126.                                     var sameDateRow = sameDateRows.Dequeue();
  127.                                     sameDateRow.RollingSum45 = currentSum;
  128.                                     yield return sameDateRow;
  129.                                 }
  130.  
  131.                                 // Now working with a new current date
  132.                                 currentDate = currentRow.TransactionDate;
  133.                             }
  134.  
  135.                             // Now that any finalized rows have been returned,
  136.                             // Add the current cost to the running sum
  137.                             currentSum += currentRow.ActualCost;
  138.  
  139.                             // Only for a change of product
  140.                             if (currentRow.ProductID != currentProduct)
  141.                             {
  142.                                 // Clear the cached 45-day running sum adjustment info
  143.                                 cachedCostInfo.Clear();
  144.  
  145.                                 // Set current values
  146.                                 currentProduct = currentRow.ProductID;
  147.                                 currentDate = currentRow.TransactionDate;
  148.                                 currentSum = currentRow.ActualCost;
  149.  
  150.                                 // Yield
  151.                                 Thread.Sleep(0);
  152.                             }
  153.  
  154.                             // Always save the date & cost for future running sum adjustments
  155.                             cachedCostInfo.Enqueue(
  156.                                 new DateCostInfo
  157.                                 {
  158.                                     TransactionDate = currentRow.TransactionDate,
  159.                                     ActualCost = currentRow.ActualCost
  160.                                 });
  161.  
  162.                             // Always save the current row, because the next one might
  163.                             // have the same date, and all need to have the same sum
  164.                             sameDateRows.Enqueue(
  165.                                 new RowInfo
  166.                                 {
  167.                                     ProductID = currentProduct,
  168.                                     TransactionDate = currentRow.TransactionDate,
  169.                                     ReferenceOrderID = currentRow.ReferenceOrderID,
  170.                                     ActualCost = currentRow.ActualCost
  171.                                 });
  172.                         }
  173.                     }
  174.  
  175.                     // Adjust final sum for any remaining cached row(s)
  176.                     while (
  177.                         cachedCostInfo.Count > 0 &&
  178.                         currentRow.TransactionDate > cachedCostInfo.Peek().TransactionDate.AddDays(45))
  179.                     {
  180.                         currentSum -= cachedCostInfo.Dequeue().ActualCost;
  181.                     }
  182.  
  183.                     // Return the last finalized date
  184.                     while (sameDateRows.Count > 0)
  185.                     {
  186.                         var qi = sameDateRows.Dequeue();
  187.                         qi.RollingSum45 = currentSum;
  188.                         yield return qi;
  189.                     }
  190.  
  191.                     // Tidy up
  192.                     cachedCostInfo.Clear();
  193.                     sameDateRows.Clear();
  194.                     currentRow = null;
  195.                 }
  196.             }
  197.         }
  198.     }
  199.  
  200.     private class DateCostInfo
  201.     {
  202.         public DateTime TransactionDate { get; set; }
  203.         public decimal ActualCost { get; set; }
  204.     }
  205.  
  206.     private class RowInfo
  207.     {
  208.         public int ProductID { get; set; }
  209.         public DateTime TransactionDate { get; set; }
  210.         public int ReferenceOrderID { get; set; }
  211.         public decimal ActualCost { get; set; }
  212.         public decimal RollingSum45 { get; set; }
  213.     }
  214.  
  215.     public static void RollingSum_FillRow
  216.         (
  217.             object obj,
  218.             out SqlInt32 ProductID,
  219.             out DateTime TransactionDate,
  220.             out SqlInt32 ReferenceOrderID,
  221.             out SqlMoney ActualCost,
  222.             out SqlMoney RollingSum45
  223.         )
  224.     {
  225.         // Return the row to SQL Server
  226.         var rsr = (RowInfo)obj;
  227.         ProductID = rsr.ProductID;
  228.         TransactionDate = rsr.TransactionDate;
  229.         ReferenceOrderID = rsr.ReferenceOrderID;
  230.         ActualCost = rsr.ActualCost;
  231.         RollingSum45 = rsr.RollingSum45;
  232.     }
  233. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top