Rounding Functions IN SQL Server, C# and JavaScript

/// SQL Query to Round the values in different format

 DECLARE @value decimal(18,5)
SET @value = 6.001
SELECT ROUND(@value, 1)
SELECT CEILING(@value)
SELECT FLOOR(@value)


/// C# code to Round the values in different format

decimal Value = Convert.ToDecimal(Console.ReadLine());
System.Console.WriteLine("RoundValue={0}\nCeilingValue={1}\nFloorValue={2}", Math.Round(Value),Math.Ceiling(Value),Math.Floor(Value));

/// JavaScript code to Round the values in different format

var ceilValue = Math.ceil(Value); 
var floorValue = Math.floor(Value);
var roundValue = Math.round(Value);
 

Sample Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE

I have not written about this subject for long time, as I strongly believe that Book On Line explains this concept very well. SQL Server 2005 has total of 4 ranking function. Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic.

ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of each row within the partition of a result set.

DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

NTILE (integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)
Distributes the rows in an ordered partition into a specified number of groups.

More Details:

http://blog.sqlauthority.com/2007/10/09/sql-server-2005-sample-example-of-ranking-functions-row_number-rank-dense_rank-ntile/ 

Difference between On clause and Where clause when used with left join in SQL Server

 Below Link has the Elaborate explanation. Please go through it.
http://www.codeproject.com/Articles/231132/Difference-between-On-clause-and-Where-clause-when

Use of PARSEONLY Keyword in SQL Server

Syntax:

SET PARSEONLY { ON | OFF }

* When SET PARSEONLY is ON, SQL Server only parses the statement.
* When SET PARSEONLY is OFF, SQL Server compiles and executes the statement.
The setting of SET PARSEONLY is set at parse time and not at execute or run time.
Do not use PARSEONLY in a stored procedure or a trigger. SET PARSEONLY returns offsets if the OFFSETS option is ON and no errors occur.

Examples


 Eg 1:   SET PARSEONLY ON;
            SELECT * FROM Lawrence_Setup
//This will parse the query. It wont compile and execute. It will check the syntax error only.


  Eg 2:  SET PARSEONLY OFF;
            SELECT * FROM Lawrence_Setup  
//This will compile and execute the query and also It will return the output vales based on the query

Using NOLOCK and READPAST table hints in SQL Server

//About Lock in SQL Server
URL-1:http://www.techrepublic.com/article/using-nolock-and-readpast-table-hints-in-sql-server/6185492

URL-2:http://blog.sqlauthority.com/2011/05/08/sql-server-what-kind-of-lock-with-nolock-hint-takes-on-object/

To filter the value from the Data Table

            DataSet DSDocumentationFee = SelectLeaseDocumentationFeeSlabDetails();
            DataTable DTDocumentationFee = DSDocumentationFee.Tables[0];
            DataRow[] rows = DTDocumentationFee.Select("LeaseCostFrom <= " + LeaseCost + " AND  LeaseCostTo >=" + LeaseCost);
            DocumentationFee = Convert.ToDouble(rows[0]["DocumentationFeeAmount"]);

To get the Date format in SQL Server

 Method:1
//SET DATEFORMAT mdy
Declare @DueDay varchar(10)           
Declare @Month varchar(10)
Declare @Year varchar(10)
DECLARE @date nvarchar(50)
Declare @CommenceDate varchar(11) 

SET @Month = '06';
SET @Year = '2012';
SET @DueDay = 15;
select @CommenceDate=CONVERT(varchar(11), Convert(varchar(2),@DueDay)+'/'+Convert(varchar(2),@Month)+'/' + Convert(varchar(4),@Year), 104)
print @CommenceDate

Method:2
CREATE FUNCTION [dbo].[FnDateTime]
(
@Date datetime,
@fORMAT VARCHAR(80)
)
RETURNS NVARCHAR(80)
AS
BEGIN
    DECLARE @Dateformat INT
    DECLARE @ReturnedDate VARCHAR(80)
    SELECT @DateFormat=CASE @format
    WHEN 'mm/dd/yyyy' THEN 101
    WHEN 'dd/mm/yyyy' THEN 103
    WHEN 'yyyy/mm/dd' THEN 111
    END
    SELECT @ReturnedDate=CONVERT(VARCHAR(80),@Date,@DateFormat)
RETURN @ReturnedDate
END

SELECT [dbo].[FnDateTime] ('8/7/2008', 'dd/mm/yyyy')

SELECT [dbo].[FnDateTime] ('8/7/2008', 'mm/dd/yyyy')

SELECT [dbo].[FnDateTime] ('8/7/2008', 'yyyy/mm/dd')

SELECT [dbo].[FnDateTime] ('8/7/2008', 'yyyy/mm/dd')

Declare @DueDay varchar(5)           
Declare @Month varchar(5)
Declare @Year varchar(5)
DECLARE @date nvarchar(50)
Declare @CommenceDate varchar(50)  

SET @Month = '06';
SET @Year = '2012';
SET @DueDay = 15;
select CONVERT(DATETIME, Convert(varchar(5),@DueDay)+'/'+Convert(varchar(5),@Month)+'/' + Convert(varchar(5),@Year), 104)
set @CommenceDate= [dbo].[FnDateTime] (CONVERT(DATETIME, Convert(varchar(5),@DueDay)+'/'+Convert(varchar(5),@Month)+'/' + Convert(varchar(5),@Year), 104),'dd/mm/yyyy')

print @CommenceDate

//More Detail you can use the below link
http://blog.sqlauthority.com/2008/08/14/sql-server-get-date-time-in-any-format-udf-user-defined-functions/

http://sandeep-tada.blogspot.in/2012/01/format-date-with-sql-server-function-in.html