Saturday, January 21, 2017

Delphi Source Code to Convert between TColor and RGB Color in String Format

DELPHI Source Code — Function: Convert between TColor and RGB Color (as String representation)

Here is the code for a pair of useful Delphi functions for converting and moving color values between Delphi TColor (object / class) type and string representations of an RGB Color value (with or without leading # character).

If you use Delphi to output HTML code or CSS code that includes RGB color values, and you are using Delphi to edit colors or are otherwise manipulating colors using the Delphi TColor type, these routines should make it simple to convert between TColor and RGB-encoded strings.

The surrounding code shows the few USES units that were referenced. (and, this was tested through Delphi 2010)

Delphi Functions Source Code

--********************************************************************************
--This source code is Copyright (c) 2007-2017
--     Author: Mike Eberhart
--
--I hereby release this code under the terms of the MIT License (for freeware).
--
--Permission is hereby granted, free of charge, to any person obtaining a copy
--of this software and associated documentation files (the "Software"), to deal
--in the Software without restriction, including without limitation the rights
--to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
--copies of the Software, and to permit persons to whom the Software is
--furnished to do so, subject to the following conditions:
--
--The above copyright notice and this permission notice shall be included in
--all copies or substantial portions of the Software.
--
--THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
--IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
--FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
--AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
--LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
--OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
--THE SOFTWARE.
--********************************************************************************

unit ColorsUtilRGB;

interface

uses
  Windows,   //for RGB Macros
  Math,      //IfThen
  StrUtils,  //IfThen
  SysUtils,  //Format
  Classes,
  Graphics;  //for Shape type

type

  //Our RGB/Color Routines
  function GetColorASRGBString(
    const ColorToConvert : TColor; 
    const IncludePrefixChar : Boolean = True) : String;
    
  function GetRGBStringAsColor(const RGBString : String) : TColor;

implementation

 

{==============================================================================
GetColorASRGBString:

Convert a Tcolor Type to a String representation of the TColor's RGB equivalent
value in Hexadecimal digits.  If IncludePrefixChar (true by default), append
the "#" prefix to the string (showing Hex prefix).


Some TColor Notes:
  To assign a HEX value to a TColor type, cast as follows (example is for a 
constant declaration): clLightTan    = TColor($00CCEEEE);

  The HEX values represent RGB, but are in low-order byte to high-order byte
arrangement (i.e., it's really BRG reading left-to-right, so read right-to-left
for two-byte pairs of Red/Green/Blue)
===============================================================================}
function GetColorASRGBString(
  const ColorToConvert : TColor; 
  const IncludePrefixChar: Boolean): String;
var
  r,g,b         : Byte;
  CurrentColor  : TColor;
  HexColorWithSpaces : String;
const
  HexFormatStr  : String = '%2x';
begin
  CurrentColor  := ColorToConvert;

  CurrentColor  := ColorToRGB(CurrentColor);
  r := GetRValue(CurrentColor);
  g := GetGValue(CurrentColor);
  b := GetBValue(CurrentColor);

  HexColorWithSpaces := IfThen(IncludePrefixChar, '#','') 
    + Format(HexFormatStr, [r]) 
    + Format(HexFormatStr, [g]) 
    + Format(HexFormatStr, [b]);
  Result := AnsiReplaceStr(HexColorWithSpaces, ' ', '0');
end;


{==============================================================================
GetRGBStringAsColor:
This is the opposite of the prior function... Take a string representation of 
an RGB-encoded color and return a Delphi TColor equivalent.

ASSUMES inbound RGB String is EITHER:
  6 CHARACTERS LONG, NUMBERS/LETTERS ONLY!!
    OR
  7 CHARACTERS (WHERE FIRST CHAR IS "#" PREFIX)
===============================================================================}
function GetRGBStringAsColor(const RGBString : String) : TColor;
var
  RGBStringToConvert    : String[9];
  RBGStringChecked      : String;
begin
  if LeftStr(RGBString, 1) = '#' then
    RBGStringChecked := RightStr(RGBString, Length(RGBString) -1)
  else
    RBGStringChecked := RGBString;

  //Put in proper order for the StrToInt conversion 
  //(expects as B, G, R and NOT IN RGB order).
  RGBStringToConvert    := '$00' 
    + Copy(RBGStringChecked, 5, 2) 
    + Copy(RBGStringChecked, 3, 2) 
    + Copy(RBGStringChecked, 1, 2);

  Result := TColor(StrToInt(RGBStringToConvert));
end;



end.


Continue to read this Software Development and Technology Blog for computer programming, software development, and technology Techniques, How-To's, Fixes, Reviews, and News — focused on Dart Language, SQL Server, Delphi, Nvidia CUDA, VMware, TypeScript, SVG, other technology tips and how-to's, and my varied political and economic opinions.

DELPHI Source Code Function: GetAlternateColor, alternating row color, Color-Shifting algorithm

DELPHI Source Code — Function: GetAlternateColor Color-Shifting for alternating grid-row color

If you ever wanted to create a (Borland, CodeGear, Embarcadero) Delphi grid, or other control, with alternating row-colors / highlight-colors, the first thing you will need is a function with an algorithm that helps automate the determination of the alternate-row-color, based on a given TColor value, that is a rather well coordinated and appropriate color.

In the source code I provide here for one such function, this alternating row-color / highlight-color calculation is done by shifting the individual color-channels (R/G/B) based on their current values. You should be able to modify the code quite easily to your own specific requirements.

Note: To see this method put to use, read my blog entry about how to include this functionality within the Delphi DBGrids.pas source code as a modification which enables alternating grid row colors as demonstrated in these images:

Delphi Grid Control using this Alternating-Row-Color / Highlight logic ("Classic" look)

Delphi Grid Control using this Alternating-Row-Color / Highlight logic ("Aero" / modern look)
This procedure has been tested within Delphi version from Delphi 7 through Delphi 2010.

Delphi Function Source Code

--********************************************************************************
--This source code is Copyright (c) 2007-2017
--     Author: Mike Eberhart
--
--I hereby release this code under the terms of the MIT License (for freeware).
--
--Permission is hereby granted, free of charge, to any person obtaining a copy
--of this software and associated documentation files (the "Software"), to deal
--in the Software without restriction, including without limitation the rights
--to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
--copies of the Software, and to permit persons to whom the Software is
--furnished to do so, subject to the following conditions:
--
--The above copyright notice and this permission notice shall be included in
--all copies or substantial portions of the Software.
--
--THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
--IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
--FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
--AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
--LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
--OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
--THE SOFTWARE.
--********************************************************************************

{*******************************************************************************
GetAlternateColor is just a color-shifting algorithm, where if any component
of a color code (R, G, or B) gets too "close" to either 00 or FF, it is shifted
in the opposite direction.
I use this to achieve a "green-bar" effect in the DBGrid, regardless of what
background color someone has chosen for the grid.  This algorithm makes a fairly
logical guess at what the alternating-band-color should be for a given color.

See other example on this blog https://suretalent.blogspot.com 
for how this code was plugged into Borland / Embarcadero Delphi source-code 
(as tested with Delphi 2006 through 2010)
*******************************************************************************}
function GetAlternateColor(CurrentColor : TColor) : TColor;
var
  r,g,b : Byte;
const
  //The amount of "shifting" per color channel we're going to make (out of 256 max)
  ColorShiftBy = 24;  
  MinShiftThreshold = ColorShiftBy * 2;
  MaxValue = 255;
begin
  CurrentColor := ColorToRGB(CurrentColor);
  r := GetRValue(CurrentColor);
  g := GetGValue(CurrentColor);
  b := GetBValue(CurrentColor);

  //nearly "black" in color already... brighten all channels
  if (r < MinShiftThreshold) and (g < MinShiftThreshold) and (b < MinShiftThreshold) then
  begin
    r := r + ColorShiftBy;
    g := g + ColorShiftBy;
    b := b + ColorShiftBy;
    Result := RGB(r,g,b);
    exit;
  end;

  //Special case to handle "white" fields.  
  //In one of our product GUIs, white fields indicated editable fields in Grids;
  //perform a MINIMAL "shift" in color so field is almost white, but yet visibly different
  if (r = MaxValue ) and (g = MaxValue) and (b = MaxValue) then
  begin
    r := r - ColorShiftBy div 2;
    g := g - ColorShiftBy div 2;
    b := b - ColorShiftBy div 2;
    Result := RGB(r,g,b);
    exit;
  end;

  if r > MinShiftThreshold then
    r := r - ColorShiftBy
  else
    if r > 0 then
      r := r + ColorShiftBy;

  if g > MinShiftThreshold then
    g := g - ColorShiftBy
  else
    if g > 0 then
      g := g + ColorShiftBy;

  if b > MinShiftThreshold then
    b := b - ColorShiftBy
  else
    if b > 0 then
      b := b + ColorShiftBy;

  Result := RGB(r,g,b);

end; //function GetAlternateColor



{*******************************************************************************
Some useful CONSTANTS for row-color testing.
Note:
The HEX values represent RGB, but are in low-order byte to high-order byte
arrangement (i.e., it's really BRG reading left-to-right, so read right-to-left
for two-byte pairs of Red/Green/Blue).  So, use ($00BBGGRR) patterns.
*******************************************************************************}
const
  //                        BBGGRR
  clLightTan    = TColor($00CCEEEE);
  clMidTan      = TColor($00B6D4D4);
  clDarkTan     = TColor($00A0BABA);



Continue to read this Software Development and Technology Blog for computer programming, software development, and technology Techniques, How-To's, Fixes, Reviews, and News — focused on Dart Language, Delphi, SQL Server, Nvidia CUDA, VMware, TypeScript, SVG, other technology tips and how-to's, and my varied political and economic opinions.

Delphi Source Code: DBGrids.pas enhancement for automatic alternating grid row colors / highlights

DELPHI Class Enhancement: DBGrids.pas Delphi source-code modification to implement alternating grid row colors

NOTE: This Delphi DBGrids.pas source code modification makes use of my GetAlternateColor Delphi function (source code here) for alternating grid-row-colors calculations. See comments within this Delphi source code (below) for where to insert that function (search for "GetAlternateColor").

Have you ever wanted to create a (Borland, CodeGear, Embarcadero) Delphi-based DBgrid with alternating row-colors / highlight-colors, like what is shown in these images:

Delphi Grid Control using this Alternating-Row-Color / Highlight logic ("Classic" look)

Delphi Grid Control using this Alternating-Row-Color / Highlight logic ("Aero" / modern look)

A requirement for such "green-bar" effects (alternating row-colors or row-highlight-colors in a DBGrid) came up a lot for various applications I have developed, and I was honestly frustrated by the fact that Delphi did not included this functionality to begin with, especially after so many releases. With every new Delphi release... Delphi 7, Delphi 2005, Delphi 2006, Delphi 2009, and Delphi 2010, I just kept hoping for this to just be included, but it was not.

My solution was to modify the source code provided with Delphi, since the DBGrid.pas DrawCell (TCustomDBGrid.DrawCell method) provided no simple way to extend this routine. The code that follows (below) will hopefully guide you through where to "hack" the existing Delphi DBGrid source code if you choose to. Next, I simply move the modified Delphi DBGrids.pas file into the directory with the rest of my project source-code and compile it in (thus, overriding the existing outdated-looking Grid).

See the somewhat detailed comments within the source-code modifications for why I made that changes that I did. I do not just alternate the grid-row colors, I also do some other things like modifying how bookmarks work and selected-rows work and such.

Even if this does not do exactly what you want, it should provide you with enough guidance to be able to modify DBGRID.pas quite easily to meet your specific requirements.

This procedure has been tested within Delphi version from Delphi 7 through Delphi 2010.

Delphi Function Source Code

--********************************************************************************
--This source code is Copyright (c) 2007-2017
--     Author: Mike Eberhart
--
--I hereby release this code under the terms of the MIT License (for freeware).
--
--Permission is hereby granted, free of charge, to any person obtaining a copy
--of this software and associated documentation files (the "Software"), to deal
--in the Software without restriction, including without limitation the rights
--to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
--copies of the Software, and to permit persons to whom the Software is
--furnished to do so, subject to the following conditions:
--
--The above copyright notice and this permission notice shall be included in
--all copies or substantial portions of the Software.
--
--THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
--IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
--FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
--AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
--LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
--OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
--THE SOFTWARE.
--********************************************************************************

procedure TCustomDBGrid.DrawCell(ACol, ARow: Longint; ARect: TRect; AState: TGridDrawState);
...
...

//NOTE: I simply PLACED MY GetAlternateColor method here; get the source code from my other blog entry at:
//https://suretalent.blogspot.com/2017/01/delphi-source-code-function-alternating-highlight-row-color-algorithm.html


//Now, look for the following lines of code (at the start of the DrawCell method body)
var
  OldActive: Integer;
  Indicator: Integer;
  Value: string;
  DrawColumn: TColumn;
  MultiSelected: Boolean;
  ALeft: Integer;

  {*******************************************************************************
  I placed my constants here... ADDITIONS BEGIN
  *******************************************************************************}
  const
  //                        BBGGRR
  clLightTan    = TColor($00CCEEEE);
  clMidTan      = TColor($00B6D4D4);
  clDarkTan     = TColor($00A0BABA);
  {*******************************************************************************
  ADDITIONS - END
  *******************************************************************************}

begin
  if csLoading in ComponentState then
  begin
    Canvas.Brush.Color := Color;
    Canvas.FillRect(ARect);
    Exit;
  end;

  ...
  ... (about 70 lines of code here...;  look for the following...)
  ...
  
    if ARow < 0 then
      DrawTitleCell(ACol, ARow + FTitleOffset, DrawColumn, AState)
    else if (FDataLink = nil) or not FDataLink.Active then
      FillRect(ARect)
    else
    begin
      Value := '';
      OldActive := FDataLink.ActiveRecord;
      try
        FDataLink.ActiveRecord := ARow;
        if Assigned(DrawColumn.Field) then
          Value := DrawColumn.Field.DisplayText;
        if HighlightCell(ACol, ARow, Value, AState) and DefaultDrawing then
          DrawCellHighlight(ARect, AState, ACol, ARow);
        if not Enabled then
          Font.Color := clGrayText;
        if FDefaultDrawing then
          WriteText(Canvas, ARect, 3, 2, Value, DrawColumn.Alignment,
            UseRightToLeftAlignmentForField(DrawColumn.Field, DrawColumn.Alignment));
        if Columns.State = csDefault then
          DrawDataCell(ARect, DrawColumn.Field, AState);
  
//>> LOOK FOR THE PREVIOUS DELPHI DBGRID SOURCE CODE LINES ABOVE (appearning near the END of DrawCell method body)
//>> and place this provided custom code right below it...

        {*******************************************************************************
        ADDITIONS - BEGIN

        This section of code is required to accomplish a few particular GUI
        goals for the DBGrid that the standard DBGrid does not provide:
          1) Alternate colors between each row in grid to make it visually
             simple to quickly scan a row's data from left-to-right.
          2) maintain Bookmarks, even when I just want to allow ONLY one-row to
             be selected, since my applications regularly depend on Bookmarks
             to return to a particular row in the grid.
             The DBGrid normally only maintains bookmarks if dgMultiSelect
             option is True/Enabled, and in that state, DBGrid (as expected)
             allows the user to select as many rows as they want.
             My alterations make dgMultiSelect truly mean MULTI-SELECT ONLY WHEN
             the dgIndicator Option is True/Enabled at the same time
             dgMultiSelect is True/Enabled.
             When dgMultiSelect is used WITHOUT dgIndicator, only ONE ROW at
             a time (i.e., one row maximum) can be set to "selected" state.
             This allows for TRUE multiselect as well as my ONE-ROW-ONLY
             "multiselect" (where I am relying on multi-select just to set the
             bookmark on my one selected row).
          4) Highlight "selected" row(s) in a color/theme that makes the selection
             quickly apparent, whether just one row is selected or many rows are selected.
          5) Only show "Selected" rows when the grid has Focus, OR when the
             dgAlwaysShowSelection option is True (to be consistent with normal
             DBGrid behaviour).

        CODE COMMENTS:
        Make sure only ONE record selected UNLESS dgIndicator is TRUE.
        Set Current "Active" Row to "Selected" row if allowing only one row to be selected.
        Only do this if we have focus on the grid, or if AlwaysShowSelection is ON.
        For all other situations, color the alternating lines in the grid or
        any other selected-rows (if TRUE MULTI-select is in effect).
        *******************************************************************************}
        if ((DataLink.ActiveRecord = Row - 1) and ((dgAlwaysShowSelection in Options) or Focused)) or
           ((dgAlwaysShowSelection in Options) and SelectedRows.CurrentRowSelected and not (dgIndicator in Options)) then
        begin
          if (ACol = 0) then  //only need to set "Selected" once per row - do so when painting Column Zero, lest flicker ensue
          begin
            if not (dgIndicator in Options) then   //use the showing INDICATOR to mean ALLOW TRUE MULTI-SELECT!
              if SelectedRows.Count > 1 then       //Remove stragglers from MultiSelect
                SelectedRows.Clear;

            SelectedRows.CurrentRowSelected := True;
          end;

          //NOTE: for "classic" look only, replace following 3 lines with: Canvas.Brush.Color := clSelectedRow;
          Canvas.Brush.Color  := clHighlight;
          Canvas.Brush.Style  := bsClear;
          Canvas.Font.Color   := IfThen(DrawingStyle <> gdsClassic, clWindowText, clHighlightText); //set optimal text-color per draw-style
        end
        else  //Logic for coloring other rows.
        begin
          Font.Color            := clWindowText;

          //This only kicks in for the multi-selection (TRUE multiselection that is, which requires dgIndicator to be on too),
          //since above logic only highlights the SINGLE ACTIVE/SELECTED ROW (this catches other multi-select rows)
          if SelectedRows.CurrentRowSelected then
          begin
            //NOTE: for "classic" look only, replace following 3 lines with: Canvas.Brush.Color := clSelectedRow;
            Canvas.Brush.Color  := clHighlight;
            Canvas.Brush.Style  := bsClear;
            Canvas.Font.Color   := IfThen(DrawingStyle <> gdsClassic, clWindowText, clHighlightText); //set optimal text-color per draw-style
          end
          else
            if ((Columns[ACol].Field.DataSet.RecNo mod 2) =1) then  //Is it an "alternating-line" to have "green-bar paper" effect?
              Canvas.Brush.Color    := GetAlternateColor(Columns[ACol].Color)
            else
              Canvas.Brush.Color    := Columns[ACol].Color;   //TPrevent weird line-coloration if focused moved off grid after a row is selected
        end;

        DefaultDrawColumnCell( ARect, ACol, Columns[ACol], AState );  //Perform our chosen coloration
        
        {*******************************************************************************
        ADDITIONS - END
        *******************************************************************************}        

//>> LOOK FOR THE FOLLOWING DELPHI DBGRID.pas SOURCE CODE (near the END of DrawCell method body)
//>> and place provided custom code right above it
           
        DrawColumnCell(ARect, ACol, DrawColumn, AState);
      finally
        FDataLink.ActiveRecord := OldActive;
      end;
      Canvas.Brush.Style := bsSolid;
      if FDefaultDrawing and (gdSelected in AState)
        and ((dgAlwaysShowSelection in Options) or Focused)
        and not (csDesigning in ComponentState)
        and not (dgRowSelect in Options)
        and (UpdateLock = 0)
        and (ValidParentForm(Self).ActiveControl = Self) then
      begin
        if (FInternalDrawingStyle = gdsThemed) and (Win32MajorVersion >= 6) then
          InflateRect(ARect, -1, -1);
        Windows.DrawFocusRect(Handle, ARect);
      end;
    end;
  end;
  if (gdFixed in AState) and ([dgRowLines, dgColLines] * Options =
     [dgRowLines, dgColLines]) and (FInternalDrawingStyle = gdsClassic) and
     not (gdPressed in AState) then
  begin
    InflateRect(ARect, 1, 1);
    DrawEdge(Canvas.Handle, ARect, BDR_RAISEDINNER, BF_BOTTOMRIGHT);
    DrawEdge(Canvas.Handle, ARect, BDR_RAISEDINNER, BF_TOPLEFT);
  end;
end;



Continue to read this Software Development and Technology Blog for computer programming, software development, and technology Techniques, How-To's, Fixes, Reviews, and News — focused on Dart Language, Delphi, SQL Server, Nvidia CUDA, VMware, TypeScript, SVG, other technology tips and how-to's, and my varied political and economic opinions.

Friday, January 20, 2017

SQL-Server Procedure for Parameterized GROUP BY Without Using Dynamic-SQL

SQL SERVER Procedure: Parameterized GROUP BY Without Using Dynamic-SQL : the "impossible" is possible.

NOTE: This procedure and source code builds upon my previous parameterized ORDER BY without using Dynamic-SQL examples:

In this blog entry, I present a Microsoft SQL-Server query / procedure that can accomplish what many would consider truly impossible: performing a parameterized GROUP BY without using Dynamic-SQL to do it. This method builds on some other examples I have provided demonstrating parameterized ORDER BY operations (also without using Dynamic-SQL). This method of performing a GROUP BY on one or more columns, as specified by parameters/directives, is very flexible though it has the drawback of not executing as quickly as if we knew ahead of time (when writing our SQL code) what column(s) an ORDER BY / GROUP BY / aggregation was to act on.

Avoidance of Dynamic-SQL is primarily motivated by security considerations, and this procedure does not use any dynamic-SQL to perform what would otherwise be considered a "dynamic GROUP BY" operation, where the column(s) for sorting and grouping on are variable. SQL-injection exploits can be avoided completely by not using dynamic-SQL. This is particularly important for applications that provide the "public" with access to a database via web-interfaces and the like. And, very often, data-selection and sorting options are provided to the public (or are a desired application feature) that would typically make dynamic-SQL a requirement.

DISCUSSION:

Just in case (pun) you wondered how far can SQL-Server be pushed by using CASE statements to accomplish what would otherwise only be possible with Dynamic-SQL, I put together this procedural example that demonstrates how you can even perform GROUP BY (i.e., for aggregation operations) dynamically via run-time parameters, without dynamic-SQL.

Not only does this example demonstrate how, at execution time, I can force the GROUP BY operation to vary what column is used to group information by, but how I can also cause the aggregation level to happen for one column, two columns, three columns, etc.

This can be some wonderful time-saving code for management reporting where "drill-down" capabilities are required in an application, and simply by specifying additional column(s) to slice and dice our data by, we can view ever increasing detail and be quite flexible about the grouping of that data.

Although it is really an interesting experiment in regards to what is possible with non-dynamic set-based SQL using a single SQL SELECT to perform a tricky piece of logic, there are definitely some drawbacks to the approach. First, there is the need to essentially duplicate a large portion of the logic (pattern) within the SELECT result-column definitions as well as the ORDER BY and the GROUP BY column definitions, since we need to handle all possible cases without dynamic SQL.

NOTE: even though this works and works completely, it is presented as a "proof of concept" solution more than anything, due to the fact it is rather inefficient when used against large tables. The query-optimizer can not do much to help with performance when everything about the query is unknown until run-time (as each row has its table-column values evaluated).

But, now that I have presented a quick argument against this techniques use, I will consider a few of the reasons it is actually a worthwhile approach:

  • We can achieve, with a single procedure, what would otherwise take many procedures to accomplish (presuming dynamic-SQL is not an option)
  • Although the code may appear a bit lengthy, it is not terribly difficult to maintain, as changes are made to the overall "pattern" of the code within each major section — meaning, if you change the format of an output column in one CASE condition, it's a simple matter of copying and pasting that change to the other region(s) where it is used, with minor changes to the surrounding code; 
  • It is easily extended to include additional grouping levels, should that be necessary.


This procedure has been tested against the AdventureWorks sample databases in SQL-Server 2005 and SQL-Server 2008.

SQL-Server Stored Procedure (SP) Source Code

--********************************************************************************
--This source code is Copyright (c) 2007-2017
--     Author: Mike Eberhart
--
--I hereby release this code under the terms of the MIT License (for freeware).
--
--Permission is hereby granted, free of charge, to any person obtaining a copy
--of this software and associated documentation files (the "Software"), to deal
--in the Software without restriction, including without limitation the rights
--to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
--copies of the Software, and to permit persons to whom the Software is
--furnished to do so, subject to the following conditions:
--
--The above copyright notice and this permission notice shall be included in
--all copies or substantial portions of the Software.
--
--THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
--IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
--FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
--AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
--LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
--OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
--THE SOFTWARE.
--********************************************************************************

--**********************************************************************************************
-- BEGIN: NON-DYNAMIC-SQL for Dynamically/Variably-Grouped data at run-time via Parameters.
--
-- This procedure builds on the prior example(s) by adding the ability to perform aggregate
-- operations dynamically on one or more columns as directed by parameters.
--**********************************************************************************************

--**********************************************************************************************
-- Using the AdventureWorks database's Production.TransactionHistory Table for this example.
-- I will refer to the various Columns I want to (potentialy) GROUP BY as columns 1 through 3,
-- with the values being assigned as follows:
-- 1 = ProductID
-- 2 = ReferenceOrderID
-- 3 = TransactionDate
--**********************************************************************************************
CREATE PROCEDURE uspProducts_ReturnProductsDynamicallyGrouped
 @GroupColumn1  TINYINT,
 @GroupColumn2  TINYINT,
 @GroupColumn3  TINYINT
AS
BEGIN
 SELECT
  MAX
  (CONVERT(VARCHAR(80),
   COALESCE(
   CASE
    WHEN @GroupColumn1 = 1 THEN 
     'ProductID: ' + 
     dbo.[udfGetIntegerAsPaddedString](T.ProductID, 8, ' ')
    WHEN @GroupColumn1 = 2 THEN
     'ReferenceOrderID: ' + 
     dbo.[udfGetIntegerAsPaddedString](T.ReferenceOrderID, 8, ' ')
    WHEN @GroupColumn1 = 3 THEN
     'TransactionDate: ' + 
     dbo.udfConvertDateToString(T.TransactionDate)
    ELSE NULL
   END, '')
  +
   COALESCE(
   CASE
    WHEN @GroupColumn2 = 1 THEN 
     ', ProductID: ' + 
     dbo.[udfGetIntegerAsPaddedString](T.ProductID, 8, ' ')
    WHEN @GroupColumn2 = 2 THEN
     ', ReferenceOrderID: ' + 
     dbo.[udfGetIntegerAsPaddedString](T.ReferenceOrderID, 8, ' ')
    WHEN @GroupColumn2 = 3 THEN
     ', TransactionDate: ' + 
     dbo.udfConvertDateToString(T.TransactionDate)
    ELSE NULL
   END, '')
  +
   COALESCE(
   CASE
    WHEN @GroupColumn3 = 1 THEN 
     ', ProductID: ' + 
     dbo.[udfGetIntegerAsPaddedString](T.ProductID, 8, ' ')
    WHEN @GroupColumn3 = 2 THEN
     ', ReferenceOrderID: ' + 
     dbo.[udfGetIntegerAsPaddedString](T.ReferenceOrderID, 8, ' ')
    WHEN @GroupColumn3 = 3 THEN
     ', TransactionDate: ' + 
     dbo.udfConvertDateToString(T.TransactionDate)
    ELSE NULL
   END, '')
  ))     AS 'Grouping-Value',
  COUNT(1)   AS 'Count', 
  SUM(ActualCost)  AS 'Total Cost' 
 FROM 
  Production.TransactionHistory AS T
 GROUP BY 
  (
   CASE
    WHEN @GroupColumn1 = 1 THEN 
     dbo.[udfGetIntegerAsPaddedString](T.ProductID, 8, '0')
    WHEN @GroupColumn1 = 2 THEN
     dbo.[udfGetIntegerAsPaddedString](T.ReferenceOrderID, 8, '0')
    WHEN @GroupColumn1 = 3 THEN
     dbo.udfConvertDateToString(T.TransactionDate)
    ELSE NULL
   END
  ),
  (
   CASE
    WHEN @GroupColumn2 = 1 THEN 
     dbo.[udfGetIntegerAsPaddedString](T.ProductID, 8, '0')
    WHEN @GroupColumn2 = 2 THEN
     dbo.[udfGetIntegerAsPaddedString](T.ReferenceOrderID, 8, '0')
    WHEN @GroupColumn2 = 3 THEN
     dbo.udfConvertDateToString(T.TransactionDate)
    ELSE NULL
   END
  ),
  (
   CASE
    WHEN @GroupColumn3 = 1 THEN 
     dbo.[udfGetIntegerAsPaddedString](T.ProductID, 8, '0')
    WHEN @GroupColumn3 = 2 THEN
     dbo.[udfGetIntegerAsPaddedString](T.ReferenceOrderID, 8, '0')
    WHEN @GroupColumn3 = 3 THEN
     dbo.udfConvertDateToString(T.TransactionDate)
    ELSE NULL
   END
  )
 ORDER BY 
  (
   CASE
    WHEN @GroupColumn1 = 1 THEN 
     dbo.[udfGetIntegerAsPaddedString](T.ProductID, 8, '0')
    WHEN @GroupColumn1 = 2 THEN
     dbo.[udfGetIntegerAsPaddedString](T.ReferenceOrderID, 8, '0')
    WHEN @GroupColumn1 = 3 THEN
     dbo.udfConvertDateToString(T.TransactionDate)
    ELSE NULL
   END
  ),
  (
   CASE
    WHEN @GroupColumn2 = 1 THEN 
     dbo.[udfGetIntegerAsPaddedString](T.ProductID, 8, '0')
    WHEN @GroupColumn2 = 2 THEN
     dbo.[udfGetIntegerAsPaddedString](T.ReferenceOrderID, 8, '0')
    WHEN @GroupColumn2 = 3 THEN
     dbo.udfConvertDateToString(T.TransactionDate)
    ELSE NULL
   END
  ),
  (
   CASE
    WHEN @GroupColumn3 = 1 THEN 
     dbo.[udfGetIntegerAsPaddedString](T.ProductID, 8, '0')
    WHEN @GroupColumn3 = 2 THEN
     dbo.[udfGetIntegerAsPaddedString](T.ReferenceOrderID, 8, '0')
    WHEN @GroupColumn3 = 3 THEN
     dbo.udfConvertDateToString(T.TransactionDate)
    ELSE NULL
   END
  )
END --Procedure



--**********************************************************************************************
--TESTING / EXAMPLE EXECUTIONS
--**********************************************************************************************

--No grouping: get grand totals only
EXEC uspProducts_ReturnProductsDynamicallyGrouped NULL, NULL, NULL
--Grouping-Value                                              Count       Total Cost
------------------------------------------------------------- ----------- ---------------------
--                                                            113443      27307331.1537


--Group totals by TransactionDate
EXEC uspProducts_ReturnProductsDynamicallyGrouped 3, NULL, NULL
--Grouping-Value                                              Count       Total Cost
------------------------------------------------------------- ----------- ---------------------
--TransactionDate: 20030901                                   3944        1675058.5669
--TransactionDate: 20030902                                   227         39771.10
--TransactionDate: 20030903                                   203         39824.63
--...


--Group totals by TransactionDate and ProductID
EXEC uspProducts_ReturnProductsDynamicallyGrouped 3, 1, NULL
--Grouping-Value                                              Count       Total Cost
------------------------------------------------------------- ----------- ---------------------
--...
--TransactionDate: 20030901, ProductID:      996              32          2786.9806
--TransactionDate: 20030901, ProductID:      997              10          4039.1252
--TransactionDate: 20030901, ProductID:      998              26          9871.0172
--TransactionDate: 20030901, ProductID:      999              22          8553.4416
--TransactionDate: 20030902, ProductID:        3              1           0.00
--TransactionDate: 20030902, ProductID:      316              1           0.00
--...


--Group totals by ProductID and TransactionDate (and show how NULL parms do not affect outcome)
EXEC uspProducts_ReturnProductsDynamicallyGrouped 1, NULL, 3
--Grouping-Value                                              Count       Total Cost
------------------------------------------------------------- ----------- ---------------------
--...
--ProductID:      971, TransactionDate: 20040622              1           0.00
--ProductID:      971, TransactionDate: 20040626              1           1214.85
--ProductID:      971, TransactionDate: 20040629              3           2429.70
--ProductID:      971, TransactionDate: 20040702              1           0.00
--ProductID:      972, TransactionDate: 20030901              31          26240.76
--ProductID:      972, TransactionDate: 20030904              1           0.00
--...


--**********************************************************************************************
-- END: NON-DYNAMIC-SQL for Dynamically/Variably-Grouped data at run-time via Parameters.
--**********************************************************************************************


Continue to read this Software Development and Technology Blog for computer programming, software development, and technology Techniques, How-To's, Fixes, Reviews, and News — focused on Dart Language, SQL Server, Nvidia CUDA, VMware, TypeScript, SVG, other technology tips and how-to's, and my varied political and economic opinions.

Transact-SQL: Set-Based SQL Technique for Running-List of Accumulated Values (String/Number), with Break-Level Resets

MS SQL Server: Algorithm and Query for Set-Based Running Accumulators (i.e., Accumulate Row-Level Values across multiple Rows) into a String of Delimited-values, with Break-Level Resets

Have you ever wanted to perform a set-based running-subtotal operation or running-accumulation-of-values (across multiple rows) in SQL-Server — i.e., have row-level accumulators store a running total or running subtotal or aggregation of values within a "break level", stored in a String as delimited-values (i.e., delimited-list of values)? Want the solution to not use cursors?

Well, in this blog I present one such cursorless method of implementing that type of functionality in a SQL-Server Query, Stored-Procedure, or Function.

The source code provided here is for a Microsoft SQL-Server Transact-SQL (T-SQL) solution that provides a basis for a query / stored procedure (SP) that you wish to perform Set-Based Running String Accumulation with Break-Level Resets operations in. This demonstrates how to accumulate values from multiple rows in a table into a single string, and how to perform a "reset" of that accumulator at your chosen break-level.

This code is very handy for reporting requirements where you need to display on a report a single field whose value is really made up of the aggregation of values from multiple rows in a database. The example scenario used here is reporting all OrderLineIDs related to a product. The AdventureWorks database (sample) from Microsoft provided the basis and data for this example.

WHY DO THIS?

I have run into a few situations where this has been extremely useful. A typical situation involves reporting functionality, where a report is supposed to show a list of values that exist for all items in a group, but only report this information in summary at a group level. Like, e.g., you have a part number with (potentially) a lot of sub-parts / components that make up the "parent" product, and you want to generate a report showing (at the product level) information like price, cost, build-time, and a list of sub-components (in summary - like just their part numbers). Well, this query demonstrates one method for how that can be accomplished. Also, I have used this as a technique to "bind" two reporting procedures together where a master-detail report links the details as a comma-delimited list of primary-key values (and then, in the detail report, I use the power of a user-defined function to transform that delimited list of key-values into a table (link to source code here on my blog) for joining, using this user-defined delimited-list parser SQL function.

I have seen running totals done before, with varied methods, but what I have not seen much about elsewhere is how to do running subtotals. The code I wrote that appears below can easily do both, and does. It is easily adapted to do multi-column break-value running subtotals, as noted in the code comments.

Note: for this example, I used the SQL Server 2012 Adventureworks sample database, which provided me with a test case of approximately 113,000 rows against which I perform the running-subtotal (by product level) logic and do my row-level accumulator manipulations (using SET logic). This query is relatively efficient and took only just under 6 seconds to execute on my early Core-i7 Desktop development PC within a VMware Workstation virtual machine, for the entire operation (before I limited output with TOP() function.

SQL-Server Query Source Code

--********************************************************************************
--This source code is Copyright (c) 2007-2017
--     Author: Mike Eberhart
--
--I hereby release this code under the terms of the MIT License (for freeware).
--
--Permission is hereby granted, free of charge, to any person obtaining a copy
--of this software and associated documentation files (the "Software"), to deal
--in the Software without restriction, including without limitation the rights
--to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
--copies of the Software, and to permit persons to whom the Software is
--furnished to do so, subject to the following conditions:
--
--The above copyright notice and this permission notice shall be included in
--all copies or substantial portions of the Software.
--
--THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
--IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
--FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
--AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
--LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
--OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
--THE SOFTWARE.
--********************************************************************************

--**********************************************************************************************
-- BEGIN: SET-BASED Running-String-Accumulator Technique
--**********************************************************************************************
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS; -- Clears the data cache
DBCC FREEPROCCACHE    WITH NO_INFOMSGS; -- Clears the procedure cache

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @starttime DATETIME = GetDate();

--Variable to accumulate our delimited string of values-per-break-level
DECLARE @RunningSubtotalDelimString VARCHAR(MAX) = '';

--Our break-level-value variables (Data-Types match those of the columns we are comparing to)
--Initialize these to some values that will NOT exist in each break-column's actual data.
DECLARE @SubTotalBreakValue1 INT = -999; 

--Our work-table, where we can ensure the ORDER with which we later access the data, being
--by default, the order of the PRIMARY KEY declared here in UniqueID column.
DECLARE @Results TABLE
(
  UniqueID                INT IDENTITY NOT NULL PRIMARY KEY,
  SubtotalBreakColumn1    INT,
  ReferenceOrderID        INT,
  OrderLineIDsForProduct  VARCHAR(MAX)
);


--Insert all values to be totaled, into our work table in the REQUIRED BREAK-LEVEL(S) ORDER
INSERT INTO @Results(
    SubtotalBreakColumn1,
    ReferenceOrderID)
SELECT
    ProductID,
    ReferenceOrderID
FROM
    Production.TransactionHistory
ORDER BY
    ProductID,        --Insert into table in our subtotal-breaking order (IMPORTANT!)
    ReferenceOrderID  --and, if we care, sort the OrderIDs too
;

--**********************************************************************************************
-- ALGORITHM EXPLANATION:
--    See SET-BASED RUNNING SUBTOTALS Example 1, which this builds upon and can easily be
--    extended per the comments therein and/or as demonstrated in Subtotals Example 2.
--**********************************************************************************************
UPDATE
    @Results
SET
    @RunningSubtotalDelimString   = 
        OrderLineIDsForProduct    = 
            CASE 
            WHEN @SubTotalBreakValue1 = SubtotalBreakColumn1 
                THEN @RunningSubtotalDelimString + 
                CASE WHEN @RunningSubtotalDelimString <> '' THEN ',' ELSE '' END +  
                CONVERT(VARCHAR(10), ReferenceOrderID) 
            ELSE CONVERT(VARCHAR(10), ReferenceOrderID) 
            END,
    @SubTotalBreakValue1= SubtotalBreakColumn1
;


SELECT DateDiff(ms, @starttime, GetDate()); --Display elapsed Milliseconds 
--**********************************************************************************************
--Output the results, showing a few rows to demonstrate the accumulation...
--**********************************************************************************************
SELECT TOP(200) * FROM @results
ORDER BY UniqueID

--UniqueID    SubtotalBreakColumn1 ReferenceOrderID OrderLineIDsForProduct
------------- -------------------- ---------------- ---------------------------------------
--1           1                    426              426
--2           1                    505              426,505
--3           1                    588              426,505,588
--4           1                    675              426,505,588,675
--5           1                    758              426,505,588,675,758
--6           1                    841              426,505,588,675,758,841
--...
--...
--45          1                    3931             426,505,588,[...etc...],3852,3931
--46          2                    425              425
--47          2                    504              425,504
--48          2                    587              425,504,587
--49          2                    674              425,504,587,674
--...
--...


--**********************************************************************************************
--Perhaps we only want one row with the delimited list of unique values for entire break-level 
--(i.e., accumulator level, "subtotal level", aggregation level, running subtotal level)
--NOTE: Limit sample using TOP here.  Demonstrate GROUP BY with MAX to get just break-level val.
--**********************************************************************************************
SELECT TOP(10)
    SubtotalBreakColumn1,
    MAX(OrderLineIDsForProduct) AS AccumulatedOrderIDs  --The "MAX()" row has the most OrderIDs
FROM @results
GROUP BY SubtotalBreakColumn1
ORDER BY SubtotalBreakColumn1
;

--SubtotalBreakColumn1 AccumulatedOrderIDs 
---------------------- -------------------------------------------------------------------------
--1                    426,505,588,675,758,841,[...etc...]3457,3536,3615,3694,3773,3852,3931
--2                    425,504,587,674,757,836,[...etc...]3456,3535,3614,3693,3772,3851,3930

--**********************************************************************************************
-- END: SET-BASED Running-String-Accumulator Technique
--**********************************************************************************************


Continue to read this Software Development and Technology Blog for computer programming, software development, and technology Techniques, How-To's, Fixes, Reviews, and News — focused on Dart Language, SQL Server, Nvidia CUDA, VMware, TypeScript, SVG, other technology tips and how-to's, and my varied political and economic opinions.

Thursday, January 19, 2017

SQL-Server Set-Based Moving Averages with Break-Level Accumulator-Resets. No Cursors, Self-Joins, or Subselects used.

Microsoft SQL-Server Query / Procedure: Set-Based Moving Averages (without Cursors, self-joins, or sub-selects) — Example 2, with break-level average-resetting abilities

This blog builds on my prior entry where I provided the source code for a Microsoft SQL-Server Transact-SQL (T-SQL) query example (easily adapted to a stored procedure) that demonstrates an innovative way of efficiently calculating moving-averages, with variable window-widths / queue-depths, without using cursors or self-joins or any of the usual techniques.

NOTE: for a more in-depth discussion, read my prior blog which compares this approach to APPLY and OVER approaches as well. This code introduces the new feature of being able to reset the moving-average accumulator(s) value at your chosen break level(s).

This is purely a set-based method, and is very efficient — the only solution that I have found to be more efficient is the native implementation of windowing (OVER clause) functionality in Microsoft SQL-Server 2012, but that has limitations since the native approach does not support using variables to define the number of data-points (i.e., the "window width") to include in the moving-average value like my custom solution is capable of.

This example used the Microsoft AdventureWorks sample database tables and values from SQL-Server 2012 release.

SQL-Server Procedure / Query Source Code

--********************************************************************************
--This source code is Copyright (c) 2007-2017
--     Author: Mike Eberhart
--
--I hereby release this code under the terms of the MIT License (for freeware).
--
--Permission is hereby granted, free of charge, to any person obtaining a copy
--of this software and associated documentation files (the "Software"), to deal
--in the Software without restriction, including without limitation the rights
--to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
--copies of the Software, and to permit persons to whom the Software is
--furnished to do so, subject to the following conditions:
--
--The above copyright notice and this permission notice shall be included in
--all copies or substantial portions of the Software.
--
--THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
--IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
--FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
--AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
--LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
--OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
--THE SOFTWARE.
--********************************************************************************
--**********************************************************************************************
-- BEGIN: Mike's SET-BASED Moving-Average Technique #2 (includes break-level resets of averages)
-- Demonstrate how, without any self-JOIN operation, without any CURSOR, without subselects, 
-- we can still calculate a moving average, including the ability to reset that moving average
-- at particular break-values, and easily alter the number of values included in average.
--
-- Queue-Depth (i.e., "moving average values-count") size changes have nearly no impact on
-- execution speed, meaning there is essentially no performance penalty for larger moving-avg
-- "windows" than small ones -- so, a 30-day moving average calculates at nearly same speed 
-- as a 120-day moving average due to efficient algorithm.
--
-- Excellent for financial-type applications, like moving-average stock-price calculations.
-- This example even "resets" the moving-average at a break-value, and can easily be extended
-- to do so at multi-column breaks, etc (see SET-BASED RUNNING TOTALS EXAMPLE 2 for technique).
--**********************************************************************************************
DBCC DROPCLEANBUFFERS  WITH NO_INFOMSGS  -- Clears the data cache
DBCC FREEPROCCACHE     WITH NO_INFOMSGS  -- Clears the procedure cache
GO

SET NOCOUNT ON

DECLARE @starttime DATETIME
SELECT  @starttime = GetDate()

--**********************************************************************************************
-- BEGIN: SET-BASED Moving-Average Technique
-- Demonstrate how, without any self-JOIN operation, without any CURSOR, without subselects, 
-- we can still calculate a moving average, including the ability to reset that moving average
-- at particular break-values.
-- Excellent for financial-type applications, like moving-average stock-price calculations.
-- This example even "resets" the moving-average at a break-value, and can easily be extended
-- to do so at multi-column breaks, etc (see SET-BASED RUNNING TOTALS EXAMPLE 2 for technique).
--**********************************************************************************************

--Our work-table, where we can ensure the ORDER with which we later access the data, being
--by default, the order of the PRIMARY KEY declared here in UniqueID column.
DECLARE @Results TABLE
(
  UniqueID                  INT IDENTITY NOT NULL PRIMARY KEY,
  AverageResetBreakColumn1  INT,
  OrderID                   INT,
  ValueBeingAveraged        MONEY,
  TotalSaleMovingAvg        MONEY
)


--Insert all values to be totaled, into our work table in the REQUIRED ORDER by which
--we will be calculating moving-average for.  In this example, we will look at moving
--average of Order SubTotals by OrderDate within each Teritory.
INSERT INTO @Results(
  AverageResetBreakColumn1,
  OrderID,
  ValueBeingAveraged)
SELECT
  Detail.ProductID,
  Detail.SalesOrderID,
  ISNULL(Detail.LineTotal, 0) --Handle NULL values
FROM 
  Sales.SalesOrderDetail    AS Detail
ORDER BY
  Detail.ProductID,
  Detail.SalesOrderID


--Whether we call it "moving window width", or "Queue Depth", or whatever, this indicates how
--many elements are to be included in our moving average.  
--E.g., a common moving average in finance situations could be a 30 day moving average, you
--would set "depth" to 30.  For this example, keep queue small for easy validation of calcs.
DECLARE @QueueDepth  INT
SET     @QueueDepth  = 2

--Space we'll use to store each value in our queue. 
--In this example, allow for up to 9 leading digits, the decimal, and 4 trailing digits each.
DECLARE  @SingleValueCharLength  INT
SET     @SingleValueCharLength   = 14

--Variable to accumulate our delimited string of values-per-break-level used to calc moving avg.
--This is, essentially, our values-queue.  Initialize it, so always predictable fixed length.
--New values (i.e., current-row value) are prepended to this String, with oldest (in queue)
--value appearing in the rightmost position of string... i.e., add new value to front of string
--and old values fall off the "end" (right side).
--
--NOTE: SET SIZE of this var to @QueueDepth * @SingleValueCharLength, OR leave as 8000 or MAX,
--keeping in mind that MAX will add slight performance penalty.
DECLARE @MovingSubtotalValuesString VARCHAR(8000)
SET     @MovingSubtotalValuesString = REPLICATE('0', @SingleValueCharLength * @QueueDepth)


--Our break-level-value variables (Data-Types match those of the columns we are comparing to)
--Initialize these to some values that will NOT exist in each break-column's actual data.
DECLARE @AverageResetBreakVal  INT
SET     @AverageResetBreakVal  = -999 



--We will need to track the moving (or "windowed") subtotal during the update process
DECLARE  @MovingSubtotal      MONEY
SET      @MovingSubtotal      = 0

DECLARE @RowNumberThisGroup   INT
SET     @RowNumberThisGroup   = 1

SELECT DateDiff(ms, @starttime, GetDate()) --Display elapsed Milliseconds 

--**********************************************************************************************
-- ALGORITHM EXPLANATION:
--    See SET-BASED RUNNING SUBTOTALS and CONCATENTATION examples for background info, and 
--    the remainder of specific moving-average logic is described (intracode comments) below.
--**********************************************************************************************
UPDATE
    @Results
SET
  --Keep track of what row# within a break-grouping we are on
  @RowNumberThisGroup =
    CASE 
      WHEN @AverageResetBreakVal = AverageResetBreakColumn1 
        THEN @RowNumberThisGroup + 1
      ELSE 1 
    END,
  --If at break, reset moving-subtotal (first value in group is current value); otherwise we
  --add the most recent value (current row value) to be included in the subtotal, and then
  --subtract the last value falling outside of queue-depth -- this is the secret to getting 
  --the "moving subtotal window" to work, and the entire reason we need the values-queue!
  @MovingSubtotal =
    CASE 
      WHEN @AverageResetBreakVal = AverageResetBreakColumn1 
        THEN @MovingSubtotal + ValueBeingAveraged  --ADD NEW VALUE
          - CONVERT(MONEY, RIGHT(@MovingSubtotalValuesString, @SingleValueCharLength)) --POP A VALUE OFF THE QUEUE TO ADJUST SUBTOTAL DOWN BY (AS IT MOVES OUTSIDE OUR "WINDOW" OR QUEUE-DEPTH)
      ELSE ValueBeingAveraged
    END,
  --If at break, reset moving-values-list-string to contain current row value, with the rest
  --of our "queue" (this string) holding just zero/empty values.
  --Otherwise, we will be adding new value to front of the queue-string and dropping the 
  --last value from the end of the queue.
    @MovingSubtotalValuesString  = 
    CASE 
      WHEN @AverageResetBreakVal = AverageResetBreakColumn1 
        THEN
          LEFT(CONVERT(CHAR(14), ValueBeingAveraged, 2), @SingleValueCharLength) +    --ADD NEW VAL TO LEFT OF QUEUE-STRING
          LEFT(@MovingSubtotalValuesString,  @SingleValueCharLength * (@QueueDepth -1))  --DROP THE LAST RIGHTMOST VALUE
      ELSE
          CONVERT(CHAR(14), ValueBeingAveraged, 2) + REPLICATE('0', @SingleValueCharLength * (@QueueDepth -1))  --RESET AT BREAK! 
      END ,
  --If at break, reset moving-avg (first value in group is current value); otherwise if
  --less than our queue-depth into a group, moving average is our moving-subtotal divided by
  --how many rows into group we are; otherwise, simply divide moving subtotal by queue-depth
  TotalSaleMovingAvg  = 
    CASE 
      WHEN (@AverageResetBreakVal = AverageResetBreakColumn1) 
      AND  (@RowNumberThisGroup <= @QueueDepth)
        THEN @MovingSubtotal / @RowNumberThisGroup
      WHEN (@AverageResetBreakVal = AverageResetBreakColumn1) 
        THEN @MovingSubtotal / @QueueDepth
      ELSE ValueBeingAveraged  
    END,
  --And finally, keep track of whether we hit a new break-value.
    @AverageResetBreakVal= AverageResetBreakColumn1


--**********************************************************************************************
--Output the results, showing all rows to demonstrate the accumulation...
--**********************************************************************************************
SELECT * FROM @results

--**********************************************************************************************
-- END: SET-BASED Moving-Average Technique
--**********************************************************************************************
SELECT DateDiff(ms, @starttime, GetDate()) --Display elapsed Milliseconds


Continue to read this Software Development and Technology Blog for computer programming, software development, and technology Techniques, How-To's, Fixes, Reviews, and News — focused on Dart Language, SQL Server, Nvidia CUDA, VMware, TypeScript, SVG, other technology tips and how-to's, and my varied political and economic opinions.

SQL-Server Set-Based Moving Averages without Cursors, Self-Joins, or Subselects

Microsoft SQL-Server Query / Procedure: Set-Based Moving Averages (without Cursors, self-joins, or sub-selects) — Example 1, with comparison to APPLY and OVER

In this blog, I provide the source code for a Microsoft SQL-Server Transact-SQL (T-SQL) query example (easily adapted to a stored procedure) that demonstrates an innovative way of efficiently calculating moving-averages, with variable window-widths / queue-depths, without using cursors or self-joins or any of the usual techniques. This is purely a set-based method, and is very efficient — the only solution that I have found to be more efficient is the native implementation of windowing (OVER clause) functionality in Microsoft SQL-Server 2012, but that has limitations since the native approach does not support using variables to define the number of data-points (i.e., the "window width") to include in the moving-average value like my custom solution is capable of.

In addition to presenting the source code for an efficient SQL moving averages implementation, I examine and compare it to alternatives that make use of APPLY (CROSS APPLY / OUTER APPLY) as well as the new OVER / PARTITION BY features of SQL-Server 2012. Keep in mind that the CROSS APPLY and OUTER APPLY are NOT ANSI Standard SQL (it is a Microsoft extension to Transact SQL), and additionally it is no where near as efficient as my custom and fast moving-average SQL function (or the very fast SQL2012 windowing-functionality). My code will work on older versions of SQL-Server (SQL2005, SQL2008, etc) as well as newer versions.

I have included performance comparisons and execution-speed results for each of the techniques demonstrated here. Three distinct query techniques (that produce the same results) appear below.

NOTE: See my next blog for a related example that extends upon this and shows the source code for performing SQL-Server T-SQL Set-Based Moving-Averages with resets at breaks for another variation on this where I also included the ability to perform break-level resets (to the moving averages; i.e., restart the moving-average calculation at a break-level).

DISCUSSION

This is one way of implementing a solution to a rather common problem — calculating moving average — using any version of Microsoft T-SQL that lacks full ANSI SQL2003 window functions (i.e., any SQL-Server version prior to SQL 2012). If you are not familiar with windowing functions in SQL, perhaps it is because of the current limitations that Microsoft Transact-SQL imposes on Aggregate window functions using the OVER clause. The OVER clause determines the partitioning and ordering of the rowset before the associated window function is applied (and, OVER is relevant to Ranking window functions and Aggregate window functions). This is what SQL-Server 2005, 2008, and 2008r2 provides for with the OVER clause:

Ranking Window Functions
< OVER_CLAUSE > :: =
    OVER ( [ PARTITION BY value_expression, ... [ n ] ] )


Aggregate Window Functions
< OVER_CLAUSE > :: =
    OVER ( [ PARTITION BY value_expression, ... [ n ] ] )


Now, compare SQL-Server's limited Window Functions implementation to the SQL2003 standard for Window Functions, where the SQL2003 standard specifies the following rather powerful syntax for windowing functions:
FUNCTION_NAME(expr) OVER {window_name|(window_specification)}

window_specification ::= [window_name][partitioning][ordering][framing]

partitioning ::= PARTITION BY value [, value...] [COLLATE collation_name]

ordering ::= ORDER [SIBLINGS] BY rule [, rule...]

rule ::= {value|position|alias} [ASC|DESC] [NULLS {FIRST|LAST}]

framing ::= {ROWS|RANGE} {start|between} [exclusion]

start ::= {UNBOUNDED PRECEDING|unsigned-integer PRECEDING|CURRENT ROW}

between ::= BETWEEN bound AND bound
     bound ::= {start|UNBOUNDED FOLLOWING|unsigned-integer FOLLOWING}

exclusion ::= {EXCLUDE CURRENT ROW|EXCLUDE GROUP
              |EXCLUDE TIES|EXCLUDE NO OTHERS}


The window "framing" options were lacking in SQL-Server (prior to SQL-Server version 2012). So, I put together this example (and others on this blog) to demonstrate how to accomplish the same result using purely SET BASED SQL methods for achieving framing in SQL-Server without T-SQL framing being supported as a language feature yet. I did not have to use self-joins or cursors to pull this off, and the technique should extend readily if you desire.

This example used the Microsoft AdventureWorks sample database tables and values from SQL-Server 2012 release.

SQL-Server Procedure / Query Source Code

--********************************************************************************
--This source code is Copyright (c) 2007-2017
--     Author: Mike Eberhart
--
--I hereby release this code under the terms of the MIT License (for freeware).
--
--Permission is hereby granted, free of charge, to any person obtaining a copy
--of this software and associated documentation files (the "Software"), to deal
--in the Software without restriction, including without limitation the rights
--to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
--copies of the Software, and to permit persons to whom the Software is
--furnished to do so, subject to the following conditions:
--
--The above copyright notice and this permission notice shall be included in
--all copies or substantial portions of the Software.
--
--THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
--IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
--FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
--AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
--LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
--OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
--THE SOFTWARE.
--********************************************************************************
--**********************************************************************************************
-- BEGIN: Mike's SET-BASED Moving-Average Technique
-- Demonstrate how, without any self-JOIN operation, without any CURSOR, without subselects, 
-- we can still calculate a moving average, including the ability to reset that moving average
-- at particular break-values, and easily alter the number of values included in average.
--
-- Queue-Depth (i.e., "moving average values-count") size changes have nearly no impact on
-- execution speed, meaning there is essentially no performance penalty for larger moving-avg
-- "windows" than small ones -- so, a 30-day moving average calculates at nearly same speed 
-- as a 120-day moving average due to efficient algorithm.
--
-- Excellent for financial-type applications, like moving-average stock-price calculations.
-- This example even "resets" the moving-average at a break-value, and can easily be extended
-- to do so at multi-column breaks, etc (see SET-BASED RUNNING TOTALS EXAMPLE 2 for technique).
--**********************************************************************************************
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS; -- Clears the data cache
DBCC FREEPROCCACHE    WITH NO_INFOMSGS; -- Clears the procedure cache

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @starttime DATETIME;
SELECT  @starttime = GetDate();

SET NOCOUNT ON;

--==============================================================================================
--Our work-table, where we can ensure the ORDER with which we later access the data, being
--by default, the order of the PRIMARY KEY declared here in UniqueID column.
--==============================================================================================
DECLARE @Results TABLE
(
  UniqueID                 INT IDENTITY NOT NULL PRIMARY KEY,
  AverageResetBreakColumn1 INT,
  OrderDate                DATETIME,
  ValueBeingAveraged       MONEY,
  TotalSaleMovingAvg       MONEY
);

--==============================================================================================
--Insert all values to be totaled, into our work table in the REQUIRED ORDER by which
--we will be calculating moving-average for.  In this example, we will look at moving
--average of Order SubTotals by OrderDate within each Territory.
--==============================================================================================
INSERT INTO @Results(
  AverageResetBreakColumn1,
  OrderDate,
  ValueBeingAveraged)
SELECT
  Customers.TerritoryID, 
  Header.OrderDate, 
  SUM(ISNULL(Header.SubTotal, 0)) --Handle NULL values
FROM 
  Sales.SalesOrderHeader      AS Header
  JOIN Sales.SalesOrderDetail AS Detail
    ON (Detail.SalesOrderID = Header.SalesOrderID)
  JOIN Sales.Customer         AS Customers
    ON (Customers.CustomerID  = Header.CustomerID)
GROUP BY
  Customers.TerritoryID,
  Header.OrderDate
ORDER BY
  Customers.TerritoryID,
  Header.OrderDate
;

--==============================================================================================
--Whether we call it "moving window width", or "Queue Depth", or whatever, this indicates how
--many elements are to be included in our moving average.  
--E.g., a common moving average in finance situations could be a 30 day moving average, you
--would set "depth" to 30.  For this example, keep queue small for easy validation of calcs.
--==============================================================================================
DECLARE @QueueDepth  INT;
SET     @QueueDepth  = 5;

--==============================================================================================
--Space we'll use to store each value in our queue. 
--In this example, allow for up to 9 leading digits, the decimal, and 4 trailing digits each.
--==============================================================================================
DECLARE  @SingleValueCharLength  INT;
SET      @SingleValueCharLength  = 14;

--==============================================================================================
--Variable to accumulate our delimited string of values-per-break-level used to calc moving avg.
--This is, essentially, our values-queue.  Initialize it, so always predictable fixed length.
--New values (i.e., current-row value) are prepended to this String, with oldest (in queue)
--value appearing in the rightmost position of string... i.e., add new value to front of string
--and old values fall off the "end" (right side).
--
--NOTE: SET SIZE of this var to @QueueDepth * @SingleValueCharLength, OR leave as 8000 or MAX,
--keeping in mind that MAX will add slight performance penalty.
--==============================================================================================
DECLARE @MovingSubtotalValuesString VARCHAR(MAX);
SET     @MovingSubtotalValuesString = REPLICATE('0', @SingleValueCharLength * @QueueDepth);

--==============================================================================================
--Our break-level-value variables (Data-Types match those of the columns we are comparing to)
--Initialize these to some values that will NOT exist in each break-column's actual data.
--==============================================================================================
DECLARE @AverageResetBreakVal  INT;
SET     @AverageResetBreakVal  = -999;

--We will need to track the moving (or "windowed") subtotal during the update process
DECLARE  @MovingSubtotal       MONEY;
SET      @MovingSubtotal       = 0;

DECLARE @RowNumberThisGroup    INT;
SET     @RowNumberThisGroup    = 1;

--==============================================================================================
-- ALGORITHM EXPLANATION:
--    See SET-BASED RUNNING SUBTOTALS and CONCATENATION examples for background info, and 
--    the remainder of specific moving-average logic is described (intra-code comments) below.
--==============================================================================================
UPDATE
    @Results
SET
  --Keep track of what row# within a break-grouping we are on
  @RowNumberThisGroup =
    CASE 
      WHEN @AverageResetBreakVal = AverageResetBreakColumn1 
        THEN @RowNumberThisGroup + 1
      ELSE 1 
    END,
  --If at break, reset moving-subtotal (first value in group is current value); otherwise we
  --add the most recent value (current row value) to be included in the subtotal, and then
  --subtract the last value falling outside of queue-depth -- this is the secret to getting 
  --the "moving subtotal window" to work, and the entire reason we need the values-queue!
  @MovingSubtotal =
    CASE 
      WHEN @AverageResetBreakVal = AverageResetBreakColumn1 
        THEN @MovingSubtotal + ValueBeingAveraged  --ADD NEW VALUE
          - CONVERT(MONEY, RIGHT(@MovingSubtotalValuesString, @SingleValueCharLength))
      ELSE ValueBeingAveraged
    END,
  --If at break, reset moving-values-list-string to contain current row value, with the rest
  --of our "queue" (this string) holding just zero/empty values.
  --Otherwise, we will be adding new value to front of the queue-string and dropping the 
  --last value from the end of the queue.
    @MovingSubtotalValuesString  = 
    CASE 
      WHEN @AverageResetBreakVal = AverageResetBreakColumn1 
        THEN
          LEFT(CONVERT(CHAR(14), ValueBeingAveraged, 2), @SingleValueCharLength) +
          LEFT(@MovingSubtotalValuesString,  @SingleValueCharLength * (@QueueDepth -1))
      ELSE
          CONVERT(CHAR(14), ValueBeingAveraged, 2) + 
          REPLICATE('0', @SingleValueCharLength * (@QueueDepth -1))
      END ,
  --If at break, reset moving-avg (first value in group is current value); otherwise if
  --less than our queue-depth into a group, moving average is our moving-subtotal divided by
  --how many rows into group we are; otherwise, simply divide moving subtotal by queue-depth
  TotalSaleMovingAvg  = 
    CASE 
      WHEN (@AverageResetBreakVal = AverageResetBreakColumn1) 
      AND  (@RowNumberThisGroup <= @QueueDepth)
        THEN @MovingSubtotal / @RowNumberThisGroup
      WHEN (@AverageResetBreakVal = AverageResetBreakColumn1) 
        THEN @MovingSubtotal / @QueueDepth
      ELSE ValueBeingAveraged  
    END,
  --And finally, keep track of whether we hit a new break-value.
    @AverageResetBreakVal= AverageResetBreakColumn1;


--==============================================================================================
--Output the results, showing all rows to demonstrate the accumulation...
--==============================================================================================
SELECT 
  UniqueID, 
  AverageResetBreakColumn1, 
  CONVERT(VARCHAR(10), OrderDate, 111) AS 'OrderDate', 
  ValueBeingAveraged, 
  TotalSaleMovingAvg 
FROM
  @results;

--With a Queue-Depth of 2 (FOR EASY VISUAL VALIDATION OF CALCULATIONS)...
--UniqueID    AverageResetBreakColumn1 OrderDate  ValueBeingAveraged    TotalSaleMovingAvg
------------- ------------------------ ---------- --------------------- ---------------------
--1           1                        2005/07/01 999484.4736           999484.4736
--2           1                        2005/07/04 3578.27               501531.3718
--3           1                        2005/07/06 3578.27               3578.27
--4           1                        2005/07/07 699.0982              2138.6841
--5           1                        2005/07/09 3578.27               2138.6841
--6           1                        2005/07/10 3578.27               3578.27
--7           1                        2005/07/12 3578.27               3578.27
--...
--5037        9                        2008/07/25 1003.05               1067.27
--5038        9                        2008/07/26 557.79                780.42
--5039        9                        2008/07/27 545.30                551.545
--5040        9                        2008/07/28 672.90                609.10
--5041        9                        2008/07/29 149.01                410.955
--5042        9                        2008/07/30 1069.68               609.345
--5043        9                        2008/07/31 1731.23               1400.455
--5044        10                       2008/07/03 699.0982              699.0982
--5045        10                       2005/07/05 3578.27               2138.6841
--5046        10                       2005/07/06 3578.27               3578.27
--5047        10                       2005/07/07 3578.27               3578.27
--5048        10                       2005/07/09 3399.99               3489.13
--5049        10                       2005/07/11 3578.27               3489.13
--5050        10                       2005/07/12 3578.27               3578.27
--...


--With a Queue-Depth of 5 (Most easily Verified with something like a Spreadsheet)...
--UniqueID    AverageResetBreakColumn1 OrderDate  ValueBeingAveraged    TotalSaleMovingAvg
------------- ------------------------ ---------- --------------------- ---------------------
--1           1                        2005/07/01 999484.4736           999484.4736
--2           1                        2005/07/04 3578.27               501531.3718
--3           1                        2005/07/06 3578.27               335547.0045
--4           1                        2005/07/07 699.0982              251835.0279
--5           1                        2005/07/09 3578.27               202183.6763
--6           1                        2005/07/10 3578.27               3002.4356
--7           1                        2005/07/12 3578.27               3002.4356
--...
--5037        9                        2008/07/25 1003.05               1063.094
--5038        9                        2008/07/26 557.79                1051.072
--5039        9                        2008/07/27 545.30                1093.24
--5040        9                        2008/07/28 672.90                782.106
--5041        9                        2008/07/29 149.01                585.61
--5042        9                        2008/07/30 1069.68               598.936
--5043        9                        2008/07/31 1731.23               833.624
--5044        10                       2008/07/03 699.0982              699.0982
--5045        10                       2005/07/05 3578.27               2138.6841
--5046        10                       2005/07/06 3578.27               2618.546
--5047        10                       2005/07/07 3578.27               2858.477
--5048        10                       2005/07/09 3399.99               2966.7796
--5049        10                       2005/07/11 3578.27               3542.614
--5050        10                       2005/07/12 3578.27               3542.614
--...

SELECT DateDiff(ms, @starttime, GetDate()); --Display elapsed Milliseconds 
--**********************************************************************************************
-- END: SET-BASED Moving-Average Technique using Mike's approach: 
--
-- 275ms average (queue-depth makes essentially no difference in speed) : FAST!
--**********************************************************************************************
    


--**********************************************************************************************
--**********************************************************************************************
-- BEGIN: alternative SET-BASED Moving-Average Technique using "APPLY" functionality.
--
-- NOTE: (CROSS / OUTER) APPLY is NOT an ANSI Standard SQL feature.
--**********************************************************************************************
--**********************************************************************************************
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS; -- Clears the data cache
DBCC FREEPROCCACHE    WITH NO_INFOMSGS; -- Clears the procedure cache

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @starttime DATETIME;
SELECT  @starttime = GetDate();

DECLARE @QueueDepth  INT;
SET     @QueueDepth  = 5;

WITH topNValues AS
(
    SELECT * FROM (SELECT
      Customers.TerritoryID, 
      Header.OrderDate
    FROM 
      Sales.SalesOrderHeader      AS Header
      JOIN Sales.SalesOrderDetail AS Detail
        ON (Detail.SalesOrderID = Header.SalesOrderID)
      JOIN Sales.Customer         AS Customers
        ON (Customers.CustomerID  = Header.CustomerID)
    GROUP BY
        Customers.TerritoryID,
        OrderDate
) AS t
  OUTER APPLY
    (
      SELECT
        TOP(@QueueDepth) topN.ValueBeingAveraged
        FROM (
            SELECT
              Customers.TerritoryID, 
              Header.OrderDate, 
              SUM(ISNULL(Header.SubTotal, 0)) AS ValueBeingAveraged  --Handle NULL values
            FROM 
              Sales.SalesOrderHeader      AS Header
              JOIN Sales.SalesOrderDetail AS Detail
                    ON (Detail.SalesOrderID = Header.SalesOrderID)
              JOIN Sales.Customer         AS Customers
                    ON (Customers.CustomerID  = Header.CustomerID)
            GROUP BY
              Customers.TerritoryID,
              OrderDate
            ) AS topN
      WHERE 
            t.TerritoryID =  topN.TerritoryID
        AND t.OrderDate   >= topN.OrderDate
      ORDER BY
        topN.OrderDate DESC
    ) topNapply
)
SELECT
  TerritoryID, 
  OrderDate,
  AVG(ValueBeingAveraged) AS 'TotalSaleMovingAvg'
FROM 
  topNValues
GROUP BY
  TerritoryID,
  OrderDate
ORDER BY
  TerritoryID,
  OrderDate;


SELECT DateDiff(ms, @starttime, GetDate()); --Display elapsed Milliseconds 
--**********************************************************************************************
-- END: OUTER APPLY / CROSS APPLY BASED Moving-Average Technique, which produces same result 
-- but is MUCH, MUCH SLOWER compared to technique demonstrated first above!)
--
-- 19100ms average (queue-depth of 2)
-- 20500ms average (queue-depth of 20) 
-- i.e., this technique SLOWS as queue-depth increases, and OUTER / CROSS makes little difference
-- (in fact, CROSS APPLY was slightly slower than OUTER APPLY).
--**********************************************************************************************



--**********************************************************************************************
--**********************************************************************************************
-- BEGIN: alternative SET-BASED Moving-Average Technique using "OVER" (windowing) functionality
-- introduced in SQL-Server 2012.
--**********************************************************************************************
--**********************************************************************************************
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS; -- Clears the data cache
DBCC FREEPROCCACHE    WITH NO_INFOMSGS; -- Clears the procedure cache

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @starttime DATETIME;
SELECT  @starttime = GetDate();

--The OVER (windowing) function within SELECT cannot handle *variables* defining the window-width apparently?!
--Thus, instead of using these variables, we must hard-code the value into the "BETWEEN" in upcoming SELECT statement.
--DECLARE @QueueDepth  INT;
--SET     @QueueDepth  = 5;

SELECT
    TerritoryID, 
    OrderDate, 
    AVG(ValueBeingAveraged) 
OVER
    (
    PARTITION BY TerritoryID
    ORDER BY OrderDate
    ROWS BETWEEN 19 PRECEDING AND CURRENT ROW --NOTE: Queue-Depth of 5 is represented as "BETWEEN 4 PRECEDING AND CURRENT ROW" 
    )
FROM
    (SELECT
        Customers.TerritoryID, 
        Header.OrderDate, 
        SUM(ISNULL(Header.SubTotal, 0)) AS ValueBeingAveraged  --Handle NULL values
    FROM 
        Sales.SalesOrderHeader      AS Header
        JOIN Sales.SalesOrderDetail AS Detail
        ON (Detail.SalesOrderID = Header.SalesOrderID)
        JOIN Sales.Customer         AS Customers
        ON (Customers.CustomerID  = Header.CustomerID)
    GROUP BY
        Customers.TerritoryID,
        OrderDate
    ) AS topN


SELECT DateDiff(ms, @starttime, GetDate()); --Display elapsed Milliseconds 
--**********************************************************************************************
-- END: "OVER" (WINDOWING FUNCTION) BASED Moving-Average Technique, which produces same result 
-- but requires SQL-Server 2012+ and also imposes a potential issue due to the fact that the 
-- "window-width" (aka, queue-depth) for our moving-average must be HARD-CODED INTEGER VALUE!
--
-- 170ms average (queue-depth makes essentially no difference in speed) : FAST!
-- Being "native" functionality in SQL-Server 2012, it is not surprising this approach produces
-- the SPEEDIEST EXECUTION TIME, and IF Microsoft implements variable-support in the window-width
-- specifiers, this approach will win out in almost all situations. 
--
-- But, for a non-native and/or pre-SQL2012 solution, our original approach I laid out (above)
-- is remarkably close to this performance and DOES support variable-window-width / queue-depth. 
--**********************************************************************************************


Continue to read this Software Development and Technology Blog for computer programming, software development, and technology Techniques, How-To's, Fixes, Reviews, and News — focused on Dart Language, SQL Server, Nvidia CUDA, VMware, TypeScript, SVG, other technology tips and how-to's, and my varied political and economic opinions.