SQL Query to return a table containing all dates falling within a date range along with their week day names

Following is the T-SQL code to return all dates falling within a given date range. This uses CTE for populating all dates, and store them in a temporary table.

DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '01-Apr-2013'
SET @EndDate = '30-Apr-2013'

;WITH DatesList
AS
(
SELECT @StartDate [fldDate]
UNION ALL
SELECT fldDate + 1 FROM DatesList WHERE fldDate < @EndDate
)

SELECT fldDate, DATENAME(dw, fldDate) as fldDayFullName INTO #DatesList FROM DatesList option (maxrecursion 0)

SELECT * FROM #DatesList

CodeProject

Create Unique Index with IGNORE_DUP_KEY ON/ OFF on SQL Server column

Unique index/ constraint in SQL Server is used to enforce uniqueness over an SQL Column. That is, if a particular column(s) is defined with UNIQUE constraint/ index, user cannot insert duplicate values in that column.

We can have only one NULL value for that column. means, not more than one row can contain NULL value in that column.

While creating a UNIQUE index, we also can specify how to handle duplicate violation. I.e. whether to throw an error, or silently ignore adding a row having a value getting duplicated for that column.

Try following example -

create table #Countries (Id int, Name varchar(20))
GO
ALTER TABLE #Countries
ADD CONSTRAINT IX_Countries
UNIQUE (Name)
WITH (IGNORE_DUP_KEY = ON)
GO
insert into #Countries (Id, Name)
select 1, 'India'
union
select 10, NULL
union
select 11, NULL
union
select 2, 'Australia'
union
select 3, 'India'
GO
select * from #Countries

drop table #Countries

This query inserts only 3 rows in the table

10    NULL
2    Australia
1    India

But it does not throw any error, because we have configured to ignore duplicate rows.

In the same query if you set IGNORE_DUP_KEY =OFF, it will give you following message:

Violation of UNIQUE KEY constraint 'IX_Countries'. Cannot insert duplicate key in object 'dbo.#Countries'.

Find pattern-matched duplicate rows within a group using PARTITION BY in SQL Server.

This article aims to explain how to find duplicate rows within a group matching patterns.

Scenario:

Following is the structure and data of “tblPeople” table:

PersonID	CityName	ForeNames	LastName
1	Ahmedabad	Bhavin	Parikh
2	Ahmedabad	Bharat	Soni
3	Ahmedabad	Jagdish	Shah
4	Ahmedabad	Byomesh	Soni
5	Ahmedabad	Jagdish	Shyam
6	Vadodara	Bhavin	Patel
7	Vadodara	Amay	Kapoor
8	Vadodara	Ajit	Shah
9	Vadodara	Ajit	Shah
10	Vadodara	Soham	Patel
11	Vadodara	Amay	Shah
12	Surat	Rajesh	Desai
13	Surat	Bhavin	Parikh
14	Surat	Bhaumik	Shah

Our SQL should have input parameters for ForeNames, LastName, and CityName to define search pattern for duplicate items search.

Parameters - @foreNames, @lastName, @cityName

Our objective is as follow:

1.       If none of the above parameters are supplied, find all persons having same ForeNames and LastName within same City.

Expected result:

2.       If @cityName is only supplied, then find all persons having same ForeNames within particular City.

Expected result: (input >> @cityName = ‘%Vad%’)

3.       If @foreNames is supplied (with/ without @cityName supplied), but @lastName is not supplied.

In that case, consider all persons matching @foreNames within same city as duplicates, and return them.

Expected result: (input >> @foreNames=’%Bh%’)

4.       If @lastName is supplied (with /without @cityName supplied), but @foreNames is not supplied.

In that case, consider all persons matching @lastName within same city as duplicates, and return them.

Expected result: (input >> @lastName=’%Sh%’)

5.       If @foreNames and @lastName are supplied (with/without @cityName supplied).

In that case, consider all persons matching @foreNames, and @lastName within same city as duplicates, and return them.

Expected result: (input >> @foreNames=’%A%’, @lastName=’%S%’, @cityName=’%Ahm%’)

Solution:

First of all, we cannot use GROUP BY here, because we need also need ID, and other details of the rows which are duplicated. GROUP BY simply cannot allow selecting those columns which are not used in grouping or aggregate.

To achieve this, we need to partition the rows in groups of “City”, “Forenames”, and “LastName”. Here, while partitioning the rows we also need to consider pattern-matching for ForeNames and LastName.

This can be done using PARTITION BY in SELECT query, and within single T-SQL statement only.

Following is the T-SQL serving the purpose:

WITH CTE AS

(SELECT ID, City, ForeNames, LastName,

COUNT(*) OVER(PARTITION BY City,

CASE WHEN @foreNames <> '' THEN @foreNames

ELSE

CASE WHEN @lastName <> '' THEN '' ELSE ForeNames END

END,

CASE WHEN @lastName <> ''  THEN @lastName

ELSE

CASE WHEN @foreNames <> '' THEN ''

ELSE

CASE WHEN @cityName <> '' THEN ''

ELSE LastName

END

END

END) as MAXCount

FROM tblPeople

WHERE ForeNames LIKE  

CASE WHEN @foreNames <> '' THEN @foreNames ELSE '%' END

AND LastName LIKE CASE WHEN @lastName <> '' THEN @lastName ELSE '%' END

AND City LIKE CASE WHEN @cityName <> '' THEN @cityName ELSE '%' END

)

SELECT * FROM CTE WHERE MAXCount > 1

Here, I have used Common Table Expression (but you can use temporary table to store the result of SELECT statement) before further narrowing down the resultset to exclude non-duplicates.

The point of interest in above T-SQL is how the field “MAXCount” gets generated.

COUNT(*) – indicates the total number of rows in each partition. This is what ultimately will be stored in “MAXCount” field.

Now, each partition is built using following values:

City >> Because we need to find duplicates within the same city always, so this column is an obvious choice in partition.

ForeNames >> Partition on this column depends upon @foreNames parameter.

·         If it is not supplied, and neither @lastName is supplied it will create partition by grouping rows having exact ForeNames.

·         If it is not supplied, but @lastName is supplied, it will ignore ForeNames column in partitioning.

·         If supplied, it will create partition by grouping all matching rows in a single partition.

LastName >> Partition on this column depends upon @lastName parameter.

·         If it is not supplied, and not even @foreNames, and @cityName, then it will create partition by grouping rows having same LastName.

·         If it is not supplied, but either @foreNames, or @cityName is supplied, it will ignore LastName column in partitioning.

·         If supplied, it will create partition by grouping all matching rows in single partition.

Get only Date part for SQL Server DateTime value

There may be various scenarios wherein you need only Date part of an SQL Server DateTime value, rather than full DateTime value.

This is very much a requirement in case of comparing dates (only date and no datetime).

SQL Server 2008 offers a new datatype DATE which holds only Date. 

So you can simply use Convert(Date, GetDate()) to convert your full DateTime into Date. Its as simple as that. 

But if you are using a version prior to SQL Server 2008, you need to do a workaround. There are lot many ways to achieve that. Following is one of them which is being used too frequently.

Say your DateTime value is "2012-04-20 21:45:00.938"

First convert date into a varchar in format such as (dd/mm/yyyy or mm/dd/yyyy) depending upon the regional settings of the SQL Server's host environment.

SELECT CONVERT(varchar(10), GETDATE(), 101) -- 101 stands for mm/dd/yyyy (mostly in case of US)

SELECT CONVERT(varchar(10), GETDATE(), 103) -- 103 stands for dd/mm/yyyy

So, it will result into - "04/20/2012", "20/04/2012" respectively

But this is a varchar type, so you need to convert it back to DateTime type to make it able to be used in Date time operations.

Use either of this depending upon the settings in SQL server's host environment.

SELECT CONVERT(DATETIME, CONVERT(varchar(10), GETDATE(), 101) )

or

SELECT CONVERT(DATETIME, CONVERT(varchar(10), GETDATE(), 103) )

This will now result to "2012-04-20 00:00:00.000"

Development and Deployment - Troubleshooting, Investigations (Series of articles)

At times, we, developers or deployment people do come across some technical problems that eat up our hours (and days sometimes).. and when we find the resolution, or real cause of the issue, it makes us feel that such issues were not worth of the time and efforts we spent.

Here, I am making a collection of such issues/ troubleshooting notes that may help us to take extra care in order to avoid that issues raising while we develop or deploy.

There are each new post for each different issue/ troubleshooting note, and this post is an index that will keep growing as I add new post in this category.

Keep checking this place regularly.

INDEX:

ASP.NET

1. ExecuteNonQuery() always returns -1

2. SelectedIndexChanged not firing up

ASP.NET MVC

1. Values do not retain in model while posting it from view to controller action in MVC

SQL Query to return each date of month in each different row

Sometimes we need a resultset in SQL that should list all dates of a particular month.
For example, you want to return a resultset containing total of all expenses done against each date. But for that your expense table not necessary to contain entry of each and every date.
There may be some dates for which there is no expense, and simply that date will not be in the expense table.
In such scenarios you would always need to have one resultset (virtual table) containing list of all dates, which you can use to join (Left Join, ideally) with your expenses table by dates to get your desired result.

This is possible using CTE by performing recursive SELECT.

Following is the complete SQL Query to generate list of all dates for a particular month:



Image: Resultset after executing below SELECT Query
P.S. - There are actually 31 rows, its just due to screen truncate it showing 27 in image.


DECLARE @MonthName VARCHAR(3)
DECLARE @Year VARCHAR(4)
DECLARE @MaxDay INT

SET @MonthName = 'MAR'
SET @Year = '2012'
SET @MaxDay = 31;

WITH CTE_MonthDays AS (
SELECT 1 AS fldDay,
CONVERT(datetime, '1/' + @MonthName + '/' + @Year) AS fldDate
UNION ALL
SELECT fldDay + 1,
CONVERT(Datetime, CONVERT(varchar(2), fldDay + 1) + '/' + @MonthName + '/' + @Year) AS fldDate
FROM CTE_MonthDays
WHERE
fldDay + 1 <= @MaxDay
)

SELECT fldDate FROM CTE_MonthDays

Optimizing SQL Code by replacing IF..ELSE with CASE WHEN

Many times, we come across a need of writing different SQL SELECT queries in a stored procedure, just to seperate the WHERE clause logic depending upon if the parameter value is passed or not.

Lets consider following case -
Your stored procedure sp_GetCities has a parameter @CountryID.
Now, if the parameter is not null then you want to filter Cities based on passed CountryID.
But, if the parameter is Null then you need to return all cities irrespective of their country.

On many occasions, we write following logic in stored proc.


IF @CountryID is null // Or, IF ISNULL(@CountryID, 0) <= 0
BEGIN
SELECT ....
---No WHERE condition to filter countries-----
END
ELSE
BEGIN
SELECT ....
WHERE CountryID = @CountryID
END


There is one workaround to avoid writing multiple SELECT queries in similar situations.

Use of CASE..WHEN in SELECT statement


SELECT city_id, city_name
FROM tblCities
WHERE
1 = CASE WHEN ISNULL(@country_id, 0) <= 0 THEN 1 ELSE CASE WHEN @country_id = country_id THEN 1 ELSE 0 END END


Let us understand the WHERE clause -
If the right-hand side of the expression returns 1 then the city will be returned in resultset, otherwise it wont be.

If "@country_id" is not passed i.e. ISNULL(@country_id, 0), then obviously it will return 1. And in this case, all cities will be selected, as 1=1 is always true.
If "@country_id" is passed then if it matches with city's country_id (i.e., country_id = @country_id) then it will return 1, otherwise it will be 0.

We can use this upto any level, so we can definitely reduce number of SELECT queries in our code.

SQL Query - To search for a column (by column name) in all tables/ views within a database

At times you need to know if a column of a specific name do exist in any table/ view of your SQL Server database.
In that case, we have certain options to browse throgh each table, or to generate script of all tables (in single go) and to do find operation. But its a time-consuming job.

As such, SQL Server stores information about any database object (i.e. column,index, table,SPs,etc) in system tables. So following SQL query can be very handy at times:


USE DatabaseName
select * from sys.objects where object_id in
(select object_id from sys.columns where name = @column_name)


This will return a resultset containing list of all tables which are having a column of the particular name. You can also perform wild-card search etc here.