Using SQL Server Windowing Function with Aggregates Function

In this post we will see how we can use SQL Server Windowing function in conjunction with Aggregate functions. In SQL Server 2005 Windowing (Over Clause) was supported with all Aggregate functions (Avg , Count, Min, Max and Sum) , in SQL Server 2012 it got further better with support of "Order By" Clause.


Let's look at some Query and their O/P..

Query

1. Lets Create a TransData Table, with following Schema


CREATE TABLE TransData (AcctId int, TransDate date, Amount decimal)
INSERT INTO TransData (AcctId, TransDate, Amount) VALUES
  (1, DATEFROMPARTS(2011, 8, 10), 500),  -- 5 transactions for acct 1
  (1, DATEFROMPARTS(2011, 8, 22), 250),
  (1, DATEFROMPARTS(2011, 8, 24), 75),
  (1, DATEFROMPARTS(2011, 8, 26), 125),
  (1, DATEFROMPARTS(2011, 8, 28), 175),
  (2, DATEFROMPARTS(2011, 8, 11), 500),  -- 8 transactions for acct 2
  (2, DATEFROMPARTS(2011, 8, 15), 50),
  (2, DATEFROMPARTS(2011, 8, 22), 5000),
  (2, DATEFROMPARTS(2011, 8, 25), 550),
  (2, DATEFROMPARTS(2011, 8, 27), 105),
  (2, DATEFROMPARTS(2011, 8, 27), 95),
  (2, DATEFROMPARTS(2011, 8, 29), 100),
  (2, DATEFROMPARTS(2011, 8, 30), 2500),
  (3, DATEFROMPARTS(2011, 8, 14), 500),  -- 4 transactions for acct 3
  (3, DATEFROMPARTS(2011, 8, 15), 600),
  (3, DATEFROMPARTS(2011, 8, 22), 25),
  (3, DATEFROMPARTS(2011, 8, 23), 125)


2. Now we write a Query which will partition data by AccountId and Order them by TransDate


SELECT AcctId, TxnDate, Amount,
  RAvg = AVG(Amount) OVER (PARTITION BY AcctId ORDER BY TxnDate),
  RCnt = COUNT(*)    OVER (PARTITION BY AcctId ORDER BY TxnDate),
  RMin = MIN(Amount) OVER (PARTITION BY AcctId ORDER BY TxnDate),
  RMax = MAX(Amount) OVER (PARTITION BY AcctId ORDER BY TxnDate),
  RSum = SUM(Amount) OVER (PARTITION BY AcctId ORDER BY TxnDate)
 FROM TxnData
 ORDER BY AcctId, TxnDate
GO

3. Here is the O/P of above Query

Delegates in C#

A method that is specified in an interface is implemented with the same name in the

base class. However, such close coupling is not always appropriate. The delegate construct

can be used to break the coupling for this purpose.

A delegate is a type that

defines a method signature. A delegate instance is then able to accept a method of that

signature, regardless of its method name or the type that encapsulates it.

The delegate syntax in C# evolved considerably from Versions 1.0 to 2.0 to 3.0. We

shall concentrate on the 3.0 version, which is the simplest to code. The language has

predefined standard generic delegate types, as follows:

        delegate R Func<R>();

        delegate R Func<A1, R>(A1 a1);

        delegate R Func<A1, A2, R>(A1 a1, A2 a2);

        // ... and up to 16 arguments

where R is the return type and the As and as represent the argument types and names,

respectively. Thus, declaring a delegate instance is now straightforward. For example,

we can define a Request delegate that takes an integer parameter and returns a string:

public Func<int, string> Request;

Next, we can assign an actual method to Request, as in:

Request = Target.Estimate;

The delegate can then be invoked just as any other method would be:

string s = Request(5);

This statement would invoke the Estimate method in the Target class, returning a

string.

Anonymous functions simplify the creation of one-time behavior for delegates. They are

useful when additional behavior is to be added before or after a method is invoked. For

example:

Request = delegate(int i) {

return "Estimate based on precision is " +

(int) Math.Round(Precise (i,3));

};

Here, the method to be invoked is Precise. The parameters are different from the ones

in the delegate, as is the return value. The anonymous function can wrapupthe

changes and assign a “complete solution” to the delegate for later invocation.

Delegates are used extensively in Windows GUI event-driven programming, where

they reflect the need to call back into the user’s code when some event happens.

Mostly, existing code of this type will use an older syntax. Also, because the new Func

delegates must have return types, void delegates must use the original syntax too. Consider

a simple example of wanting to inform one object that input has occurred in

another object (this is part of Example 4-4). We first declare a delegate visible to both

classes:

public delegate void InputEventHandler(object sender, EventArgs e, string s);

Then, in the class where the event is handled, we create an instance of the delegate and

add it to the event object of the class that will receive the event. When creating the delegate,

we indicate the method that it will call (in this case, OnInput):

   visuals.InputEvent += new InputEventHandler(OnInput);

        void OnInput(object sender, EventArgs e, string s)

        {

            // Do something

        }

The signature of OnInput must match that of InputEventHandler, which it does. Now,

in the class where event occurs, we declare the event:

       public event InputEventHandler InputEvent;

and in some method we invoke it:

       public void Input(object source, EventArgs e)

       {

           InputEvent(this, EventArgs.Empty, who);

       }

The action of invoking the InputEvent delegate causes the method currently assigned

to it (here, OnInput) to be invoked. Thus, the callback from one class to the other is

achieved.

More than one method can be associated with a delegate; when such a delegate is

invoked, all its methods are called. Thus, if another object needed to know about input

in the preceding example, it could add its own handler method on to InputEvent using

+=. Event handlers can be removed using -=

Adapter Design Pattern

The Adapter pattern helps in Integrating 2 classes which have altogether different Interfaces. It’s useful for off-the-shelf code, for toolkits, and for libraries.  Generally Toolkit or 3^rd Party controls needs a lot of adaptor classes as in many scenario it’s not possible to use Toolkit’s interfaces.

Design:

In following UML class diagram , we have a Client class and Adaptee class. Adaptee class has method “SepecificRequest” which needs to be called by Client class. Client class uses an Adaptor class which implements ITarget interface and exposes Request method. Adapter class implements Request method and it’s a child object in Client Class. So, Client doesn’t need to know anything about Adaptee class all it has to know is about ITarget interface and Request method.

Implementation:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace ConsoleApplication1

{

    // Existing way requests are implemented

    class Adaptee

    {

        // Provide full precision

        public double SpecificRequest(double a, double b)

        {

            return a / b;

        }

    }

    // Required standard for requests

    interface ITarget

    {

        // Rough estimate required

        string Request(int i);

    }

    // Implementing the required standard via Adaptee

    class Adapter : Adaptee, ITarget

    {

        public string Request(int i)

        {

            return "Rough estimate is " + (int)Math.Round(SpecificRequest(i, 3));

        }

    }

    class Client

    {

        public void CallRequest()

        {

            // Showing the Adapteee in standalone mode

            Adaptee first = new Adaptee();

            Console.Write("Before the new standard\nPrecise reading: ");

            Console.WriteLine(first.SpecificRequest(5, 3));

            // What the client really wants

            ITarget second = new Adapter();

            Console.WriteLine("\nMoving to the new standard");

            Console.WriteLine(second.Request(5));

        }

    }

    class Program

    {

        static void Main(string[] args)

        {

            Client cl = new Client();

            cl.CallRequest();

            Console.Read();

        }

    }

}

Output:

Depending on their Usage, there are following 2 types of Adapter classes

1) Pluggable Adapters : Developers who recognize that their systems will need to work with other components can increase their chances of adaptation. Identifying in advance the parts of the system that might change makes it easier to plug in adapters for a variety of new situations.

Keeping down the size of an interface also increases the opportunities for new
systems to be plugged in. Although not technically different from ordinary adapters, this feature of small interfaces gives them the name pluggable adapters.
A distinguishing feature of pluggable adapters is that the name of a method called by the client and that existing in the ITarget interface can be different. The adapter must be able to handle the name change. In the previous adapter variations, this was true for all Adaptee methods, but the client had to use the names in the ITarget interface.
Suppose the client wants to use its own names, or that there is more than one client and they have different terminologies.



2) Two-Way Adapters : Adapters provide access to some behavior in the Adaptee (the behavior required in the ITarget interface), but Adapter objects are not interchangeable with Adaptee objects. They cannot be used where Adaptee objects can because they work on the implementation of the Adaptee, not its interface. Sometimes we need to have objects
that can be transparently ITarget or Adaptee objects. 

This could be easily achieved if the Adapter inherited both interfaces; however, such multiple inheritance is not possible in C#, so we must look at other solutions.

The two-way adapter addresses the problem of two systems where the characteristics of one system have to be used in the other, and vice versa. An  Adapter class is set up to absorb the important common methods of both and to provide adaptations to both. The resulting adapter objects will be Acceptable to both sides. Theoretically, this idea can be extended to more than two systems, so we can have multiway adapters, but there are some implementation limitations: without multiple inheritance, we have to insert an interface between each original class and the adapter.