Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- using Microsoft.SqlServer.Server;
- using System;
- using System.Collections;
- using System.Collections.Generic;
- using System.Data;
- using System.Data.SqlClient;
- using System.Data.SqlTypes;
- using System.Threading;
- public partial class UserDefinedFunctions
- {
- [SqlFunction(
- DataAccess = DataAccessKind.Read,
- SystemDataAccess = SystemDataAccessKind.None,
- FillRowMethodName = "RollingSum_FillRow",
- TableDefinition = @"
- ProductID integer NULL,
- TransactionDate date NULL,
- ReferenceOrderID integer NULL,
- ActualCost money NULL,
- RollingSum45 money NULL")
- ]
- public static IEnumerable RollingSum(
- [SqlFacet(MaxSize = 128)] string InstanceName,
- [SqlFacet(MaxSize = 128)] string DatabaseName)
- {
- // Construct connection string for loopback connection
- var csb = new SqlConnectionStringBuilder();
- csb.DataSource = InstanceName;
- csb.InitialCatalog = DatabaseName;
- csb.Enlist = false;
- csb.IntegratedSecurity = true;
- using (var conn = new SqlConnection(csb.ConnectionString))
- {
- conn.Open();
- using (var cmd = conn.CreateCommand())
- {
- // The source data
- cmd.CommandText = @"
- SELECT
- TH.ProductID,
- TH.TransactionDate,
- TH.ReferenceOrderID,
- TH.ActualCost
- FROM Production.TransactionHistory AS TH
- ORDER BY
- TH.ProductID,
- TH.TransactionDate,
- TH.ReferenceOrderID;";
- using (var rdr = cmd.ExecuteReader(CommandBehavior.SingleResult))
- {
- // The source data row we're currently working with
- var currentRow = new RowInfo();
- // Rows with the same product and date, waiting
- // for the running sum for that date to be finalized
- var sameDateRows = new Queue<RowInfo>(45);
- // Up to 45 days of date & cost information
- // used to adjust the running sum when the
- // sliding window moves past rows
- var cachedCostInfo = new Queue<DateCostInfo>(45);
- // Current product, date, and running sum
- int currentProduct;
- DateTime currentDate;
- decimal currentSum = 0m;
- if (rdr.Read())
- {
- // Initialize from the first row
- currentRow.ProductID = rdr.GetInt32(0);
- currentRow.TransactionDate = rdr.GetDateTime(1);
- currentRow.ReferenceOrderID = rdr.GetInt32(2);
- currentRow.RollingSum45 = currentRow.ActualCost = rdr.GetDecimal(3);
- currentProduct = currentRow.ProductID;
- currentDate = currentRow.TransactionDate;
- currentSum = currentRow.RollingSum45;
- cachedCostInfo.Enqueue(
- new DateCostInfo
- {
- TransactionDate = currentRow.TransactionDate,
- ActualCost = currentRow.ActualCost
- });
- sameDateRows.Enqueue(
- new RowInfo
- {
- ProductID = currentProduct,
- TransactionDate = currentRow.TransactionDate,
- ReferenceOrderID = currentRow.ReferenceOrderID,
- ActualCost = currentRow.ActualCost
- });
- // Main reading loop
- while (rdr.Read())
- {
- // New current row values
- currentRow.ProductID = rdr.GetInt32(0);
- currentRow.TransactionDate = rdr.GetDateTime(1);
- currentRow.ReferenceOrderID = rdr.GetInt32(2);
- currentRow.ActualCost = rdr.GetDecimal(3);
- // On a change of date or product
- if (currentRow.ProductID != currentProduct || currentRow.TransactionDate != currentDate)
- {
- // Some rows that contributed to the current running sum may now be
- // outside the 45 day frame, so we need to remove those contributions
- while (
- cachedCostInfo.Count > 0 &&
- currentDate > cachedCostInfo.Peek().TransactionDate.AddDays(45))
- {
- // Remove the row from the queue and subtract its contribution from the running sum
- currentSum -= cachedCostInfo.Dequeue().ActualCost;
- }
- // Return finalized row(s) for the single date in the sameDateRows queue
- while (sameDateRows.Count > 0)
- {
- // Dequeue and set the rolling sum
- var sameDateRow = sameDateRows.Dequeue();
- sameDateRow.RollingSum45 = currentSum;
- yield return sameDateRow;
- }
- // Now working with a new current date
- currentDate = currentRow.TransactionDate;
- }
- // Now that any finalized rows have been returned,
- // Add the current cost to the running sum
- currentSum += currentRow.ActualCost;
- // Only for a change of product
- if (currentRow.ProductID != currentProduct)
- {
- // Clear the cached 45-day running sum adjustment info
- cachedCostInfo.Clear();
- // Set current values
- currentProduct = currentRow.ProductID;
- currentDate = currentRow.TransactionDate;
- currentSum = currentRow.ActualCost;
- // Yield
- Thread.Sleep(0);
- }
- // Always save the date & cost for future running sum adjustments
- cachedCostInfo.Enqueue(
- new DateCostInfo
- {
- TransactionDate = currentRow.TransactionDate,
- ActualCost = currentRow.ActualCost
- });
- // Always save the current row, because the next one might
- // have the same date, and all need to have the same sum
- sameDateRows.Enqueue(
- new RowInfo
- {
- ProductID = currentProduct,
- TransactionDate = currentRow.TransactionDate,
- ReferenceOrderID = currentRow.ReferenceOrderID,
- ActualCost = currentRow.ActualCost
- });
- }
- }
- // Adjust final sum for any remaining cached row(s)
- while (
- cachedCostInfo.Count > 0 &&
- currentRow.TransactionDate > cachedCostInfo.Peek().TransactionDate.AddDays(45))
- {
- currentSum -= cachedCostInfo.Dequeue().ActualCost;
- }
- // Return the last finalized date
- while (sameDateRows.Count > 0)
- {
- var qi = sameDateRows.Dequeue();
- qi.RollingSum45 = currentSum;
- yield return qi;
- }
- // Tidy up
- cachedCostInfo.Clear();
- sameDateRows.Clear();
- currentRow = null;
- }
- }
- }
- }
- private class DateCostInfo
- {
- public DateTime TransactionDate { get; set; }
- public decimal ActualCost { get; set; }
- }
- private class RowInfo
- {
- public int ProductID { get; set; }
- public DateTime TransactionDate { get; set; }
- public int ReferenceOrderID { get; set; }
- public decimal ActualCost { get; set; }
- public decimal RollingSum45 { get; set; }
- }
- public static void RollingSum_FillRow
- (
- object obj,
- out SqlInt32 ProductID,
- out DateTime TransactionDate,
- out SqlInt32 ReferenceOrderID,
- out SqlMoney ActualCost,
- out SqlMoney RollingSum45
- )
- {
- // Return the row to SQL Server
- var rsr = (RowInfo)obj;
- ProductID = rsr.ProductID;
- TransactionDate = rsr.TransactionDate;
- ReferenceOrderID = rsr.ReferenceOrderID;
- ActualCost = rsr.ActualCost;
- RollingSum45 = rsr.RollingSum45;
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement