# 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 (ADO.NET) 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
```

# Calculate All Dates For Weeks that Overlap a Month

```

SET DATEFIRST 1;
DECLARE @paramDate DATE ='2015-08-04'

--- --get date
--Select dateadd(month,datediff(month,0,@paramDate),0) --Month start
--,dateadd(month,datediff(month,0,@paramDate)+1,0)----start of next month
--,datepart(week, dateadd(month,datediff(month,0,@paramDate),0)) ----start week number in a year
-- ,datepart(week, dateadd(month,datediff(month,0,@paramDate)+1,0))  ---- end week number in a year

--===== 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)

,mydates as (
from Nums
--you can modify this where condition to have the day range you need.
WHERE n<  45  ) -- including days extended to previous month and next month

select datepart(week, dt) WeekNum,  dt DayDate,  datename(weekday,dt) DayStr from mydates

WHere
AND datepart(week, dateadd(month,datediff(month,0,@paramDate)+1,0)) >=datepart(week, dt)

SET DATEFIRST 7;

```

http://forums.asp.net/p/2067756/5965730.aspx?p=True&t=635778199497626560

# Pass a list of Databases to an SSIS package to run Powershell script to backup these databases.

You need to have an array object to accept a list of database names and create an SSIS package with a parameter to run the powershell script. Set an agent job to pass the list value to run the package.
A little bit details of each component:

SSIS package

Step 1: Add a package scope variable: Var1 as string for example to receive the list value from the agent job;

Step 2: Add an “Execute Process Task”

Go to Process tab: add the path to point to Executable: C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe
Go to RExpressions tab: click on … to open up Property Expressions Editor and choose Arguments from Property tab to add the following Expression to include the variable:

“-ExecutionPolicy ByPass -command powershell ” + ” ‘C:\\temp\\call_backup_cmdlet_with_parameter.ps1’ ” + @[User::Var1]

Agent Job

In an agent job to run SSIS package to pass parameter as a list, set the parameter value from Job step Property (to run ssis) Set Values tab:
Under Properties:
Property Path \Package.Variables[Var1].Value
Value ‘myDb1,mydb2,myDb3′

If you script our the job, you can find this line:

@command=N’/FILE “\”E:\Packages\myPowerShell1.dtsx\”” /CHECKPOINTING OFF /SET “\”\Package.Variables[Var1].Value\””;”\””myDb1,mydb2,mydb3”\”” /REPORTING E’,

Powershell script:

Use an array parameter to accept the list and unpack the list with a loop to use the individual value.

```

###'myDb1,mydb2,mydb3'
#Get values from script
param([parameter(Mandatory=\$true)][string[]]\$myDBs = \$(Throw "Please provide a DB name."))

# Test to see if the SQLPS module is loaded, and if not, load it
if (-not(Get-Module -name 'SQLPS')) {
if (Get-Module -ListAvailable | Where-Object {\$_.Name -eq 'SQLPS' }) {
Push-Location # The SQLPS module load changes location to the Provider, so save the current location
Import-Module -Name 'SQLPS' -DisableNameChecking
}
}

\$dt = Get-Date -Format yyyyMMddHHmmss
\$workdir='C:\Backup'

\$svrname = "MC047012\MSSQL2014"
foreach ( \$db in \$myDBs ) {

Try
{

#Change default timeout time from 600 to unlimited
\$svr =  new-object ('Microsoft.SqlServer.Management.Smo.Server') \$svrname
\$svr.ConnectionContext.StatementTimeout = 0

\$bfil = "\$workdir\\$(\$db)_db_\$(\$dt).bak"
\$q = @"
BACKUP DATABASE [\$db]
TO  DISK = N'\$bfil'
WITH  DESCRIPTION = N'Full backup of \$db',
NAME = N'\$db Backup',
NOSKIP, REWIND, NOUNLOAD,  STATS = 10
,COPY_ONLY,INIT, COMPRESSION
"@
Invoke-SQLCmd -ServerInstance \$svr.Name  -Database master -Query \$q -QueryTimeout 0
}
Catch [Exception]
{
write-host "Database - \$(\$db ): "\$_.Exception.Message
}
}

```

# SSRS Report Expression for Sorting AlphaNumerical Field

```

create table test (id int identity, unSorted varchar(30))
insert into test(unSorted)
values ('2-KAN'),('1'),('2'),('4'),('B1A'),('B2C'),('B4A'),('B4B'),('C2B'),('E1'),('E7'),('3-WEL1'),('30-WEL1'),('D1-WEL1'),('1B-WEL2'),
('CH1'),('1-ANW'),('14B-ANW'),('4-MOS2'),('P25/23'),('GORD-8'),('3A-SIL'),('4-DIE H'),('3B')

= CDbl(CStr(IIF(Microsoft.VisualBasic.AscW(Getchar(Fields!unSorted.Value, 1 ) ) <=57 and Microsoft.VisualBasic.AscW(Getchar(Fields!unSorted.Value, 1 ) ) >=48
,CStr(Getchar(Fields!unSorted.Value, 1) )
+Cstr(  IIF(len(Fields!unSorted.Value)>1,
IIF(Microsoft.VisualBasic.AscW(Getchar(Fields!unSorted.Value,IIF(len(Fields!unSorted.Value)>1,2,1)) ) <=57 and Microsoft.VisualBasic.AscW(Getchar(Fields!unSorted.Value,IIF(len(Fields!unSorted.Value)>1,2,1)) ) >=48 ,Getchar(Fields!unSorted.Value,IIF(len(Fields!unSorted.Value)>1,2,1)),""),"") )

,
CDbl(Cstr(Microsoft.VisualBasic.AscW(GetChar(Fields!unSorted.Value,1)))+
Cstr(IIF(len(Fields!unSorted.Value)>1, Microsoft.VisualBasic.AscW(Getchar(Fields!unSorted.Value,IIF(len(Fields!unSorted.Value)>1,2,1))),0.0))/100
+
Cstr(IIF(len(Fields!unSorted.Value)>2, Microsoft.VisualBasic.AscW(Getchar(Fields!unSorted.Value,IIF(len(Fields!unSorted.Value)>2,3,1))),0.0))/1000
+
Cstr(IIF(len(Fields!unSorted.Value)>3, Microsoft.VisualBasic.AscW(Getchar(Fields!unSorted.Value,IIF(len(Fields!unSorted.Value)>3,4,1))),0.0))/100000
+
Cstr(IIF(len(Fields!unSorted.Value)>4, Microsoft.VisualBasic.AscW(Getchar(Fields!unSorted.Value,IIF(len(Fields!unSorted.Value)>4,5,1))),0.0))/10000000
)))
)

```