Dynamic datepart with DATEADD Function
Posted: June 22, 2017 Filed under: Uncategorized Leave a comment
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]
Missing Object Error from Profiler to Insert Newly Create Table
Posted: June 14, 2017 Filed under: Uncategorized Leave a commentWhen 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.
Sample Code for Gaps and Islands
Posted: June 13, 2017 Filed under: Uncategorized Leave a comment
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
Restore Database From Backup File (From 2005 to 2016 )
Posted: June 12, 2017 Filed under: Uncategorized Leave a commentYou 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
Posted: June 8, 2017 Filed under: Uncategorized Leave a commentI 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
Posted: June 6, 2017 Filed under: Uncategorized Leave a comment
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
--ANOTHER SAMPLE:
SELECT getdate() dt
, CAST(FORMAT(getdate(),'yyyyMMddHHmmssfff') AS bigint) bigintString
,cast(stuff(stuff(stuff(stuff (Cast(CAST(FORMAT(getdate(),'yyyyMMddHHmmssfff') AS bigint) as varchar(20)), 9,0,' '),12,0,':'),15,0,':'),18,0,'.') as datetime)
--cahnge backto datetime form
Jingyang Li 