Time difference between two columns in sql server

Suppose our requirement is to calculate working hours for Employee in particular company.
Below example is shows the how to create the table and update the Working hours for every employee.


USE tempdb
GO

/*****************************************
Copyright      :
Author           : Raju M
Date              : 12/22/2011
Module         :
Desc              : Script to Create EmpTime table.

Change History :
Name        Date        Description

SELECT * FROM EmpTime

******************************************/

IF NOT EXISTS(SELECT * FROM sys.objects WHERE TYPE= 'U' AND Name = 'EmpTime')
BEGIN
    /*    Creating the EmpTime Table    */
    CREATE TABLE EmpTime
    (
        EmployeeID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
        StartingTime DATETIME NOT NULL,
        FinishingTime DATETIME NOT NULL,
        DeductBreakMM BIGINT NOT NULL,
        WorkingTime TIME(3) NULL
    )

    /*    Inserting the Data into EmpTime Table    */
    INSERT INTO EmpTime
        (
            StartingTime,
            FinishingTime,
            DeductBreakMM
        )

    SELECT '2011-12-22 09:58:07.943' AS StartingTime,
           '2011-12-22 19:18:07.003' AS FinishingTime,
           45 DeductBreakMM

    UNION ALL

    SELECT '2011-12-22 08:28:07.943' AS StartingTime,
           '2011-12-22 21:18:07.003' AS FinishingTime,
           51 DeductBreakMM


    /*    Updating the EmpTime Column in EmpTime Table    */
    UPDATE EmpTime
    SET WorkingTime = CAST(DATEADD(SS,(DATEDIFF(SS,StartingTime, FinishingTime)) - (DeductBreakMM*60),0) AS TIME(3))

END
GO

SQL Server 2012 (Denali)

New Features in SQL Server 2012 - RC0 Released

Microsoft SQL Server 2012 RC0 was recently released. RC stands for Release Candidate which is basically the version virtually production ready. Microsoft referred to this release as SQL Server Code Named "Denali" but has settled on SQL Server 2012 as the final name for the product.. Business intelligence (BI) is critically important to organizations both large and small. In the latest release of SQL Server, there is no shortage of BI enhancements in addition to many other enhancements. This article will give you a preview of the requirements, new features and enhancements in SQL Server 2012 (code named Denali) including:

• Hardware and Software Requirements
• Multi-Subnet Failover Clustering
• Programming Enhancements, including sequences, ad-hoc query paging and full-text search tweaks
• BI and Web Development Environment Improvements
• Web-based Visualization
• Data Quality Services

Keep in mind that this information is for preview only and is subject to change by Microsoft.

Hardware and Software Requirements

• Microsoft recommends using NTFS file format instead of FAT32. FAT32 will work but you should probably not use it.
• You can't install SQL Server 2012 (code-named Denali) on mapped drives or compressed drives.
• You have to have the "no-reboot" package installed prior to installing SQL Server 2012 (code-named Denali). This is included in Windows 7 SP1 and Windows Server 2008 R2. Otherwise, you can download the no-reboot package from Microsoft.
• SQL Server 2012 (code-named Denali) requires the .NET Framework 4.0.
• Virtualization is supported using Microsoft's Hyper-V technology.
• You will need at least 3.6 GB of free disk space.
• Microsoft recommends that you do not install SQL Server 2012 (code-named Denali) on a domain controller.
• Recommended Processors & RAM
  • 64-bit version: AMD Opteron, AMD Athlin 64, Intel Xeon with Intel EM64T Support or Intel Pentium IV with EM64T support running 2.0 GHz or faster. Recommended RAM is maximum that operating system supports or at least 2 GB.
  • 32-bit version: Pentium III or compatible running at 2.0 GHz of faster. Recommended RAM is maximum that operating system supports or at least 2 GB.
• Windows PowerShell 2.0 is a pre-requisite for installing SQL Server 2012 (code-named Denali). You can get this software from the Windows Management Framework page.
• Check out the step by step installation guide with screenshots to get a preview of the SQL Server 2012 install and configuration process.

Multi-Subnet Failover Clustering

With SQL Server 2012 (code-named Denali), you can configure SQL Server where failover cluster nodes can be connected to a completely different subnet. The subnets can be spread out to different geographical locations providing disaster recovery along with high availability. In order for this to work correctly, you will need to replicate the data across the databases involved in this configuration. The SQL Server failover cluster is dependent on the Windows Server failover cluster so this has to be set up first. Keep in mind that all of the subnets involved in this configuration must be in the same Active Directory domain.

Programming Enhancements

• Sequences: Sequences have been requested by the SQL Server community for years, and it's included in this release. Sequence is a user defined object that generates a sequence of a number. Here is an example using Sequence.

  /****** Create Sequence Object ******/
  CREATE SEQUENCE MySequence
  START WITH 1
  INCREMENT BY 1;
  /****** Create Temp Table ******/
  DECLARE @Person TABLE
  (
  ID int NOT NULL PRIMARY KEY,
  FullName nvarchar(100) NOT NULL
  );
  /****** Insert Some Data ******/
  INSERT @Person (ID, FullName)
  VALUES (NEXT VALUE FOR MySequence, 'Jim Johnson'),
  (NEXT VALUE FOR MySequence, 'Bob Thompson'),
  (NEXT VALUE FOR MySequence, 'Tim Perdue');
  /****** Show the Data ******/
  SELECT * FROM @Person;
  The results would look like this:
  ID FullName
  1 Jim Johnson
  2 Bob Thompson
  3 Tim Perdue
  

  • Things a Developer Needs to Know About Sequences in SQL Server 2012 (code-named Denali)
• Ad-Hoc Query Paging: Paging results in SQL Server has been discussed for years. The Order By option in the SQL SELECT statement has been enhanced in SQL Server 2012. Using a combination of OFFSET and FETCH along with ORDER BY gives you control of paging through a result set. Using this technique can really help performance by bring back only the results you want to show to your users when they are needed. The following TSQL code runs against the Person table in the AdventureWorks sample database (available from Microsoft). In the sample query below, SQL Server would return 10 records beginning with record 11. The OFFSET command provides a starting point for the SELECT statement in terms of paging, and the FETCH command provides how many records to return at a time.

  SELECT BusinessEntityID, FirstName, LastName
  FROM Person.Person
  ORDER BY BusinessEntityID
  OFFSET 10 ROWS
  FETCH NEXT 10 ROWS ONLY;
  

• Full Text Search: The Full Text Search in SQL Server 2012 has been enhanced by allowing you to search and index data stored in extended properties or metadata. Consider a PDF document that has "properties" filled in like Name, Type, Folder path, Size, Date Created, etc. In the newest release of SQL Server, this data could be indexes and searched along with the data in the document itself. The data does have to be exposed to work, but it's possible now.
  

BI and Web Development Environment Improvements
Microsoft moved BI (Business Intelligence) closer to the end user with SQL Server 2008 R2. The Excel PowerPivot tool help users by creating a self-service reporting model. To understand the impact of SQL Server 2008 R2, check out the About.com article Why SQL Server 2008 R2 Matters to Small Business. The good news is PowerPivot is being enhanced in SQL Server 2012 (code-named Denali). Microsoft is adding KPIs and drill through, which will be really useful for all users.
Analysis Services will include a new BI Semantic Model (BISM). BISM is a 3-layer model that includes:

• Data Model
• Business Logic
• Data Access

BISM will enhance Microsoft's front end analysis experiencing including Excel, Reporting Services and SharePoint Insights. Microsoft has said that BISM is not a replacement for the current BI Models but more of an alternative model. In simple terms, BISM is a relation model that includes BI artifact such as KPIs and hierarchies.
Web Based Visualization - Project Crescent
Project Crescent is the Microsoft code name for the new reporting and visualization tool expected in SQL Server 2012 (code-named Denali). Project Crescent provides drag and drop ad-hoc reporting functionality and was built entirely on Silverlight. It includes a powerful query tool and interactive storyboarding to allow a user to share visualizations of large datasets.

Read my article Project Crescent Reporting and Visualization in SQL Server 2012 (code-named Denali) for more on Crescent.

Data Quality Services
Data Quality Services is a knowledge-based approach that runs in SSIS (SQL Services Integration Services). Data quality is one of those things that you never get perfect. Microsoft is introducing "Impact Analysis and Lineage" which will give you information about what your data depends on. It also shows the lineage of the data, including where it comes from and the systems that are behind it.

SQL Server 2012 Resources (SQL Server 2011/Denali)

• Installation Guide With Image Gallary
• A Guide to Sequences in SQL Server 2012
• Project Crescent - Reporting and Visualization in SQL Server 2012 (Denali)

SQL Server 2011 Downloads

• Download SQL Server 2012 - Code Names Denali
• Adventureworks Sample Database for SQL Server 2012
• SQL Server 2012 - Books Online

Related Articles

• Introduction to Microsoft SQL Server 2000
• Excel Front End to SQL Server
• SQL Server 2000 SP1 Released
• Creating Tables in Microsoft SQL Server

How to Encrypt/Decrypt the passward in SQL Server..?

DECLARE @Name VARCHAR(100) = 'RAJU'
DECLARE @EncName VARCHAR(500)

SELECT  EncryptByPassphrase('A',@Name) EncryptByPassphrase

OutPut
======
0x010000001AB718D8402648B2CCD2C46DACFCDF7844ADD7243B8C9ECA


SELECT @EncName = EncryptByPassphrase('A',@Name)

SELECT CAST(DecryptByPassphrase('A', @EncName) AS VARCHAR(100)) DecryptByPassphrase

OutPut
======
RAJU

SCRIPT TO CHECK QUERY

Below query to check the current running SQL statement for a Stored Procedure.

All you need to do is, give the ‘SPID’ of the session in which the Stored Procedure is running.

SELECT SDER.[session_id], SDER.[request_id],SDER.[statement_start_offset],  
  SDER.[statement_end_offset], 
  CASE  
     WHEN SDER.[statement_start_offset] > 0 THEN 
        --The start of the active command is not at the beginning of the full command text
        CASE SDER.[statement_end_offset] 
           WHEN -1 THEN 
              --The end of the full command is also the end of the active statement
              SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, 2147483647)
           ELSE  
              --The end of the active statement is not at the end of the full command
              SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, (SDER.[statement_end_offset] - SDER.[statement_start_offset])/2)  
        END 
     ELSE 
        --1st part of full command is running
        CASE SDER.[statement_end_offset] 
           WHEN -1 THEN 
              --The end of the full command is also the end of the active statement
              RTRIM(LTRIM(DEST.[text])) 
           ELSE 
              --The end of the active statement is not at the end of the full command
              LEFT(DEST.TEXT, (SDER.[statement_end_offset]/2) +1) 
        END 
     END AS [executing statement], 
  DEST.[text] AS [full statement code] 
FROM sys.[dm_exec_requests] SDER CROSS APPLY sys.[dm_exec_sql_text](SDER.[sql_handle]) DEST 
WHERE SDER.session_id = 65
ORDER BY SDER.[session_id], SDER.[request_id]

INTRODUCTION TO SQL SERVER

SQL Server is a client-server Relational Database Management System developed by Microsoft Corporation. A client-server RDBMS is an RDBMS that can be shared. SQL Server is the RDBMS chosen as back end by most of the developers on Windows operating system. This is because

1. It is tightly integrated with Windows
2. It is easy to use as it provides wizards to perform various tasks like object creation, database tuning, backup etc.,
3. It scales from mobile laptop to symmetric multi-processor systems.
4. It provides business intelligence concepts, which are until now available only in oracle and other expensive DBMS’s.
5. It is a bundle of at least four products Relational Database Engine, Analysis Services, Reporting Services and Integration Services. Relational Database Engine is used to work with database and database objects, analysis service is used to work with business intelligence concepts like data warehousing and data mining. Reporting Services is used to create and deploy enterprise reports and Integration Services is used to integrate data from different types of data sources to SQL Server.

History Of SQL Server

            SQL Server was developed and implemented by Sybase Corporation. In 1988, Microsoft licensed SQL Server for OS/2 operating system and began developing it for windows in early 1990. At almost the same time, further development of SQL Server for OS/2 has been cancelled. In 1994, Microsoft cancels the agreement with Sybase and starts developing SQL Server for windows. Since then several versions of SQL Server are released and the new version in the market is SQL Server 2005.

10 reasons why SQL Server 2008 is going to rock

10.  Plug-in model for SSMS.   SSMS 2005 also had a plug-in model, but it was not published, so the few developers that braved that environment were flying blind.  Apparently for 2008, the plug-in model will be published and a thousand add-ins will bloom. 

9.  Inline variable assignment.  I often wondered why, as a language, SQL languishes behind the times.  I mean, it has barely any modern syntactic sugar.  Well, in this version, they are at least scratching the the tip of the iceberg. 

Instead of:

DECLARE @myVar int

SET @myVar = 5

you can do it in one line:

DECLARE @myVar int = 5

Sweet.

8.  C like math syntax.  SET @i += 5.  Enough said.  They finally let a C# developer on the SQL team. 

7.  Auditing.  It's a 10 dollar word for storing changes to your data for later review, debugging or in response to regulatory laws.  It's a thankless and a mundane task and no one is ever excited by the prospect of writing triggers to handle it.  SQL Server 2008 introduces automatic auditing, so we can now check one thing off our to do list.

6.  Compression.  You may think that this feature is a waste of time, but it's not what it sounds like.  The release will offer row-level and page-level compression.  The compression mostly takes place on the metadata.  For instance, page compression will store common data for affected rows in a single place. 

The metadata storage for variable length fields is going to be completely crazy: they are pushing things into bits (instead of bytes).  For instance, length of the varchar will be stored in 3 bits. 

Anyway, I don't really care about space savings - storage is cheap.  What I do care about is that the feature promised (key word here "promises") to reduce I/O and RAM utilization, while increasing CPU utilization.  Every single performance problem I ever dealt with had to do with I/O overloading.  Will see how this plays out.  I am skeptical until I see some real world production benchmarks.

5.  Filtered Indexes.  This is another feature that sounds great - will have to see how it plays out.  Anyway, it allows you to create an index while specifying what rows are not to be in the index.  For example, index all rows where Status != null.  Theoretically, it'll get rid of all the dead weight in the index, allowing for faster queries. 

4.  Resource governor.  All I can say is FINALLY.  Sybase has had it since version 12 (that's last millennium, people).  Basically it allows the DBA to specify how much resources (e.g. CPU/RAM) each user is entitled to.  At the very least, it'll prevent people, with sparse SQL knowledge from shooting off a query with a Cartesian product and bringing down the box.

Actually Sybase is still ahead of MS on this feature.  Its ASE server allows you to prioritize one user over another - a feature that I found immensely useful.

3.  Plan freezing.  This is a solution to my personal pet peeve. Sometimes SQL Server decides to change its plan on you (in response to data changes, etc...).  If you've achieved your optimal query plan, now you can stick with it.  Yeah, I know, hints are evil, but there are situations when you want to take a hammer to SQL Server - well, this is the chill pill.

2.  Processing of delimited strings.   This is awesome and I could have used this feature...well, always.  Currently, we pass in delimited strings in the following manner:

exec sp_MySproc 'murphy,35;galen,31;samuels,27;colton,42'

Then the stored proc needs to parse the string into a usable form - a mindless task.

In 2008, Microsoft introduced Table Value Parameters (TVP). 

CREATE TYPE PeepsType AS TABLE (Name varchar(20), Age int)

DECLARE @myPeeps PeepsType

INSERT @myPeeps SELECT 'murphy', 35

INSERT @myPeeps SELECT 'galen', 31

INSERT @myPeeps SELECT 'samuels', 27

INSERT @myPeeps SELECT 'colton', 42

exec sp_MySproc2 @myPeeps

And the sproc would look like this:

CREATE PROCEDURE sp_MySproc2(@myPeeps PeepsType READONLY) ...

The advantage here is that you can treat the Table Type as a regular table, use it in joins, etc.  Say goodbye to all those string parsing routines.

1. Intellisense in the SQL Server Management Studio (SSMS).  This has been previously possible in SQL Server 2000 and 2005 with use of 3rd party add-ins like SQL Prompt ($195).  But these tools are a horrible hack at best (e.g. they hook into the editor window and try to interpret what the application is doing). 

 

Built-in intellisense is huge - it means new people can easily learn the database schema as they go.

Built In Functions

Built In Functions

            As in other languages like C and C++, SQL also provides Built in functions. Built in functions in SQL are classified into following two categories.

1.      Scalar Functions

2.      Aggregate Functions

3.      Ranking Functions

I.                   Scalar Functions : The built in functions in SQL Server that work on a single value are called as scalar functions. Scalar functions are again classified into following categories based on data type on which they work.

a)      Numeric Functions

b)      String Functions

c)      Date Functions

d)     Conversion Functions

e)      System Functions

a)       Numeric Functions : The scalar functions of SQL that work on numeric type of data are called as numeric functions. Numeric functions in SQL are as follows.

1. Abs(n) : Returns absolute value of the given n i.e. n value without any sign.
2. Cos(n) : Returns cosine value of given n in radians.
3. Ceiling(n) : Returns smallest integer greater than or equal to n.
4. Floor(n) : Returns Largest integer less than or equal to n.
5. Radians(n) : Converts the given n in degrees to radians.
6. Degrees(n) : Converts the given n in radians to degrees.
7. Exp(n) : Returns e raised to the power of n. e is exponential whose value is constant and is 2.713
8. Log(n) : Returns natural logarithm of n. i.e. base e logarithm.
9. Log10(n) : Returns base 10 logarithm of n.
10.  Power(m,n) : Returns m raised to the power of n.
11. Square(n) : Returns Square of given n.

1. Sqrt(n) : Returns Square Root of given n.
2. Sin(n) : Returns sine value of given n in radians.
3. Tan(n) : Returns tangent value of given n in radians.
4. Sign(n) : Return -1 if n is negative, 1 if n is positive and 0 if n is zero.
5. Pi() : Return mathematical constant pi value i.e. 3.14
6. Round(n,p,t) : Rounds the given in n to p decimal places. If t is specified as 1 then it truncates n to p decimal places instead of round the value.

b)      String Functions : The built-in functions of SQL that work on string type of data are called as string functions. String functions in SQL are as follows.

1. ASCII(char) : Converts the specified character to the equivalent ascii code.
2. Char(int) : Converts ascii code to the equivalent character.
3. CharIndex(s1,s2) : returns the starting position where the partial string s1 first occurs in the string s2. Returns zero if s1 does not occur in s2.
4. Lower(S1) : Converts all uppercase letters of the string s1 to lower case letters.
5. Upper(s1) : Converts all lowercase letters of the string s1 to upper case letters.
6. Ltrim(s1) : removes leading blank spaces in the string s1.
7. Rtrim(S1) : Removes trailing blank spaces in the string s1.
8. Reverse(S1) : Reverses the string S1.
9. Right(S1,Len) : returns last Len characters from the string S1.
10. Left(S1,Len) : returns first len characters from the string S1.
11. Soundex(a) : Returns how the given string is pronounced in a four character soundex code.
12. Stuff(s1,a,len,s2) : Replaces the partial string s1 with the partial string s2 starting at position a, replacing len characters of s1.
13. Substring(S1,a,len) : creates a partial string from string s1 starting at the position a with a length of len.

c)       Date Functions : The built-in functions of SQL that work on date type of data are called as date functions and date functions in SQL server are as follows.

1. GetDate() : Returns the current system date and time.
2. DatePart(item,D) : returns the specified part item of a date D as an integer.
3. DateName(item,D) : returns the specified part item of the date D as a string.
4. DateDiff(item,D1,D2) : calculates the difference between the two date parts D1 and D2 and returns the result as an integer in units specified by the value item.
5. DateAdd(item,n,D) : Adds the number n of units specified by the value item to the given date D.

d)      Conversion Functions :  The Built-In functions of SQL Server that are used to convert one type of data to another are called as conversion functions and conversion functions in SQL Server are as follows.

1. Cast(a as type[(length)]) : converts an expression a into the specified data type type.
2. Convert(type[(length}],a) : Equivalent to cast, but the arguments are specified differently.

e)       System Functions : The SQL Server system functions provide extensive information about database objects.

1. Object_ID(objname) : returns the identifier of the database object objname.
2. Object_name(obj_id) : returns the name of the database object with id obj_id.
3. Col_Name(Obj_Id,S_Id) : returns the name of the column belonging to the object obj_id with the identifier s_id.
4. Col_Length(obj,Col) : returns the length of the column belonging to a database object obj.
5. Datalength(s1) : calculates the length of the result of the expression s1.
6. DB_Id(Db_name) : returns the id of the database db_name.
7. DB_Name(Db_id) : returns the name of the database with id Db_id.
8. User_Id(username) : returns the id of specified user name.
9. user_name(uid) : returns the name of the user with id uid.
10. Current_TimeStamp : returns the current date and time.

II.                Ranking functions

1.      Row_number: Displays row numbers

Select row_number() over (order by orderdate desc) as rownum,

Salesorder_id,customer_id,orderdate

From Sales.SalesOrderHeader

Order by OrderDate Desc

Select row_number() over (order by orderdate desc) as rownum,

Salesorder_id,customer_id,orderdate

From Sales.SalesOrderHeader

Where rownum between 101 and 120

Order by OrderDate Desc

Create proc info1 @a int, @b int

As

Select * from (select row_number() over (order by orderdate desc) as rownum,

Salesorderid,customerid,orderdate,from sales.salesorderheader) 0

Where rownum between @a and @a+@b-1

Order by orderdate desc

Other Ranking functions

Select row_number() over (order by city) as rownum,

Rank() over( order by city) as rank,

Dense_Rank() over (order by city as DenseRank,

Ntile(3) over( order by city) as ntile_3,

Ntile(4) over(order by city) as ntile_4,

City, stateprovincecode

From person.address as address

Inner join person.stateprovince as stateprov

On address.stateprovinceid=stateprov.stateprovinceID where countryregioncode=‘US’ and StateprovinceCode=‘AZ’