mystupor（部分）.sql

来自「软件外包人事综合管理系统,对员工的管理,部门管理,以及考勤管理和薪资管理.总得来」· SQL 代码 · 共 56 行

USE HR1
GO
CREATE TABLE Evaluation
  (EvaMonth	char(10) NOT NULL,
   Emp_Id 	int NOT NULL,
   ztEva	varchar(200),
   jlReason    	varchar(200),
   jlAmount	smallint,
   cfReason	varchar(200),
   cfAmount	smallint,
   Memo	varchar(200)
  )
GO


USE HR1
GO
CREATE TABLE Experience
  (Id	tinyint PRIMARY KEY IDENTITY,
   EmpId 	int NOT NULL,
   Start_Date 	char(10),
   End_Date    	char(10),
   School_Org	varchar(50),
   Title	varchar(20)
  )
GO

USE HR1
GO
CREATE TABLE Family
  (Id	tinyint PRIMARY KEY IDENTITY,
   EmpId 	int NOT NULL,
   Name	varchar(50) NOT NULL,
   Sex    	char(2),
   Age	tinyint,
   Relationship	varchar(20),
   WorkingOrg	varchar(40)
  )
GO

USE HR1
GO
CREATE VIEW dbo.v_SumCheck
AS
SELECT LEFT(dbo.Checkin.CheckDate, 7) AS CheckMonth, dbo.Employees.EmpName, 
      SUM(dbo.Checkin.qqDays) AS Sumqq, SUM(dbo.Checkin.ccDays) AS SumCc, 
      SUM(dbo.Checkin.bjDays) AS SumBj, SUM(dbo.Checkin.sjDays) AS SumSj, 
      SUM(dbo.Checkin.kgDays) AS SumKg, SUM(dbo.Checkin.fdxjDays) AS SumFdxj, 
      SUM(dbo.Checkin.nxjDays) AS SumNxj, SUM(dbo.Checkin.dxjDays) AS SumDxj, 
      SUM(dbo.Checkin.cdMinutes) AS SumCd, SUM(dbo.Checkin.ztMinutes) AS SumZt, 
      SUM(dbo.Checkin.ot1Days) AS SumOt1, SUM(dbo.Checkin.ot2Days) AS SumOt2, 
      SUM(dbo.Checkin.ot3Days) AS SumOt3, dbo.Employees.DepId
FROM dbo.Checkin INNER JOIN
      dbo.Employees ON dbo.Checkin.Emp_Id = dbo.Employees.EmpId
GROUP BY LEFT(dbo.Checkin.CheckDate, 7), dbo.Employees.EmpName, 
      dbo.Checkin.Emp_Id, dbo.Employees.DepId