Various where types

DECLARE @TEMP TABLE
                    (
                                        Name VARCHAR(50),
                                        Sex  CHAR(1)    ,
                                        Age  INT
                    )

INSERT INTO @Temp
SELECT 'Babu', 'M', 31 UNION ALL
SELECT 'Krishna', 'M', 31 UNION ALL
SELECT 'Kanagu', 'M', 31 UNION ALL
SELECT 'rathinam', 'M', 31 UNION ALL
SELECT 'Kumarasamy', 'M', 31 UNION ALL
SELECT 'Suseela', 'F', 31 UNION ALL
SELECT 'Venkatesh', 'M', 31

DECLARE @Name VARCHAR(50),
        @Sex  CHAR(1),
        @Age  INT,
        @Type VARCHAR(50)

Using boolean logic

SELECT * FROM @Temp
WHERE 1 = 1
AND (@Name IS NULL OR Name = @Name)
AND (@Sex IS NULL OR Sex = @Sex)
AND (@Age IS NULL OR Age = @Age)

SET @Name = 'BABU'

SELECT * FROM @Temp
WHERE 1 = 1
AND (@Name IS NULL OR Name = @Name)
AND (@Sex IS NULL OR Sex = @Sex)
AND (@Age IS NULL OR Age = @Age)

SET @Name = 'BABU'
SET @Sex = 'BABU'

SELECT * FROM @Temp
WHERE 1 = 1
AND (@Name IS NULL OR Name = @Name)
AND (@Sex IS NULL OR Sex = @Sex)
AND (@Age IS NULL OR Age = @Age)

Using boolean logic

SET @Type = 'NAME'

SELECT * FROM @Temp
WHERE (@Type = 'NAME' AND Name = @Name)
OR (@Type = 'SEX' AND Sex = @Sex)
OR (@Type = 'AGE' AND Age = @Age)

Using the Case expression

SET @Name = NULL
SET @Sex = NULL
SET @Age = NULL
SELECT * FROM @Temp
WHERE 1 = 1
AND Name = CASE WHEN @Name IS NOT NULL THEN @Name ELSE Name END
AND Sex = CASE WHEN @Sex IS NOT NULL THEN @Sex ELSE Sex END
AND Age = CASE WHEN @Age IS NOT NULL THEN @Age ELSE Age END

SET @Name = 'BABU'
SET @Sex = 'M'
SET @Age = NULL
SELECT * FROM @Temp
WHERE 1 = 1
AND Name = CASE WHEN @Name IS NOT NULL THEN @Name ELSE Name END
AND Sex = CASE WHEN @Sex IS NOT NULL THEN @Sex ELSE Sex END
AND Age = CASE WHEN @Age IS NOT NULL THEN @Age ELSE Age END

SET @Name = 'BABU'
SET @Sex = 'BABU'
SET @Age = NULL
SELECT * FROM @Temp
WHERE 1 = 1
AND Name = CASE WHEN @Name IS NOT NULL THEN @Name ELSE Name END
AND Sex = CASE WHEN @Sex IS NOT NULL THEN @Sex ELSE Sex END
AND Age = CASE WHEN @Age IS NOT NULL THEN @Age ELSE Age END

Using the ISNULL and NULLIF

SET @Name = NULL
SET @Sex = NULL
SET @Age = NULL
SELECT * FROM @Temp
WHERE 1 = 1
AND Name = ISNULL(NULLIF(@Name, NULL), Name)
AND Sex = ISNULL(NULLIF(@Sex, NULL), Sex)
AND Age = ISNULL(NULLIF(@Age, NULL), Age)

SET @Name = 'BABU'
SET @Sex = 'M'
SET @Age = NULL
SELECT * FROM @Temp
WHERE 1 = 1
AND Name = ISNULL(NULLIF(@Name, NULL), Name)
AND Sex = ISNULL(NULLIF(@Sex, NULL), Sex)
AND Age = ISNULL(NULLIF(@Age, NULL), Age)

SET @Name = 'BABU'
SET @Sex = 'BABU'
SET @Age = NULL
SELECT * FROM @Temp
WHERE 1 = 1
AND Name = ISNULL(NULLIF(@Name, NULL), Name)
AND Sex = ISNULL(NULLIF(@Sex, NULL), Sex)
AND Age = ISNULL(NULLIF(@Age, NULL), Age)

Execution plans

Get Currency Symbol by LanguageCode

Tutorial on using CultureInfo and RegionInfo

Creating multilingual websites

Code to set the culture and get the currency symbol for that culture.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Threading;
using System.Globalization;

/// 

/// Set the culture and get the currency symbol
/// 
public sealed class MyCulture
{
    public MyCulture()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    public void SetCulture(string CultureCode)
    {
        Thread.CurrentThread.CurrentCulture = 
            CultureInfo.CreateSpecificCulture(CultureCode);

        Thread.CurrentThread.CurrentUICulture = 
            new CultureInfo(CultureCode);
    }

    public string GetCurrencySymbol(string CultureCode)
    {
        SetCulture(CultureCode);

        CultureInfo UsersCulture = Thread.CurrentThread.CurrentCulture;
        RegionInfo myRegion = new RegionInfo(UsersCulture.LCID);

        return myRegion.CurrencySymbol;
    }
}

How to pass the input?

MyCulture obj = new MyCulture();
String symbol = obj.GetCurrencySymbol("ta-IN");

When we are passing the input culture is necessary.
i.e. we have to pass "ta-IN" or "ta" but not "IN".

List of CultureCodes
How to add currency symbol in the gridview?
We can do it both from the server side or from the client side.

//Setting currency symbol from the service side
        protected void grdWithCurrencySign_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow || e.Row.RowType == DataControlRowType.Header)
            {
                MyCulture obj = new MyCulture();
                string symbol = obj.GetCurrencySymbol("ta-in");

                e.Row.Cells[1].Text = symbol + " " + e.Row.Cells[1].Text;
            }
        }

        public string AddCurrency(string amount)
        {
            MyCulture obj = new MyCulture();
            return amount + " " + obj.GetCurrencySymbol("ta-in");
        }

                
                    
                    
                    
                        
                            <%# AddCurrency(Convert.ToString(Eval("Salary")))%>
                        
                    
                
            

GROUP BY and DISTINCT

DECLARE @Team TABLE(EventDate DATETIME, Seconds BIGINT)

INSERT INTO @Team
SELECT '2012-08-25',440 UNION ALL
SELECT '2012-08-25',1232 UNION ALL
SELECT '2012-08-25',1232 UNION ALL
SELECT '2012-08-25',1232 UNION ALL
SELECT '2012-08-25',1232 UNION ALL
SELECT '2012-08-25',1232 UNION ALL
SELECT '2012-08-25',1232 UNION ALL
SELECT '2012-08-25',7740 UNION ALL
SELECT '2012-08-25',18640 UNION ALL
SELECT '2012-08-25',18640 UNION ALL
SELECT '2012-08-25',18640 UNION ALL
SELECT '2012-08-25',512 UNION ALL
SELECT '2012-08-25',512 UNION ALL
SELECT '2012-08-25',512 UNION ALL
SELECT '2012-08-25',512 UNION ALL
SELECT '2012-08-25',512 UNION ALL
SELECT '2012-08-25',512

SELECT EventDate, Seconds FROM @Team

Sum the column Seconds for the group EventDate

SELECT EventDate, SUM(Seconds) Seconds
FROM @Team
GROUP BY EventDate

The value in the column Seconds is summed with the duplicate value.

How to sum the column without duplicate

SELECT EventDate, Sum(DISTINCT Seconds) Seconds 
FROM     @Team 
GROUP BY EventDate

SELECT   EventDate, Sum(Seconds) AS Seconds 
FROM     ( SELECT EventDate, Seconds 
                  FROM @Team 
                  GROUP BY EventDate, Seconds ) AS T 
GROUP BY EventDate

SELECT   EventDate, Sum(Seconds) AS Seconds 
FROM     ( SELECT DISTINCT EventDate, Seconds 
           FROM @Team ) AS T 
GROUP BY EventDate

Get only business days in a month in SQL SERVER

DECLARE @Start_Date DATETIME
SET @Start_Date = GETDATE()
SET @Start_Date = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@Start_Date)-1),@Start_Date),101)

DECLARE @Holiday TABLE(HoliDay DATETIME)

INSERT INTO @Holiday(HoliDay) VALUES('2012-08-15')
 
;WITH MonthDays(day_num, calendar_dt) AS
     ( SELECT 1                                        AS day_num,
             DATEADD(d, -DAY(@Start_Date)+1, @Start_Date ) AS calendar_dt
     
     UNION ALL
     
     SELECT day_num + 1,
            DATEADD(d, 1, calendar_dt)
     FROM   MonthDays
     WHERE  MONTH(DATEADD(d, 1, calendar_dt)) = MONTH(@Start_Date)
     )
--SELECT * FROM MonthDays 

/*
SELECT *, datename(dw, calendar_dt), (DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, calendar_dt), 0), calendar_dt) +1) as [Week]
FROM MonthDays
WHERE ((DATEPART(dw, calendar_dt) + @@DATEFIRST) % 7) NOT IN (1)
-- 0 for saturday, 1 for sunday

--WHERE ((DATEPART(dw, calendar_dt) + @@DATEFIRST) % 7) NOT IN (0, 1)
--http://msdn.microsoft.com/en-us/library/ms174420(v=sql.90).aspx
*/

SELECT day_num    ,
       calendar_dt,
       WeekDayName,
       [Week]
FROM  ( SELECT day_num                                 ,
               calendar_dt                             ,
               DATENAME(dw, calendar_dt)                                                            AS WeekDayName,
               (DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, calendar_dt), 0), calendar_dt) +1) AS [Week]
       FROM    MonthDays
       WHERE   ((DATEPART(dw, calendar_dt) + @@DATEFIRST) % 7 ) NOT IN (1)
       ) AS T
WHERE  ( WeekDayName != 'Saturday' 
   OR     [Week] NOT IN (1, 3, 5)
       )
       --To remove the holiday
AND    day_num NOT IN
       (SELECT DATEPART(DD, HoliDay)
       FROM    @Holiday
       )

Get only single record from a group with various conditons

• If an employee has both eligible and not eligible get the eligible record with min of Cal.
• If an employee has only eligible or not eligible get the record with min of Cal.
• For the above both conditions, we should get only one record for each employee.

DECLARE @Temp TABLE(EmpID VARCHAR(50), ISEligible CHAR(1), Cal INT)
        
        INSERT INTO @Temp(EmpID, ISEligible, Cal)
        SELECT '8829', 'Y', 10   UNION ALL
        SELECT '8829', 'N', 3    UNION ALL
        SELECT '8829', 'Y', 8    UNION ALL
        SELECT '1256', 'Y', 0    UNION ALL
        SELECT '1256', 'N', NULL UNION ALL
        SELECT '1111', 'N', NULL UNION ALL
        SELECT '1111', 'N', NULL UNION ALL
        SELECT '2222', 'Y', NULL UNION ALL
        SELECT '2222', 'Y', NULL
        
        SELECT * FROM @Temp
        
        SELECT *
        FROM  ( SELECT  ROW_NUMBER() OVER(PARTITION BY EmpID 
                   ORDER BY ISEligible DESC, Cal asc) RowNum,
                        EmpID     ,
                        ISEligible,
                        Cal
               FROM    @Temp
               )
               AS Temp
       WHERE  RowNum = 1

Creating custom sort rules for SQL Server

-- Create a Location table 
DECLARE @LOCATION TABLE 
  ( 
     ID           INT IDENTITY(1, 1), 
     LocationName VARCHAR(50) 
  ) 

-- Insert dummy data 
INSERT INTO @Location
SELECT 'Chitoor'  UNION 
SELECT 'Idappadi'  UNION 
SELECT 'Salem'   UNION 
SELECT 'Savadipalayam' UNION 
SELECT 'Sankri'   UNION 
SELECT 'Morasapatti' UNION 
SELECT 'Polampatti' 

-- SELECT with plain our custom sort rules 
SELECT * 
FROM   @Location 
ORDER  BY CASE LocationName 
            WHEN 'Salem' THEN 1 
            WHEN 'Idappadi' THEN 2 
            WHEN 'Chitoor' THEN 3 
            WHEN 'Savadipalayam' THEN 4 
            ELSE 5000 
          END 

The column value which have the sort order, will order by the sort value in ascending.
If the column value which didn't have no sort order will order first Try checking by removing the else part.
If we want to use like clause in the ORDER BY statement, then how?
Move the sort columnName from case to when

SELECT * 
FROM   @Location 
ORDER  BY CASE  
            WHEN LocationName = 'Salem' THEN 1 
            WHEN LocationName = 'Idappadi' THEN 2 
            WHEN LocationName LIKE '%C%' THEN 3 
            WHEN LocationName =  'Savadipalayam' THEN 4 
            ELSE 5000 
          END 

ISNULL + NULLIF instead of CASE WHEN

Difference between NULL and (Empty or ' ')
ISNULL() vs COALESCE()

ISNULL ( <check_expression>, <replacement_value>)
Replaces check_expression with the specified replacement_value, when check_expression is null

NULLIF(<expression>, <expression>)

• Returns a null value if the two specified expressions are equal.
• Returns the first expression if the two expressions are not equal.
• ReturnType is the type of the first expression
• The type of the first argument to NULLIF cannot be the NULL constant because the type of the first argument has to be known.

DECLARE @EmpID VARCHAR(50)
SELECT
       CASE
              WHEN @EmpID IS NULL
              THEN 'rkbabu'
              ELSE @EmpID
       END

SELECT ISNULL(NULLIF(@EmpID, NULL), 'rkbabu')

SELECT NULLIF(10,20) ------> 10
SELECT NULLIF(10, NULL) ------> 10
SELECT NULLIF(10, 10) -------> NULL
SELECT NULLIF('', '     ') ----------> NULL 
SELECT NULLIF('@', '@@') ------> @

SELECT NULLIF(NULL, 10) ------> ERROR -> None of the result expressions in a CASE specification can be NULL.
SELECT NULLIF(NULL, NULL) ------> ERROR -> None of the result expressions in a CASE specification can be NULL.

DECLARE @EmpID VARCHAR(50)
SELECT NULLIF(@EmpID, 10) ----------> NULL
SELECT NULLIF(@EmpID, @EmpID) ----------> NULL

SELECT CASE WHEN NULL = 10 THEN NULL ELSE 10 END ------> 10
SELECT CASE WHEN 10 = NULL THEN NULL ELSE 10 END ------> 10
SELECT CASE WHEN NULL = NULL THEN NULL ELSE 10 END ------> 10 Since one null not equal to another null

Consider some time we will use case in the where condition, this can be replaced with
ISNULL and NULLIF function

DECLARE @Column1 INT, @Column2 INT

SET @Column1 = -1;

SELECT *
FROM tableName
WHERE Column1 = CASE WHEN @Column1 = -1 THEN Column1  ELSE @Column1 END
AND Column2 = CASE WHEN @Column2 IS NOT NULL THEN @Column2 ELSE Column2 END

SELECT *
FROM tableName
WHERE Column1 = ISNULL(NULLIF(@Column1, -1), Column1)
AND Column2 = ISNULL(NULLIF(@Column2, NULL), Column2)