根据年度月份计算员工当前正在工作(活动emp)和员工数量目前不工作(InActive emp) [英] Count the no of Employee Presently working (Active emp) and count of Employee Presently not working (InActive emp) based on Month of the Year
问题描述
请任何人帮助我
我在表中有一个表名作为Empmaster我有列Emp_Joinindate,Emp_leavingdate,Emp_Status
我的问题是
假设
该公司在2012年1月雇用了100名员工
和2012年9月50名员工离开公司
和2012年10月再次公司雇用了25名新员工
这里我正在通过月份和年份我希望显示这些数据,例如有多少员工目前正在工作(Active Emp)以及如何许多员工在一年中的特定月份离开公司
假设我通过月份= jan和year = 2012
PresentWorkingEmp = 100且leftEmp = 0
假设如果通过月份= sept且年份= 2012
PresentWorkingEmp = 50且leftEmp = 50
假设如果通过月份= 10月和年份= 2012
PresentWorkingEmp = 75且leftEmp = 0因为(在月份公司雇用了2个月) 5所以50 + 25 = 75 leftEmp = 0)
请任何人帮助我
试试这个。如果您已发布样本数据。
声明 @ m varchar ( 2 ), @ y varchar ( 4 )
set @ m = ' 08'
set @ y = 2012
DECLARE @ date DATETIME
SELECT @ date = convert ( datetime , @ m + ' - 01 ' + ' - ' + @ y )
- select @date
SELECT @ date = DATEADD(d,-1,DATEADD(m,DATEDIFF(m, 0 , @ date )+ 1 , 0 ))
声明 @Empmaster table (
Emp_Joinindate datetime ,Emp_leavingdate datetime ,Emp_Status varchar ( 2 )
)
insert @Empmaster 值(' 04 -01 -2012',' 05-01-2012',' 1')
insert @Empmaster 值(' 04-01-2012', NULL ,' 1')
insert @Empmaster 值(' 05-01-2012', null ,' 1')
insert @Empmaster 值(' 05-01-2012',' 07-01-2012',' 1')
insert @Empmaster 值(' 06-01-2012', null ,' 1')
insert @Empmaster 值(' 07-01-2012 ', null ,' 1')
//活跃的emp
选择 * 来自 @Empmaster
其中 Emp_Joinindate< = @ date 和(ISNULL (Emp_leavingdate,' 12-31-9999'))> = @ date
// Emp left
选择 * 来自 @Empmaster
其中 Emp_Joinindate< = @ date 和(ISNULL(Emp_leavingdate,' 12-31-9999'))< = @ date
你也可以尝试这个
< span class =code-keyword> SELECT SUM( CASE WHEN Emp_Status = 1 AND datepart(MONTH,[Emp_Joinindate])= DATEPART(月,GETDATE() ) AND datepart(YEAR,[Emp_Joinindate])= DATEPART(年,GETDATE())
AND Emp_LeftDate IS NULL
那么 1 ELSE 0 END ) as WorkingEmp,
SUM( CASE WHEN Emp_Status = 0 AND datepart( MONTH,[Emp_Joinindate])= DATEPART(月,GETDATE()) AND datepart(年,[Emp_Joinindate])= DATEPART(年,GETDATE())
AND Emp_Lef tDate IS NOT NULL
THEN 1 ELSE 0 END ) as LeftEmployee,
来自 EmployeeMaster
对于正在工作的员工我假设他们的离职日期显然是空的
选择*来自Empmaster,其中Emp_LeavindDate为空>
和DatePart(mm,EmpJoinInDate)='数字中的月份参数'
和DatePart(yy,EmpJoinInDate)='年数参数此处数字'
对于已经离开的员工我假设他们的离职日期显然不是空的
选择*来自Empmaster Emp_LeavindDate不为空的地方
和DatePart(mm,EmpLeavingDate)='数字中的月份参数'
和DatePart(yy,EmpLeavingDate)='年份参数此处在数字
Please can any one help me
I have one table name as Empmaster in that table i have columns Emp_Joinindate ,Emp_leavingdate,Emp_Status
my question is
Suppose
the company hired 100 employee in the month of January 2012
and September 2012 50 employee left from the company
and October 2012 again company hired 25 new employees
here i am passing month and year soo i want show this data like how many employee presently working (Active Emp)and and how many Employee left the company on particular month of year
suppose if i pass month=jan and year =2012
PresentWorkingEmp=100 and leftEmp=0
suppose if pass month=sept and year =2012
PresentWorkingEmp=50 and leftEmp=50
suppose if pass month=October and year =2012
PresentWorkingEmp=75 and leftEmp=0 because (in month oct company hired 25 so 50+25=75 leftEmp=0)
please can any one help me
Try this. If you have post the sample data.
declare @m varchar(2), @y varchar(4) set @m = '08' set @y=2012 DECLARE @date DATETIME SELECT @date = convert(datetime, @m + '-01' + '-' + @y) --select @date SELECT @date=DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0)) declare @Empmaster table ( Emp_Joinindate datetime,Emp_leavingdate datetime,Emp_Status varchar(2) ) insert @Empmaster values ('04-01-2012','05-01-2012','1') insert @Empmaster values ('04-01-2012',NULL,'1') insert @Empmaster values ('05-01-2012',null,'1') insert @Empmaster values ('05-01-2012','07-01-2012','1') insert @Empmaster values ('06-01-2012',null,'1') insert @Empmaster values ('07-01-2012',null,'1') //Active emp select * from @Empmaster where Emp_Joinindate <= @date and (ISNULL(Emp_leavingdate,'12-31-9999')) >= @date // Emp left select * from @Empmaster where Emp_Joinindate <= @date and (ISNULL(Emp_leavingdate,'12-31-9999')) <= @date
You can try this one too
SELECT SUM(CASE WHEN Emp_Status = 1 AND datepart(MONTH,[Emp_Joinindate]) = DATEPART(Month,GETDATE()) AND datepart(YEAR,[Emp_Joinindate]) = DATEPART(YEAR,GETDATE()) AND Emp_LeftDate IS NULL THEN 1 ELSE 0 END) as WorkingEmp , SUM(CASE WHEN Emp_Status = 0 AND datepart(MONTH,[Emp_Joinindate]) = DATEPART(Month,GETDATE()) AND datepart(YEAR,[Emp_Joinindate]) = DATEPART(YEAR,GETDATE()) AND Emp_LeftDate IS NOT NULL THEN 1 ELSE 0 END) as LeftEmployee , from EmployeeMaster
For Employees Who Are Working I am assuming That Their Leaving Date Is Null Obviously
Select * From Empmaster Where Emp_LeavindDate Is Null
And DatePart(mm,EmpJoinInDate) = 'MONTH PARAMETER HERE In Numeric'
And DatePart(yy,EmpJoinInDate) = 'YEAR MONTH PARAMETER HERE In Numeric'
For Employees Who Have Left I am assuming That Their Leaving Date Is Not Null Obviously
Select * From Empmaster Where Emp_LeavindDate Is Not Null
And DatePart(mm,EmpLeavingDate) = 'MONTH PARAMETER HERE In Numeric'
And DatePart(yy,EmpLeavingDate) = 'YEAR MONTH PARAMETER HERE In Numeric'
这篇关于根据年度月份计算员工当前正在工作(活动emp)和员工数量目前不工作(InActive emp)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!