Just for fun, I created this ECMAScript 2015 (i.e., ES2015 or modern object-oriented JavaScript) version of a Borland / Embarcadero Delphi TList VCL Class equivalent (or, at least partial equivalent) to demonstrate one approach to Object Oriented Programming (OOP) in JavaScript, and I call the class "DList". I originally implemented this years ago in straight JavaScript, and have updated it to take advantage of ES2015 syntax and encapsulation. I have implemented some of the core TList methods like Add(), Insert(), InsertRange(), Delete(), IndexOf(), Delete, and Sort() in hopes of demonstrating the power and speed of JavaScript. This DList class wraps up the native JS array object and adds some nice extra functionality.

My DList JS/ES Class also implements my proprietary multi-column / multi-property sort algorithm that is very adaptable and flexible and should be easy to understand (see comments in source code) where I use a powers-of-two column-precedence algorithm within the sort-comparison closure callback method (in particular, see the Sorter: function(a, b) {} closure code in the source comments).

When I first wrote the original JS code, ES2015 Classes were not even an agreed standard, so I was quite excited to move on to the much simpler and much better real object oriented web-development language and framework: Google's Dart programming language. And, even though this JS/ES class makes life in JavaScript easier, simply put, Google Dart makes this type of functionality ultra-simple since their core API / framework / library includes a rich set of List / Collections classes. But, just in case you want to stick with JavaScript / ECMAScript, this should be an interesting example for you.

Live Example

In addition to the DList Object-List Class source-code, I also included the source-code I use for running various tests to confirm sorting and list-object(s) manipulation. Just call the test routine in your HTML body tag by including: onload="DListTest();"

NOTE: you definitely need a modern browser (e.g., Chrome or FireFox) with ES2015 Class support. Either one has rather fantastic built in developer tools for stepping, tracing, object-interrogation, and other features you may also want for seeing how this all works.

The Delphi VCL TMemo controls are very useful for multi-line text input, as they support embedded "hard" carriage-returns / line-feeds (CR / LF), but they also support "soft" carriage returns via the WordWrap = True property value. When Word-Wrap functionality is enabled, some tasks that should be simple become rather difficult, like inserting text (a string) into the Memo control's existing text and having that Text then show immediately as "selected" / highlighted using SelText related properties of SelStart and SelLength.

This Delphi TMemo Class-Helper adds a very handy function to the standard TMemo control that will make the task of highlighting (selecting) newly-inserted text simple, regardless of whether WordWrap is True (On) or False (Off). See the inline (in source code below) comments for more details about how this method works.

This has been tested with Delphi 2006 and 2010 and should work with any version of Delphi with the standard VCL TMemo control and the class helpers language feature. In absence of classhelpers, you can certainly still use this as a standalone function in earlier versions of Delphi (pre-Delphi 2005).

You may need to adjust the Uses clause(s), but hopefully all references to the units and/or functions you will need have been included. No matter what, the core algorithm for determining the proper SelStart within the TMemo should give you what you need to work with soft line feeds / carriage returns within a TMemo just like you would as if WordWrap was not enabled.

SQL-Server User Defined Function (UDF) 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 ClassHelpers_VCL_Example;
interface
uses
Classes,
StdCtrls;
type
procedure LockWindowUpdateEx(Handle: HWnd; SleepTicks: LongWord; Retries: LongWord);
{▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
Extend the Memo controls
{▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪}
TMemoHelper = class helper for TMemo
public
procedure SelectCharsEndingAtLineCol(const Row, Col : Integer; const NumCharsToSelect: Integer);
end;
implementation
uses
SysUtils,
StrUtils;
{■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Procedure: SelectCharsEndingAtLineCol
Parms:
Row, Col: These are the Line, Column coordinates in the Memo where your
Characters-to-Select END.
NumCharsToSelect: obvious
Note:
In a TMemo, Line and Col are 1-INDEXED variables, meaning Line = 1 when
at top or memo, and Col = 1 when at leftmost side of memo.
If WordWrap is True on the Memo, this procedure contains necessary algorithm
to "detect" and adjust for any "Soft Carriage Returns" that WordWrap is
injecting into the memo for visual display.
If only SelStart was specified in Line, Col format like everything else in
Memo-control coordinates, this custom-code would not be necessary.
Example of how this is useful:
//Let us consider wanting to programmatically insert text into a memo control and
//have that inserted-text instantly show as "selected" (seltext) upon its insertion.
//We will Insert our text (at current cursor location or to replaced existing
//selected text) via SelText; then, call this helper routine to "highlight" our
//newly inserted text.
//See the source-code comments for how we ultimately determine and set the new
//values for SelStart and SelLength to accomplish our goal.
SelText := OurTextToInsertIntoMemoContents;
SelectCharsEndingAtLineCol(self.Line, self.Column, Length(OurTextToInsertIntoMemoContents));
{■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■}
procedure TMemoHelper.SelectCharsEndingAtLineCol(const Row, Col : Integer; const NumCharsToSelect: Integer);
var
SoftCRLFsToRemove, LineToSkip, SkipChars : Integer;
slNoWrap, slWrapped : TStringlist;
WrappedIndex, NoWrapIndex, NoWrapLength, AccumWrapLength : Integer;
begin
SkipChars := 0;
SoftCRLFsToRemove := 0;
//No use processing unless these conditions are met...
if (Lines.Count > 0) and
(Row <= Lines.Count ) and
(NumCharsToSelect > 0 ) then
begin
{═══════════════════════════════════════════════════════════════════════════════
Due to the INSANITY of a Memo's "SOFT-CARRIAGE-RETURNS" (if WordWrap is True)
it is impossible to calculate SelStart without doing some really whacky
processing. In particular, we create a "duplicate" of the Memo-Lines with the
lines of text up to Cursor-Position, and then count the difference between
the "lines" that exist pre/post WordWrap -- will need to later subtract off
the difference in lines (times 2 - one each for CR, LF chars).
{═══════════════════════════════════════════════════════════════════════════════}
if (WordWrap = True) and
(Row > 1) then //if row to insert/select on is first row, no "adjustment" will be needed...
begin
slNoWrap := TStringlist.Create;
slWrapped := TStringlist.Create;
try
slWrapped.Assign(Lines);
Lines.BeginUpdate;
LockWindowUpdateEx(Handle, 20, 5);
WordWrap := False;
slNoWrap.Assign(Lines);
//ShowMessage(IntToStr(slNoWrap.Count) + ' ' + IntToStr(slWrapped.Count));
WrappedIndex := 0;
for NoWrapIndex := 0 to slNoWrap.Count - 1 do
begin
NoWrapLength := Length(slNoWrap.Strings[NoWrapIndex]);
if Length(slWrapped.Strings[WrappedIndex]) < NoWrapLength then
begin
AccumWrapLength := 0;
While (AccumWrapLength < NoWrapLength) do
begin
AccumWrapLength := AccumWrapLength + Length(slWrapped.Strings[WrappedIndex]);
Inc(WrappedIndex);
//if we have gone as far as the Row (Line) in which the cursor was
//positioned while wordwrap was on, then time to break out of here...
if WrappedIndex = Row then
begin
AccumWrapLength := NoWrapLength + 1; //to break out of while... (and, indicate to break the "for"
break; //break out of While...
end
else
if AccumWrapLength < NoWrapLength then
Inc(SoftCRLFsToRemove);
end; //while
if AccumWrapLength = NoWrapLength + 1 then
break; //break FOR loop if Row target met.
end
else
begin
Inc(WrappedIndex);
if WrappedIndex = Row then
break; //break out of For...
end;
end; //for NoWrapIndex
finally
slNoWrap.Free;
slWrapped.Free;
LockWindowUpdateEx(0, 20, 5);
WordWrap := True;
Lines.EndUpdate;
Application.ProcessMessages;
end;
end; //if WordWrap was on and adjustment calc needed...
//Count characters in line(s) up to, but not including the line on which
//our selected text is on.
for LineToSkip := 0 to Row - 2 do
SkipChars := SkipChars + Length(Lines[LineToSkip]) + 2; //the "+2" adjusts for CR/LF not otherwise counted
//Now move over appropriate number of columns, less length of string we'll select
SkipChars := SkipChars + Col - 1 - NumCharsToSelect - (SoftCRLFsToRemove * 2);
//Now, "select" the appropriate region in memo
SelStart := SkipChars;
SelLength := NumCharsToSelect;
end; //if
end; //SelectCharsEndingAtLineCol
{▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
Prevent repainting of window/control during heavy manipulation of visual items
- especially useful with visual "lists" (e.g., memos, treeviews, shelltrees, etc).
{▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪}
procedure LockWindowUpdateEx(Handle: HWnd; SleepTicks, Retries: LongWord);
var
CurrentRetry : LongWord;
begin
CurrentRetry := 0;
If Handle = 0
then LockWindowUpdate(Handle)
else
While (CurrentRetry <= Retries) and not LockWindowUpdate(Handle) do
begin
Inc(CurrentRetry);
Sleep(SleepTicks);
end;
end; //LockWindowUpdateEx
end.

Saturday, January 21, 2017

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)

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.

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;

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.

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
--**********************************************************************************************

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

About Me

I have spent much of my life working with computers, technology, programming, software development, and business consulting. I have a strong attachment to technology and science in general.

My interests and insight go well beyond computers though, as you will soon discover while you peruse my blog entries covering topics from finance to gluten free diets. I have even recently authored a Gluten-Free Dessert Recipes Cookbook of gourmet quality desserts for Celiacs and Wheat-Free / Gluten-Free individuals.

Note: This site is not intended for use as a source of technology, health, legal, or professional advice, and I assume no responsibility for errors, inaccuracies, omissions, or any thing else resulting from you reading this blog. All content is copyrighted with all rights reserved.