清欠率（存储过程）

set ANSI_NULLS ON
2

set QUOTED_IDENTIFIER ON
3

GO
4

/************************************************
5

作者：
6

创建日期：2009-06-01
7

功能说明：根据所传的组织架构编号，计算清欠率
8

************************************************/
9

CREATE PROCEDURE [dbo].[sp_Get_FinanceRate_ALL]
10

@UnitCode varchar(5000),--组织机构编码
11

@Type int, --查询类型(0代表总部查询,1代表区域查询,2代表楼盘查询,3代表自定义查询
12

@StartDate datetime, --开始日期
13

@EndDate datetime --截止日期
14

AS
15

BEGIN
16

declare @TotalAccountMoney decimal(18,2)--往年应收总额
17

select @TotalAccountMoney=sum(AccountMoney) from Finance_Areceivable where ItemType=7 and year(EndDate)<year(getdate())
18

IF(@startDate<@endDate and @TotalAccountMoney>0)--截止日期必须大于开始日期才统计,往年应收累计必须大于0
19

BEGIN
20

declare @UnitName varchar(50) --组织机构名称
21

Declare @Cursor_Expr Cursor--游标
22

--创建临时表
23

CREATE TABLE #Temp_SumRate
24

(
25

UnitName varchar(1000),
26

SumDate varchar(100),
27

acceptSum decimal(10,4)
28

)
29

IF(@Type=0)--总部查询
30

BEGIN
31

--向临时表中插入数据
32

select @UnitName=UnitName From System_FrameWork Where UnitCode=@UnitCode--根据公司编码得到公司名称
33

INSERT INTO #Temp_SumRate
34

select @UnitName as UnitName,cast(SumYear as varchar)+'年'+dbo.left_fill_zero(SumMonth,2)+'月' as SumDate,acceptSum from
35

--cast(SumYear as varchar)+'年'+case when month(b.GatherDate)<10 then '0'+cast(month(b.GatherDate) as varchar) else cast(month(b.GatherDate) as varchar) end+'月' as SumDate,acceptSum from
36

(
37

select
38

year(b.GatherDate) as SumYear,
39

month(b.GatherDate) as SumMonth,
40

Round(sum(c.GainMoney)/@TotalAccountMoney,4) as acceptSum
41

--sum(c.GainMoney)/@TotalAccountMoney as acceptSum
42

--cast(cast(((sum(c.GainMoney)/@TotalAccountMoney)*100) as decimal(10,2)) as varchar(100)), as acceptSum
43

from Finance_Arecei_ReceiptList as c
44

inner join Finance_Areceivable as a on a.AreceivableID=c.AreceivableID
45

inner join Finance_PReceipt as b on b.ReceiptID=c.ReceiptID
46

where b.GatherDate between @StartDate and @EndDate
47

and exists(select * from V_BuildMessage where UnitCode like @UnitCode+'%' and CompanyID= b.CompanyID)
48

--and b.CompanyID in(select distinct CompanyID from V_BuildMessage where UnitCode like @UnitCode+'%')
49

and c.ItemType=7
50

and year(a.EndDate)<year(getdate())
51

group by year(b.GatherDate),month(b.GatherDate)
52

)as newTable
53

END
54

ELSE IF(@Type=1)--区域查询
55

BEGIN
56

set @Cursor_Expr=Cursor scroll dynamic for
57

select UnitCode,UnitName From System_FrameWork AS s Where exists(
58

select * From V_BuildMessage Where UnitCode like @UnitCode+'%' AND PunitCode=s.UnitCode )
59

Open @Cursor_Expr--打开游标
60

Fetch Next From @Cursor_Expr into @UnitCode,@UnitName--区域信息
61

While @@Fetch_Status=0
62

BEGIN
63

--向临时表中插入数据
64

INSERT INTO #Temp_SumRate
65

select @UnitName AS UnitName,cast(SumYear as varchar)+'年'+dbo.left_fill_zero(SumMonth,2)+'月' as SumDate,acceptSum from
66

--cast(SumYear as varchar)+'年'+case when month(b.GatherDate)<10 then '0'+cast(month(b.GatherDate) as varchar) else cast(month(b.GatherDate) as varchar) end+'月' as SumDate,acceptSum from
67

(
68

select
69

year(b.GatherDate) as SumYear,
70

month(b.GatherDate) as SumMonth,
71

--sum(c.GainMoney)/@TotalAccountMoney as acceptSum
72

Round(sum(c.GainMoney)/@TotalAccountMoney,4) as acceptSum
73

from Finance_Arecei_ReceiptList as c
74

inner join Finance_Areceivable as a on a.AreceivableID=c.AreceivableID
75

inner join Finance_PReceipt as b on b.ReceiptID=c.ReceiptID
76

where b.GatherDate between @StartDate and @EndDate
77

and exists(select * from V_BuildMessage where UnitCode like @UnitCode+'%' and CompanyID= b.CompanyID)
78

--and b.CompanyID in(select distinct CompanyID from V_BuildMessage where UnitCode like @UnitCode+'%')
79

and c.ItemType=7
80

and year(a.EndDate)<year(getdate())
81

group by year(b.GatherDate),month(b.GatherDate)
82

)as newTable
83

Fetch Next From @Cursor_Expr into @UnitCode,@UnitName
84

END
86

CLOSE @Cursor_Expr--关闭游标
87

DEALLOCATE @Cursor_Expr--删除游标
88

END
91

ELSE IF(@Type=2)--楼盘查询
92

BEGIN
93

--向临时表中插入数据
94

INSERT INTO #Temp_SumRate
95

select UnitName,cast(SumYear as varchar)+'年'+dbo.left_fill_zero(SumMonth,2)+'月' as SumDate,acceptSum from
96

--cast(SumYear as varchar)+'年'+case when month(b.GatherDate)<10 then '0'+cast(month(b.GatherDate) as varchar) else cast(month(b.GatherDate) as varchar) end+'月' as SumDate,acceptSum from
97

(
98

select v.UnitCode,v.UnitName,
99

year(b.GatherDate) as SumYear,
100

month(b.GatherDate) as SumMonth,
101

--sum(c.GainMoney)/@TotalAccountMoney as acceptSum
102

Round(sum(c.GainMoney)/@TotalAccountMoney,4) as acceptSum
103

from Finance_Arecei_ReceiptList as c
104

inner join Finance_Areceivable as a on a.AreceivableID=c.AreceivableID
105

inner join Finance_PReceipt as b on b.ReceiptID=c.ReceiptID
106

inner join System_FrameWork as v on v.UnitID=a.CompanyID
107

where b.GatherDate between @StartDate and @EndDate
108

and exists(select * from V_BuildMessage where UnitCode like @UnitCode+'%' and CompanyID= b.CompanyID)
109

--and b.CompanyID in(select distinct companyID from V_BuildMessage where UnitCode like @UnitCode+'%')
110

and c.ItemType=7
111

and year(a.EndDate)<year(getdate())
112

group by v.UnitCode,v.UnitName, year(b.GatherDate),month(b.GatherDate)
113

)as newTable
114

END
115

ELSE IF(@Type=3)--自定义查询
116

Begin
117

set @Cursor_Expr=Cursor scroll dynamic for
118

select UnitCode,UnitName From System_FrameWork Where UnitCode
119

in(select Context from CW_F_GetStringsTable_CLR(@UnitCode,','))
120

Open @Cursor_Expr--打开游标
121

Fetch Next From @Cursor_Expr into @UnitCode,@UnitName
122

While @@Fetch_Status=0
123

BEGIN
124

125

--向临时表中插入数据
126

INSERT INTO #Temp_SumRate
127

select @UnitName AS UnitName,cast(SumYear as varchar)+'年'+dbo.left_fill_zero(SumMonth,2)+'月' as SumDate,acceptSum from
128

--cast(SumYear as varchar)+'年'+case when month(b.GatherDate)<10 then '0'+cast(month(b.GatherDate) as varchar) else cast(month(b.GatherDate) as varchar) end+'月' as SumDate,acceptSum from
129

(
130

select
131

year(b.GatherDate) as SumYear,
132

month(b.GatherDate) as SumMonth,
133

--sum(c.GainMoney)/@TotalAccountMoney as acceptSum
134

Round(sum(c.GainMoney)/@TotalAccountMoney,4) as acceptSum
135

from Finance_Arecei_ReceiptList as c
136

inner join Finance_Areceivable as a on a.AreceivableID=c.AreceivableID
137

inner join Finance_PReceipt as b on b.ReceiptID=c.ReceiptID
138

inner join System_FrameWork as v on v.UnitID=a.CompanyID
139

where b.GatherDate between @StartDate and @EndDate
140

and exists(select * from V_BuildMessage where UnitCode like @UnitCode+'%' and CompanyID= b.CompanyID)
141

--and b.CompanyID in(select distinct companyID from V_BuildMessage where UnitCode like @UnitCode+'%')
142

and c.ItemType=7
143

and year(a.EndDate)<year(getdate())
144

group by year(b.GatherDate),month(b.GatherDate)
145

)as newTable
146

Fetch Next From @Cursor_Expr into @UnitCode,@UnitName
147

END
148

CLOSE @Cursor_Expr--关闭游标
149

DEALLOCATE @Cursor_Expr--删除游标
150

END
151

152

/*--方法1----------------------------------
153

--动态拼接动态列名（如：[2008年12月]，[2009年1月]，[2009年2月]，[2009年3月]，[2009年4月]，[2009年5月]）
154

Declare @tableColumn varchar(8000)
155

SET @tableColumn =''
156

Declare @SumDate varchar(100)
157

declare Cursor_col Cursor for select distinct SumDate from #Temp_SumRate --order by SumYear,SumMonth--声明游标
158

open Cursor_col--打开游标
159

FETCH NEXT FROM Cursor_col INTO @SumDate
160

WHILE @@Fetch_Status=0
161

BEGIN
162

SET @tableColumn =@tableColumn+'['+@SumDate+'],'
163

FETCH NEXT FROM Cursor_col INTO @SumDate
164

END
165

CLOSE Cursor_col--关闭游标
166

DEALLOCATE Cursor_col--释放游标
167

set @tableColumn=SUBSTRING(@tableColumn,1,len(@tableColumn)-1)
168

169

--动态拼接Sql查询语句,实现行列转化
170

declare @sql varchar(8000)
171

set @sql = 'select UnitName as 组织机构, '+@tableColumn +' from #Temp_SumRate PIVOT(MAX(acceptSum) for SumDate in ('+@tableColumn+'))as p'
172

exec(@sql)*/
173

174

--方法2--------------------------------------
175

--动态拼接Sql查询语句,实现行列转化
176

declare @sql varchar(8000)
177

set @sql = 'select UnitName as 组织机构 '
178

select @sql = @sql + ' , cast(cast(100*max(case SumDate when ''' + SumDate + ''' then acceptSum else null end) as decimal(10,2)) as varchar)+''%'' [' + SumDate + ']'from (select distinct SumDate from #Temp_SumRate) as a
179

set @sql = @sql + ' from #Temp_SumRate group by UnitName '
180

exec(@sql)
181

182

--删除临时表
183

DROP TABLE #Temp_SumRate
184

END
185

END
186

187

188

189

190

191

192

193