34,576
社区成员
发帖
与我相关
我的任务
分享
create table DiaTaskClient
(taskid varchar(10),
clientid varchar(10),
agent varchar(10),
caseresult varchar(10))
create table DiaClient
(clientid varchar(10),
batchid varchar(10),
DPD int)
create table DiaTrial
(taskid varchar(10),
clientid varchar(10),
[datetime] varchar(15))
insert into DiaTaskClient
select 'T1','C1','agent01','成功扣款' union all
select 'T1','C2','agent02','成功扣款' union all
select 'T1','C3','agent02','成功扣款' union all
select 'T1','C4','agent01','成功扣款'
insert into DiaClient
select 'C1','B1',15 union all
select 'C2','B1',16 union all
select 'C3','B1',15 union all
select 'C4','B1',17
insert into DiaTrial
select 'T1','C1','2013/12/4' union all
select 'T1','C2','2013/12/4' union all
select 'T1','C3','2013/12/4' union all
select 'T1','C4','2013/12/4'
declare @tsql varchar(6000),@tsql2 varchar(6000),@tsql3 varchar(6000)
select @tsql=isnull(@tsql+',','')
+'rtrim(max(case when DPD='+rtrim(DPD)+' then 1 else 0 end)) ''DPD'+rtrim(DPD)+''' ',
@tsql2=isnull(@tsql2+',',''''',''Total'',')
+'rtrim(sum(case when DPD='+rtrim(DPD)+' then 1 else 0 end)) ',
@tsql3=isnull(@tsql3+',',''''',''Avg'',')
+'rtrim(cast(sum(case when DPD='+rtrim(DPD)+' then 1 else 0 end)/
(select count(distinct a.[datetime]+b.agent)*1.0
from DiaTrial a
inner join DiaTaskClient b on a.taskid=b.taskid and a.clientid=b.clientid) as decimal(5,1))) '
from (select number 'DPD'
from master.dbo.spt_values
where type='P' and number between 14 and 20) t
select @tsql2='select '+@tsql2+' from DiaClient',
@tsql3='select '+@tsql3+' from DiaClient'
select @tsql='select a.[datetime],b.agent,'+@tsql
+' from DiaTrial a
inner join DiaTaskClient b on a.taskid=b.taskid and a.clientid=b.clientid
inner join DiaClient c on b.clientid=c.clientid
group by a.[datetime],b.agent
union all '+@tsql2
+' union all '+@tsql3
exec(@tsql)
/*
datetime agent DPD14 DPD15 DPD16 DPD17 DPD18 DPD19 DPD20
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
2013/12/4 agent01 0 1 0 1 0 0 0
2013/12/4 agent02 0 1 1 0 0 0 0
Total 0 2 1 1 0 0 0
Avg 0.0 1.0 0.5 0.5 0.0 0.0 0.0
(4 row(s) affected)
*/
create table DiaTaskClient
(taskid varchar(10),
clientid varchar(10),
agent varchar(10),
caseresult varchar(10))
create table DiaClient
(clientid varchar(10),
batchid varchar(10),
DPD int)
create table DiaTrial
(taskid varchar(10),
clientid varchar(10),
[datetime] varchar(15))
insert into DiaTaskClient
select 'T1','C1','agent01','成功扣款' union all
select 'T1','C2','agent02','成功扣款' union all
select 'T1','C3','agent02','成功扣款' union all
select 'T1','C4','agent01','成功扣款'
insert into DiaClient
select 'C1','B1',15 union all
select 'C2','B1',16 union all
select 'C3','B1',15 union all
select 'C4','B1',17
insert into DiaTrial
select 'T1','C1','2013/12/4' union all
select 'T1','C2','2013/12/4' union all
select 'T1','C3','2013/12/4' union all
select 'T1','C4','2013/12/4'
declare @tsql varchar(6000),@tsql2 varchar(6000),@tsql3 varchar(6000)
select @tsql=isnull(@tsql+',','')
+'rtrim(max(case when DPD='+rtrim(DPD)+' then 1 else 0 end)) ''DPD'+rtrim(DPD)+''' ',
@tsql2=isnull(@tsql2+',',''''',''Total'',')
+'rtrim(sum(case when DPD='+rtrim(DPD)+' then 1 else 0 end)) ',
@tsql3=isnull(@tsql3+',',''''',''Avg'',')
+'rtrim(cast(sum(case when DPD='+rtrim(DPD)+' then 1 else 0 end)/
(select count(distinct a.[datetime]+b.agent)*1.0
from DiaTrial a
inner join DiaTaskClient b on a.taskid=b.taskid and a.clientid=b.clientid) as decimal(5,1)))'
from (select distinct DPD from DiaClient) t
select @tsql2='select '+@tsql2+' from DiaClient',
@tsql3='select '+@tsql3+' from DiaClient'
select @tsql='select a.[datetime],b.agent,'+@tsql
+' from DiaTrial a
inner join DiaTaskClient b on a.taskid=b.taskid and a.clientid=b.clientid
inner join DiaClient c on b.clientid=c.clientid
group by a.[datetime],b.agent
union all '+@tsql2
+' union all '+@tsql3
exec(@tsql)
/*
datetime agent DPD15 DPD16 DPD17
--------------- ---------- ------------- ------------- -------------
2013/12/4 agent01 1 0 1
2013/12/4 agent02 1 1 0
Total 2 1 1
Avg 1.0 0.5 0.5
(4 row(s) affected)
*/
CREATE TABLE [dbo].[DiaTrial](
[id] [int] IDENTITY(1,1) NOT NULL,
[trial_id] [varchar](20) NULL,
[task_id] [varchar](20) NULL,
[client_id] [varchar](20) NULL,
[tel] [varchar](20) NULL,
[call_id] [varchar](20) NULL,
[call_id2] [varchar](20) NULL,
[call_diretion] [varchar](10) NULL,
[result] [nvarchar](100) NULL,
[remark] [nvarchar](500) NULL,
[connect_date] [datetime] NULL,
[disconnect_date] [datetime] NULL,
[recording_file] [varchar](200) NULL,
[create_cro] [varchar](20) NULL,
[create_date] [datetime] NULL,
[modify_cro] [varchar](20) NULL,
[modify_date] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DiaTaskClient](
[task_id] [varchar](20) NOT NULL,
[client_id] [varchar](20) NOT NULL,
[assign_agent] [varchar](20) NULL,
[read_result] [nvarchar](100) NULL,
[read_date] [datetime] NULL,
[read_agent] [varchar](20) NULL,
[over_date] [datetime] NULL,
[over_agent] [varchar](20) NULL,
[submit_date] [datetime] NULL,
[submit_agent] [varchar](20) NULL,
[case_result] [nvarchar](100) NULL,
[result_code] [nvarchar](100) NULL,
[booking_date] [datetime] NULL,
[booking_tel] [varchar](20) NULL,
[booking_remark] [nvarchar](500) NULL,
[remark] [nvarchar](4000) NULL,
[sms_status] [nvarchar](20) NULL,
[receive_id] [int] NULL,
[create_cro] [varchar](20) NULL,
[create_date] [datetime] NULL,
[modify_cro] [varchar](20) NULL,
[modify_date] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[task_id] ASC,
[client_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DiaClient](
[client_id] [varchar](20) NOT NULL,
[batch_id] [varchar](20) NULL,
[status] [nvarchar](20) NULL,
[client_name] [nvarchar](200) NULL,
[client_no] [nvarchar](20) NULL,
[agreement_id] [nvarchar](10) NULL,
[spouse_name] [nvarchar](40) NULL,
[spouse_no] [nvarchar](20) NULL,
[guarantor_name] [nvarchar](50) NULL,
[guarantor_no] [nvarchar](20) NULL,
[dpd] [int] NULL,
[overdue_amount] [nvarchar](50) NULL,
[principal_outstanding] [nvarchar](50) NULL,
[last_pay_date] [nvarchar](40) NULL,
[city] [nvarchar](200) NULL,
[tel01] [nvarchar](200) NULL,
[tel02] [nvarchar](200) NULL,
[tel03] [nvarchar](200) NULL,
[spouse_tel01] [nvarchar](200) NULL,
[spouse_tel03] [nvarchar](200) NULL,
[guarantor_tel01] [nvarchar](200) NULL,
[guarantor_tel02] [nvarchar](200) NULL,
[guarantor_tel03] [nvarchar](200) NULL,
[queue] [nvarchar](200) NULL,
[collector_name] [nvarchar](200) NULL,
[action_date01] [nvarchar](200) NULL,
[action_code01] [nvarchar](200) NULL,
[action_next01] [nvarchar](200) NULL,
[action_remark01] [nvarchar](200) NULL,
[action_date02] [nvarchar](200) NULL,
[action_code02] [nvarchar](200) NULL,
[action_next02] [nvarchar](200) NULL,
[action_remark02] [nvarchar](200) NULL,
[action_date03] [nvarchar](200) NULL,
[action_code03] [nvarchar](200) NULL,
[action_next03] [nvarchar](200) NULL,
[action_remark03] [nvarchar](200) NULL,
[remark] [nvarchar](4000) NULL,
PRIMARY KEY CLUSTERED
(
[client_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DiaBatch](
[batch_id] [varchar](20) NOT NULL,
[batch_name] [nvarchar](2000) NULL,
[import_file] [nvarchar](2000) NULL,
[create_cro] [varchar](20) NULL,
[create_date] [datetime] NULL,
[modify_cro] [varchar](20) NULL,
[modify_date] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[batch_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DiaTask](
[task_id] [varchar](20) NOT NULL,
[task_name] [nvarchar](200) NULL,
[task_type] [nvarchar](200) NULL,
[remark] [nvarchar](4000) NULL,
[project_id] [int] NULL,
[begin_date] [datetime] NULL,
[end_date] [datetime] NULL,
[enable_begin_date] [bit] NULL,
[enable_end_date] [bit] NULL,
[status] [nvarchar](20) NULL,
[create_cro] [varchar](20) NULL,
[create_date] [datetime] NULL,
[modify_cro] [varchar](20) NULL,
[modify_date] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[task_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO