|
1
|
CREATE OR ALTER PROC [dbo].[rpt_BUD_UTILIZED_EFF_Excel]
|
|
2
|
@p_QUARTER INT,
|
|
3
|
@p_YEAR INT,
|
|
4
|
@p_FROM_DT VARCHAR(20),
|
|
5
|
@p_TO_DT VARCHAR(20)
|
|
6
|
AS
|
|
7
|
BEGIN
|
|
8
|
DECLARE @p_FROM VARCHAR(20), @p_TO VARCHAR(20)
|
|
9
|
IF(@p_QUARTER = 1)
|
|
10
|
BEGIN
|
|
11
|
SET @p_FROM = '01/01/'
|
|
12
|
SET @p_TO = '31/03/'
|
|
13
|
END
|
|
14
|
ELSE IF(@p_QUARTER = 2)
|
|
15
|
BEGIN
|
|
16
|
SET @p_FROM = '01/04/'
|
|
17
|
SET @p_TO = '30/06/'
|
|
18
|
END
|
|
19
|
ELSE IF(@p_QUARTER = 3)
|
|
20
|
BEGIN
|
|
21
|
SET @p_FROM = '01/07/'
|
|
22
|
SET @p_TO = '30/09/'
|
|
23
|
END
|
|
24
|
ELSE IF(@p_QUARTER = 4)
|
|
25
|
BEGIN
|
|
26
|
SET @p_FROM = '01/10/'
|
|
27
|
SET @p_TO = '30/12/'
|
|
28
|
END
|
|
29
|
IF(@p_FROM_DT IS NULL OR @p_FROM_DT = '' OR LEN(@p_FROM_DT) < 6) SET @p_FROM_DT = @p_FROM + CONVERT(VARCHAR, @p_YEAR)
|
|
30
|
IF(@p_TO_DT IS NULL OR @p_TO_DT = '' OR LEN(@p_TO_DT) < 6) SET @p_TO_DT = @p_TO + CONVERT(VARCHAR, @p_YEAR)
|
|
31
|
|
|
32
|
DECLARE @RANGE_TIME INT = (SELECT DATEDIFF(DAY, CONVERT(DATE,@p_FROM_DT,103), CONVERT(DATE,@p_TO_DT,103)))
|
|
33
|
|
|
34
|
DECLARE @TITLE NVARCHAR(500) = N'VIETBANK - TỔNG HỢP DANH SÁCH DOANH THU KHÁCH THUÊ QUÝ ' + CAST(@p_QUARTER AS VARCHAR(1)) + N' NĂM ' + CAST(@p_YEAR AS VARCHAR(4))
|
|
35
|
|
|
36
|
DECLARE @Lst_CUST TABLE(CUST_CODE VARCHAR(50), CUST_NAME NVARCHAR(150))
|
|
37
|
INSERT INTO @Lst_CUST SELECT CC.CUSTOMER_CODE, CC.CUSTOMER_NAME FROM CM_CUSTOMER CC WHERE CC.AUTH_STATUS = 'A'
|
|
38
|
|
|
39
|
DECLARE @MoneyByMonth TABLE ( MONTH_DT NVARCHAR(150), AMT DECIMAL(18,2))
|
|
40
|
|
|
41
|
|
|
42
|
---- TABLE 0
|
|
43
|
SELECT
|
|
44
|
CAST(ROW_NUMBER() OVER(ORDER BY BCR.CUST_CODE) AS VARCHAR(5)) STT, BCR.CUST_CODE AS MAKH,
|
|
45
|
CASE WHEN LEFT(BCR.CUST_CODE,2) = 'MB' THEN N'Miền Bắc'
|
|
46
|
WHEN LEFT(BCR.CUST_CODE,2) = 'MT' THEN N'Miền Trung'
|
|
47
|
ELSE N'Miền Nam' END AS MAVM,
|
|
48
|
ISNULL(BCR.CUST_NAME,ISNULL(CC.CUSTOMER_NAME, BCR.BUDC_RENT_NAME)) AS TENKH,
|
|
49
|
--- SELECT 12 Tháng
|
|
50
|
SUM(CASE WHEN @p_QUARTER = 1 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T1],
|
|
51
|
SUM(CASE WHEN @p_QUARTER = 1 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T2],
|
|
52
|
SUM(CASE WHEN @p_QUARTER = 1 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T3],
|
|
53
|
SUM(CASE WHEN @p_QUARTER = 2 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T4],
|
|
54
|
SUM(CASE WHEN @p_QUARTER = 2 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T5],
|
|
55
|
SUM(CASE WHEN @p_QUARTER = 2 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T6],
|
|
56
|
SUM(CASE WHEN @p_QUARTER = 3 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T7],
|
|
57
|
SUM(CASE WHEN @p_QUARTER = 3 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T8],
|
|
58
|
SUM(CASE WHEN @p_QUARTER = 3 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T9],
|
|
59
|
SUM(CASE WHEN @p_QUARTER = 4 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T10],
|
|
60
|
SUM(CASE WHEN @p_QUARTER = 4 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T11],
|
|
61
|
SUM(CASE WHEN @p_QUARTER = 4 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T12],
|
|
62
|
0 AS [T13]
|
|
63
|
FROM
|
|
64
|
BUD_CONTRACT_RENT BCR
|
|
65
|
LEFT JOIN
|
|
66
|
BUD_CONTRACT_RENT_MOVEMENT BCRM ON BCR.BUDC_RENT_ID = BCRM.BUDC_RENT_ID
|
|
67
|
AND CONVERT(DATE,BCRM.START_DT,103) <= CONVERT(DATE,@p_TO_DT,103) AND CONVERT(DATE,BCRM.END_DT,103) >= CONVERT(DATE,@p_FROM_DT,103)
|
|
68
|
LEFT JOIN CM_CUSTOMER CC ON CC.CUSTOMER_CODE = BCR.CUST_CODE
|
|
69
|
WHERE BCR.BUDC_RENT_TYPE = 'CT' AND BCR.AUTH_STATUS = 'A'
|
|
70
|
GROUP BY
|
|
71
|
BCR.BUDC_RENT_NAME, BCR.CUST_CODE,
|
|
72
|
BCR.CUST_NAME,CC.CUSTOMER_NAME
|
|
73
|
|
|
74
|
---- TABLE 1
|
|
75
|
SELECT '' AS STT, '' AS MAKH, '' AS MAVM, N'TỔNG CỘNG' AS CUST_NAME,
|
|
76
|
SUM(T1) AS SUM_T1,
|
|
77
|
SUM(T2) AS SUM_T2,
|
|
78
|
SUM(T3) AS SUM_T3,
|
|
79
|
SUM(T4) AS SUM_T4,
|
|
80
|
SUM(T5) AS SUM_T5,
|
|
81
|
SUM(T6) AS SUM_T6,
|
|
82
|
SUM(T7) AS SUM_T7,
|
|
83
|
SUM(T8) AS SUM_T8,
|
|
84
|
SUM(T9) AS SUM_T9,
|
|
85
|
SUM(T10) AS SUM_T10,
|
|
86
|
SUM(T11) AS SUM_T11,
|
|
87
|
SUM(T12) AS SUM_T12,
|
|
88
|
SUM(T1) + SUM(T2) + SUM(T3) + SUM(T4) + SUM(T5) + SUM(T6) + SUM(T7) + SUM(T8) + SUM(T9) + SUM(T10) + SUM(T11) + SUM(T12) AS SUM_T13
|
|
89
|
FROM (
|
|
90
|
SELECT
|
|
91
|
ROW_NUMBER() OVER(ORDER BY BCR.CUST_CODE) AS STT, BCR.CUST_CODE AS MAKH,
|
|
92
|
CASE WHEN LEFT(BCR.CUST_CODE,2) = 'MB' THEN N'Miền Bắc'
|
|
93
|
WHEN LEFT(BCR.CUST_CODE,2) = 'MT' THEN N'Miền Trung'
|
|
94
|
ELSE N'Miền Nam' END AS MAVM,
|
|
95
|
ISNULL(BCR.CUST_NAME,ISNULL(CC.CUSTOMER_NAME, BCR.BUDC_RENT_NAME)) AS TEN_KH,
|
|
96
|
--- SELECT 12 Tháng
|
|
97
|
--- SELECT 12 Tháng
|
|
98
|
SUM(CASE WHEN @p_QUARTER = 1 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T1],
|
|
99
|
SUM(CASE WHEN @p_QUARTER = 1 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T2],
|
|
100
|
SUM(CASE WHEN @p_QUARTER = 1 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T3],
|
|
101
|
SUM(CASE WHEN @p_QUARTER = 2 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T4],
|
|
102
|
SUM(CASE WHEN @p_QUARTER = 2 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T5],
|
|
103
|
SUM(CASE WHEN @p_QUARTER = 2 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T6],
|
|
104
|
SUM(CASE WHEN @p_QUARTER = 3 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T7],
|
|
105
|
SUM(CASE WHEN @p_QUARTER = 3 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T8],
|
|
106
|
SUM(CASE WHEN @p_QUARTER = 3 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T9],
|
|
107
|
SUM(CASE WHEN @p_QUARTER = 4 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T10],
|
|
108
|
SUM(CASE WHEN @p_QUARTER = 4 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T11],
|
|
109
|
SUM(CASE WHEN @p_QUARTER = 4 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T12]
|
|
110
|
FROM
|
|
111
|
BUD_CONTRACT_RENT BCR
|
|
112
|
LEFT JOIN
|
|
113
|
BUD_CONTRACT_RENT_MOVEMENT BCRM ON BCR.BUDC_RENT_ID = BCRM.BUDC_RENT_ID
|
|
114
|
AND CONVERT(DATE,BCRM.START_DT,103) <= CONVERT(DATE,@p_TO_DT,103) AND CONVERT(DATE,BCRM.END_DT,103) >= CONVERT(DATE,@p_FROM_DT,103)
|
|
115
|
LEFT JOIN CM_CUSTOMER CC ON CC.CUSTOMER_CODE = BCR.CUST_CODE
|
|
116
|
WHERE BCR.BUDC_RENT_TYPE = 'CT' AND BCR.AUTH_STATUS = 'A'
|
|
117
|
GROUP BY
|
|
118
|
BCR.BUDC_RENT_NAME, BCR.CUST_CODE,
|
|
119
|
BCR.CUST_NAME,CC.CUSTOMER_NAME
|
|
120
|
) AS SUM_NAM
|
|
121
|
|
|
122
|
|
|
123
|
---- TABLE 2 -- SUM TỔNG THEO NĂM
|
|
124
|
SELECT STT, T1+T2+T3+T4+T5+T6+T7+T8+T9+T10+T11+T12 AS TONG_NAM
|
|
125
|
FROM (
|
|
126
|
SELECT
|
|
127
|
ROW_NUMBER() OVER(ORDER BY BCR.CUST_CODE) AS STT, BCR.CUST_CODE AS MAKH,
|
|
128
|
CASE WHEN LEFT(BCR.CUST_CODE,2) = 'MB' THEN N'Miền Bắc'
|
|
129
|
WHEN LEFT(BCR.CUST_CODE,2) = 'MT' THEN N'Miền Trung'
|
|
130
|
ELSE N'Miền Nam' END AS MAVM,
|
|
131
|
ISNULL(BCR.CUST_NAME,ISNULL(CC.CUSTOMER_NAME, BCR.BUDC_RENT_NAME)) AS TEN_KH,
|
|
132
|
--- SELECT 12 Tháng
|
|
133
|
--- SELECT 12 Tháng
|
|
134
|
SUM(CASE WHEN @p_QUARTER = 1 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T1],
|
|
135
|
SUM(CASE WHEN @p_QUARTER = 1 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T2],
|
|
136
|
SUM(CASE WHEN @p_QUARTER = 1 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T3],
|
|
137
|
SUM(CASE WHEN @p_QUARTER = 2 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T4],
|
|
138
|
SUM(CASE WHEN @p_QUARTER = 2 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T5],
|
|
139
|
SUM(CASE WHEN @p_QUARTER = 2 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T6],
|
|
140
|
SUM(CASE WHEN @p_QUARTER = 3 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T7],
|
|
141
|
SUM(CASE WHEN @p_QUARTER = 3 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T8],
|
|
142
|
SUM(CASE WHEN @p_QUARTER = 3 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T9],
|
|
143
|
SUM(CASE WHEN @p_QUARTER = 4 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T10],
|
|
144
|
SUM(CASE WHEN @p_QUARTER = 4 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T11],
|
|
145
|
SUM(CASE WHEN @p_QUARTER = 4 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T12]
|
|
146
|
FROM
|
|
147
|
BUD_CONTRACT_RENT BCR
|
|
148
|
LEFT JOIN
|
|
149
|
BUD_CONTRACT_RENT_MOVEMENT BCRM ON BCR.BUDC_RENT_ID = BCRM.BUDC_RENT_ID
|
|
150
|
AND CONVERT(DATE,BCRM.START_DT,103) <= CONVERT(DATE,@p_TO_DT,103) AND CONVERT(DATE,BCRM.END_DT,103) >= CONVERT(DATE,@p_FROM_DT,103)
|
|
151
|
LEFT JOIN CM_CUSTOMER CC ON CC.CUSTOMER_CODE = BCR.CUST_CODE
|
|
152
|
WHERE BCR.BUDC_RENT_TYPE = 'CT' AND BCR.AUTH_STATUS = 'A'
|
|
153
|
GROUP BY
|
|
154
|
BCR.BUDC_RENT_NAME, BCR.CUST_CODE,
|
|
155
|
BCR.CUST_NAME,CC.CUSTOMER_NAME
|
|
156
|
) AS TONG_NAM
|
|
157
|
|
|
158
|
---- TABLE 3
|
|
159
|
SELECT @TITLE AS TITLE
|
|
160
|
|
|
161
|
END
|