Dynamic datepart with DATEADD Function

  

 

CREATE TABLE [dbo].[ProcessMaster](
[ProcessID] [int] IDENTITY(1,1) NOT NULL,
[Frequency] [varchar](20) NULL,
[FrequencyQty] [int] NULL,
[LastStarted] [datetime] NULL)

insert into [ProcessMaster] (Frequency,FrequencyQty, LastStarted) 
values( 'dd', 1, '2017-06-15 08:16:20.587')
,('mi' ,5,'2010-06-22 11:12:00.537')
 
declare  @Frequency nvarchar(2), @FrequencyQty int, 
--more variables
@NextStart datetime , @ProcessID int=2
   
Select  @Frequency = Frequency, @FrequencyQty = FrequencyQty from ProcessMaster where ProcessID =  @ProcessID 

declare @sql nvarchar(2000) =N'Select  @NextStart=(Select dateadd('+@Frequency+',@FrequencyQty,LastStarted) from [dbo].[ProcessMaster] where ProcessID = @ProcessID )'
 

EXECUTE sp_executesql @sql, N'@NextStart datetime output,@Frequency char(2),@FrequencyQty int, @ProcessID int' ,@NextStart =@NextStart output , @Frequency =@Frequency , @FrequencyQty=@FrequencyQty , @ProcessID=@ProcessID
 
 
Select @NextStart 


drop table [ProcessMaster]


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/48a9fd46-3577-4b0b-9925-b9e67992b9a3/trying-to-dynamically-add-to-dates?forum=transactsql


Missing Object Error from Profiler to Insert Newly Create Table

When you insert rows immediately after the table creation, you may find there is an exception report for missing object if you dig into Profiler to view it. It is working fine from front end but you still see this error report. This behavior is caused by “deferred name resolution” and you will see this behavior with stored procedure as well.

Here is the code that generates this exception:

  

create table #temp (id int) 
insert into #temp (id) values (1)
 

 

You can avoid this error report by using different batch for your creation and insert.

  

create table #temp (id int) 

Go

insert into #temp (id) values (1)

Go

 

 

Or use global temp table

 

  

 

create table ##temp (id int)

insert into ##temp (id) values (1)

 

 

Or do nothing and keep coding.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/773b0265-1547-4d8a-9e43-b6a5ab8f33bd/getting-invalid-object-name-by-creating-a-temp-table-and-inserting-rows-right-away?forum=transactsql
 

 


Sample Code for Gaps and Islands

  

 create table mytableTest
(
   PAT_NAME        VARCHAR(8) NOT NULL  
  ,LOC_ID          INTEGER  NOT NULL
  ,MRN             INTEGER  NOT NULL
  ,DEPARTMENT_NAME VARCHAR(17) NOT NULL
  ,EFFECTIVE_TIME datetime NOT NULL
  ,ADMISSION       datetime
  ,Transfer_In     datetime
  ,Transfer_Out    datetime
  ,Discharged      datetime
  ,EVENT_TYPE_IN   VARCHAR(11)
  ,Event_Type_Out  VARCHAR(12)
)
Go

INSERT INTO mytableTest (PAT_NAME,LOC_ID,MRN,DEPARTMENT_NAME,EFFECTIVE_TIME,ADMISSION,Transfer_In,Transfer_Out,Discharged,EVENT_TYPE_IN,Event_Type_Out) VALUES
 ('Doe,Jane',11111,2525,'FantasyLand','1/10/16 17:52','1/10/16 17:52',NULL,NULL,NULL,'Admission',NULL),
 ('Doe,Jane',11111,2525,'FantasyLand','1/12/16 15:21',NULL,NULL,'1/12/16 15:21',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'AdventureLand','1/12/16 15:21',NULL,'1/12/16 15:21',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'AdventureLand','1/12/16 18:24',NULL,NULL,'1/12/16 18:24',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/12/16 18:24',NULL,'1/12/16 18:24',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/14/16 14:27',NULL,NULL,'1/14/16 14:27',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'AdventureLand','1/14/16 14:27',NULL,'1/14/16 14:27',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'AdventureLand','1/14/16 16:03',NULL,NULL,'1/14/16 16:03',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/14/16 16:03',NULL,'1/14/16 16:03',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/17/16 11:42',NULL,NULL,'1/17/16 11:42',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'AdventureLand','1/17/16 11:42',NULL,'1/17/16 11:42',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'AdventureLand','1/17/16 11:43',NULL,NULL,'1/17/16 11:43',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/17/16 11:43',NULL,'1/17/16 11:43',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/18/16 12:00',NULL,NULL,'1/18/16 12:00',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'TTH 19W ADULT INT','1/18/16 12:00',NULL,'1/18/16 12:00',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'TTH 19W ADULT INT','1/19/16 11:05',NULL,NULL,'1/19/16 11:05',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'AdventureLand','1/19/16 11:05',NULL,'1/19/16 11:05',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'AdventureLand','1/19/16 16:46',NULL,NULL,'1/19/16 16:46',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/19/16 16:46',NULL,'1/19/16 16:46',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/23/16 15:52',NULL,NULL,'1/23/16 15:52',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/23/16 15:52',NULL,'1/23/16 15:52',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/23/16 16:03',NULL,NULL,'1/23/16 16:03',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/23/16 16:03',NULL,'1/23/16 16:03',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/23/16 20:45',NULL,NULL,'1/23/16 20:45',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'LibertySquare','1/23/16 20:45',NULL,'1/23/16 20:45',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'LibertySquare','1/25/16 19:06',NULL,NULL,'1/25/16 19:06',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'FrontierLand','1/25/16 19:06',NULL,'1/25/16 19:06',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'FrontierLand','2/3/16 20:45',NULL,NULL,NULL,'2/3/16 20:45',NULL,'Discharge');
Go

;with mycte as (
select   PAT_NAME,LOC_ID, MRN, DEPARTMENT_NAME, EFFECTIVE_TIME, ADMISSION , Transfer_In,Transfer_Out  ,Discharged     
,row_number() Over(order by EFFECTIVE_TIME )-row_number() Over(partition by  DEPARTMENT_NAME  order by EFFECTIVE_TIME )  grp  
,(row_number() Over(order by EFFECTIVE_TIME )+1)/2  grp2
from mytableTest

)

select  PAT_NAME,LOC_ID CSN, DEPARTMENT_NAME, max(EFFECTIVE_TIME) as EFFECTIVE_TIME
,max(ADMISSION) ADMISSION
,max(Transfer_In) Transfer_In
,max(Transfer_Out) Transfer_Out
,max(Discharged) Discharged

from mycte
Group by  PAT_NAME,LOC_ID, MRN,DEPARTMENT_NAME,grp, grp2
order by EFFECTIVE_TIME
 
drop table mytableTest
 





 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cddcfd88-72f4-479a-855f-84eea10a8d44/cherry-picking-from-my-data-set-to-consolidate-rows-by-department?forum=transactsql


Restore Database From Backup File (From 2005 to 2016 )

You can restore a full backup file taken from an early SQL Server to current SQL Server 2016 instance with either T-SQL code or through SSMS.
Please check the code sample and the steps to use SSMS:

  

 

--Get database logicalNames
restore filelistonly from disk='C:\mssql\mydatabase2005.BAK'
--mydatabase
--mydatabase_Log

---Restore to a new database

RESTORE DATABASE myDB2016 FROM DISK = N'C:\mssql\mydatabase2005.BAK'
WITH MOVE N'mydatabase' TO N'C:\mssql\myDB2016.mdf',
MOVE N'mydatabase_Log' TO N'C:\mssql\myDB2016_Log.ldf',
REPLACE;
GO

---Get the logical names of the new database

select name as logicalname  from sys.master_files
where DB_NAME(database_id)='myDB2016'

--result:
--mydatabase
--mydatabase_Log

-- Rename logical names to new names at your choice

ALTER DATABASE myDB2016 
MODIFY FILE (NAME = 'mydatabase', NEWNAME = 'MyDB2016_data')
GO
ALTER DATABASE MyDB2016
MODIFY FILE (NAME = 'mydatabase_Log', NEWNAME = 'MyDB2016_log')
GO

--Check out the new names

select name, physical_name  from sys.master_files
where DB_NAME(database_id)='myDB2016'






 

From SSMS,you need to right-clcik on Databases>>Restore From Files and Filegroups>> type a new database name To database box and choose From device to point to the backup file we want to restore. And check the full backup from the list and click on Ok. Your new database will be ready after the restore finished.
If you have already had a database you want to replace, you need to go to Options page to check the Overwrite the existing database option (WITH REPLACE T-SQL cod), click OK and it is done. Your old database has been replaced with this one.
Your restore database will have the same logical names it has in the database the backup was taken.
You can change the names from Properties>> Files >> type new Logical names and click OK.
You can use the Script choice to view the code for your action:
Here is T-SQL code generated by SSMS:

  

 USE [myDB2016]
GO
ALTER DATABASE [myDB2016]  MODIFY FILE (NAME=N'mydatabase', NEWNAME=N'MyDB2016_data')
GO
USE [myDB2016]
GO
ALTER DATABASE [myDB2016] MODIFY FILE (NAME=N'mydatabase_log', NEWNAME=N'MyDB2016_log')
GO

 

Multiple Options to Force Datetime to Bigint

I don’t need this in my coding but the multiple ways to do it can demonstrate how you can use the language.

  

 
declare @dt datetime ='2017-06-08 03:31:39.167'
--20170608033139

declare @dt datetime ='2017-06-08 03:31:39.167'


--Option 1
Select Cast(year(@dt)*10000000000
+ month(@dt)*100000000 
+ day(@dt)*1000000 
+ datepart(hour, @dt)*10000 
+ datepart(minute,@dt)*100 
+ datepart(second,@dt)*1
 as bigint)
 

 --Option 2
select CAST(concat(year(@dt)
,Right('0'+Cast(month(@dt) as varchar(2)),2)
,Right('0'+Cast(day(@dt) as varchar(2)),2)
,Right('0'+Cast(datepart(hour, @dt) as varchar(2)),2)
,Right('0'+Cast(datepart(minute,@dt) as varchar(2)),2)
,Right('0'+Cast(datepart(second,@dt) as varchar(2)),2)
) as BIGINT)
 
 --Option 3
 select Cast(Convert(varchar(8),@dt,112)+Replace(Convert(varchar(8),@dt,114),':','') as BIGINT)

 --option 4

 Select CAST(FORMAT(@dt,'yyyyMMddHHmmss') AS bigint) 



  --Option 5
 select  Cast(Replace(Replace(Replace(Convert(varchar(19),@dt,121),' ',''),'-',''),':','')  as BIGINT)

   --Option 6
 select  Cast(Replace(Replace(Replace(Convert(varchar(19),@dt,121),' ',''),'-',''),':','')  as BIGINT)
  --Option 7
 select Cast(Convert(varchar(8),@dt,112)+stuff(stuff(Convert(varchar(8),@dt,114),3,1,''),5,1,'') as BIGINT)
  --Option 8
 select Cast(Convert(varchar(8),@dt,112)
 +(substring(Convert(varchar(8),@dt,114),1,2)
 + substring(Convert(varchar(8),@dt,114),4,2)
 + substring(Convert(varchar(8),@dt,114),7,2)) as BIGINT)


 

Convert Datetime to Bigint (or string) and Change back to Datetime

  

 declare @dtBigInt bigint
--declare @dtstringFromDatetime varchar(14)

select @dtBigInt=CAST(concat(year(getdate())
,Right('0'+Cast(month(getdate()) as varchar(2)),2)
,Right('0'+Cast(day(getdate()) as varchar(2)),2)
,Right('0'+Cast(datepart(hour, getdate()) as varchar(2)),2)
,Right('0'+Cast(datepart(minute,getdate()) as varchar(2)),2)
,Right('0'+Cast(datepart(second,getdate()) as varchar(2)),2)
) as BIGINT)
print @dtBigInt

--declare @dtstringFromDatetime varchar(20)
declare @dt datetime
--Convert bigint to datetime
select @dt=cast(stuff(stuff(stuff (Cast(@dtBigInt as varchar(20)), 9,0,' '),12,0,':'),15,0,':') as datetime)
print @dt
--STUFF function with length 0 will insert string to the string after the starting position


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ca6426f6-add6-47d7-a110-9620d9864c9a/convert-string-to-datetime?forum=transactsql

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6dc38e9e-70b2-42d2-bbfd-2fbae0791ea8/sql-2012-convert-datetime-to-interget-value?forum=transactsql


Beginning of the Minute and Second of Datetime

  

create table test (StartDatetime datetime);


insert into  test values ('2017-10-03 00:11:01.000'),('2017-10-03 00:20:02.333'),
('2017-10-03 00:33:03.333'),
('2017-10-03 00:44:04.667'),
('2017-10-03 00:55:00.333'),
('2017-10-03 00:06:01.697')
 

 select  
  dateadd(mi, datediff(mi, 0, CURRENT_TIMESTAMP), 0) beginningOfTheMinute_current,
 dateadd(mi, datediff(mi, 0, StartDatetime), 0) beginningOfTheMinute1 
 , dateadd(minute,datediff(minute,Cast(Cast(StartDatetime as date) as datetime), StartDatetime)
 ,Cast(Cast(StartDatetime as date) as datetime)) beginningofminute2
 
  , dateadd(second,datediff(second,Cast(Cast(StartDatetime as date) as datetime), StartDatetime)
 ,Cast(Cast(StartDatetime as date) as datetime)) beginningofsecond

  from test


drop table test 

--https://jingyangli.wordpress.com/?s=beginning