SQL Server 数据库存储过程实例

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

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,1)=ic2.cInvCCode
WHERE Ic2.cInvCCode='5' OR Ic2.cInvCCode='7'
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,3)=ic2.cInvCCode
WHERE Ic2.cInvCCode='720'
GROUP BY det2.cDepName,ic2.cInvCName,rd.dDate,ic2.cInvCCode,det2.cDepCode

 

EXEC sys.sp_rename p_lll,p_lll　　——修改名称 旧名字，新名字
DROP PROCEDURE dbo.p_lxc　　——删除