RESTRICT MANUAL DATE INPUT USING JAVASCRIPT EVENTS

To simply restrict manual date input in a asp:textbox that shows a popup calendar use 

onKeyPress = "javascript: return false;" onPaste = "javascript: return false;"           onCut="javascript: return false;"

(SQL SERVER) PIVOT WITH DYNAMIC COLUMNS

Pivot is generally used in creating reports that converts table row values into column names. There are two ways of creating a pivot table. One way is to manually specify the column names that we want and create the desired output. Let me explain it with an example table named Products which when executed creates an output,

                         

Now, we need to create a table that displays the ProductName values into column names and prices of that particular product as the row values. So the query to be executed is,

SELECT [Milk],[Fresh Juice], [Horlicks], [Boost], [Complan] FROM

(SELECT ProductName, Price FROM dbo.Products) prod

PIVOT (SUM(price) FOR prod.ProductName

IN ([Milk],[Fresh Juice], [Horlicks], [Boost], [Complan])) AS pvt

GROUP BY [Milk],[Fresh Juice], [Horlicks], [Boost], [Complan]

The output for above executed query will be,

This will be useful when we want to create a table with a desired name of columns. Another way to create a pivot table is by using dynamic columns i.e., all the values of particular column should be converted into column names. Let’s create it by using two different methods.

First Method,

DECLARE @cols NVARCHAR(2000)                         

DECLARE @output VARCHAR(MAX)

SELECT @cols = COALESCE(@cols + ',[' + ProductName + ']','[' + ProductName + ']')

FROM dbo.Products

SET @output = N'SELECT ' + @cols + ' FROM

(SELECT ProductName, Price FROM dbo.Products) prod

PIVOT (SUM(price) FOR prod.ProductName

IN ( ' + @cols + ' )) AS pvt'

EXEC (@output)

Executing the above query produces the output,

This method uses coalesce to generate the comma separated values of ProductName column and assigns the price value to the particular column.

Second Method,

DECLARE Products CURSOR FOR

SELECT ProductName FROM dbo.Products

DECLARE @ProductName NVARCHAR(MAX),

           @Prod VARCHAR(50),

           @Output NVARCHAR(MAX)

SET @Prod = ''

OPEN Products

FETCH NEXT FROM Products INTO @ProductName

WHILE @@FETCH_STATUS = 0

BEGIN

 SET @Prod = @Prod + '[' + @ProductName + '],'

 FETCH NEXT FROM Products INTO @ProductName

END

CLOSE Products

DEALLOCATE Products

SET @Prod = SUBSTRING(@Prod, 0, LEN(@Prod))

SET @Output = 'SELECT ' + @Prod + ' FROM

(SELECT ProductName, Price FROM dbo.Products) prod

PIVOT

(SUM(Price) for ProductName in ('+ @Prod +')) AS pvt'

PRINT @Output

EXEC sp_executesql @Output

Executing the above query produces the same output as the first method. This method uses CURSOR to generate the comma separated values of ProductName column. Hope this helps. :)

   

(SQL SERVER) ROW_NUMBER (), RANK () AND DENSE_RANK ()

              

              

                Generating serial numbers is made easy by the use of in-built functions Row_number (), Rank () and Dense_Rank () in SQL SERVER. Let’s understand the use of these functions with a simple example.

Row_Number()

Row_number() is used to generate serial numbers in sequential order. It outputs the result by including the column names in the ORDER BY clause. Based upon the column, it will generate the result.

Let’s create an example table first and insert some dummy values into it,

CREATE TABLE Products

(

ProductId INT IDENTITY(1,1),

ProductName VARCHAR(50),

Price INT,

ProductRating INT

)

Go

INSERT INTO Products

VALUES ('Milk', 15, 5 ),

       ('Fresh Juice', 20, 4 ),

       ('Horlicks', 200, 2 ),

       ('Boost', 200, 3 ),

       ('Complan', 300, 3 )

GO

SELECT * FROM Products

When executing the select query produces the below output,

If we want to add serial numbers along with the output, the SQL statement to be used is,

SELECT ProductId,ProductName,Price,ProductRating,

ROW_NUMBER() OVER (ORDER BY ProductId) AS _Row_number FROM Products

The above SQL statement will produce an output,

It generates running serial numbers by including ProductId in the ORDER BY clause. Let’s say, if we want to generate serial numbers based upon the Price column then the output will be different. To understand it further execute the below code to view the output.

SELECT ProductId,ProductName,Price,ProductRating,

ROW_NUMBER() OVER (PARTITION BY Price ORDER BY ProductId) AS _Row_number FROM Products

As you can see, the serial numbers is changed to display according to  the price column.It is achieved by including the column name price in the PARTITION BY. The PARTITION BY acts similar to the GROUP BY to generate the serial numbers.

Rank()

Rank()  function is used to assign rank based on a particular column. For example, if there are two products that share the same price then the rank generated will be same let’s say 1, where in the third product with some other value will generate the rank 3 instead of 2. To understand it better execute the below SQL query and view the output.

SELECT ProductId,ProductName,Price,ProductRating,

RANK() OVER (ORDER BY Price) AS _Rank FROM Products

           

                As you can see, the products with price 200 generates the rank 3 but the next price generates 5 instead of 4.

Dense_Rank()

            Dense_Rank() function is similar to Rank() with one difference that if in rank() the number generated for two products sharing same value is 3, then the next product will have rank number 5, but Dense_Rank() generates the next number to the previous number. Lets understand this with executing the select query,

SELECT ProductId,ProductName,Price,ProductRating,

DENSE_RANK() OVER (ORDER BY Price) AS _Dense_Rank FROM Products

The above SQL statement produces an output,

The products with price 200 generates the rank 3 whereas the next product generates the next number 4. This function will be useful when trying to retrieve highest to lowest ranks as 1^st, 2^nd, 3^rd , etc. Accordingly, we can use PARTITION BY to filter the Rank() and Dense_Rank() further.

(SQL SERVER) WHAT IS NEWID()? USE OF NEWID()?

SQL NEWID() is a T-SQL function that returns type “uniqueidentifier”. It is very useful in generating random numbers and random records from a sql table.

To generate random numbers,

DECLARE @myid uniqueidentifier

SET @myid = NEWID()

PRINT 'Value of @myid is: '+ CONVERT(varchar(255), @myid)

OUTPUT

Value of @myid is: E05F972A-51E3-41AF-AC08-C8009FC0BF5F

The above sql statement results in a random uniqueidentifier value whereas the sql statement,

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [NewID]

OUTPUT

NEWID

1737359308

Results in an integer value (note that the values will be different everytime the query is executed). To generate values with the desired no. of digits, we can use the RAND() function with the NEWID() as the seed parameter.

SELECT CAST(RAND(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))) * 100 AS INT) AS [Rand]

OUTPUT

Rand

33

To generate random records,

Random records can be retrieved from a sql table by including the NEWID() function in the ORDER BY clause in the select statement.

SELECT * FROM TABLENAME ORDER BY NEWID()

To make use of the function further, we can retrieve desired number of random records by including the TOP clause.

SELECT TOP 5 * FROM TABLENAME ORDER BY NEWID()

(SQL SERVER) CONVERT SQL DATES TO DESIRED DATE FORMAT

SELECT CONVERT(VARCHAR, GETDATE(), 0)   -- Mar 23 2012  3:51PM

SELECT CONVERT(VARCHAR, GETDATE(), 100) -- Mar 23 2012  3:51PM

SELECT CONVERT(VARCHAR, GETDATE(), 101) -- 03/23/2012

SELECT CONVERT(VARCHAR, GETDATE(), 102) -- 2012.03.23

SELECT CONVERT(VARCHAR, GETDATE(), 103) -- 23/03/2012

SELECT CONVERT(VARCHAR, GETDATE(), 104) -- 23.03.2012

SELECT CONVERT(VARCHAR, GETDATE(), 105) -- 23-03-2012

SELECT CONVERT(VARCHAR, GETDATE(), 106) -- 23 Mar 2012

SELECT CONVERT(VARCHAR, GETDATE(), 107) -- Mar 23, 2012

SELECT CONVERT(VARCHAR, GETDATE(), 108) -- 15:52:23

SELECT CONVERT(VARCHAR, GETDATE(), 109) -- Mar 23 2012 3:52:35:097PM

SELECT CONVERT(VARCHAR, GETDATE(), 110) -- 03-23-2012

SELECT CONVERT(VARCHAR, GETDATE(), 111) -- 2012/03/23

SELECT CONVERT(VARCHAR, GETDATE(), 112) -- 20120323

SELECT CONVERT(VARCHAR, GETDATE(), 113) -- 23 Mar 2012 15:53:23:143

SELECT CONVERT(VARCHAR, GETDATE(), 114) -- 15:53:31:690

SELECT CONVERT(VARCHAR, GETDATE(), 120) -- 2012-03-23 15:53:31

SELECT CONVERT(VARCHAR, GETDATE(), 121) -- 2012-03-23 15:53:31.690