1 USE [ibcs_wuhu_true]
2 GO
3 /****** Object: StoredProcedure [dbo].[P_RP_JBXX_XHQK] Script Date: 04/20/2011 09:55:43 ******/
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8 /*
9 用户销户情况一览表
10 P_RP_JBXX_XHQK '00','','2010-12-06','2011-01-28'
11 P_RP_JBXX_XHQK '91','','',''
12 */
13 ALTER PROCEDURE [dbo].[P_RP_JBXX_XHQK]
14 @S_ST VARCHAR(10),
15 @S_CH VARCHAR(10),
16 @StartCB VARCHAR(10), --拆表开始时间
17 @EndCB VARCHAR(10) --拆表结束时间
18 --D_ChaiBiaoRQ
19 AS
20 BEGIN
21 DECLARE @STNAME VARCHAR(50),@Condition varchar(500),@SQL VARCHAR(1000)
22 SELECT @STNAME = '天之饺子制造'
23 SELECT @S_ST = CASE WHEN @S_ST = '' THEN '00' ELSE @S_ST END
24
25 IF @S_ST <> '00' AND @S_ST <>''
26 BEGIN
27 SELECT @STNAME = RTRIM(S_ZhanDianMC) FROM SX_ZhanDianXX WHERE S_ST = @S_ST
28 END
29
30 CREATE TABLE #REPORT_DATA(STNAME VARCHAR(50),TJ_CH VARCHAR(10), ID INT IDENTITY(1,1) NOT NULL,S_CH VARCHAR(10),S_CID VARCHAR(16),S_HM VARCHAR(100),S_DZ VARCHAR(100),
31 LXDH VARCHAR(50),CBRQ VARCHAR(10),DJRQ VARCHAR(10),QFJE NUMERIC(18,2),YE NUMERIC(18,2),JBR VARCHAR(50)
32 )
33 SET @SQL='INSERT INTO #REPORT_DATA
34 SELECT '''+@STNAME+''',
35 '''+@S_CH+''',
36 B.S_CH,
37 A.S_CID,
38 B.S_HM,
39 B.S_DZ,
40 '''',
41 CONVERT(VARCHAR(10),D.D_ChaiBiaoRQ,112),
42 CONVERT(VARCHAR(10),A.D_CaoZuoSJ,112),
43 A.N_QianFeiJE,
44 ISNULL(C.N_YuFuFJE,0),
45 A.S_CaoZuoRMC
46 FROM RZ_ZhuXiaoBTBG A
47 INNER JOIN KG_BiaoKaXX B ON B.S_CID = A.S_CID
48 LEFT JOIN BW_ChaiBiaoXX D ON D.S_CID = A.S_CID
49 LEFT JOIN YF_YuFuFXX C ON C.S_CID = A.S_CID
50 WHERE A.I_CaoZuoLX = ''1''
51 AND (''00'' = '''+@S_ST+''' OR B.S_ST ='''+@S_ST+''') --AND ('00' = '91' OR B.S_ST ='91') 当前面的条件不成立就执行后面成立的条件
52 AND ('''' ='''+@S_CH+''' OR B.S_CH ='''+@S_CH+''')'
53 --AND ('' = '' OR B.S_CH ='') 54 IF @StartCB=''
55 BEGIN
56 EXEC(@SQL)
57 END
58 IF @StartCB<>''
59 BEGIN
60 set @Condition='AND D.D_ChaiBiaoRQ between '''+@StartCB+''' and '''+@EndCB+''''
61 SET @SQL=@SQL+@Condition
62 EXEC(@SQL)
63 PRINT (@SQL)
64 END
65 SELECT * FROM #REPORT_DATA ORDER BY ID
66 DROP TABLE #REPORT_DATA
67 END
68
69 /*
70 select * from RZ_ZhuXiaoBTBG 用户注销/报停恢复表
71
72 select top 100 * from KG_BiaoKaXX 表卡信息表
73
74 select top 1000 * from BW_ChaiBiaoXX 拆表信息表
75
76 select top 100 * from YF_YuFuFXX 预付费信息表
77
78 */
