· 8 years ago · Nov 16, 2017, 06:28 PM
1/* -------------------------------------------------
2exec usp_DropTemp '#TBDados'
3create table #TBDados (
4ID int identity(1,1)
5, Classf varchar(512)
6, COD varchar(max)
7, Valor Money
8primary key (ID)
9)
10insert into #TBDados
11 SELECT --
12 top 10
13 Classf =ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 3+1
14 , COD = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))+1000
15 , VALOR = ROUND(CONVERT(MONEY, ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) / 100000.0 ),2)
16
17FROM sys.objects
18-- */ -------------------------------------------------
19SELECT --
20*
21FROM #TBDados
22 -------------------------------------------------Parametros e formaça
23 DECLARE --
24 @VALORALVO MONEY
25 ,@QtdHipMaxExc int
26 ,@QtdHipMaxAdd int
27 ,@QtdHipMax int
28 ,@NHipEXC int
29 ,@NHipAdd int
30 , @Parar_Primeira bit
31 , @ClassF varchar(512)
32 , @DEBUG INT
33 SELECT --
34 @Parar_Primeira =0
35 , @QtdHipMax =2
36 , @QtdHipMaxAdd =1
37 , @QtdHipMaxExc =1
38 , @ClassF = 1
39 SELECT @VALORALVO = 34900.55
40 /*
41 (
42 --simular um valor alvo
43 SELECT --
44 sum(Valor)
45 FROM #TBDados
46 WHERE 1 = 1
47 AND (
48 (
49 Classf = @ClassF
50 --/*
51 AND COD NOT IN (
52 SELECT TOP 1 cod
53 FROM #TBDados
54 WHERE 1 = 1
55 AND Classf = @ClassF
56 order by CAST(CAST(NEWID() AS VARBINARY) AS INT)
57 )
58 -- */
59 )
60 --/*
61 OR COD IN (
62 SELECT TOP 1 cod
63 FROM #TBDados
64 WHERE 1 = 1
65 AND Classf <> @ClassF
66 order by CAST(CAST(NEWID() AS VARBINARY) AS INT)
67 )
68 -- */
69 )
70 )-- */
71 -------------------------------------------------Visualizar dados de entrada
72BEGIN
73 -------------------------------------------------Variaveis suporte
74 DECLARE --
75 @SumClassf MONEY
76 EXEC USP_DROPTEMP '#TBHip'
77 CREATE TABLE #tbHip (
78 ID_HIP INT IDENTITY(1, 1)
79 , QMov int
80 , CODS VARCHAR(1000)
81 , valor Money
82 , [SumDifClassfValor] Money
83 , ID int )
84 EXEC usp_DropTemp '#TBRES_CTE'
85 CREATE TABLE #TBRES_CTE (
86 ID int identity ( 1,1)
87 , CODS_Retirar VARCHAR(1000)
88 , SumDifClassfValor MONEY
89 , QMov INT
90 , CODS_INCLUIR varchar(max) DEFAULT ''
91 , valor MONEY
92 , SumDifClassfValor_Plus_Valor MONEY
93 )
94
95 -------------------------------------------------
96 SELECT --
97 @SumClassf= sum(Valor)
98 FROM #TBDados
99 WHERE 1=1
100 AND Classf=@ClassF
101 -------------------------------------------------
102 ;with CTE as
103 (
104 SELECT
105 QMov =1
106 , t1.COD
107 , valor
108 , t1.ID
109 FROM #TBDados t1
110 WHERE 1=1
111 AND Classf=@ClassF
112
113 UNION ALL
114 SELECT
115 QMov =CTE.QMov+ 1
116 , CTE.COD + ',' + t2.COD
117 , CTE.valor + t2.Valor
118 , t2.ID
119 FROM
120 CTE INNER JOIN #TBDados t2 on t2.ID < CTE.ID
121 WHERE 1=1
122 AND CTE.QMov<@QtdHipMax
123 AND Classf=@ClassF
124 )
125 ,RES_CTE AS (
126 SELECT QMov, COD, valor, Id
127 FROM CTE
128 WHERE 1=1
129 )
130 -------------------------------------------------
131 INSERT INTO #TBHIP
132 SELECT
133 QMov=QMov
134 , CODS=',' + COD +','
135 , Valor=valor
136 , [SumDifClassfValor]=@SumClassf-Valor
137 ,ID
138 from
139 RES_CTE
140 ORDER BY --
141 1,4,2
142
143 --
144 INSERT INTO #TBHIP
145 SELECT
146 QMov=0
147 , CODS=''
148 , Valor=0
149 , [SumDifClassfValor]=@SumClassf
150 ,ID =0
151
152
153 INSERT INTO #TBRES_CTE
154 SELECT --
155 CODS_Retirar = MAX(CODS)
156 , SumDifClassfValor = 0
157 , QMov =MAX(QMov )
158 , CODS_INCLUIR = convert(varchar(max),'')
159 , valor = SUM(A.VALOR)
160 , SumDifClassfValor_Plus_Valor =0
161 FROM #TBDados A ,#tbHip B
162 WHERE 1=1
163 AND CHARINDEX(',' + UPPER(A.COD) + ',', CODS)=0
164 AND Classf = @ClassF
165 GROUP BY --
166 B.ID_HIP
167 HAVING SUM(A.VALOR) =@VALORALVO
168 --
169 --SELECT * FROM #tbHip GOTO FIM
170
171-- WHILE 1=1
172-- begin
173 IF NOT EXISTS(
174 SELECT --
175 TOP 1 1
176 FROM #TBRES_CTE)
177 BEGIN
178 -------------------------------------------------
179 ;with CTE as
180 (
181 SELECT --
182 CODS_Retirar = T11.CODS
183 ,T11.SumDifClassfValor
184 ,QMov = 1
185 ,t1.ID
186 ,CODS_INCLUIR = t1.COD
187 ,T1.valor
188 ,SumDifClassfValor_Plus_Valor = T11.SumDifClassfValor + T1.valor
189 FROM #TBDados t1
190 ,#tbHip T11
191 WHERE 1 = 1
192 AND t1.Classf <> @ClassF
193 UNION ALL
194 SELECT CTE.CODS_Retirar
195 ,CTE.SumDifClassfValor
196 ,QMov = CTE.QMov + 1
197 ,t2.ID
198 ,CTE.CODS_INCLUIR + ',' + t2.COD
199 ,CTE.valor + t2.Valor
200 ,SumDifClassfValor_Plus_Valor = CTE.SumDifClassfValor_Plus_Valor + t2.Valor
201 FROM CTE
202 INNER JOIN #TBDados t2 ON t2.ID < CTE.ID
203 WHERE 1 = 1
204 AND CTE.QMov < @QtdHipMax
205 AND t2.Classf <> @ClassF
206
207 ) ,RES_CTE as ( select * from CTE )
208 -------------------------------------------------
209 INSERT INTO #TBRES_CTE
210 SELECT
211
212 CODS_Retirar = CODS_Retirar
213 , SumDifClassfValor
214 , QMov
215 , CODS_INCLUIR = ',' + CODS_INCLUIR + ','
216 , valor
217 , SumDifClassfValor_Plus_Valor
218
219 FROM
220 RES_CTE
221 ORDER BY --
222 1,4,2
223 END
224-- /*
225 SELECT --
226 B.ID
227 ,CODS_INCLUIR=MAX(CODS_INCLUIR)
228 ,CODS_Retirar=MAX(CODS_Retirar)
229 ,[Soma Alvo]= SUM(A.VALOR)
230 ,ALVO=@VALORALVO
231 FROM #TBDados A ,#TBRES_CTE B
232 WHERE 1=1
233 AND CHARINDEX(',' + UPPER(A.COD) + ',', CODS_Retirar)=0
234 and (
235 CHARINDEX(',' + UPPER(A.COD) + ',', CODS_INCLUIR)>0
236 OR
237 A.COD in (
238 SELECT cod
239 FROM #TBDados
240 WHERE 1 = 1
241 AND Classf = @ClassF
242 )
243 )
244 GROUP BY --
245 B.ID
246 HAVING SUM(A.VALOR) =@VALORALVO
247 -- */
248
249
250
251 IF ISNULL(@DEBUG ,0)>0
252 BEGIN
253 SELECT --
254 *
255 FROM (
256 SELECT --
257 ID
258 ,Classf
259 ,COD
260 ,Valor
261 ,[@VALORALVO] = @VALORALVO
262 ,[@SumClassf] = @SumClassf
263 ,[@Parar_Primeira ] = @Parar_Primeira
264 ,[@QtdHipMax] = @QtdHipMax
265 ,[@ClassF ] = @ClassF
266 FROM #TBDados
267 ) A
268 FULL JOIN (
269 SELECT --
270 BMK = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
271 ,B.ID
272 ,CODS_INCLUIR = MAX(CODS_INCLUIR)
273 ,CODS_Retirar = MAX(CODS_Retirar)
274 ,[Soma Alvo] = SUM(A.VALOR)
275 FROM #TBDados A
276 ,#TBRES_CTE B
277 WHERE 1 = 1
278 AND CHARINDEX(',' + UPPER(A.COD) + ',', CODS_Retirar) = 0
279 AND (
280 CHARINDEX(',' + UPPER(A.COD) + ',', CODS_INCLUIR) > 0
281 OR A.COD IN (
282 SELECT cod
283 FROM #TBDados
284 WHERE 1 = 1
285 AND Classf = @ClassF
286 )
287 )
288 GROUP BY --
289 B.ID
290 HAVING SUM(A.VALOR) = @VALORALVO
291 ) B
292 ON A.ID = B.bmk
293
294 /*
295 SELECT --
296 *
297 FROM #TBRES_CTE
298 -- */
299 END
300END
301FIM: