Tuesday, September 25, 2012

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


Monday, September 24, 2012

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")))%>
                        
                    
                
            

Friday, September 21, 2012

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