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(),YEAR(@XDate))+'-'+CONVERT(nchar(),MONTH(@XDate))+'-01')

select T1.cDept_num,T1.cDepName,isnull(T2.aaa,) as PriveMonth,isnull(T3.aaa,) as NowMonthIn,isnull(T4.aaa,) as NowMonthGo,(isnull(T2.aaa,) + isnull(T3.aaa,)  - isnull(T4.aaa,) ) 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),) 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),) as aaa,h.cDept_num from hr_hi_person h WHERE dEnterUnitDate>=@NowMonFir AND dEnterUnitDate<DATEADD(MONTH,,@NowMonFir)  GROUP BY cDept_num ) T3

    on T1.cDept_num=T3.cDept_num

    left JOIN

    (select isnull(COUNT(h.cDept_num),) as aaa,h.cDept_num from hr_hi_person h WHERE dLeaveDate>=@NowMonFir AND dLeaveDate<DATEADD(MONTH,,@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,)=det2.cDepCode

JOIN InventoryClass ic2

ON LEFT(rds.cInvCode,)=ic2.cInvCCode

WHERE Ic2.cInvCCode='' OR Ic2.cInvCCode=''

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,)=det2.cDepCode

JOIN InventoryClass ic2

ON LEFT(rds.cInvCode,)=ic2.cInvCCode

WHERE Ic2.cInvCCode=''

GROUP BY det2.cDepName,ic2.cInvCName,rd.dDate,ic2.cInvCCode)a

declare @YFDate datetime

DECLARE @MFDate datetime

SET @YFDate=CONVERT(datetime,convert(nvarchar(),YEAR(@LDate))+'-01-01')

SET @MFDate=CONVERT(datetime,convert(nvarchar(),YEAR(@LDate))+'-'+convert(nvarchar(),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

SELECT sum(rds.iPrice ) AS iPrice ,ic2.cInvCName,ic2.cInvCCode,det2.cDepName,det2.cDepCode,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 rd.cDepCode=det2.cDepCode

JOIN InventoryClass ic2    --存货分类档案

ON LEFT(rds.cInvCode,)=ic2.cInvCCode

WHERE Ic2.cInvCCode='' OR Ic2.cInvCCode=''

GROUP BY det2.cDepName,ic2.cInvCName,rd.dDate,ic2.cInvCCode,det2.cDepCode

UNION

SELECT sum(rds.iPrice ) AS iPrice ,ic2.cInvCName,ic2.cInvCCode,det2.cDepName,det2.cDepCode,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 rd.cDepCode=det2.cDepCode

JOIN InventoryClass ic2

ON LEFT(rds.cInvCode,)=ic2.cInvCCode

WHERE Ic2.cInvCCode=''

GROUP BY det2.cDepName,ic2.cInvCName,rd.dDate,ic2.cInvCCode,det2.cDepCode