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



 

http://forums.asp.net/p/2068622/5968893.aspx?p=True&t=635785499755487339

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
 

http://forums.asp.net/t/2068480.aspx?how+to+count+for+datetime

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 (
Select   dateadd(day,n-7, dateadd(month,datediff(month,0,@paramDate),0) ) dt 
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 
datepart(week, dateadd(month,datediff(month,0,@paramDate),0))<=datepart(week, dt)  
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',
MEDIADESCRIPTION = N'Disk',
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
)))
)