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