![]()
USE [UFDATA_999_2014]
GO
/****** Object: StoredProcedure [dbo].[p_XMonPerNums] Script Date: 06/12/2017 16:32:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>dEnterUnitDate
-- Description: <Description,,> 当月在职员工统计
-- =============================================
ALTER PROCEDURE [dbo].[p_XMonPerNums]
@XDate datetime
AS
BEGIN
declare @NowMonFir datetime
set @NowMonFir=convert(datetime,convert(nchar(15),YEAR(@XDate))+'-'+CONVERT(nchar(15),MONTH(@XDate))+'-01')
select T1.cDept_num,T1.cDepName,isnull(T2.aaa,0) as PriveMonth,isnull(T3.aaa,0) as NowMonthIn,isnull(T4.aaa,0) as NowMonthGo,(isnull(T2.aaa,0) + isnull(T3.aaa,0) - isnull(T4.aaa,0) ) as NowMonthNum from(
(select h.cDept_num,d.cDepName from Department d join hr_hi_person h on d.cDepCode=h.cDept_num GROUP BY h.cDept_num,d.cDepName ) T1
left join
(select isnull(COUNT(h.cDept_num),0) as aaa,h.cDept_num from hr_hi_person h WHERE dEnterUnitDate <@NowMonFir AND (dLeaveDate is NULL OR dLeaveDate>=@NowMonFir) GROUP BY cDept_num ) T2
on T1.cDept_num=T2.cDept_num
left JOIN
(select isnull(COUNT(h.cDept_num),0) as aaa,h.cDept_num from hr_hi_person h WHERE dEnterUnitDate>=@NowMonFir AND dEnterUnitDate<DATEADD(MONTH,1,@NowMonFir) GROUP BY cDept_num ) T3
on T1.cDept_num=T3.cDept_num
left JOIN
(select isnull(COUNT(h.cDept_num),0) as aaa,h.cDept_num from hr_hi_person h WHERE dLeaveDate>=@NowMonFir AND dLeaveDate<DATEADD(MONTH,1,@NowMonFir) GROUP BY cDept_num ) T4
on T1.cDept_num=T4.cDept_num
)
END
当月在职员工统计
![]()
CREATE PROCEDURE p_lll
@LDate datetime
AS
BEGIN
select * into
#templxc
from
(
SELECT sum(rds.iPrice ) AS iPrice ,ic2.cInvCName,ic2.cInvCCode,det2.cDepName,rd.dDate
FROM rdrecord11 rd
JOIN rdrecords11 rds
ON rd.id=rds.id
JOIN Inventory inv
ON rds.cInvCode=inv.cInvCode
JOIN Department det2
ON LEFT(rd.cDepCode,2)=det2.cDepCode
JOIN InventoryClass ic2
ON LEFT(rds.cInvCode,1)=ic2.cInvCCode
WHERE Ic2.cInvCCode='5' OR Ic2.cInvCCode='7'
GROUP BY det2.cDepName,ic2.cInvCName,rd.dDate,ic2.cInvCCode
UNION
SELECT sum(rds.iPrice ) AS iPrice ,ic2.cInvCName,ic2.cInvCCode,det2.cDepName,rd.dDate
FROM rdrecord11 rd
JOIN rdrecords11 rds
ON rd.id=rds.id
JOIN Inventory inv
ON rds.cInvCode=inv.cInvCode
JOIN Department det2
ON LEFT(rd.cDepCode,2)=det2.cDepCode
JOIN InventoryClass ic2
ON LEFT(rds.cInvCode,3)=ic2.cInvCCode
WHERE Ic2.cInvCCode='720'
GROUP BY det2.cDepName,ic2.cInvCName,rd.dDate,ic2.cInvCCode)a
declare @YFDate datetime
DECLARE @MFDate datetime
SET @YFDate=CONVERT(datetime,convert(nvarchar(15),YEAR(@LDate))+'-01-01')
SET @MFDate=CONVERT(datetime,convert(nvarchar(15),YEAR(@LDate))+'-'+convert(nvarchar(15),month(@LDate))+'-01')
SELECT T0.cDepname,T0.cInvCName,T1.a as TDate,T2.a as TMonth,T3.a as TYear from(
(SELECT cDepname,cInvCCode,cInvCName from #templxc group BY cDepname,cInvCCode,cInvCName) T0
left join
(SELECT cInvCCode,SUM(iPrice) As a FROM #templxc where dDate=@LDate GROUP BY cInvCCode) T1
on T0.cInvCCode=T1.cInvCCode
left join
(select cInvCCode,SUM(iPrice) As a FROM #templxc where dDate>=@MFDate and dDate<=@LDate GROUP BY cInvCCode) T2
on T1.cInvCCode=T2.cInvCCode
left join
(select cInvCCode,SUM(iPrice) As a FROM #templxc where dDate>=@YFDate and dDate<=@LDate GROUP BY cInvCCode) T3
on T1.cInvCCode=T3.cInvCCode)
ORDER BY cDepname
END
GO
