Static Variables, Static Methods and Objects in C#

Each object has its own set of member variables and all the member variables have a scope.

If we want a variable to have the same value throughout all instances of the object then we can declare it as a static variable in our program. To manipulate and use the values of static variables we can also define a function as static.

The keyword "static" means that only one instance of a given variable exists for a class. Static variables are used to define constants because their values can be retrieved by invoking the class without creating an instance of it.

Static variables can be initialized outside the member function or class definition. The following code is an example of a static variable, which shows the declaration and initialization of a static variable.

using System;

namespace staticexample

{

    class Program

    {

        public static int i;

        public static void display()

        {

            i=10;

            Console.WriteLine(i);

        }

        public void demo()

        {

            int j=20;

            Console.WriteLine(j);

        }

 

        static void Main(string[] args)

        {

            Program obj = new Program();

            Program.display();

            obj.demo();

            Console.Read();

        }

    }

}

Here we have not used an instance of the class for the static method display(). However, for the nonstatic demo() method we need the instance of the class to call it. This is how to call static and non-static methods.

Note

Unlike other member variables, only one copy of the static variable exists in memory for all the objects of that class. Therefore, all objects share one copy of the static variable in memory.
Objects

An object is an instance of a class. We create objects to access member variables and member functions of a class. To use the member of a class, we need to create an object of the class. Objects interact with each other by passing messages and by responding to the received messages. In C# , the task of passing messages can be done using member functions.

class Program

{

    static int x, y;

    void display()

    {

        int result = x + y;

        Console.WriteLine("the result is " + result);

    }

   static void Main(string[] args)

   {

        Console.WriteLine("enter the numbers");

        x=Convert.ToInt16(Console.ReadLine());

        y=Convert.ToInt16(Console.ReadLine());

        Program obj=new Program();

        obj.display();
        Console.ReadLine();
 }

In the code above we have declared a class named "Program". To create an object of the class named "obj", the following code is given in the Main() method:Program obj=new Program();

The member function of the class is accessed through an object of the class using the "." operator, as shown in the following example:obj.display();

The code above is used to access the member function display(). This means that the object obj of the class Program has invoked the function display(). This function calculates the result and displays it on the console.

Lead and Lag Functions in SQL Server 2012

Introduction

Have you ever been in a situation where you needed to write a query that needed to do comparisons or access data from the subsequent rows along with the data from the current row? This article discusses different ways to write these types of queries and more specifically examines LEAD and LAG analytics functions, which were introduced with SQL Server 2012, and helps you understand how leveraging these functions can aid you in such situations.
Accessing Prior or Subsequent Rows
SQL Server 2012 introduced LAG and LEAD functions for accessing prior or subsequent rows along with the current row but before we go into the details of these functions, let me explain how you can write these queries in earlier versions of SQL Server.
Let’s first create a table and load some sample data with the script below. This table contains customer information along with when a specific plan for the customer was started, assuming when a new plan starts, the older one gets ended automatically.

DECLARE   @CustomerPlan TABLE
  (
       CustomerCode VARCHAR(10),
         PlanCode   VARCHAR(10),
       StartDate  DATE
    )
INSERT INTO @CustomerPlan VALUES ('C00001', 'P00001', '1-Sep-2014')
INSERT INTO @CustomerPlan VALUES ('C00001', 'P00002', '1-Oct-2014')
INSERT INTO @CustomerPlan VALUES ('C00001', 'P00003', '10-Oct-2014')
INSERT INTO @CustomerPlan VALUES ('C00001', 'P00004', '25-Oct-2014')
INSERT INTO @CustomerPlan VALUES ('C00002', 'P00001', '1-Oct-2014')
INSERT INTO @CustomerPlan VALUES ('C00002', 'P00002', '1-Nov-2014')
 
SELECT * FROM   @CustomerPlan;

You can use Common Table Expression (CTE) along with the ROW_NUMBER ranking function to access subsequent rows in the same result set. For example, for a given customer I want to know the expiration date for the current plan based on the activation date of the next plan. Basically, when a new plan is started the previous plan is automatically ended and hence the end date for a previous plan is the start date minus one day of the next plan:

WITH   CTE as 
(
        SELECT RN = ROW_NUMBER() OVER (PARTITION BY   CustomerCode ORDER BY   StartDate ASC),   * 
        FROM @CustomerPlan
)
SELECT
        [Current   Row].*, 
        ISNULL(DATEADD(DAY, -1, [Next Row].StartDate), '31-Dec-2099') AS EndDate
FROM   CTE [Current Row]
        LEFT JOIN CTE [Next   Row] ON [Current Row].CustomerCode   = [Next Row].CustomerCode   AND  [Next Row].RN   = [Current Row].RN   + 1
ORDER BY [Current Row].CustomerCode, [Current Row].RN;

In the image above, you can see the plan P00002 of customer C00001 starts on 1^st October 2014 and hence the end date for plan P00001 is the 30^th September 2014 (1^st October 2014 minus one day), likewise P00003 starts on 10^th October and hence the end date for P00002 ends on 9^th October 2014.

LEAD Function in SQL Server 2012

Though it was very much possible to write queries as shown above using CTE and ranking function to access prior or subsequent rows in earlier versions of SQL Server, SQL Server 2012 has simplified it further.
SQL Server 2012 introduced LEAD analytic function to access the subsequent row (or columns from the subsequent row) without using self-join or CTE or ranking function.
There are three main parameters for the LEAD function along with the OVER clause, which works in the same way as it works with ranking functions:

• With the first parameter, you can specify a scalar expression or column name whose value from the subsequent row is to be returned.
• With the second parameter, you can specify an offset to access not only the next immediate row but any row after the current row. For example, its default value of 1 accesses the next immediate row whereas a value of 3 accesses the third row from the current row.
• With the third parameter, you can specify the default value to be returned in case the returned value is NULL.

The script below is a re-write of the above script (which uses CTE and ranking function) using the LEAD function, which gives the same result as above, but as you can see this query is more simplified and does not uses self-join.

SELECT *, 
DATEADD(DAY, -1, LEAD(StartDate, 1,'01-Jan-2100') 
        OVER (PARTITION BY   CustomerCode ORDER BY   StartDate ASC))   AS EndDate
FROM   @CustomerPlan

LAG Function in SQL Server 2012

Like the LEAD function, LAG is another analytic function introduced in SQL Server 2012 that has the same three parameters as the LEAD function along with the OVER clause, which works in same way as it works with ranking functions. But unlike the LEAD function, which allows accessing subsequent rows, the LAG function allows accessing previous rows from the same result set, again without using self-join. With the second offset parameter, you can specify the physical offset that comes before the current row.
If you look at the structure of the table we created above, each row contains a customer’s current plan. With the help of the LAG function, we will find out the previous (plan from the last row) as shown in the script below:

SELECT   CustomerCode, PlanCode AS CurrentPlanCode,
LAG(PlanCode, 1, 'NA') 
        OVER (PARTITION BY   CustomerCode ORDER BY   StartDate ASC)   AS LastPlan
FROM   @CustomerPlan;

If you notice in the figure below, the second row contains the P00002 as the current plan and P00001 as the last plan (coming the immediate previous row); likewise the third row contains P00003 as the current plan and P00002 as the last plan (again coming from the immediate previous row).

LEAD and LAG functions are flexible to let you specify the number of rows to move backward or forward from the current row with the offset parameter. For example, as you can see in the script below I have specified 2 as offset with the LAG function and hence for the third row where the current plan is P00003, the last plan is P00001 (as it goes two rows back to pick up the value).

SELECT   CustomerCode, PlanCode AS CurrentPlanCode,
LAG(PlanCode, 2, 'NA') 
        OVER (PARTITION BY   CustomerCode ORDER BY   StartDate ASC)   AS LastPlan
FROM   @CustomerPlan;

Conclusion

In this article I discussed how you can use CTE and ranking function to access or query data from previous or subsequent rows. Then I also talked about how to leverage LEAD and LAG analytics functions, introduced with SQL Server 2012, to achieve the same without writing  self-join query using CTE and ranking function.

MERGE Statement to Perform an UPSERT in sql

What is the Value of the MERGE Statement?

Prior to the introduction of SQL Server 2008 if you needed to write logic that inserted rows into a target table if they didn’t exist, or updated them if they did exist you needed a series of “if then else” logic and needed to perform both the UPDATE and INSERT statement.  With the introduction of the MERGE statement with SQL Server 2008 you can perform either an INSERT or UPDATE statement using a single MERGE statement.   Being able to use the MERGE statement to perform inserts or updates to a table makes it easier to code your UPSERT logic in TSQL.

Performing UPSERT Using MERGE Statement

The MERGE statement supports inserting and updating rows in a table with a single operation.  In order to accomplish this the MERGE statement requires both a Source and Target table.  The Source table is used to identify the rows that needed be inserted or update, and the Target table is the table that rows will be inserted or updated.
In order to demo the MERGE statement I will need a Source and Target table, which will be used in my MERGE example.   I will use the following code to create my Source and Target table:

USE tempdb;
GO
SET NOCOUNT ON;
CREATE TABLE dbo.Product (
       Id int identity, 
       ProductName varchar(100),
       Qty int);
INSERT INTO dbo.Product (ProductName, Qty) VALUES('Magic Stripper', 5);
CREATE TABLE dbo.NewInventory(
       Id int identity, 
       ProductName varchar(100), 
       Qty int);
INSERT INTO dbo.NewInventory (ProductName, Qty) VALUES
       ('Sandpaper',20), 
       ('Paint Brush 1 inch',15),
       ('Magic Stripper',5);   

With this code I created two tables.   The first table I created was the Product table, which will be my Target table.  This is the table that I will be updating or inserting rows using the MERGE statement.  The other table, NewInventory, is the Source table.  My Source table identifies the records that will be used to determine if a new record needs to be inserted into my Product table.  The Source table is also used to determine if an UPDATE needs to be performed against any existing records in my Product table.  These two tables will be used in the following MERGE statement, which performs an UPSERT operation:

USE tempdb;
GO
MERGE dbo.Product AS T  
USING dbo.NewInventory AS S 
ON T.ProductName = S.ProductName
WHEN MATCHED THEN  
  UPDATE SET T.Qty = T.Qty + S.Qty
WHEN NOT MATCHED THEN  
  INSERT (ProductName,Qty) VALUES (S.ProductName,S.Qty);
SELECT * FROM dbo.Product;
 

When I run this code I get the following output:

Id          ProductName                                                     Qty
----------- --------------------------------------------------------------- -----------
1           Magic Stripper                                                  10
2           Sandpaper                                                       20
3           Paint Brush 1 inch                                              15
 

Let me review the above code and explain how it worked.  The table identified right after the MERGE statement, the one with the table alias of T is known as the Target.  The Target table is the table in which I will be performing an UPDATE, or an INSERT.  The table following the USING clause, with the table alias of S is known as the Source table.  The Source table identifies the potential records that will be inserted or updated in the Target table.  The ON clause identifies how to join the Target and Source tables.  In my example above I’m joining the Source and Target table based on the ProductName column.  Following the ON clause are two WHEN clauses.  These clauses identify the conditions when a UPDATE or an INSERT will be performed based on the results of the join between the Target and Source tables.
The first WHEN clause says “WHEN MATCHED”.  The “WHEN MATCHED” conditions means when the Target and Source tables are joined based on the “ON” clause if there is a match then the UPDATE statement will be performed.  In the UPDATE statement I take the existing Qty value in the Target table and add to it the Qty value in the Source table to increase the amount of inventory I have on hand for a given ProductName.
The second WHEN clause has “WHEN NOT MATCHED”.  The “WHEN NOT MATCH” condition means when joining the Target and Source if there is a ProductName in the Source table that is not found in the Target table then this condition will be met.  When this condition is met based on the Source and Target table join operation the Source row will be inserted into the Product table.  This condition allows me to insert a new row into the Product table when new ProductName’s are found in the Source table.
If you review the original rows I had in my Product table you will see that I only had the “Magic Stripper” product in my Product table and that product had a Qty of 5.  After the MERGE statement ran, the “Magic Stripper” product now has a Qty of 10.  This happened because the MERGE statement’s WHEN MATCH condition was met and therefore the Qty value from the Product table, which was 5 and the Qty value from the New Inventory, which was also 5 were summed together to UPDATE the matched row in the Target table. The other two Products “Sandpaper” and “Paint Brush 1 inch” where inserted into the Product table because these two products didn’t already exist in the Product table so they met the “WHEN NOT MATCHED” condition. When the “WHEN NOT MATCHED” condition is met the unmatched rows in the Source table were inserted into the Target table.

What about Deleting Rows Using the Merge Statement?

Can the MERGE statement be used to delete records from a table?  Yes it can!   To see how this works let’s suppose we have a business requirement to delete rows from the Target table of the MERGE statement when they don’t exist in the Source table.  In order to show how this works I’m going to using my exiting Product table, which now after running the prior examples has records for the following three products:

• Magic Stripper
• Sandpaper
• Paint Brush 1 Inch

But this time I’m going to use the NewInventory2 table that is created with the following TSQL code.  This new table will be the Source table for the MERGE statement:

USE tempdb;
GO
CREATE TABLE dbo.NewInventory2(
       Id int identity, 
       ProductName varchar(100), 
       Qty int);
INSERT INTO dbo.NewInventory2 (ProductName, Qty) VALUES
       ('Sandpaper',5), 
       ('Paint Brush 1 inch',10);
 

To use the MERGE statement to perform a delete from my Product table I will use the following code:

USE tempdb;
GO
MERGE dbo.Product AS T  
USING dbo.NewInventory2 AS S
ON T.ProductName = S.ProductName 
WHEN NOT MATCHED BY SOURCE THEN  
  DELETE;
SELECT * FROM dbo.Product;

When I run this code I get the following output:

Id          ProductName                                                      Qty
----------- ---------------------------------------------------------------- -----------
2           Sandpaper                                                        20
3           Paint Brush 1 inch                                               15

By reviewing the result set above you can see that the “Magic Stripper” product got deleted from the Product table.   This was accomplished by using the “WHEN NOT MATCHED BY SOURCE” condition of the above MERGE statement.  This condition is met when there is no row in the Source table that matches a Target table row.  When this occurs SQL Server deletes rows in the Target table that don’t have a corresponding matching row in the Source table.   Note you can include the WHEN NOT MATCHED BY SOURCE, WHEN MATCHED and WHEN NOT MATCHED is the same MERGE statement.

Beware of Filtering Rows Using ON Clause

If you read Books Online (BOL) there is a cautionary note on Filtering Rows by associating additional constraints with the ON clause of the MERGE statement.  Here is the actual text regarding trying to perform additional filtering using the ON clause that was found in the BOL:
It is important to specify only the columns from the target table that are used for matching purposes. That is, specify columns from the target table that are compared to the corresponding column of the source table. Do not attempt to improve query performance by filtering out rows in the target table in the ON clause, such as by specifying AND NOT target_table.column_x = value. Doing so may return unexpected and incorrect results.
To show you an example of how adding additional filtering using the ON clause of a MERGE statement can cause unexpected results. I’m going to first run this code to recreate my Target and Source tables:

USE tempdb;
GO
DROP table Product, NewInventory
SET NOCOUNT ON;
CREATE TABLE dbo.Product (
       Id int identity, 
       ProductName varchar(100),
       Qty int);
INSERT INTO dbo.Product (ProductName, Qty) VALUES('Magic Stripper', 5);
CREATE TABLE dbo.NewInventory(
       Id int identity, 
       ProductName varchar(100), 
       Qty int);
INSERT INTO dbo.NewInventory (ProductName, Qty) VALUES
       ('Sandpaper',20), 
       ('Paint Brush 1 inch',15),
       ('Magic Stripper',5); 
            

This code just sets my Target and Source back to what it was originally, where there is only 1 row in the Product table and 3 rows in the NewInventory table.
Suppose I only want to add a subset of the rows in my NewInventory table to my Product table.  For this example, suppose I only want to add rows to my Product table if the Qty value in my NewInventory table is greater than 5.
To demonstrate how a MERGE statement can produce unexpected results if you add additional filtering criteria using the ON clause, let me run the following code: 

USE tempdb;
GO
MERGE dbo.Product AS T  
USING dbo.NewInventory AS S 
ON T.ProductName = S.ProductName
   AND S.QTY > 5
WHEN MATCHED THEN  
  UPDATE SET T.Qty = T.Qty + S.Qty
WHEN NOT MATCHED THEN  
  INSERT (ProductName,Qty) VALUES (S.ProductName,S.Qty);
SELECT * FROM dbo.Product;

When I run this code I get this output:

Id          ProductName                                                   Qty
----------- ------------------------------------------------------------- -----------
1           Magic Stripper                                                5
2           Sandpaper                                                     20
3           Paint Brush 1 inch                                            15
4           Magic Stripper                                                5
 

If you look at this output you can see it doesn’t meet my requirement by only performing an UPSERT operation of the records in my Source table that have a Qty value greater than 5.   Why did it do this?  It did this because I added an additional filter rule that wasn’t an equality comparison between a column in the Target table and a column in the Source tables.  This additional filter criteria is the “S.Qty > 5” condition.   By adding this additional condition that only identified a source column I told SQL Server this was part of the matching criteria.  This additional condition was used to determine which records match between the Source and Target tables.   In this particular example no row in the Source table matched any rows in the Target table therefore all three records in my Source table where inserted into the Target table.  This is why I now have two product records with a ProductName of “Magic Stripper”.
In order to meet my requirements I can write my MERGE statement like below:

USE tempdb;
GO
MERGE dbo.Product AS T  
USING dbo.NewInventory AS S 
ON T.ProductName = S.ProductName
WHEN MATCHED AND S.Qty > 5 THEN  
  UPDATE SET T.Qty = T.Qty + S.Qty
WHEN NOT MATCHED AND S.Qty > 5 THEN  
  INSERT (ProductName,Qty) VALUES (S.ProductName,S.Qty);
SELECT * FROM dbo.Product;

In this code I place the additional filter rule on the WHEN clause.  Now SQL Server first matches the rows between the Source and Target tables based on the ON condition and then when SQL Server executes the WHEN conditions it excludes the Source rows that have a Qty less than 6 from being inserted or updated.   To test this for yourself you will first need to run the first code block in this section that creates the original Source and Target rows, and then run this code.

Duplicates in Source Table

Another condition that causes problems with the MERGE statement is when the Source table contains multiple records with the same column values associated with the ON clause.  When this occurs SQL Server will throw a duplicate error message.  To demonstrate let me create a new Source table “NewInventory3” and then run it through my MERGE statement.  Here is the code that will throw the MERGE error:

USE tempdb;
GO
CREATE TABLE NewInventory3 (
       Id int identity, 
       ProductName varchar(100), 
       Qty int);
INSERT INTO NewInventory3 values 
       ('Magic Stripper',15),
       ('Magic Stripper',5); 
       USE tempdb;
GO
MERGE dbo.Product AS T  
USING dbo.NewInventory3 AS S 
ON T.ProductName = S.ProductName
WHEN MATCHED THEN  
  UPDATE SET T.Qty = T.Qty + S.Qty
WHEN NOT MATCHED THEN  
  INSERT (ProductName,Qty) VALUES (S.ProductName,S.Qty);
SELECT * FROM dbo.Product;   

When I run this code I get this error:

Msg 8672, Level 16, State 1, Line 12

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
This error was caused because I had two duplicate rows in my Source table that matched to a single Target row.   To resolve this problem I need to eliminate the duplicate rows based on the matching criteria.  Suppose I wanted both of those rows to actually add to my inventory of “Magic Stripper”.  In this case, I could run the following MERGE statement to accomplish this:

MERGE dbo.Product AS T  
USING (SELECT ProductName, SUM(Qty) as Qty
      FROM dbo.NewInventory3
         GROUP by ProductName) AS S 
ON T.ProductName = S.ProductName
WHEN MATCHED THEN  
  UPDATE SET T.Qty = T.Qty + S.Qty
WHEN NOT MATCHED THEN  
  INSERT (ProductName,Qty) VALUES (S.ProductName,S.Qty);
SELECT * FROM dbo.Product;  

Here I removed the duplicate row in my Source table by first aggregating all the similar ProductName rows in my Source table by using a sub-query when I identified the Source rows for my MERGE statement.

Summary

The MERGE statement allows you to write a single TSQL statement that allows you to INSERT, UPDATE, and/or DELETE records from a Target table. The MERGE statement controls whether an INSERT, UPDATE, or DELETE clause is executed by matching a set of rows in the Source table against the Target table.  When a Source row matches a Target row the Target row is updated, with information from the Source row.  If the Source row does not match a Target row then the Source row is inserted into the Target table.  The MERGE statement can also DELETE rows from the Target table when Target table rows are not found within the Source table. Using the MERGE statement greatly simplifies the amount of code you would need to write using “if then else” logic to perform INSERT, UPDATE, and/or DELETE operations against a Target table.   Next time you need to perform an UPSERT operation look into using the MERGE statement if you are on

Using the ROLLUP, CUBE, and GROUPING SETS Operators

What do the ROLLUP, CUBE, and GROUPING SETS operators do?  They allow you to create subtotals and grand totals a number of different ways.
The ROLLUP operator is used with the GROUP BY clause.  It is used to create subtotals and grand totals for a set of columns.  The summarized amounts are created based on the columns passed to the ROLLUP operator.
The CUBE operators, like the ROLLUP operator produces subtotals and grand totals as well.  But unlike the ROLLUP operator it produces subtotals and grand totals for every permutation of the columns provided to the CUBE operator.

Lastly the GROUPING SETS operator allows you to group your data a number of different ways in a single SELECT statement.  To better understand these three different grouping operators let’s review some examples of how they can be used.

Sample Data for Examples

Before I can show you how to use these different GROUP BY operators I first need to generate some test data that can be used for my examples.  The code below will create a sample table that I will be using for all of my examples.

SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE PurchaseItem (
      PurchaseID smallint identity, 
      Supplier varchar(50),
      PurchaseType varchar(20), 
      PurchaseAmt money, 
      PurchaseDate date);
INSERT INTO PurchaseItem VALUES
    ('McLendon''s','Hardware',2121.09,'2014-01-12'),
      ('Bond','Electrical',12347.87,'2014-01-18'),
      ('Craftsman','Hardware',999.99,'2014-01-22'),
      ('Stanley','Hardware',6532.09,'2014-01-31'),
      ('RubberMaid','Kitchenware',3421.10,'2014-02-03'),
      ('RubberMaid','KitchenWare',1290.90,'2014-02-07'),
      ('Glidden','Paint',12987.01,'2014-02-10'),
      ('Dunn''s','Lumber',43235.67,'2014-02-21'),
      ('Maytag','Appliances',89320.19,'2014-03-10'),
      ('Amana','Appliances',53821.19,'2014-03-12'),
      ('Lumber Surplus','Lumber',3245.59,'2014-03-14'),
      ('Global Source','Outdoor',3331.59,'2014-03-19'),
      ('Scott''s','Garden',2321.01,'2014-03-21'),
      ('Platt','Electrical',3456.01,'2014-04-03'),
      ('Platt','Electrical',1253.87,'2014-04-21'),
      ('RubberMaid','Kitchenware',3332.89,'2014-04-20'),
      ('Cresent','Lighting',345.11,'2014-04-22'),
      ('Snap-on','Hardware',2347.09,'2014-05-03'),
      ('Dunn''s','Lumber',1243.78,'2014-05-08'),
      ('Maytag','Appliances',89876.90,'2014-05-10'),
      ('Parker','Paint',1231.22,'2014-05-10'),
      ('Scotts''s','Garden',3246.98,'2014-05-12'),
      ('Jasper','Outdoor',2325.98,'2014-05-14'),
      ('Global Source','Outdoor',8786.99,'2014-05-21'),
      ('Craftsman','Hardware',12341.09,'2014-05-22');
     
     

You can create this table if you want to follow along with my examples.

ROLLUP Examples

The ROLLUP operator allows SQL Server to create subtotals and grand totals, while it groups data using the GROUP BY clause.  For my first example let me use the ROLLUP operator to generator a grand total by PurchaseType by running this code:

USE tempdb;
GO
SELECT coalesce (PurchaseType,'GrandTotal') AS PurchaseType
     , Sum(PurchaseAmt) as SummorizedPurchaseAmt
FROM PurchaseItem
GROUP BY ROLLUP(PurchaseType);

When I run this code I get this output:

PurchaseType         SummorizedPurchaseAmt
-------------------- ---------------------
Appliances           233018.28
Electrical           17057.75
Garden               5567.99
Hardware             24341.35
Kitchenware          8044.89
Lighting             345.11
Lumber               47725.04
Outdoor              14444.56
Paint                14218.23
GrandTotal           364763.20

By reviewing the output above you can see that this code created subtotals for all the different PurchaseTypes and then at the end produced a GrandTotal for all the PurchaseTypes combined.   If you look at the code above, I got the PurchaseType of “Grand Total” to display by using the coalesce clause.  Without the coalesce clause the PurchaceType column value would have been “Null’ for the grand total row.
Suppose I wanted to calculate the subtotals of ProductTypes by month, with a monthly total amount for all the products sold in the month.  I could do that by running the following code:

USE tempdb;
GO
SELECT month(PurchaseDate) PurchaseMonth
     , CASE WHEN month(PurchaseDate) is null then 'Grand Total' 
                   ELSE coalesce (PurchaseType,'Monthly Total') end AS PurchaseType
     , Sum(PurchaseAmt) as SummorizedPurchaseAmt
FROM PurchaseItem
GROUP BY ROLLUP(month(PurchaseDate), PurchaseType);
 
When I run this code I get this output:
 
PurchaseMonth PurchaseType         SummorizedPurchaseAmt
------------- -------------------- ---------------------
1             Electrical           12347.87
1             Hardware             9653.17
1             Monthly Total        22001.04
2             Kitchenware          4712.00
2             Lumber               43235.67
2             Paint                12987.01
2             Monthly Total        60934.68
3             Appliances           143141.38
3             Garden               2321.01
3             Lumber               3245.59
3             Outdoor              3331.59
3             Monthly Total        152039.57
4             Electrical           4709.88
4             Kitchenware          3332.89
4             Lighting             345.11
4             Monthly Total        8387.88
5             Appliances           89876.90
5             Garden               3246.98
5             Hardware             14688.18
5             Lumber               1243.78
5             Outdoor              11112.97
5             Paint                1231.22
5             Monthly Total        121400.03
NULL          Grand Total          364763.20
 
 

Here I have included two columns in the ROLLUP clause.  The first column was the month of the purchase, and the second column is PurchaseType.  This allowed me to create the subtotals by ProductType by month, as well as Monthly Total amount at the end of every month.  Additionally this code creates a Grant Total amount of all product sales at the end.

CUBE Example

The CUBE operator allows you to summarize your data similar to the ROLLUP operator.  The only difference is the CUBE operator will summarize your data based on every permutation of the columns passed to the CUBE operator.  To demonstrate this I will run the code below:

USE tempdb;
GO
SELECT month(PurchaseDate) PurchaseMonth
     , CASE WHEN month(PurchaseDate) is null 
                  THEN coalesce ('Grand Total for ' + PurchaseType,'Grand Total')  
                ELSE coalesce (PurchaseType,'Monthly SubTotal') end AS PurchaseType
     , Sum(PurchaseAmt) as SummorizedPurchaseAmt
FROM PurchaseItem
GROUP BY CUBE(month(PurchaseDate), PurchaseType);
 

When I run this code it will generates summarized amounts for every permutation of the columns passed to the CUBE operator.  In my example I had two values pass to the CUBE operator: “month(PurchaseDate)” and “PurchaseType”.  Therefore the different permutations of summarized amounts are “PurchaseType”, “PurchaseType and month(PurchaseDate)”, and then lastly “month(PurchaseDate)”.   You can see these different summarized amounts in the output below:

PurchaseMonth PurchaseType                         SummorizedPurchaseAmt
------------- ------------------------------------ ---------------------
3             Appliances                           143141.38
5             Appliances                           89876.90
NULL          Grand Total for Appliances           233018.28
1             Electrical                           12347.87
4             Electrical                           4709.88
NULL          Grand Total for Electrical           17057.75
3             Garden                               2321.01
5             Garden                               3246.98
NULL          Grand Total for Garden               5567.99
1             Hardware                             9653.17
5             Hardware                             14688.18
NULL          Grand Total for Hardware             24341.35
2             Kitchenware                          4712.00
4             Kitchenware                          3332.89
NULL          Grand Total for Kitchenware          8044.89
4             Lighting                             345.11
NULL          Grand Total for Lighting             345.11
2             Lumber                               43235.67
3             Lumber                               3245.59
5             Lumber                               1243.78
NULL          Grand Total for Lumber               47725.04
3             Outdoor                              3331.59
5             Outdoor                              11112.97
NULL          Grand Total for Outdoor              14444.56
2             Paint                                12987.01
5             Paint                                1231.22
NULL          Grand Total for Paint                14218.23
NULL          Grand Total                          364763.20
1             Monthly SubTotal                     22001.04
2             Monthly SubTotal                     60934.68
3             Monthly SubTotal                     152039.57
4             Monthly SubTotal                     8387.88
5             Monthly SubTotal                     121400.03
 

The results above first generated the subtotals for each PurchaseType by month, followed by the Grand Total for each PurchaseType.  Once each PurchaseType is displayed by month with their Grand Total amounts this code then produces a “Grand Total” amount for all purchases.   Lastly it produces the monthly subtotals. 

GROUPING SETS Example

Sometimes you want to group your data multiple different ways.  The GROUPING SETS operator allows you to do this with a single SELECT statement, instead of multiple SELECT statements with different GROUP BY clauses union-ed together.  To demonstrate, review the code in below:

USE tempdb;
GO
SELECT month(PurchaseDate) PurchaseMonth
     , PurchaseType AS PurchaseType
     , Sum(PurchaseAmt) as SummorizedPurchaseAmt
FROM PurchaseItem
GROUP BY GROUPING SETS (month(PurchaseDate), PurchaseType);
 

When run the code above I get the following output:

PurchaseMonth PurchaseType         SummorizedPurchaseAmt
------------- -------------------- ---------------------
NULL          Appliances           233018.28
NULL          Electrical           17057.75
NULL          Garden               5567.99
NULL          Hardware             24341.35
NULL          Kitchenware          8044.89
NULL          Lighting             345.11
NULL          Lumber               47725.04
NULL          Outdoor              14444.56
NULL          Paint                14218.23
1             NULL                 22001.04
2             NULL                 60934.68
3             NULL                 152039.57
4             NULL                 8387.88
5             NULL                 121400.03
 
 

Here you can see SQL Server first groups my sample data based on the PurchaseType, then it groups the data based on purchase month.  This code is equivalent to the code below that uses two SELECT statements with the output of both statement joined using a UNION ALL clause:

USE tempdb;
GO
SELECT NULL as PurchaseMonth
     , PurchaseType
     , Sum(PurchaseAmt) as SummorizedPurchaseAmt
FROM PurchaseItem
GROUP BY PurchaseType
UNION ALL
SELECT month(PurchaseDate) AS PurchaseMonth
     , NULL as PurchaseType
     , Sum(PurchaseAmt) as SummorizedPurchaseAmt
FROM PurchaseItem
GROUP BY month(PurchaseDate)

If you run the code above you will see that that it produces the same results as the single SELECT statement with a GROUPING SETS clause.

Summary

As you can see using the ROLLUP, CUBE or GROUPING SETS clauses allow you many different ways to produce subtotal and grand total values for a set of records.  Having these different methods to create subtotals and grand totals allows you more options for how you can summarize your data with a single SELECT statement.  The next time you want to produce multiple summarized record sets try using ROLLUP, CUBE, or GROUPING SETS to obtain your summarized data.

Reset All Field using JQUERY in asp.net

1. Take a reference of latest jQuery in your source. I am using jquery-1.10.2.js in my example.
2. In the form tag of your Web form add your controls which you would like to use in the page.
3. Simply Copy and Paste the below code in the script tag :

 <script type="text/javascript">
        function resetFields(form) {
            $(':input', form).each(function() {
                var type = this.type;
                var tag = this.tagName.toLowerCase(); // normalize case
                // to reset the value attr of text inputs,
                // password inputs, fileUpload and textareas
                if (type == 'text' || type == 'password' || tag == 'textarea' || type=='file')
                    this.value = "";
                // checkboxes and radios need to have their checked state cleared                
                else if (type == 'checkbox' || type == 'radio')
                    this.checked = false;
                // select elements need to have their 'selectedIndex' property set to -1
                // (this works for both single and multiple select elements)
                else if (tag == 'select')
                    this.selectedIndex = 0;
            });
        }

    </script> 

Bind Tree View in asp.net

   <asp:TreeView ID="SqlTree" OnSelectedNodeChanged="SqlTreeSelectedNodeChanged" CssClass="example2"
                                                                                Style="font-weight: bold; text-align: left;" ShowLines="true" ShowExpandCollapse="true"
                                                                                runat="server" >
                                                                                <NodeStyle Height="28px"  Width="250px"/>
                                                                            </asp:TreeView>




IN CS file


protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            if (!Page.IsPostBack)
            {
             
                SqlTree.CollapseAll();
                FillTreeView();
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

   public void FillTreeView()
    {
        DataTable Modules = new DataTable();
        Modules = GetModuleDetails();
        SqlTree.Nodes.Clear();
        PopulateTreeNode(Modules, null, 0);
    }
    private void PopulateTreeNode(DataTable ModuleList, TreeNode parent, int parentID)
    {
        TreeNodeCollection baseNodes;
        TreeNode node;
        if (parent == null)
        {
            baseNodes = SqlTree.Nodes;
        }
        else
            baseNodes = parent.ChildNodes;
        foreach (DataRow dtRow in ModuleList.Rows)
        {
            if (int.Parse(dtRow["ChildID"].ToString()) == parentID)
            {
                node = new TreeNode();
                node.Text = dtRow["Description"].ToString();
                node.Value = dtRow["ID"].ToString();
                node.SelectAction = TreeNodeSelectAction.Select;
                baseNodes.Add(node);
                PopulateTreeNode(ModuleList, node, int.Parse(dtRow["ID"].ToString()));
            }
        }
        SqlTree.ExpandAll();
    }
 protected void SqlTreeSelectedNodeChanged(object sender, EventArgs e)
    {
        string a = SqlTree.SelectedNode.Text;
        if (a == "HPI")
      {
//Do Something
}

   
    }


 public DataTable GetModuleDetails()
    {
        DataTable dt = new DataTable();
        dt = ObjComplaint.ReadCommentTreeAll(Convert.ToInt32(DepID));
        return dt;
    }