Adding time values stored as numbers

One of my friends told me that he had a data set in which numbers are expressed in decimal format.

For example 3.45 means 3 hours and 45 minutes, 4.30 means 4 hours 30 minutes etc.

The challenge is to add these numbers not as decimal values but as time values so adding 3.45 and 4.30 should be 8.15 and not 7.75.

Let us create this data set

create table #t (gtime numeric(8,2)) 
insert into #t values (3.45),(4.30),(4.45),(4.45) 

There are two methods that I come up with

Method 1 : Use CONVERT function

select 
convert(varchar(10), 
	dateadd(minute,sum(floor(gtime)*60+gtime%1*100),0),
108)  
        as time_value
from 
	#t   

The result is

time_value
----------
17:45:00

Method 2 : CONVERT the sum into TIME

select 
	cast(dateadd(minute,sum(floor(gtime)*60+gtime%1*100),0) as time)  
        as time_value
from 
	#t 

The result is

time_value
----------------
17:45:00.0000000
Advertisements

How to know the start time of SQL Server?

One of my friends asked me “How do I find out when the SQL Server is started/restarted?”

Well. There can be many ways to know this. The two methods I know are querying the system views sysdatabases and dm_os_sys_info

Method 1 : Query sysdatabases


select
	crdate
from
	master..sysdatabases
where
	name='tempdb'

The result is

crdate
-----------------------
2017-09-01 15:58:28.417

Method 2 : Query dm_os_sys_info

select
	sqlserver_start_time
from
	sys.dm_os_sys_info

The result is


sqlserver_start_time
-----------------------
2017-09-01 15:58:12.613

Note : This shows the time that my SQL server instance is started. You will get different time

Usage of CTE and maxrecursion option in view

You know that it is possible to use Common Table Expression in a View. But some people wonder why the following is not possible.

Let us create the following view that creates a number table that will have values from 1 to 10000

create view numbers
as
with numbers(n) as
(
select 1 as n union all select n+1 from numbers where n<10000
)
select n from numbers option(maxrecursion 0)

Which when created results to the error

Msg 156, Level 15, State 1, Procedure numbers, Line 15
Incorrect syntax near the keyword 'option'.

The correct way of doing it is to create a view without option(maxrecursion 0) and use it when querying a view

create view numbers 
as 
with numbers(n) as 
( 
select 1 as n union all select n+1 from numbers where n<10000 
) 
select n from numbers 

GO
Now you can execute the view with option as shown below

select * from numbers option (maxrecursion 0)

Use IN clause with care

IN clause can be used as a JOIN to get data by matching the column values from other table. Although this is much similar to INNER JOIN, be careful about the usage of IN

Let us create the following data sets

use tempdb 

create table customer_master
(
cust_id int,
cust_name varchar(100)
);

insert into customer_master(cust_id,cust_name)
select 1,'Sankar' union all
select 2,'Madhivanan' union all
select 3,'Gopal';

create table premium_customers
(
cust_id int
);


insert into premium_customers(cust_id)
select 1 union all
select 2

Now to get all customers who are in premium_customers table, you can use this

select 
	* 
from 
	customer_master 
where 
	cust_id in 
		(
		select cust_id from premium_customers
		)

which returns the following result

cust_id     cust_name
----------- -----------
1           Sankar
2           Madhivanan

Now, let us re name cust_id of premium_customers into customer_id

exec sp_rename 'premium_customers.cust_id','customer_id','COLUMN'

Now execute the same query

select 
	* 
from 
	customer_master 
where 
	cust_id in 
		(
		select cust_id from premium_customers
		)

The result is

cust_id     cust_name
----------- -------------
1           Sankar
2           Madhivanan
3           Gopal

It returns ALL ROWS from customer_master table although there is no column named cust_id in premium_customers. Because it is a column in customer_master table, it is matched with customer_master table and gives all rows

How do we avoid this?

Always use table alias and qualify the columns using table alias

Executing the following code

select 
	t1.* 
from 
	customer_master as t1
where 
	t1.cust_id in 
		(
		select t2.cust_id from premium_customers as t2
		)

now throws an error

Msg 207, Level 16, State 1, Line 39
Invalid column name 'cust_id'.

So it is always a better practice to use table alias and qualify the columns with it.

Avoid view creation with “SELECT *”

A view is very useful in giving out only the required/restricted information to the users. However see what happens when you create a view with “SELECT * from table” definition.

Let us create this simple table

create table emp_master
(
emp_id int,
emp_name varchar(100),
joined_date date
)

Create a view based on this table using “SELECT *”

Create view emp_master_view as
select * from emp_master;

Now see the column information of this view using sp_columns system procedure

EXEC sp_columns 'emp_master_view'

The result is shown in the below imageview1

Now let us add a new column in the emp_master table

alter table emp_master add address varchar(200)

Now see the column information of view using sp_columns system procedure

EXEC sp_columns 'emp_master_view'

The result is shown in the below image. As you see the address column is not yet updated in the view.view1

So what is the proper way to update the column information of the view?

Use sp_refreshview system rocedure

EXEC sp_refreshview emp_master_view

After executing the sp_refreshview procedure, let us see the column information

EXEC sp_columns 'emp_master_view'

As you see in the following result, new column is added in the viewview2

Points to consider when creating a view

1 Do not use *. Explicitly type out the column names in the view definition. When new columns are required, alter the view to add them
2 Use schemabinding option when creating view which will prevent the usuage of *

Dynamic sql execution – EXEC vs sp_executesql

The only way of executing dynamic sql is thru the usage of methods EXEC and sp_executesql. In this post we will explore the differences between the two.

 

EXEC is not parameterised

EXEC(‘select 1’)

SP_executesql is parameterised

EXEC sp_executesql N'select 1'

Let us create this simple data set

use tempdb

create table customer (cust_id int, cust_name varchar(100))
insert into customer(cust_id,cust_name)
select 1,'testing'

Now assume that you want to pass table name, column name and value for the column to get the data

It can be done in dynamic sql in the following methods

Method 1 EXEC

Declare @sql varchar(1000)
Declare @table_name varchar(100), @col_name varchar(20)='cust_id',@col_value int=1
Select @sql='', @table_name='customer'
Set @sql='select * from '+@table_name+' where '+@col_name+' = '+cast(@col_value as varchar(10))
Exec(@sql)

The above method is not parameterised and the sql query is constructed and executed using EXEC

Method 2 sp_executesql


Declare @sql nvarchar(1000)
Declare @table_name varchar(100), @col_name varchar(20)='cust_id',@col_value int=1
Select @sql='', @table_name='customer'
set @sql='select * from '+ @table_name +' where '+@col_name+'=@col_value'
exec sp_executesql @sql,N' @col_value int',@col_value 

The above method is parameterised and the sql query is constructed and executed via parameter using sp_executesql

Both of them produce the following result

cust_id     cust_name
----------- ----------------
1           testing

In the next post we will explore SQL Injection

Scripting out Data from a table as INSERT statements

Everyone know how to script out a definition of a table. However many do not aware that you can script out data as well in the form of SELECT statements

This option is available only from SQL Server Management Studio (SSMS). 

Here is what you need to do

Right click on database

–>tasks–>Generate scripts–>click next–>Choose specific database_objects

Now check out the tables you want to script data out

Choose one of the saving options (file, clipboard,etc).

Click Advanced and in the drop down for label Type of data to script , choose the value Data only

Click next. You get all the data in the form of SELECT statements

Here are the pictures for easy understanding

 

 

Dynamic Crosstab with multiple PIVOT columns – Part 2

In Part 1 of this post, I showed a method of producing crosstab results using two PIVOT columns. In this post, I will show you the method of how you can change the grouping columns dynamically.

I am using the same data set created in Part 1. In Part 1, the grouping column was Last name of the employee. Basically it showed the total orders handled by each employee. Now in this post, we will see how to change Last name into some other column.

Let us use Shipping company’s name as grouping column. Executing the following code

EXEC CrossTab_multiple_columns
'SELECT Shipname, OrderDate,shipcity FROM Employees Employees
INNER JOIN Orders Orders ON (Employees.EmployeeID=Orders.EmployeeID) ',
'shipcity,Year(OrderDate)',
'Count(Shipname)[]',
'Shipname'

will show you the following result

pivot3

Let us use Shipping Postal code as grouping column. Executing the following code


EXEC CrossTab_multiple_columns
'SELECT Shipname, OrderDate,shippostalcode FROM Employees Employees
INNER JOIN Orders Orders ON (Employees.EmployeeID=Orders.EmployeeID) ',
'shippostalcode,Year(OrderDate)',
'Count(Shipname)[]',
'Shipname'

will show you the following result

pivot4

Now, as you see, this stored procedure is very dynamic and will work for any dataset (any tables, views, etc). All you need to do is effectively use this procedure and pass the relevant names for aggregation , grouping and PIVOT columns