Primay & FK settings with scripts

--alter table T_Tracking alter column ENTERPRISE_NBR varchar(10) not null
ALTER TABLE T_PersonnelInformation
ADD CONSTRAINT pk_PERSONNEL_id PRIMARY KEY (PERSONNEL_id)

alter table T_PersonnelInformation alter column PERSONNEL_id varchar(10) not null

ALTER TABLE T_PersonnelInformation
ADD CONSTRAINT fk_ENTERPRISE_NBR
FOREIGN KEY (ENTERPRISE_NBR)
REFERENCES T_CompanyInformation (ENTERPRISE_NBR) ON DELETE CASCADE
GO


ALTER TABLE T_Tracking
ADD CONSTRAINT fk_ENTERPRISE_NBR_TR
FOREIGN KEY (ENTERPRISE_NBR)
REFERENCES T_CompanyInformation (ENTERPRISE_NBR) ON DELETE CASCADE
GO

Nearest Time in sqlserver

/*CREATE FUNCTION RoundToNearest15
(
@Date SMALLDATETIME
)

RETURNS SMALLDATETIME
AS
*/
DECLARE @Date SMALLDATETIME=GETDATE()
BEGIN

DECLARE @Miniute TINYINT
DECLARE @Mult SMALLINT
DECLARE @Value TINYINT

SET @Miniute = DATEPART(MI,@Date)
SET @Value = 0
SET @Mult = 1

IF @Miniute < 8 BEGIN SET @Mult = -1 SET @Value = @Miniute END IF @Miniute BETWEEN 16 AND 22 BEGIN SET @Mult = -1 SET @Value = @Miniute - 15 END IF @Miniute BETWEEN 31 AND 37 BEGIN SET @Mult = -1 SET @Value = @Miniute - 30 END IF @Miniute BETWEEN 46 AND 52 BEGIN SET @Mult = -1 SET @Value = @Miniute - 45 END IF @Miniute BETWEEN 8 AND 14 SET @Value = 15 - @Miniute IF @Miniute BETWEEN 23 AND 29 SET @Value = 30 - @Miniute IF @Miniute BETWEEN 38 AND 44 SET @Value = 45 - @Miniute IF @Miniute BETWEEN 53 AND 59 SET @Value = 60 - @Miniute --SELECT DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)) --SELECT DATEADD(MI,@Value * @Mult,@Date) --SELECT DATEPART(MINUTE,@Date ) END --select convert(varchar(10),getdate(),108) --SELECT Convert( VarChar( 10 ), GETDATE(), 114 ) --SELECT substring(convert(varchar(10), GETDATE(), 108),1,2) SELECT cast( case when cast(substring(convert(varchar(10),DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)), 108),1,2) as integer) > 12 then
CAST(cast(substring(convert(varchar(10),DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)), 108),1,2) as integer)-12 AS VARCHAR)+
substring(convert(varchar(10),DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)), 108),3,3)
when
substring(convert(varchar(10),DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)), 108),1,2)='00' then
CAST(12 AS VARCHAR)+substring(convert(varchar(10),DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)), 108),3,3)
else
substring(convert(varchar(10),DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)), 108),1,5)
end
as varchar(10))+
case
when
cast(substring(convert(varchar(10),DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)), 108),1,2) as integer) > 12 then
' pm'
else
' am'
end
as time1,
cast(
case
when
cast(substring(convert(varchar(10),DATEADD(MI,@Value * @Mult,@Date), 108),1,2) as integer) > 12 then
CAST(cast(substring(convert(varchar(10),DATEADD(MI,@Value * @Mult,@Date), 108),1,2) as integer)-12 AS VARCHAR)+
substring(convert(varchar(10),DATEADD(MI,@Value * @Mult,@Date), 108),3,3)
when
substring(convert(varchar(10),DATEADD(MI,@Value * @Mult,@Date), 108),1,2)='00' then
CAST(12 AS VARCHAR)+substring(convert(varchar(10),DATEADD(MI,@Value * @Mult,@Date), 108),3,3)
else
substring(convert(varchar(10),DATEADD(MI,@Value * @Mult,@Date), 108),1,5)
end
as varchar(10))+
case
when
cast(substring(convert(varchar(10),DATEADD(MI,@Value * @Mult,@Date), 108),1,2) as integer) > 12 then
' pm'
else
' am'
end
as time2