Lesson Learnt - 2(PA Intel)

While Inserting Data to huge tables with many columns, always cross verify and check if the columns in the target table to which the data is getting inserted are always mapped with the correct input columns

Validate atleast 10 - 20 records after insertion

Translate Date to English Words

The Function to convert number to English words has been modified slightly to get th english words when date is given as an input.

Note: The function can still be optimized and some code can be reduced. This function is just a draft. When using this function in real time scenario some unused code has to be removed.


-- SQL user-defined function - UDF - SQL scalar-valued function - SQL Date to text

 

-- SQL user-defined function - UDF - SQL scalar-valued function - SQL Date to text

CREATE FUNCTION fnDateToWords(@date as varchar(20))

    RETURNS VARCHAR(1024)

AS

BEGIN

         DECLARE @DATE_CONVERTED DATETIME

         SET @DATE_CONVERTED = CAST(@date AS DATETIME)

         DECLARE @MonthName VARCHAR(100), @DAY INT, @MONTH INT, @YEAR INT

         SET @DAY = DAY(@DATE_CONVERTED)

         SET @YEAR = YEAR(@DATE_CONVERTED)

         SET @MONTH = MONTH(@DATE_CONVERTED)

         declare   @date_english varchar(1024)

      DECLARE @Below20 TABLE (ID int identity(0,1), Word varchar(32))

      DECLARE @Below100 TABLE (ID int identity(2,1), Word varchar(32))

              DECLARE @day_tbl TABLE (ID int identity(0,1), Word varchar(32))

      INSERT @Below20 (Word) VALUES

                        ( 'Zero'), ('One'),( 'Two' ), ( 'Three'),

                        ( 'Four' ), ( 'Five' ), ( 'Six' ), ( 'Seven' ),

                        ( 'Eight'), ( 'Nine'), ( 'Ten'), ( 'Eleven' ),

                        ( 'Twelve' ), ( 'Thirteen' ), ( 'Fourteen'),

                        ( 'Fifteen' ), ('Sixteen' ), ( 'Seventeen'),

                        ('Eighteen' ), ( 'Nineteen' )

       INSERT @Below100 VALUES ('Twenty'), ('Thirty'),('Forty'), ('Fifty'),

                               ('Sixty'), ('Seventy'), ('Eighty'), ('Ninety')

DECLARE @YEARNAME varchar(1024) =

(

  SELECT Case

    WHEN @YEAR = 0 THEN  ''

    WHEN @YEAR BETWEEN 1 AND 19

      THEN (SELECT Word FROM @Below20 WHERE ID=@YEAR)

   WHEN @YEAR BETWEEN 20 AND 99  

     THEN  (SELECT Word FROM @Below100 WHERE ID=@YEAR/10)+ '-' +

           dbo.fnNumberToWords( @YEAR % 10)

   WHEN @YEAR BETWEEN 100 AND 999  

     THEN  (dbo.fnNumberToWords( @YEAR / 100))+' Hundred '+

         dbo.fnNumberToWords( @YEAR % 100)

   WHEN @YEAR BETWEEN 1000 AND 999999  

     THEN  (dbo.fnNumberToWords( @YEAR / 1000))+' Thousand '+

         dbo.fnNumberToWords( @YEAR % 1000) 

          ELSE ' INVALID INPUT' END

)

SELECT @YEARNAME = RTRIM(@YEARNAME)

SELECT @YEARNAME = RTRIM(LEFT(@YEARNAME,len(@YEARNAME)-1))

                 WHERE RIGHT(@YEARNAME,1)='-'

Insert @day_tbl(Word) values

('Zero'), ('First'), ('Second'), ('Third'), ('Fourth'), ('Fifth'), ('Sixth'), ('Seventh'), ('Eighth'), ('Ninth'), ('Tenth'), ('Eleventh'), ('Thirteenth'), ('Fourteenth'), ('Fifteenth'),

('Sixteenth'), ('Seventeenth'), ('Eighteenth'), ('Ninteenth'), ('Twentieth')

DECLARE @DAYNAME VARCHAR(100)

=

(

       select case when @DAY = 30 THEN 'Thirtieth'

                     when @DAY = 31 then 'Thrity First'

                     when @day between 1 and 21

                        THEN (SELECT Word FROM @day_tbl WHERE ID=@day)

                     when @day between 21 and 30

                     then 'Tewenty '+ (SELECT Word FROM @day_tbl WHERE ID=@day)

                    

          ELSE ' INVALID DAY INPUT' END

       )

SELECT @MonthName  = DATENAME(month,@DATE_CONVERTED)

set @date_english = @DAYNAME + ' '+ @MonthName+', '+@YEARNAME

RETURN (@date_english)

END

GO

 /* Example

 SELECT DateInEnglish=dbo.fnDateToWords ('2012-01-02')

 */

Translate Numbers to Words

Use the below user - defined function to translate numbers to words:

-- SQL user-defined function - UDF - SQL scalar-valued function - SQL number to text

-- SQL numeric to words - integer to English - sql convert number to string

CREATE FUNCTION fnNumberToWords(@Number as BIGINT)

    RETURNS VARCHAR(1024)

AS

BEGIN

      DECLARE @Below20 TABLE (ID int identity(0,1), Word varchar(32))

      DECLARE @Below100 TABLE (ID int identity(2,1), Word varchar(32))

      INSERT @Below20 (Word) VALUES

                        ( 'Zero'), ('One'),( 'Two' ), ( 'Three'),

                        ( 'Four' ), ( 'Five' ), ( 'Six' ), ( 'Seven' ),

                        ( 'Eight'), ( 'Nine'), ( 'Ten'), ( 'Eleven' ),

                        ( 'Twelve' ), ( 'Thirteen' ), ( 'Fourteen'),

                        ( 'Fifteen' ), ('Sixteen' ), ( 'Seventeen'),

                        ('Eighteen' ), ( 'Nineteen' )

       INSERT @Below100 VALUES ('Twenty'), ('Thirty'),('Forty'), ('Fifty'),

                               ('Sixty'), ('Seventy'), ('Eighty'), ('Ninety')

DECLARE @English varchar(1024) =

(

  SELECT Case

    WHEN @Number = 0 THEN  ''

    WHEN @Number BETWEEN 1 AND 19

      THEN (SELECT Word FROM @Below20 WHERE ID=@Number)

   WHEN @Number BETWEEN 20 AND 99  

     THEN  (SELECT Word FROM @Below100 WHERE ID=@Number/10)+ '-' +

           dbo.fnNumberToWords( @Number % 10)

   WHEN @Number BETWEEN 100 AND 999  

     THEN  (dbo.fnNumberToWords( @Number / 100))+' Hundred '+

         dbo.fnNumberToWords( @Number % 100)

   WHEN @Number BETWEEN 1000 AND 999999  

     THEN  (dbo.fnNumberToWords( @Number / 1000))+' Thousand '+

         dbo.fnNumberToWords( @Number % 1000) 

   WHEN @Number BETWEEN 1000000 AND 999999999  

     THEN  (dbo.fnNumberToWords( @Number / 1000000))+' Million '+

         dbo.fnNumberToWords( @Number % 1000000)

   WHEN @Number BETWEEN 1000000000 AND 999999999999  

     THEN  (dbo.fnNumberToWords( @Number / 1000000000))+' Billion '+

         dbo.fnNumberToWords( @Number % 1000000000)

   WHEN @Number BETWEEN 1000000000000 AND 999999999999999  

     THEN  (dbo.fnNumberToWords( @Number / 1000000000000))+' Trillion '+

         dbo.fnNumberToWords( @Number % 1000000000000)

  WHEN @Number BETWEEN 1000000000000000 AND 999999999999999999  

     THEN  (dbo.fnNumberToWords( @Number / 1000000000000000))+' Quadrillion '+

         dbo.fnNumberToWords( @Number % 1000000000000000)

  WHEN @Number BETWEEN 1000000000000000000 AND 999999999999999999999  

     THEN  (dbo.fnNumberToWords( @Number / 1000000000000000000))+' Quintillion '+

         dbo.fnNumberToWords( @Number % 1000000000000000000)

        ELSE ' INVALID INPUT' END

)

SELECT @English = RTRIM(@English)

SELECT @English = RTRIM(LEFT(@English,len(@English)-1))

                 WHERE RIGHT(@English,1)='-'

RETURN (@English)

END

GO

 /* Example

 SELECT NumberInEnglish=dbo.fnNumberToWords ( 6050)

*/

Link:
http://www.sqlusa.com/bestpractices2008/number-to-words/
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/de10193c-5000-42ea-84fa-7bec509ce5ab

List of SQL Jobs which are currently running

Frequently we may want to know the list of jobs which are currently running on a server.
From the msdb..sysjobhistory we can get the details of the jobs which are completed either successfully or the jobs which are failed/cancelled.

To know the list of jobs currently running execute the below query:
exec msdb..sp_help_job @execution_status = 1

Auto Increase the data in the column of a Database

For Example, there is a Employee Table which has the column No_Of_Days_Worked.

Requirement is to increase the value of the column No_Of_Days_Worked by 1 in the database

2 Approaches can be used.

Approach 1:

Use the update statement and run the update statement as a daily job
UPDATE Employee_computedcol

SET No_Of_Days_Worked = No_Of_Days_Worked + 1
Approach 2:
Using  the computed column

CREATE TABLE Employee_computedcol (
EmpID INT IDENTITY(1,1) PRIMARY KEY,EmpName nvarchar(128) NOT NULL UNIQUE,DOJ DATE NOT NULL,No_Of_Days_Worked AS DATEDIFF(DD, DOJ, CURRENT_TIMESTAMP));
INSERT Employee_computedcol(EmpName, DOJ)VALUES('Test', '2012-10-01');
select * from Employee_computedcol
Value of No_Of_Days_Worked will be 7

Change the date and time settings of your computer and extend it to 2 more days(if the sql server is in your local machine).
No_Of_Days_Worked will be 9