Personal Folder

Datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).

Without century (yy)	With century (yyyy)	Standard	Input/Output**
-	0 or 100 (*)	Default	mon dd yyyy hh:miAM (or PM)
1	101	USA	mm/dd/yy
2	102	ANSI	yy.mm.dd
3	103	British/French	dd/mm/yy
4	104	German	dd.mm.yy
5	105	Italian	dd-mm-yy
6	106	-	dd mon yy
7	107	-	Mon dd, yy
8	108	-	hh:mm:ss
-	9 or 109 (*)	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	USA	mm-dd-yy
11	111	JAPAN	yy/mm/dd
12	112	ISO	yymmdd
-	13 or 113 (*)	Europe default + milliseconds	dd mon yyyy hh:mm:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 or 120 (*)	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)
-	21 or 121 (*)	ODBC canonical (with milliseconds)	yyyy-mm-dd hh:mi:ss.mmm(24h)
-	126(***)	ISO8601	yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
-	130*	Hijri****	dd mon yyyy hh:mi:ss:mmmAM
-	131*	Hijri****	dd/mm/yy hh:mi:ss:mmmAM

Please check http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx for detail.

Get first day of current year:
SELECT     *
FROM         tbl
WHERE     (tbl.datefield = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))

Get last day of current year:
SELECT     *
FROM         tbl
WHERE     DATEADD(dd,-1,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0)))

Get first day of current month:
SELECT     *
FROM         tbl
WHERE     select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

Get first day of last month:
SELECT     *
FROM         tbl
WHERE     DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,getdate())), 0)

Get month part of a datetime field.
DATEPART(MONTH, A.AEDAT)

Get year part of a datetime field.
DATEPART(YEAR, A.AEDAT)

Cast field to char.
CAST(aField AS char(4))

Set 2 digit literal with trailing 0.
RIGHT('0' + RTRIM(MONTH(A.AEDAT)), 2)

Get Date Part Only of Datetime Field
CONVERT(varchar(8), GETDATE(), 112)

Get Time Part Only of Datetime Field
CONVERT(varchar(8), GETDATE(), 108)

Get Yesterday Date

DATEADD(DAY,DATEDIFF(DAY,'20000102',GETDATE()),'20000101')

And many else (from shanecooper.net) 
Monday of the Current Week
select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
Sunday of the Current Week
set DATEFIRST 1
select DATEADD(dd, 1 - DATEPART(dw, getdate()), getdate())
First Day of the Year
select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
First Day of the Quarter
select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
Midnight for the Current Day
select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
Last Day of Prior Month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  ), 0))
Last Day of Prior Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  ), 0))
Last Day of Current Month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+1, 0))
Last Day of Current Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  )+1, 0))
First Monday of the Month
select DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate()) ), 0)    

Typical users using employee in out absent report:

1. Employee, see accurate minute he entry to and leave from plant, check whether he is late/early home or not. Absent data is one of criterias employee deserve for incentive from the company.
2. HR Dept, count employee pay work hour and employee overtime.
3. HSE Dept, count safety work hour and overtime. Superintendent level and above usually not report their existence on plant on overtime hour since their not get pay. But HSE want their data to increase safety work hour.

With that user requirement, the data must contain:

• employee id
• employee daily/shift schedule on subsequent date
• employee’s real in out absent time (and date)
• pay work hour
• safety work hour
• pay overtime
• safety overtime

Note that on daily type employee consist of Daily Merak (07:30 – 16:30) and Daily Jakarta (08:00-17:00). And for shift employee, there are 3 shift (morning shift, afternoon and night). Each shift has 4 group, group A, B, C and D. Night shift have special process, since leave time is smaller than entry time. Below are complete schedule of employee:

Type	Kode	JamDatang	JamPulang	Keterangan
Daily Merak	DM	0730	1630	Normal Jam Kerja Merak
Daily Jakarta	DJ	0800	1700	Normal Jam Kerja Jakarta
Shift Pagi	M0	0600	1400	Technisi
Shift Siang	A0	1400	2200	Technisi
Shift Malam	N0	2200	0600	Technisi
Shift Spv Pagi	M1	0700	1500	Shift Supervisor,Warehouse Assistant
Shift Spv Siang	A1	1500	2300	Shift Supervisor,Warehouse Assistant
Shift Spv Malam	N1	2300	0700	Shift Supervisor,Warehouse Assistant
AC Pagi	M4	0600	1500	Technisi AC
AC Siang	A4	1000	1900	Technisi AC
Driver Pagi	M5	0600	1500	Driver
Driver Siang	A5	1500	2400	Driver
12 Jam Pagi	M2	0600	1800	12 Jam 06:00 - 18:00
12 Jam Malam	N2	1800	0600	12 Jam 18:00 - 06:00
12 Jam Spv & Security Pagi	M3	0700	1900	12 Jam 07:00 - 19:00
12 Jam Spv & Security Malam	N3	1900	0700	12 Jam 19:00 - 07:00
Rest/Libur	R0	0	0	Libur Shift
Weighbridge Opr Pagi	W0	0700	1500	Weighbridge Operator
Weighbridge Opr Siang	W1	1500	2300	Weighbridge Operator
Security Pagi	S0	0700	1500	Security Pagi
Security Siang	S1	1500	2300	Security Siang
Security Malam	S2	2300	0700	Security Malam
Driver Pola A Pagi	MD0	0700	1500	Driver Shift Pola A Pagi
Driver Pola A Siang	AD0	1500	2300	Driver Shift Pola A Siang
Driver Pola A Malam	ND0	2300	0700	Driver Pola A Pagi
Driver Pola B Pagi	MD1	0600	1400	Driver Pola B Pagi
Driver Pola B Siang	AD1	1400	2200	Driver Pola B Siang
Driver Daily	DD0	0730	1630	Driver Daily

Here is the result:

Many table and view contribute to produce the result above, and below are the steps:

Process on ESOFNET Database

• Trans_R > detail of employee in out, consist of card number, time, code whether employee is entering or leaving the plant.
• vTrans_R_v2 > Trans_R left join with Cards table so that the view has staff number and employee daily shift code.

SELECT dbo.Trans_R.Tr_Data, ISNULL(dbo.Cards.Staff_Number, '') AS staff_number, dbo.Trans_R.Tr_Date, dbo.Trans_R.Tr_Time, dbo.Trans_R.Tr_Code
FROM dbo.Trans_R LEFT OUTER JOIN
dbo.Cards ON dbo.Trans_R.Tr_Data = dbo.Cards.Card_Number
WHERE (dbo.Cards.Staff_Number <> '')

• vTrans_R_v2_step1 > vTrans_R_v2 with min in and max out of time as RealDatang dan RealPulang, grouped by entry/leave code.

SELECT Tr_Data, staff_number, Tr_Date, MIN(Tr_Time) AS RealDatang, MAX(Tr_Time) AS RealPulang, Tr_Code
FROM dbo.vTrans_R_v2
GROUP BY Tr_Data, staff_number, Tr_Date, Tr_Code

• vTrans_R_v2_step2 > normalizing vTrans_R_v2_step1 so it don’t contain redundant double data, and also switch in out data and put into new column (needed for night shift data that leave time is smaller than entry time).

SELECT Tr_Data, staff_number, Tr_Date, MIN(RealDatang) AS RealDatang, MAX(RealPulang) AS RealPulang, MAX(RealPulang) AS RealDatangN,
MIN(RealDatang) AS RealPulangN
FROM dbo.vTrans_R_v2_step1
GROUP BY Tr_Data, staff_number, Tr_Date

• vTrans_R_v2_step3 > vTrans_R_v2_step2 with safety work minute calculation. Also contain switching process for night shift.

SELECT Tr_Data, staff_number, Tr_Date, RealDatang, CAST(SUBSTRING(RealDatang, 1, 2) AS int) * 60 + CAST(SUBSTRING(RealDatang, 3, 2) AS int)
AS RealDatangMinute, RealPulang, CAST(SUBSTRING(RealPulang, 1, 2) AS int) * 60 + CAST(SUBSTRING(RealPulang, 3, 2) AS int) AS RealPulangMinute,
RealDatangN, CAST(SUBSTRING(RealDatangN, 1, 2) AS int) * 60 + CAST(SUBSTRING(RealDatangN, 3, 2) AS int) AS RealDatangMinuteN, RealPulangN,
CAST(SUBSTRING(RealPulangN, 1, 2) AS int) * 60 + CAST(SUBSTRING(RealPulangN, 3, 2) AS int) AS RealPulangMinuteN,
(CAST(SUBSTRING(RealPulang, 1, 2) AS int) * 60 + CAST(SUBSTRING(RealPulang, 3, 2) AS int)) - (CAST(SUBSTRING(RealDatang, 1, 2) AS int)
* 60 + CAST(SUBSTRING(RealDatang, 3, 2) AS int)) AS WorkMinuteHSE, (CAST(SUBSTRING(RealPulangN, 1, 2) AS int)
* 60 + CAST(SUBSTRING(RealPulangN, 3, 2) AS int) + 1440) - (CAST(SUBSTRING(RealDatangN, 1, 2) AS int) * 60 + CAST(SUBSTRING(RealDatangN, 3, 2)
AS int)) AS WorkMinuteHSEN
FROM dbo.vTrans_R_v2_step2

• vTrans_R_v2_step4 > vTrans_R_v2_step3 with safety work hour calculation.

SELECT Tr_Data, staff_number, Tr_Date, RealDatang, RealDatangMinute, RealPulang, RealPulangMinute, RealDatangN, RealDatangMinuteN, RealPulangN,
RealPulangMinuteN, WorkMinuteHSE, WorkMinuteHSEN, RIGHT('00' + CAST(CAST(WorkMinuteHSE / 60 AS int) AS nvarchar), 2)
+ RIGHT('00' + CAST(WorkMinuteHSE % 60 AS nvarchar), 2) AS WorkHourHSE, RIGHT('00' + CAST(CAST(WorkMinuteHSEN / 60 AS int) AS nvarchar), 2)
+ RIGHT('00' + CAST(WorkMinuteHSEN % 60 AS nvarchar), 2) AS WorkHourHSEN
FROM dbo.vTrans_R_v2_step3

Process at EMPLOYEE Database

• Table_JamDailyShift > time schedule based on schedule code.
• Table_EmpTimeSheet2 > time schedule per employee.
• vTable_EmpTimeSheet2_v2 > Table_EmpTimeSheet2 joined Table_EmpTimeSheet2, to count pay work minute.

SELECT dbo.Table_EmpTimeSheet2.empid, dbo.Table_EmpTimeSheet2.tr_date, dbo.Table_EmpTimeSheet2.sched_code,
ISNULL(dbo.Table_JamDailyShift.JamDatang, 0) AS JamDatang, ISNULL(dbo.Table_JamDailyShift.JamPulang, 0) AS JamPulang,
CAST(SUBSTRING(dbo.Table_JamDailyShift.JamPulang, 1, 2) AS int) * 60 + CAST(SUBSTRING(dbo.Table_JamDailyShift.JamPulang, 3, 2) AS int)
AS JamPulangMinute, (CAST(SUBSTRING(dbo.Table_JamDailyShift.JamPulang, 1, 2) AS int)
* 60 + CAST(SUBSTRING(dbo.Table_JamDailyShift.JamPulang, 3, 2) AS int)) - (CAST(SUBSTRING(dbo.Table_JamDailyShift.JamDatang, 1, 2) AS int)
* 60 + CAST(SUBSTRING(dbo.Table_JamDailyShift.JamDatang, 3, 2) AS int)) AS MenitKerja, (CAST(SUBSTRING(dbo.Table_JamDailyShift.JamPulang, 1,
2) AS int) * 60 + CAST(SUBSTRING(dbo.Table_JamDailyShift.JamPulang, 3, 2) AS int) + 1440) - (CAST(SUBSTRING(dbo.Table_JamDailyShift.JamDatang,
1, 2) AS int) * 60 + CAST(SUBSTRING(dbo.Table_JamDailyShift.JamDatang, 3, 2) AS int)) AS MenitKerjaN
FROM dbo.Table_EmpTimeSheet2 LEFT OUTER JOIN
dbo.Table_JamDailyShift ON dbo.Table_EmpTimeSheet2.sched_code = dbo.Table_JamDailyShift.Kode

• vTable_EmpTimeSheet2_v2_step1 > from vTable_EmpTimeSheet2_v2, pay minute of daily type employee has to subtract by 60 since he have an hour rest time (shift employee don’t have rest time).

SELECT empid, tr_date, sched_code, JamDatang, JamPulang, JamPulangMinute, CASE upper(substring(sched_code, 1, 1))
WHEN 'N' THEN MenitKerjaN ELSE (CASE upper(substring(sched_code, 1, 1)) WHEN 'D' THEN MenitKerja - 60 ELSE MenitKerja END)
END AS MenitKerja
FROM dbo.vTable_EmpTimeSheet2_v2

• vAbsensi_step1 > left join Table_EmpTimeSheet2 (scheduled in out) with vTrans_R_v2_step4 (real in out). This view has time switch process for night shift employee, and processing on safety overtime minute.

SELECT dbo.vTable_EmpTimeSheet2_v2_step1.empid, dbo.vTable_EmpTimeSheet2_v2_step1.tr_date, dbo.vTable_EmpTimeSheet2_v2_step1.sched_code,
dbo.vTable_EmpTimeSheet2_v2_step1.JamDatang, dbo.vTable_EmpTimeSheet2_v2_step1.JamPulang,
dbo.vTable_EmpTimeSheet2_v2_step1.JamPulangMinute, dbo.vTable_EmpTimeSheet2_v2_step1.MenitKerja, CASE upper(substring(sched_code, 1, 1))
WHEN 'N' THEN isnull(ESOFNET.dbo.vTrans_R_v2_step4.RealPulang, 0) ELSE isnull(ESOFNET.dbo.vTrans_R_v2_step4.RealDatang, 0)
END AS RealDatang, CASE upper(substring(sched_code, 1, 1)) WHEN 'N' THEN isnull(ESOFNET.dbo.vTrans_R_v2_step4.RealDatang, 0)
ELSE isnull(ESOFNET.dbo.vTrans_R_v2_step4.RealPulang, 0) END AS RealPulang, CASE upper(substring(sched_code, 1, 1))
WHEN 'N' THEN isnull(ESOFNET.dbo.vTrans_R_v2_step4.RealDatangMinute, 0) ELSE isnull(ESOFNET.dbo.vTrans_R_v2_step4.RealPulangMinute, 0)
END AS RealPulangMinute, CASE upper(substring(sched_code, 1, 1)) WHEN 'N' THEN isnull(ESOFNET.dbo.vTrans_R_v2_step4.WorkMinuteHSEN, 0)
ELSE isnull(ESOFNET.dbo.vTrans_R_v2_step4.WorkMinuteHSE, 0) END AS WorkMinuteHSE, CASE upper(substring(sched_code, 1, 1))
WHEN 'N' THEN isnull(ESOFNET.dbo.vTrans_R_v2_step4.WorkHourHSEN, 0) ELSE isnull(ESOFNET.dbo.vTrans_R_v2_step4.WorkHourHSE, 0)
END AS WorkHourHSE,
ESOFNET.dbo.vTrans_R_v2_step4.WorkMinuteHSE - dbo.vTable_EmpTimeSheet2_v2_step1.MenitKerja AS OvertimeMinuteHSE
FROM dbo.vTable_EmpTimeSheet2_v2_step1 LEFT OUTER JOIN
ESOFNET.dbo.vTrans_R_v2_step4 ON dbo.vTable_EmpTimeSheet2_v2_step1.empid = ESOFNET.dbo.vTrans_R_v2_step4.staff_number AND
dbo.vTable_EmpTimeSheet2_v2_step1.tr_date = ESOFNET.dbo.vTrans_R_v2_step4.Tr_Date

• vAbsensi_step2 > left join vAbsensi_step2 with Table_Employee. Also has calculation on safety overtime hour and pay overtime minute.

SELECT dbo.vAbsensi_step1.empid, dbo.vAbsensi_step1.tr_date, dbo.vAbsensi_step1.sched_code, dbo.vAbsensi_step1.JamDatang,
dbo.vAbsensi_step1.JamPulang, dbo.vAbsensi_step1.JamPulangMinute, dbo.vAbsensi_step1.MenitKerja, dbo.vAbsensi_step1.RealDatang,
dbo.vAbsensi_step1.RealPulang, dbo.vAbsensi_step1.RealPulangMinute, dbo.vAbsensi_step1.WorkMinuteHSE, CASE upper(substring(sched_code, 1,
1)) WHEN 'D' THEN dbo.vAbsensi_step1.WorkMinuteHSE - 60 ELSE dbo.vAbsensi_step1.WorkMinuteHSE END AS PayWorkMinute,
dbo.vAbsensi_step1.WorkHourHSE, dbo.vAbsensi_step1.OvertimeMinuteHSE,
RIGHT('00' + CAST(CAST(dbo.vAbsensi_step1.OvertimeMinuteHSE / 60 AS int) AS nvarchar), 2)
+ RIGHT('00' + CAST(dbo.vAbsensi_step1.OvertimeMinuteHSE % 60 AS nvarchar), 2) AS OvertimeHourHSE,
dbo.vAbsensi_step1.RealPulangMinute - dbo.vAbsensi_step1.JamPulangMinute AS PayOvertimeMinute
FROM dbo.vAbsensi_step1 LEFT OUTER JOIN
dbo.Table_Employee ON dbo.vAbsensi_step1.empid = dbo.Table_Employee.EmpId

• vAbsensi_step3 > process vAbsensi_step2, convert pay work minute to pay work hour.

SELECT empid, tr_date, sched_code, JamDatang, JamPulang, MenitKerja, RealDatang, RealPulang, WorkMinuteHSE, PayWorkMinute, WorkHourHSE,
RIGHT('00' + CAST(CAST(PayWorkMinute / 60 AS int) AS nvarchar), 2) + RIGHT('00' + CAST(PayWorkMinute % 60 AS nvarchar), 2) AS PayWorkHour,
OvertimeMinuteHSE, OvertimeHourHSE, CASE substring(sched_code, 1, 1)
WHEN 'R' THEN PayWorkMinute WHEN 'H' THEN PayWorkMinute ELSE PayOvertimeMinute END AS PayOvertimeMinute
FROM dbo.vAbsensi_step2

• vAbsensi_step4 > process vAbsensi_step3, convert pay overtime minute to pay overtime hour.

SELECT empid, tr_date, sched_code, JamDatang, JamPulang, MenitKerja, RealDatang, RealPulang, WorkMinuteHSE, PayWorkMinute, WorkHourHSE,
PayWorkHour, OvertimeMinuteHSE, OvertimeHourHSE, PayOvertimeMinute, RIGHT('00' + CAST(CAST(PayOvertimeMinute / 60 AS int) AS nvarchar), 2)
+ RIGHT('00' + CAST(PayOvertimeMinute % 60 AS nvarchar), 2) AS PayOvertimeHour
FROM dbo.vAbsensi_step3


vAbsensi_step4 is the final process producing the result.

1. Right click on the database tree > All Task > Restore Database



2. Choose Restore From Device and click Select Devices… button



3. Click Add button.



4. Click … button to select the backup file.



5. Select database source.



6. Click option tab on Restore Database page, check Force restore over existing database. Direct path “Move to physical file name” to [MSSQL Installation path]/Data

Go to the server tree > Management > Server Agent > Jobs. Right click for creating a new job.



Push the new button to create step on backuping database.



Script below doing command: create a database backup, stored on harddisk media, stored on specified FOLDER_PATH, save in .BAKfile format, with report. Please check xp_sqlmaint documentation for further explanation.

EXEC master.dbo.xp_sqlmaint '-S [SERVER_NAME] -U sa -P [PASSWORD] -D [DATABASE_NAME] -CkDB -CkAl -CkCat -BkUpMedia DISK -BkUpDB [C:\FOLDER_PATH] -BkExt BAK -DelBkUps 2weeks -BkUpOnlyIfClean -Rpt [C:\FOLDER_PATH\FILE_NAME.txt']



Tab notification define notification report when certain condition occurs.



There is home work in this routine. We still can not run batch file from this sql job schedule. Currently I execute batch file as a service, or we can use third party scheduler software. May be next time…

----

A little documentation of xp_sqlmaint stored procedure:

• -S ServerName
  
• -U LoginID
• -P password
• -D DatabaseName
• -Rpt text_file Path and name of the report file.
• -CkDB | -CkDBNoIdx Advises SQL Server to check database integrity.. -CkDBNoIdx skips indexes during the integrity check.
• -CkAl | -CkAlNoIdx -CkAl advises SQL Server to run DBCC NEWALLOC statement for all tables and indexes. -CkAlNoIdx omits checking of indexes with DBCC NEWALLOC
• -CkCat Check consistency between system tables
• -BkUpDB [backup_path] Specifies that full database backup must be performed. Backup_path is the full path to the backup destination files.
• -BkUpMedia Specify backup media, can be disk or tape.
  
• -DelBkUps
• -BkUpOnlyIfClean Advises SQLMaint to backup database only if integrity checks did not find any errors.
• -VrfyBackup Executes RESTORE VERIFYONLY at completion of each backup to ensure that backups that were just created are readable.

License for company backup software is expired. Meanwhile, the disaster recovery simulation schedule is approaching. So, this time, we have to make it done manually. We need to define steps on this agenda. Let see…

• Define components that need to be backuped:

1. Database. We need to create database backup job that will run every day. The backup result file is in .bak format. Later we will convert to zip file for saving space. Currently we have about 10 database to backup.
2. Intranet, executable inhouse software and the source code, including all third party compnonent since the source code wouldn’t compile without them.
3. System backup, consist of php folder, configuration, and also all used third party component. It will save an amount of time since php and third party configuration are potential in creating system error.

• Set folder permission for remote server backup destination, since there is a scheduler from source server copying and writing backup file (.zip format) to the remote destination server. We will use 7z for the compressing routing, since its free, can uncompress wide range compressing file, and support for batch instruction.
• We need a batch job for compressing all backup file, and copying it to remote destination server.
• Database backup file run every day. Inhouse software backup run every two weeks to capture any modification of the program. While system backup run once a month. Routine is done by batch file.
• Deleting backup file for expired/out of date backup file, for freeing space. For database backup, we only keep five job days for daily backup, one backup for weekly backup, one monthly, and one yearly. While other backup we only reserve 2 last backup.
• Prepare documentation, such as how to configure system, configure php, database restoration, inhouse application and intranet configuration, etc.

We will discuss those backup more technically on later article.