Grouping Sets in T-SQL (SQL Server 2008)

Introduction
This article discuss Grouping Sets in T-SQL. Grouping Sets is a new feature in T-SQL in SQL Server 2008.

Background
People cannot help appreciating GROUP BY clause whenever they have to get a DISTINCT from any result set. Additionally whenever any aggregate function is required GROUP BY clause is the only solution. There has always been requirement get these aggregate function based on different set of columns in the same result set. It is also safe to use this feature as this is an ISO standard.

Though the same result could be achieved earlier but we have to write different queries and would have to combine them using UNION operator. The result set returned by GROUPING SET is the union of the aggregates based on the columns specified in each set in the Grouping set.

Example

To understand it completely first we create a table tbl_Employee.

CREATE TABLE tbl_Employee
(
Employee_Name varchar(25),
Region varchar(50),
Department varchar(40),
sal int
)

Now we populate table with some the following rows:

INSERT into tbl_Employee(
Employee_Name,
Region,
Department,
sal
)

VALUES
('Shujaat', 'North America', 'Information Technology', 9999),
('Andrew', 'Asia Pacific', 'Information Technology', 5555),
('Maria', 'North America', 'Human Resources', 4444),
('Stephen', 'Middle East & Africa', 'Information Technology', 8888),
('Stephen', 'Middle East & Africa', 'Human Resources', 8888)

After populating with the rows, we select some rows using Grouping Sets.

SELECT Region, Department, avg(sal) Average_Salary
from tbl_Employee
Group BY
GROUPING SETS
(
(Region, Department),
(Region),
(Department) ,
()
)

The result of this statement is as follows:

You can see that the result set contains rows grouping by each set in the specified Grouping Sets. You can see the average salary of employees for each region and department. You can also appreciate the average salary of employees for the organization (NULL for both Region and Department). This was the result of empty Grouping Set i.e. ().

Before 2008, if you had to get the same result set, following query had to be written:

SELECT Region, Department, avg(sal) Average_Salary
from tbl_Employee
Group BY
Region, Department

UNION

SELECT Region, NULL, avg(sal) Average_Salary
from tbl_Employee
Group BY
Region

UNION

SELECT NULL, Department, avg(sal) Average_Salary
from tbl_Employee
Group BY
Department

UNION

SELECT NULL, NULL, avg(sal) Average_Salary
from tbl_Employee

By looking at the above query, you can appreciate the ease provided by Grouping Set to the developer.

CUBE subclause for Grouping

This is used to return power ‘n’ to 2 for ‘n’ elements.

SELECT Region, Department, avg(sal) Average_Salary
from tbl_Employee
Group BY
CUBE (Region, Department)

The above query is equivalent to the following query:

SELECT Region, Department, avg(sal) Average_Salary
from tbl_Employee
GROUPING SETS
(
(Region, Department),
(Region),
(Department) ,
()
)

ROLLUP subclause for Grouping

This is used to return ‘n+1’ grouping sets for ‘n’ elements in the hierarchy scenario.

SELECT Region, Department, avg(sal) Average_Salary
from tbl_Employee
Group BY
ROLLUP (Region, Department)

This is equivalent to the following query:

SELECT Region, Department, avg(sal) Average_Salary
from tbl_Employee
Group BY
Grouping Sets
(
(Region, Department),
(Region),
()
)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Muhammad Shujaat Siddiqi Muhammad Shujaat SiddiqiNew Jersey, USA
Occupation: Software Developer (Senior)
Location: United States

MonthFirst,MonthEnd for VB.Net

Version Compatibility : VB.Net 2005

More information : MonthFirst and MonthEnd for VB.net. it same vb6. use function name DateSerial and DateTime.Now().

Source Code :

Public Function MonthFirst(ByVal pDate As DateTime) As DateTime
Try
Return DateSerial(Year(pDate), Month(pDate), 1)
Catch ex As Exception
Return DateSerial(Year(DateTime.Now()), Month(DateTime.Now()), 1)
End Try
End Function

Public Function MonthEnd(ByVal pDate As DateTime) As DateTime
Try
Return DateSerial(Year(pDate), Month(pDate) + 1, 1).AddDays(-1)
Catch ex As Exception
Return DateSerial(Year(DateTime.Now()), Month(DateTime.Now()) + 1, 1).AddDays(-1)
End Try
End Function

For Example Use :

Call MonthFirst("15/01/2009") ' Return "01/01/2009"
Call MonthEnd("15/01/2009") ' Return "31/01/2009"

*** if you Call MonthEnd Febuary result is 28 or 29.

Visaul Basic .Net Show RoundUp, RoundMe

Version Compatibility : Microsoft Visual Studio 2005 Or Microsoft Visual Basic 6.0

More information : Function RoundUp and RoundMe for up decimal and not up decimal. Suppot VB 6.0, VB.Net 2005. and two function name support business software Example . Purchase Order, General Ledger, Order Entry and Other

Source Code :

Public Function RoundUp(ByVal pNumber As Double, ByVal pDigit As Integer) As Double
Dim mPlus As Double = 0.0, mDivide As Double = 0.0, mresult As Double = 0.0
Dim mText As String = ""
'-- if you make comment pDigit = 0 then decimal 0.1 to 0.9 will up to 1 alway.
'-- if you do not make comment pDigit = 0 then RoundUp return Integer decimal do not up.
'pDigit = 0
mDivide = 10 ^ pDigit
If pNumber <>
mPlus = -0.9 / mDivide '-- all up Decimal
Else
mPlus = 0.9 / mDivide '-- all up Decimal
End If
mresult = (pNumber + mPlus) * mDivide
mText = Trim(Str(mresult))
If InStr(mText, ".") > 0 Then
mText = Mid(mText, 1, InStr(mText, ".") - 1)
End If
mresult = Val(mText)
Return mresult / mDivide
End Function

Public Function RoundMe(ByVal pNumber As Double, ByVal pDigit As Integer) As Double
Dim mPlus As Double = 0.0, mDivide As Double = 0.0, mresult As Double = 0.0
Dim mText As String = ""
mDivide = 10 ^ pDigit
If pNumber <>
mPlus = -0.5 / mDivide '--- decimal up for over 0.5
Else
mPlus = 0.5 / mDivide '--- decimal up for over 0.5
End If
mresult = (pNumber + mPlus) * mDivide
mText = Trim(Str(mresult))
If InStr(mText, ".") > 0 Then
mText = Mid(mText, 1, InStr(mText, ".") - 1)
End If
mresult = Val(mText)
Return mresult / mDivide
End Function

For Example use:

Call RoundUp(100.055,2) '-- Return = 100.06 ' if you make comment pDigit = 0.
Call RoundUp(100.055,2) '-- Return = 100 ' if you do not comment pDigit = 0
Call RoundMe(100.055,2) '-- Return = 100.06 ' use function name RoundMe. it same Round on VB6 Or VB.Net.

VBA Macro with Crystal Report

VBA Macro with Crystal Report

Version Compatibility : Microsoft Visual Basic 6.5,Crystal Reports 11 or Crystal Report 8.5

More informations : VBA Macro support report software business packes Example use : Sage Accpac or software business other. You can create VBA Macro with MS SQL 2000-2005, Pervasive and use Crystal report 11, Crystal report 8.5. You not build EXE file. You can copy VBA Macro Project, Crystal report file and Run.

Instructions : Copy the delarations and code below and paste directly into your VBA Macro project.

Delarations :

Add References and Components to New Form1
Available Conponents:
-Crystal ActiveX Report Viewer Controls 11.0

Available References:
-Visual Basic For Applications
-Microsoft Forms 2.0 Object Library
-Crystal ActiveX Report Viewer Library 11.0
-Crystal Reports ActiveX Designer Run Time Library 11.0

Source Code :

Private Sub UserForm_Initialize()
Dim rptReport As New CRAXDRT.Report
Dim rptRepApp As New CRAXDRT.Application
Dim rptViews As CRAXDRT.DatabaseTable
Dim strCon As CRAXDRT.ConnectionProperties
Dim rptPath As String

'--
Set rptRepApp = New CRAXDRT.Application
rptPath = ReportPath & "\" & "oeorlst1_CUS.rpt"' ' Set Report Path

'-- Show Report
With CRVReport ' CRVReport is CrystalActiveXReportViewer Control
Set rptReport = rptRepApp.OpenReport(rptPath, 1)
With rptReport
.FormulaFields.GetItemByName("fmtCUSCODEFROM").Text = "totext('" & Trim("" & strCustomerCodeFrom) & "')"
.FormulaFields.GetItemByName("fmtCUSCODETO").Text = "totext('" & Trim("" & strCustomerCodeTo) & "')"
.FormulaFields.GetItemByName("fmtPONUMFROM").Text = "totext('" & Trim("" & strPONumberFrom) & "')"
.FormulaFields.GetItemByName("fmtPONUMTO").Text = "totext('" & Trim("" & strPONumberTo) & "')"
.FormulaFields.GetItemByName("fmtDATEFROM").Text = "totext('" & Trim("" & strDateFrom) & "')"
.FormulaFields.GetItemByName("fmtDATETO").Text = "totext('" & Trim("" & strDateTo) & "')"
End With

.ReportSource = rptReport
.ViewReport
.Zoom 90
End With
End Sub

Remark : You notice
.FormulaFields.GetItemByName("fmtCUSCODEFROM").Text
.FormulaFields.GetItemByName("fmtCUSCODETO").Text
.FormulaFields.GetItemByName("fmtPONUMFROM").Text
.FormulaFields.GetItemByName("fmtPONUMTO").Text
.FormulaFields.GetItemByName("fmtDATEFROM").Text
.FormulaFields.GetItemByName("fmtDATETO").Text

is Folmura Fields Name add Stirng Value and Show to Crystal Report.

Visual Basic Show MonthEnd

Visual Basic Show MonthEnd

Version Compatibility : Visual Basic 6.0 SP6

More information : Creating a MonthEnd in Visaul Basic is not simple. You can use function
DateSerial,Year,Month,Day and You can Create is Public Function Or Private Funcation :

' Source Code Visaul Basic
Public Function MonthEnd(ByVal pDate As Variant) As Variant
On Error GoTo MonthEnd_Err
MonthEnd = (DateSerial(Year(pDate), Month(pDate), 28) + 4) - Day((DateSerial(Year(pDate), Month(pDate), 28) + 4))
MonthEnd_Exit:
Exit Function
MonthEnd_Err:
MonthEnd = (DateSerial(Year(Now), Month(Now), 28) + 4) - Day((DateSerial(Year(Now), Month(Now), 28) + 4))
Resume MonthEnd_Exit
End Function

You can creat source code in form name or copy source code to the module and cell function MonthEnd.

Example use:

' in source code form name
Call MonthEnd(Current Date)

*** if you question you can use comment please.

Visual Basic Show MonthFirst

Visaul Basic Show MonthFirst

Version Corpatibility : Visual Basic 6.0 SP6

More information : Creating a MonthFirst in Visual Basic is very simple. You can use
function name DateSerial,Year,Month follow source code:

' Source Code Visual Basic
Public Function MonthFirst(ByVal pDate As Variant) As Variant
On Error GoTo MonthFirst_Err
MonthFirst = DateSerial(Year(pDate), Month(pDate), 1)
MonthFirst_Exit:
Exit Function
MonthFirst_Err:
MonthFirst = DateSerial(Year(Now), Month(Now), 1)
Resume MonthFirst_Exit
End Function

You can creat source code in form name or copy source code in the module and call function MonthFirst.

Example use:

' in source code form name
Call MonthFirst(Current Date)

*** if you question you can use comment please.