SQL存储过程的问题

MrVN_Pan 2015-10-02 02:58:24

USE [LMGernal]

GO

/****** Object:  StoredProcedure [dbo].[LM_DeptTotal]    Script Date: 2015/10/2 13:27:11 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:    <彭传森>

-- Create date: <2015-09-24>

-- Description:   <用于查询部门就餐汇总报表>

-- =============================================

ALTER PROCEDURE [dbo].[LM_DeptTotal]

    -- Add the parameters for the stored procedure here

@CompanyID nvarchar(50),   --公司ID

@employeenum nvarchar(50),  --员工工号

@deptcode nvarchar(50), --部门编码

@startdate nvarchar(50),  --开始日期

@enddate nvarchar(50),  --结束日期

@machine nvarchar(100),  --机器编号

@language nvarchar(10)   --语言标识

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;



select

A.BrushCardID,A.EmployeeID,A.EmployeeNum,A2.DeptID,A2.DeptCode,convert(nvarchar(10),A.BrushDate,120) BrushDate,convert(nvarchar(5),A.BrushTime) BrushTime,A.MachineNum

,dbo.GetLanguangeName('BasicEmployee',A1.EmployeeID,@language) EmployeeName --员工姓名

,dbo.GetLanguangeName('BasicDept',A2.DeptID,@language) DeptName --部门名称

into #tb2

from ConsumeBruchCard A

left join BasicEmployee A1 on A.EmployeeNum=A1.EmployeeNum

left join BasicDept A2 on A1.DeptID=A2.DeptID

where (isnull(@CompanyID,'')='' or A1.CompanyID=@CompanyID)

and (ISNULL(@employeenum,'')='' or A.EmployeeNum in (select * from dbo.fn_splitChars(@employeenum,',')))

and (ISNULL(@deptcode,'')='' or A2.DeptCode in (select * from dbo.fn_splitChars(@deptcode,',')))

and (ISNULL(@machine,'')='' or A.MachineNum in (select * from dbo.fn_splitChars(@machine,',')))

and A.BrushDate between isnull(@startdate,'2001-01-01') and isnull(@enddate,'2099-01-01')

order by DeptName,BrushTime,EmployeeNum





select A.StartTime,A.EndTime,A.Price,isnull(A1.LanguageName,A.CosumeDefaultName) TimeName,A.TimeCode

into #tb1

from ConsumeGroupTime A

left join SystemAllName A1 on A.TimeID=A1.TableLineID and A1.TableCode='ConsumeGroupTime' and A1.SysMLFlag=@language

where  A.GroupID in (select GroupID from ConSumeMachine where MachineNum in(select distinct MachineNum from #tb2))





select A.*,convert(decimal(24,2),isnull(A1.Price,0)) BruchPrice,A1.TimeName,A1.TimeCode

into #tb3

from #tb2 A

left join #tb1 A1 on A1.StartTime<=A.BrushTime and A1.EndTime>A.BrushTime



select

DeptName

,sum(case when TimeCode='Breakfast' then BruchPrice  else 0 end ) BreakfastMny

,sum(case when TimeCode='Breakfast' then 1  else 0 end ) Breakfastnum

,sum(case when TimeCode='Lunch' then BruchPrice  else 0 end ) LunchMny

,sum(case when TimeCode='Lunch' then 1  else 0 end ) Lunchnum

,sum(case when TimeCode='Dinner' then BruchPrice  else 0 end ) DinnerMny

,sum(case when TimeCode='Dinner' then 1  else 0 end ) Dinnernum

,sum(BruchPrice) TotalMny

,count(*) Totalnum

from #tb3

group by DeptName





drop table #tb1

drop table #tb2



drop table #tb3



END