USE [db]
GO

/****** Object:  View [dbo].[Payroll]    Script Date: 2016-08-20 11:52:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create view [dbo].[Payroll] as select 
dbo.dindan.name1, 
dbo.dindan.name2, 
dbo.dindan.txtype, 
dbo.dindan.taoxiname,
dbo.dindan.taoxijiage,
(case when dbo.dindanbukuan.money is null then '0' else dbo.dindanbukuan.money end) as money, 
(case when dbo.dindanbukuan.date is null then dbo.dindan.time2 else (case when dbo.dindan.time2 > dbo.dindanbukuan.date then dbo.dindan.time2 else dbo.dindanbukuan.date end) end) as Paydate,
dbo.dindanbukuan.kind, 
dbo.dindanbukuan.ren,
dbo.dindanbukuan.bz, 
dbo.dindanbukuan.id,
dbo.dindanbukuan.paytype,
dbo.dindan.waiter1,
dbo.dindan.waiter12,
dbo.dindan.waiter13,
dbo.dindan.waiter14,
dbo.dindan.waiter2,
dbo.dindan.waiter22,
dbo.dindan.waiter23,
dbo.dindan.waiter24,
dbo.dindan.waiter3,
dbo.dindan.waiter4,
dbo.dindan.waiter5,
dbo.dindan.waiter6,
dbo.dindan.waiter7,
dbo.dindan.waiter8,
dbo.dindan.waiter9 from dbo.dindan 
left JOIN dbo.dindanbukuan ON dbo.dindan.id = dbo.dindanbukuan.id 
where dbo.dindan.status = 'OK'



GO

/****** Script for SelectTopNRows command from SSMS  ******/
use [db]
go
declare @name nvarchar(50)		--员工姓名;
declare @btm  nvarchar(24)		--开始时间;
declare @etm  nvarchar(24)		--结束时间;

set @name = '黄承武'
set @btm = '2016-07-01'
set @etm = '2016-07-31'

SELECT
	   [id]
	  ,[name1]
      ,[name2]
      ,[txtype]
      ,[taoxiname]
	  ,[taoxijiage]
      ,[money]
      ,[Paydate]
      ,[kind]
      ,[ren]
      ,[bz]
      ,[paytype]
	  ,[waiter1]
      ,[waiter12]
      ,[waiter13]
      ,[waiter14]
      ,[waiter2]
      ,[waiter22]
      ,[waiter23]
      ,[waiter24]
      ,[waiter3]
      ,[waiter4]
      ,[waiter5]
      ,[waiter6]
      ,[waiter7]
      ,[waiter8]
      ,[waiter9]
  FROM [db].[dbo].[Payroll] where  ( Paydate>=@btm and Paydate<=@etm ) and 
(  [waiter1]=@name				--摄影
or [waiter12]=@name				--摄影
or [waiter13]=@name				--摄影
or [waiter14]=@name				--摄影
or [waiter2]=@name				--化妆
or [waiter22]=@name				--化妆
or [waiter23]=@name				--化妆
or [waiter24]=@name				--化妆
or [waiter3]=@name				--选片
or [waiter4]=@name				--设计
or [waiter5]=@name				--初修
or [waiter5]=@name				--接单
or [waiter7]=@name				--精修
or [waiter8]=@name				--看样
or [waiter9]=@name				--????
or [waiter12]=@name				--摄影助理
or [waiter22]=@name				--化妆助理	 
)

-- 计算出所有业绩总和;
SELECT sum(convert(int,money)) FROM [db].[dbo].[Payroll] where  ( Paydate>=@btm and Paydate<=@etm ) and (
  [waiter1]=@name				--摄影
or [waiter12]=@name				--摄影
or [waiter13]=@name				--摄影
or [waiter14]=@name				--摄影
or [waiter2]=@name				--化妆
or [waiter22]=@name				--化妆
or [waiter23]=@name				--化妆
or [waiter24]=@name				--化妆
or [waiter3]=@name				--选片
or [waiter4]=@name				--设计
or [waiter5]=@name				--初修
or [waiter5]=@name				--接单
or [waiter7]=@name				--精修
or [waiter8]=@name				--看样
or [waiter9]=@name				--????
or [waiter12]=@name				--摄影助理
or [waiter22]=@name				--化妆助理	 
)

-- 计算出订单总金额;(不可用，这样算会出现重复叠加的订单金额)
SELECT sum(convert(int,taoxijiage)) from [db].[dbo].[Payroll] where  ( Paydate>=@btm and Paydate<=@etm ) and 
(  [waiter1]=@name				--摄影
or [waiter12]=@name			    --摄影
or [waiter13]=@name			    --摄影
or [waiter14]=@name			    --摄影
or [waiter2]=@name			    --化妆
or [waiter22]=@name			    --化妆
or [waiter23]=@name			    --化妆
or [waiter24]=@name			    --化妆
or [waiter3]=@name			    --选片
or [waiter4]=@name			    --设计
or [waiter5]=@name			    --初修
or [waiter5]=@name			    --接单
or [waiter7]=@name			    --精修
or [waiter8]=@name			    --看样
or [waiter9]=@name			    --????
or [waiter12]=@name			    --摄影助理
or [waiter22]=@name			    --化妆助理	 
)

-- 计算出订单总金额（去掉重复叠加）;
SELECT sum(convert(int,pay)) FROM (select max(taoxijiage) as pay from [db].[dbo].[Payroll] where ( Paydate>=@btm and Paydate<=@etm ) and 
(  [waiter1]=@name				--摄影
or [waiter12]=@name			    --摄影
or [waiter13]=@name			    --摄影
or [waiter14]=@name			    --摄影
or [waiter2]=@name			    --化妆
or [waiter22]=@name			    --化妆
or [waiter23]=@name			    --化妆
or [waiter24]=@name			    --化妆
or [waiter3]=@name			    --选片
or [waiter4]=@name			    --设计
or [waiter5]=@name			    --初修
or [waiter5]=@name			    --接单
or [waiter7]=@name			    --精修
or [waiter8]=@name			    --看样
or [waiter9]=@name			    --????
or [waiter12]=@name			    --摄影助理
or [waiter22]=@name			    --化妆助理	 
) and kind <> '1' group by id) a