# Calculate Compound Salary Raise with T-SQL (Simplify version)

```

create table test (emplID INT, annualSalary  decimal(10,4),  Meritpoint decimal(6,3), Hiredate date, Meritdate date)

INSERT INTO test  VALUES (1000,75000.0,0.030,'10/1/2015','3/1/2016'),(1001,100000.0,0.035,'9/1/2015','2/1/2016')

Select emplID, Year(Hiredate)+(n-1) [Year],  annualSalary*Power( CAST(10 AS FLOAT) + Meritpoint , n-1) as [bonusEligibleSalary]

from test cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) d(n)

Where n<4

drop table test

```

# Alter table column data type from samllint to int to all columns in a database

You need to drop keys and make the alter data type and reapply all keys back.

Here is a sample process with some script references:

Step 0:

Take a FULL backup of your database

Step 1: Prepare create and drop scripts for all table primary keys using this script:

http://social.technet.microsoft.com/wiki/contents/articles/2321.script-to-create-or-drop-all-primary-keys.aspx

Step 2: Prepare drop and re-create scripts for all foreign keys:

https://www.mssqltips.com/sqlservertip/3347/drop-and-recreate-all-foreign-key-constraints-in-sql-server/

Step 3: Prepare alter statements:

SELECT ‘Alter table ‘+ [TABLE_SCHEMA]+’.’+[TABLE_NAME] +’ ALTER COLUMN ‘+ [COLUMN_NAME] +’ int’
FROM [INFORMATION_SCHEMA].[COLUMNS]
where [DATA_TYPE]=’smallint’

You are ready to execute these scripts in sequence:

Drop primary keys;

Drop foreign keys;

Alter table column data type from samllint to Int;

Recreate primary keys;

Recreate foreign keys.

# Batch Update with Merge SampleT-SQL

```

-----Set up
--create table TabA (Accountkey int  identity(1,1), IPKey int, [StartDateTime] datetime)
--go
--insert into TabA ([StartDateTime])  values(getdate())
--go 200000

--create table TabB (aAccountkey int  identity(1,1), IPKey int, [StartDateTime] datetime)
--go
-- insert into TabB ([StartDateTime])  values(getdate())
--go 21000

----drop table taba,tabb

declare @chunk int=5000, @i int =1

WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION

;With mycte as
(
Select Accountkey,IPKey,StartDateTime , row_number() Over(Order by [StartDateTime]) rn from TabA a
where [StartDateTime]  BETWEEN  current_timestamp-10 AND  current_timestamp+10
)

,mycte1 as (
Select * from mycte
Where rn between  @chunk*(@i-1)+1 and  @chunk*@i)

Merge Top (@chunk) mycte1 a
Using TabB b on A.Accountkey=B.aAccountkey
When matched   then
Update
Set A.IPKey=Isnull(B.IPKey,-1);

IF @@ROWCOUNT = 0
BEGIN
COMMIT TRANSACTION
BREAK
END

Set @i=@i+1

COMMIT TRANSACTION

END

--select * from taba

```

# Format date and time ( Combined )

```

CREATE TABLE [dbo].[test]( date1  date, time1 VARCHAR(8))
Insert into test values('Sep 1, 2015','16:20:54')

Select date1,time1
, Cast(date1 as datetime) + Cast(time1 as   datetime)  dt1
, Replace(Convert(varchar(26),Cast(date1 as datetime) + Cast(time1 as   datetime) ,109) ,':000',' ')  dt2
--SQL Server 2012 0r 2014
--,FORMAT(Cast(date1 as datetime) + Cast(time1 as   datetime) ,'MMM dd yyyy hh:mm:ss tt') dt3
from test

drop table test

```

# The default value of CommandTimeout property of SSIS package

By default, the CommandTimeout property of SSIS package has a value of 30 seconds. If the process is longer than 30, the package will time out. The value can be set to a large number or 0.

# A Cursor Sample with Two Variables and Default datetime

```

declare @invoiceid int , @col int
declare @dt datetime =current_timestamp

--declare local fast_forward cursor
DECLARE the_cursor CURSOR local fast_forward  FOR

Select invoiceid, col from atable

OPEN the_cursor

FETCH NEXT FROM the_cursor INTO @invoiceid, @col

WHILE @@FETCH_STATUS = 0
BEGIN

---do somthing here to use the two variables and @dt if needed for timestamp

FETCH NEXT FROM the_cursor INTO   @invoiceid, @col ;

END

CLOSE the_cursor;
DEALLOCATE the_cursor;

```

# Datetime Diff in day and hour excluding weekends

```
create table test (id int identity(1,1), Checkindatetime datetime, Checkoutdate date,	Checkouttime time)
Insert into test values('Sep 1, 2015 4:37:37 AM',	'Sep 3, 2015',	'11:19:29')
,('Sep 3, 2015   3:45:44 PM',	'Sep 4, 2015',	'18:42:31')
,('Sep 2, 2015   9:05:19 AM',	'Sep 2, 2015',	'13:23:20')
,('Sep 2, 2015   9:11:20 AM',	'Sep 8, 2015',	'14:15:35')

--===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

,mydate as (
select id, Cast(dateadd(day,n-1, Checkindatetime) as date) dt , Checkoutdate,
row_number() Over(partition by id Order by Cast(dateadd(day,n-1, Checkindatetime) as date)) rn1,
row_number() Over(partition by id Order by Cast(dateadd(day,n-1, Checkindatetime) as date) desc) rn2,
Checkindatetime, Cast(Checkoutdate as datetime) + Cast(Checkouttime as   datetime) Checkoutdatetime

from test
cross apply (select n from Nums) d(n)
WHERE dateadd(day,n-1,Checkindatetime)<=Cast(Checkoutdate as datetime) + Cast(Checkouttime as   datetime)
)

,myctedt as (
Select id,  min(Checkindatetime) Checkindatetime, max(Checkoutdatetime) Checkoutdatetime,
Sum(Datediff(hour, case when rn1=1 then Checkindatetime else dt end ,
case when rn2=1 then Checkoutdatetime else dateadd(day,1,dt) end ) ) diffinHr
from mydate
WHERE datepart(weekday,dt) not in (7,1) --exclude Saturday and
Group by id)

select id,Checkindatetime,Checkoutdatetime
, Cast(diffinHr/24 as varchar(2))+' day(s) '+ Cast(diffinHr%24 as varchar(2)) +' hour(s)'   as [day:hh]
from myctedt
Order by id

drop table test
```