SQL Server: Grouping ID Example

Let us suppose you have used the rollup operator to generate summary information along with the Details information and you now want to differentiate between summary and detail. You can make use of the SQL Server grouping_id function. Let us understand the GROUPING_ID function using some code.

Consider the following example:

declare @t table(region varchar(100), name varchar(100), amount decimal(12,2))
insert into @t
select 'region1','name1',2000 union all
select 'region1','name2',100 union all
select 'region1','name3',500 union all
select 'region1','name3',3400 union all
select 'region2','name1',3233 union all
select 'region2','name2',5000 union all
select 'region2','name2',5344 union all
select 'region3','name1',1200 union all
select 'region3','name2',900 union all
select 'region4','name1',2540
select region,name,SUM(amount) as amount,GROUPING_id(name) as [grouping] from @t
group by region, name
with rollup

Look at the result set below. The last column grouping generates 0 or 1. The rows with 0’s are Details whereas the rows with 1’s, contains Summary.

SQL Server: Check if Date is End of Month

Here’s a very simple query that checks if the date is End of the Month.

DECLARE @Dt DATETIME 

SET @Dt = '2011-06-30'

IF MONTH(@Dt) != MONTH(DATEADD(DAY,1,@Dt))
BEGIN
PRINT 'End Of Month'
END

As you can see, we are using MONTH(DATEADD(DAY,1,@Dt)) which if run for the June date returns 7 if we are the end of the month. For any other June date, it will return 6. All we are doing is comparing it with MONTH(@Dt) to see if the two values match. If it does not, it is the end of the month.

OUTPUT



SQLServerCurry.com author Madhivanan has suggested an alternate method to check if the given date is the last date of the month. Here it is:

DECLARE @Dt DATETIME
SET @Dt = '2011-06-30'
IF DAY(@Dt+1) = 1
BEGIN
PRINT 'End Of Month'
END
ELSE
BEGIN
PRINT 'Not End Of Month'
END

The code DAY(@Dt+1) adds 1 day to the given date. If the given date is last day of month,
the date becomes first day of next month so the DAY function will return 1. Otherwise it is
not the last day of month.

Update: Brad Schulz made a good point on this alternate method. He says "It will not work with the SQL2008 date datatype. You can not add an integer to a date datatype like you could with datetimes. So instead of DAY(@Dt+1) it is best to do DAY(DATEADD(day,1,@Dt)), which will work in all versions." Thanks Brad!

SQL Server Tutorials on Date Time

One of the most frequently asked question by SQL developers, is on handing Date,  Time and other related datetime values in SQL Server. Developers are always looking out for solutions which demand either converting Date Time Values or finding date time ranges and so on. Madhivanan and I have already written a couple of articles on handling DateTime in SQL Server. In this post I will share some of the links with you.

Hope you find them useful. If you do, make sure you retweet and let the other devs know about this link list.

FLOOR a DateTime in SQL Server

SQL Server: Insert Date and Time in Separate Columns

SQL Server: Group By Days and Create Categories

SQL Server: Insert Date and Time in Separate Columns

SQL Server: Group By Year, Month and Day

SQL Server: First and Last Sunday of Each Month

SQL Server: First Day of Previous Month

SQL Server: Convert to DateTime from other Datatypes

Date Difference in SQL Server in Days, Hours, Minutes and Seconds

Display Dates in a Particular Format in SQL Server

Truncate Hour, Minute, Second and MilliSecond in SQL Server

Calculate Interest on an Amount Between two Dates using SQL Server

Working with Different Time Zones in SQL Server 2008

Print Dates without Day or Time in SQL Server

Calculate Age from Date Of Birth using SQL Server

Replace SQL Server DateTime records of a column with UTC time

Calculate Average Time Interval in SQL Server

List all the Weekends of the Current Year using SQL Server

Create Date Ranges for Consecutive dates in a SQL Server Table

SQL Query to find out who attended office on Saturday

Convert Date to String in SQL Server

Convert Character string ISO date to DateTime and vice-versa

Select dates within the Current Week using SQL Server

Generate a Start and End Date Range using T-SQL

Convert From EST to GMT Time and vice versa in SQL Server

Convert DateTime to VarChar in SQL Server

How to Display DateTime Formats in Different Languages using SQL Server

Finding the Business Days In a Quarter and Number them in SQL Server 2005/2008

Find the WeekEnds in this Quarter or Year using SQL Server 2005/2008

Comparing Dates In Two Columns using SQL Server\

Some Common DateTime Formats in SQL Server 2005/2008

First weekday of a month in SQL Server

DATENAME() function in SQL Server 2005

Return date without time using Sql 2005

SQL Server: Calculate Summary and Column Summary

This post shows how to show a summary of similar rows as well as column summary for SQL Server table data. Suppose you want to generate the total of a column and display it at the end of the result set. You can use at least two methods as shown below:

Consider the following data:

declare @t table(name varchar(100), amount decimal(12,2))
insert into @t
select 'test1', 1000 union all
select 'test1', 2000 union all
select 'test1', 3000 union all
select 'test2', 1500 union all
select 'test3', 600 union all
select 'test3', 1800

If you want to want to sum the amount for each name and also show the total of all names
at the end, use any of the following methods

METHOD 1: Using ROLLUP

select coalesce(name,'Total') as name, SUM(amount) as amount from @t
group by name
with rollup

METHOD 2: Using UNIONALL

select name, SUM(amount) as amount from @t
group by name
union all
select 'Total',SUM(amount) from @t

OUTPUT

Standard Deviation in SQL Server and Aggregation on Aggregates

SQL Server makes calculation of Standard Deviation relatively easy with the STDEV statistical aggregate function. What I learnt recently was that you can either apply this function to all values or can even select distinct values. You can also perform an aggregation on aggregates that can be used in reports.

I was facing one such requirement where aggregates were to be calculated and we had to consider distinct values as well while calculating standard deviation. Here’s a sample example using the Northwind database. 

SELECT
SUM(Freight) as [Total Freight],
AVG(Freight) as [Average Freight],
STDEV(ALL Freight) as [Freight Deviation],
STDEV(DISTINCT Freight) as [Distinct Freight]
FROM Orders
WHERE ShipCountry = 'UK'

As you can see, we are using the ‘ALL’ and ‘DISTINCT’ to specify all or distinct values respectively.



If you are creating reports using SSRS, you can create expressions performing aggregation on aggregates. Here’s an example:

=StDev(Sum(Fields!Products.Price))

The StDevP function is also similar except that it performs statistical standard deviation for  all values in the specified expression, it evaluates the ‘entire’ data population. The StDev on the other hand is based on a sample of the data population.

SQL Server Driver 2.0 for PHP 5

Microsoft recently released SQL Server Drivers for PHP 2.0 which adds support for PDO with a new PDO_SQLSRV and contains bug fixes for the existing SQLSRV driver. The native APIs present in the PHP Driver for SQL Server 1.1 are still present, there is additional support for the PDO API.

Microsoft says “In order to support the new PDO_SQLSRV driver to be consistent with the existing SQLSRV driver, the code was re-architected into a core functional layer and an "API" layer above. The SQLSRV driver is built from this re-architected code, and also includes some important bug fixes. This release is available to all SQL Server users at no additional charge.”

Download the SQL Server Driver 2.0 for PHP

The Drivers for PHP for SQL Server are used for connecting PHP applications to Microsoft SQL Server.

SQL Server: Different Ways to find SPID

There are many ways to know the Server Process id or now also known as Session ID (SPID). The most common methods are as follows:

1. Use global variable @@SPID

SELECT @@SPID as spid

2. Use Properties window

While working in the Query Analyzer, press F4. A Property Window opens and you can see SPID in the last row

3. Use System procedure sp_who

EXEC sp_who

The above will list out all the processes with spid. The last spid is the one related
to your session.

4. Use sysprocesses view

select spid from sys.sysprocesses

This query will list out all the processes with spid. The last spid is the one related
to your session.

SQL Azure Migration Wizard

For those who do not know about this tool, the SQL Azure Migration Wizard is an open source tool that helps you migrate your SQL Server 2005/2008 databases to and from SQL Azure. The tools analyzes your database for any compatibility issues and allows you to either fully or partially migrate your database schema and data to SQL Azure.

Note: A couple of days ago, I had also blogged about the useful SQL Server Migration Assistant v5.0 tool which automates the migration of Oracle, Sybase, MySQL and Microsoft Access databases to SQL server or SQL Azure.

You can use tools of SQL Azure Migration Wizard from command line too.

Download the SQL Azure Migration Wizard Tool

Concatenate Strings in SQL Server - Different ways

There are many ways to concatenate data in a single column. Consider the following table:



If you want to concatenate every row value separated by comma, use these methods:

1. Use Variable to Concatenate

declare @sql varchar(8000)
select @sql=coalesce(@sql+',','')+data
from @t
select @sql
In the above T-SQL code, each row value is concatenated with the variable @sql

2. Use FOR XML clause to Concatenate

select distinct
        stuff((select distinct top 100 percent ',' + data from @t as t for xml path('')), 1, 1, '')
from        @t as t

In the above example, FOR XML clause is used to concatenate row values. Since a comma is used, the query concatenates values separated by comma.

SSIS Balanced Data Distributor Transform

As MSDN describes, SQL Server Integration Services (SSIS) transformations are the components in the data flow of a package that  data. Transformations can also perform lookup operations and generate sample datasets

The Balanced Data Distributor (BDD) is a new SSIS transform which takes a single input and distributes the rows to one or more outputs uniformly via multithreading. The transform takes one pipeline buffer worth of rows at a time and moves it to the next output in a round robin fashion.

It’s balanced and synchronous so if one of the downstream transforms or destinations is slower than the others, the rest of the pipeline will stall so this transform works best if all of the outputs have identical transforms and destinations. The intention of BDD is to improve performance via multi-threading.

You can download the Balanced Data Distributor (BDD) Transform here

Dynamic Connection String in SQL Server Reporting Services 2005/2008

In this post we will see how to generate a report using dynamic connection string in SQL Server Reporting Services 2005/2008. The scenario is if we have different data with the same schemas, spread across different database and different SQL servers and people would like to see the data as per their preferences (like area wise), we can use a Dynamic Connection string.

So let’s create a report using SQL Server Business Intelligence Studio 2008 with the name ‘DynamicConnectionReports’ as shown below –

Now follow the wizard to create a simple Customer Records report from the database ‘Northwind’ with the query – ‘Select * from Customer’. I have already demonstrated creating report using wizard in my previous article ‘Designing Basic Report using SSRS 2008’. If you have not read it yet, I will highly recommend you to go through the steps.

Once your report is ready, let’s preview it and the output should look like below –

Now once your report is ready, go to the ‘Report Data’ window and add a parameter with the name ‘Databases’ as shown below –

Click on ‘OK’ button. Let’s create a Dataset which will fetch all ‘Databases’ from our SQL Server instance. To add a new dataset, right click on the data source (I have named it ‘CommonDS’) and click on ‘Add Dataset’. Now let’s configure the Dataset as shown below –

Make sure you will not use the existing Data Source. Create a new connection. Now let’s map the Parameter

‘Databases’ to take the data from ‘AddDatabases’ dataset. Right click on the ‘Databases’ parameter and configure the parameter as shown below –

Click on the ‘OK’ button. Now right click ‘CommonDS’ data source of the report and click on ‘Data Source Properties’ option. It will display the properties window. Remove the existing connection string and click on the ‘Expression’ button –

Now let’s define the connection string as shown below –

="Data Source=Localhost;Database=" & Parameters!Databases.Value

Click on the ‘OK’ button and now ‘Preview’ the report. You will see all the databases in a dropdown Listbox. Choose ‘Northwind’ database and click on the ‘View Report’ button. The report will look like the one shown below –

Now you can use this report from Web Form or Windows Form by passing the parameter value from the application. You can hide the parameter from the report by setting ‘Parameter visibility property’ ‘hidden’. Now the most important part is this dynamic connection string demo will not work with ‘Shared Data Source’.

Also make a note that in this demonstration, we are using one instance of SQL Server and fetching all the databases. You can even make your SQL Server parameter dynamic.

The reports will work fine as long as you are using the same schema for generating the report.

SQL Server 2008 R2 Training Kit (May 2011 Update)

Microsoft updated it’s SQL Server 2008 R2 Training Kit to support for Windows 7 SP1, Windows Server 2008 R2 SP1 and Visual Studio 2010 SP1.

The contents of the Training Kit are as follows:

Getting Started - for Web and BI developers who are new to SQL Server - Web Developers, BI Developers

SQL Server 2008 - For experienced SQL Server developers who want to understand what's new in 2008 - Overview, Date and Time Data Types, Spatial Data Types, FILESTREAM Blob Storage, Transact-SQL Improvements, .NET CLR Integration, Reporting Services, AdventureWorks Racing All-Up SQL Server 2008

SQL Server 2008 R2 - for experienced SQL Server developers who want to understand what's new in 2008 R2 - Overview, Data-tier Application Framework, StreamInsight, Reporting Services

Office 2010 - for experienced BI developers who want to understand what's new in 2008 R2 and Office 2010 - Excel 2010 and PowerPivot, SharePoint 2010, PerformancePoint Services and PowerPivot

Download the SQL Server 2008 R2 Training Kit (269 MB)

FLOOR a DateTime in SQL Server

Suppose you are given a datetime and want to emulate the FLOOR function on it eg: find first day of year, first day of month etc, you can use the following code



declare @date datetime
set @date=' 2011-05-17 06:36:22.252'
select
    DATEADD(year,datediff(year,0,@date),0) as year,
    DATEADD(month,datediff(month,0,@date),0) as month,
    DATEADD(day,datediff(day,0,@date),0) as day,
    DATEADD(hour,datediff(hour,0,@date),0) as hour,
    DATEADD(minute,datediff(minute,0,@date),0) as minute,
    DATEADD(day,datediff(day,0,@date),0) +convert(varchar(15),@date,108)as seconds ,
    @date as milliseconds

In the above code, datediff finds the difference in terms of parameter (year, month, etc)
and it is added to the base date 0, which omits the month, day,time part  etc.

OUTPUT

(contd…)

SQL Server 2008 R2 VHD Available for Download

Microsoft has released a pre-configured Virtual Hard Drive (VHD) which enables you evaluate SQL Server 2008 R2 Standard Edition for 180 days.

Make sure you have Hyper-V installed to run this virtual machine. You will also need a 64-bit system with hardware-assisted virtualization enabled, as well as data execution prevention (DEP).

Download SQL Server 2008 R2 VHD

There are 5 parts to be downloaded in the link given above. Once downloaded, self-extract the RAR file, accept the EULA and import the VM into your Hyper-V.

Note: To connect to the internet using this VHD, you will manually need to configure a virtual NIC.