USE [作业数据库]
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[FK_baogaof1](@timedatetime) -- drop function FK_baogaof1
returns @mytableTABLE(委托报告量 varchar(100),CECR1 int,CECR11 int,CECR3 int,CECR16int,CECR6 int,CECR7 int,OTHERS int,总量int)
as
begin
declare @CECR1Num int
declare @CECR11Num int
declare @CECR3Num int
declare @CECR16 int
declare @CECR6 int
declare @CECR7 int
declare @OtherNum int
declare @total int
declare @time1 datetime
set @time1=null
--------------如果是星期一，查询：逾期量 今天星期二三四五 和下星期一。
if datename(weekday, @time)= '星期一'
begin
-----------------------逾期量--------------------------------------------------------------------------------------------
select@CECR1Num=dbo.FK_baogaof2(@time,'CECR1',1)
select @CECR11Num=dbo.FK_baogaof2(@time,'CECR1-1',1)
select @CECR3Num=dbo.FK_baogaof2(@time,'CECR3',1)
select @CECR16=dbo.FK_baogaof2(@time,'CECR16',1)
select @CECR6=dbo.FK_baogaof2(@time,'CECR6',1)
select @CECR7=dbo.FK_baogaof2(@time,'CECR7',1)
select @OtherNum=dbo.FK_baogaof2(@time,'其他',1)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable select'逾期量',@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
--今天
select@CECR1Num=dbo.FK_baogaof2(@time,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 1,@time) --星期二
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 2,@time) --星期三
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 3,@time) --星期四
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 4,@time) --星期五
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 7,@time) --下周一
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
end
--------------如果是星期二，查询：逾期量 今天星期三四五 和下星期一二。
if datename(weekday, @time)= '星期二'
begin
-----------------------逾期量--------------------------------------------------------------------------------------------
select@CECR1Num=dbo.FK_baogaof2(@time,'CECR1',1)
select @CECR11Num=dbo.FK_baogaof2(@time,'CECR1-1',1)
select @CECR3Num=dbo.FK_baogaof2(@time,'CECR3',1)
select @CECR16=dbo.FK_baogaof2(@time,'CECR16',1)
select @CECR6=dbo.FK_baogaof2(@time,'CECR6',1)
select @CECR7=dbo.FK_baogaof2(@time,'CECR7',1)
select @OtherNum=dbo.FK_baogaof2(@time,'其他',1)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable select'逾期量',@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
--今天
select@CECR1Num=dbo.FK_baogaof2(@time,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 1,@time) --星期三
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 2,@time) --星期四
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 3,@time) --星期五
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 6,@time) --下周一
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 7,@time) --下周二
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
end
--------------如果是星期三，查询：逾期量 今天星期四五 和下星期一二三。
if datename(weekday, @time)= '星期三'
begin
-----------------------逾期量--------------------------------------------------------------------------------------------
select@CECR1Num=dbo.FK_baogaof2(@time,'CECR1',1)
select @CECR11Num=dbo.FK_baogaof2(@time,'CECR1-1',1)
select @CECR3Num=dbo.FK_baogaof2(@time,'CECR3',1)
select @CECR16=dbo.FK_baogaof2(@time,'CECR16',1)
select @CECR6=dbo.FK_baogaof2(@time,'CECR6',1)
select @CECR7=dbo.FK_baogaof2(@time,'CECR7',1)
select @OtherNum=dbo.FK_baogaof2(@time,'其他',1)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable select'逾期量',@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
--今天
select@CECR1Num=dbo.FK_baogaof2(@time,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 1,@time) --星期四
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 2,@time) --星期五
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 5,@time) --下周一
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 6,@time) --下周二
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 7,@time) --下周三
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
end
--------------如果是星期四，查询：逾期量 今天星期五 和下星期一二三四。
if datename(weekday, @time)= '星期四'
begin
-----------------------逾期量--------------------------------------------------------------------------------------------
select@CECR1Num=dbo.FK_baogaof2(@time,'CECR1',1)
select @CECR11Num=dbo.FK_baogaof2(@time,'CECR1-1',1)
select @CECR3Num=dbo.FK_baogaof2(@time,'CECR3',1)
select @CECR16=dbo.FK_baogaof2(@time,'CECR16',1)
select @CECR6=dbo.FK_baogaof2(@time,'CECR6',1)
select @CECR7=dbo.FK_baogaof2(@time,'CECR7',1)
select @OtherNum=dbo.FK_baogaof2(@time,'其他',1)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable select'逾期量',@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
--今天
select@CECR1Num=dbo.FK_baogaof2(@time,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 1,@time) --星期五
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 4,@time) --下周一
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 5,@time) --下周二
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 6,@time) --下周三
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 7,@time) --下周四
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
end
--------------如果是星期五，查询：逾期量 今天下星期一二三四五。
if datename(weekday, @time)= '星期五'
begin
-----------------------逾期量--------------------------------------------------------------------------------------------
select@CECR1Num=dbo.FK_baogaof2(@time,'CECR1',1)
select @CECR11Num=dbo.FK_baogaof2(@time,'CECR1-1',1)
select @CECR3Num=dbo.FK_baogaof2(@time,'CECR3',1)
select @CECR16=dbo.FK_baogaof2(@time,'CECR16',1)
select @CECR6=dbo.FK_baogaof2(@time,'CECR6',1)
select @CECR7=dbo.FK_baogaof2(@time,'CECR7',1)
select @OtherNum=dbo.FK_baogaof2(@time,'其他',1)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable select'逾期量',@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
--今天
select@CECR1Num=dbo.FK_baogaof2(@time,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 3,@time) --下周一
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 4,@time) --下周二
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 5,@time) --下周三
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 6,@time) --下周四
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
set @time1=dateadd(day, 7,@time) --下周五
select@CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num=dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num=dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16=dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6=dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7=dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum=dbo.FK_baogaof2(@time1,'其他',0)
set@total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable selectCONVERT(varchar(100), @time1,23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
end
return
end
USE [作业数据库]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------自定义函数FK_baogaof2 drop function FK_baogaof2
ALTER function [dbo].[FK_baogaof2](@time datetime,@报告种类varchar(20),@type int) returns int
as
begin
declare @baogaoNum int
declare @changeTime datetime
set @changeTime= CONVERT(varchar(100), @time,23)+' 00:00:00'
if @type=1
begin
if@报告种类='其他'
begin
select @baogaoNum=COUNT(*) from 国内订单表 where 预计完成时间<@changeTime and预计完成时间>dateadd(day, -15, @changeTime)
and 报告种类 <> 'CECR1' and 报告种类<> 'CECR1-1' and 报告种类<> 'CECR3' and 报告种类<> 'CECR16' and 报告种类<> 'CECR6' and 报告种类<> 'CECR7' and完成情况<>'已暂停' and完成情况<>'已取消' and 完成情况='未完成' and 委托公司名称not like '%同业%' and 委托公司名称 not like '%住友%'
end
else
begin
select@baogaoNum=COUNT(*) from 国内订单表 where 预计完成时间<@changeTime and预计完成时间>dateadd(day, -15,@changeTime) and 报告种类 =@报告种类 and完成情况<>'已暂停' and完成情况<>'已取消' and 完成情况='未完成' and 委托公司名称not like '%同业%' and 委托公司名称 not like '%住友%'
end
end
else
begin
if@报告种类='其他'
begin
select@baogaoNum = COUNT(*) from 国内订单表 where CONVERT(varchar(100), 预计完成时间, 23)= CONVERT(varchar(100), @time,23)
and报告种类 <> 'CECR1' and 报告种类<> 'CECR1-1' and 报告种类<> 'CECR3' and 报告种类<> 'CECR16' and 报告种类<> 'CECR6' and 报告种类<> 'CECR7' and完成情况<>'已暂停' and完成情况<>'已取消' and完成情况<>'已暂停' and完成情况<>'已取消' and 完成情况='未完成' and 委托公司名称not like '%同业%' and 委托公司名称 not like '%住友%'
end
else
begin
select@baogaoNum=COUNT(*) from 国内订单表 where CONVERT(varchar(100), 预计完成时间, 23)= CONVERT(varchar(100), @time,23) and 报告种类 = @报告种类 and完成情况<>'已暂停' and完成情况<>'已取消' and 完成情况='未完成' and 委托公司名称not like '%同业%' and 委托公司名称 not like '%住友%'
end
end
return @baogaoNum
end
