· 9 years ago · Feb 03, 2017, 03:16 PM
1--------------------------------------------------------------creating temp table
2IF OBJECT_ID('tempdb..#tmp_ebay_cde') IS NOT NULL
3DROP TABLE #tmp_ebay_cde
4
5SELECT
6 item_items.item_id,
7 CASE
8 WHEN is_cde.is_cde = 'cd'
9 THEN
10 CASE
11 WHEN ISNULL(eu_sell.item_price,0.00) BETWEEN 0 AND 5
12 THEN CAST(ISNULL(eu_sell.item_price,0.00) * 1.70 + 4 AS DECIMAL(9,2))
13 ELSE
14 CASE
15 WHEN eu_sell.item_price BETWEEN 5.01 AND 20
16 THEN CAST(eu_sell.item_price * 1.55 + 4 AS DECIMAL(9,2))
17 ELSE
18 CASE
19 WHEN eu_sell.item_price > 20
20 THEN CAST(eu_sell.item_price * 1.45 + 4 AS DECIMAL(9,2))
21 END
22 END
23 END
24 ELSE
25 CASE
26 WHEN (is_cde.avg_age BETWEEN 90 AND 250) AND eu_sell.item_price > 5
27 THEN CAST(0.93 * eu_sell.item_price AS DECIMAL(9,2))
28 ELSE
29 CASE
30 WHEN is_cde.avg_age BETWEEN 251 AND 400 AND eu_sell.item_price > 5
31 THEN CAST(0.91 * eu_sell.item_price AS DECIMAL(9,2))
32 ELSE
33 CASE
34 WHEN is_cde.avg_age > 400
35 THEN
36 CASE
37 WHEN CASE WHEN whse.ps_avg_cost IS NOT NULL THEN whse.ps_avg_cost ELSE whse.avg_cost END > eu_sell.item_price
38 THEN CAST(eu_sell.item_price * 1.50 + 4 AS DECIMAL(9,2))
39 ELSE
40 CASE
41 WHEN CASE WHEN whse.ps_avg_cost IS NOT NULL THEN whse.ps_avg_cost ELSE whse.avg_cost END < 1.00
42 THEN CAST(100/58 * 1.3 + 4 AS DECIMAL(9,2))
43 ELSE CAST(CASE WHEN whse.ps_avg_cost IS NOT NULL THEN whse.ps_avg_cost ELSE whse.avg_cost END * 2.1 + 4 AS DECIMAL(9,2))
44 END
45 END
46 ELSE
47 CASE
48 WHEN is_cde = 'e' AND CASE WHEN whse.ps_avg_cost IS NOT NULL THEN whse.ps_avg_cost ELSE whse.avg_cost END < 1.00
49 THEN CAST(100/58 * 1.3 + 4 AS DECIMAL(9,2))
50 ELSE
51 CASE
52 WHEN is_cde = 'e'
53 THEN CAST(CASE WHEN whse.ps_avg_cost IS NOT NULL THEN whse.ps_avg_cost ELSE whse.avg_cost END * 2.1 + 4 AS DECIMAL(9,2))
54 ELSE CAST(100 / 58.00 * CASE WHEN whse.ps_avg_cost IS NOT NULL THEN whse.ps_avg_cost ELSE whse.avg_cost END * 1.3 + 4 AS DECIMAL(9,2))
55 END
56 END
57 END
58 END
59 END
60 END AS item_price,
61 CASE
62 WHEN is_cde.is_cde = 'cd'
63 THEN
64 CASE
65 WHEN ISNULL(eu_sell.item_price,0.00) BETWEEN 0 AND 5
66 THEN 'Box Condition C/D; EU Sell is 0 € - 5 € ==>> Ebay price = EU Sell * 1,70 + 4'
67 ELSE
68 CASE
69 WHEN eu_sell.item_price BETWEEN 5.01 AND 20
70 THEN 'Box Condition C/D; EU Sell is 5,01 € - 20 € ==>> Ebay price = EU Sell * 1.55 + 4'
71 ELSE
72 CASE
73 WHEN eu_sell.item_price > 20
74 THEN 'Box Condition C/D; EU Sell > 20,01 € ==>> Ebay price = EU Sell * 1.45 + 4'
75 END
76 END
77 END
78 ELSE
79 CASE
80 WHEN (is_cde.avg_age BETWEEN 90 AND 250) AND eu_sell.item_price > 5
81 THEN '90 - 250 days on shelf ==>> discount - 7% (excluding items with EU Sell 0 € ~ 5 €)'
82 ELSE
83 CASE
84 WHEN is_cde.avg_age BETWEEN 251 AND 400 AND eu_sell.item_price > 5
85 THEN '251-400 days on shelf ==>> discount - 9% (excluding items with EU Sell 0 € ~ 5 €)'
86 ELSE
87 CASE
88 WHEN is_cde.avg_age > 400
89 THEN
90 CASE
91 WHEN CASE WHEN whse.ps_avg_cost IS NOT NULL THEN whse.ps_avg_cost ELSE whse.avg_cost END > eu_sell.item_price
92 THEN 'Average Age > 400; Proxy Set AVG Cost > EU Sell ==>> Ebay price = EU Sell * 1.50 + 4'
93 ELSE
94 CASE
95 WHEN CASE WHEN whse.ps_avg_cost IS NOT NULL THEN whse.ps_avg_cost ELSE whse.avg_cost END < 1.00
96 THEN 'Average Age > 400; Proxy Set AVG cost < 1.00 ==>> Proxy Set AVG Cost = 1.00; Ebay price = 100/58 * 1.3 + 4'
97 ELSE 'Average Age > 400 ==>> Ebay price = Proxy set AVG Cost * 2.1 + 4'
98 END
99 END
100 ELSE
101 CASE
102 WHEN is_cde = 'e' AND CASE WHEN whse.ps_avg_cost IS NOT NULL THEN whse.ps_avg_cost ELSE whse.avg_cost END < 1.00
103 THEN 'Box Condition E; Proxy Set AVG Cost < 1.00 ==>> Proxy Set AVG Cost = 1.00; Ebay price = 100/58 * 1.3 + 4'
104 ELSE
105 CASE
106 WHEN is_cde = 'e'
107 THEN 'Box Condition E ==>> Ebay price = Proxy set AVG Cost * 100/58 * 1.3 + 4'
108 ELSE 'Box Condition A/B; Average Age < 90 ==>> Ebay price = Proxy set AVG Cost * 100/58 * 1.3 + 4'
109 END
110 END
111 END
112 END
113 END
114 END AS note,
115 GETDATE() AS created,
116 ISNULL(rule_exception.user_id,4231) AS user_id, /*Sage Robot*/
117 106 AS sales_interface_id, /*Ebay DE CDE*/
118 item_prices.item_price AS existing_cde_price
119INTO
120 #tmp_ebay_cde
121FROM
122 item_items
123LEFT JOIN
124 flex_relationship_values AS ps
125 ON ps.flex_child_value = item_items.item_id
126 AND ps.flex_relationship_id = 2 /*Items to Proxy Set*/
127INNER JOIN (
128 SELECT
129 item_barcodes.sage_item_id,
130 SUM(item_barcodes.cost) / sum(item_barcodes.uofm) AS avg_cost,
131 ps_whse.ps_avg_cost
132 FROM
133 improEU.dbo.item_barcodes
134 OUTER APPLY (
135 SELECT
136 SUM(ib.cost) / sum(ib.uofm) AS ps_avg_cost
137 FROM
138 flex_relationship_values AS items_to_proxy_sets
139 INNER JOIN
140 flex_relationship_values AS items_2_proxy_sets
141 ON
142 items_2_proxy_sets.flex_relationship_id = 2 /*Items to Proxy Sets*/
143 AND items_2_proxy_sets.flex_parent_value = items_to_proxy_sets.flex_parent_value
144 INNER JOIN
145 item_items AS item
146 ON
147 item.item_id = items_2_proxy_sets.flex_child_value
148 AND item.is_deleted = 0
149 INNER JOIN
150 improEU.dbo.item_barcodes AS ib
151 ON
152 ib.sage_item_id = item.item_id
153 AND ib.post_date IS NOT NULL
154 AND ib.sale_date IS NULL
155 AND ib.summary_id > 0
156 AND ib.box_condition IN ('C','D','E')
157 WHERE
158 items_to_proxy_sets.flex_relationship_id = 2 /*Items to Proxy Sets*/
159 AND items_to_proxy_sets.flex_child_value = item_barcodes.sage_item_id
160 GROUP BY
161 items_to_proxy_sets.flex_child_value
162 HAVING
163 SUM(ib.uofm) > 0
164 ) AS ps_whse
165 WHERE
166 item_barcodes.box_condition IN ('C','D','E')
167 AND item_barcodes.post_date IS NOT NULL
168 AND item_barcodes.sale_date IS NULL
169 AND item_barcodes.summary_id > 0
170 GROUP BY
171 item_barcodes.sage_item_id,
172 ps_whse.ps_avg_cost
173 HAVING
174 SUM(item_barcodes.uofm) > 0
175) AS whse
176ON
177 whse.sage_item_id = item_items.item_id
178LEFT JOIN (
179 SELECT
180 ps_box_conditions.proxy_set_id,
181 0 item_id,
182 CASE
183 WHEN ps_box_conditions.box_conditions LIKE '%C%' OR ps_box_conditions.box_conditions LIKE '%D%'
184 THEN 'cd'
185 ELSE
186 CASE
187 WHEN
188 ps_box_conditions.box_conditions NOT LIKE '%A%'
189 AND ps_box_conditions.box_conditions NOT LIKE '%B%'
190 AND ps_box_conditions.box_conditions NOT LIKE '%C%'
191 AND ps_box_conditions.box_conditions NOT LIKE '%D%'
192 THEN 'e'
193 ELSE 'ab'
194 END
195 END AS is_cde,
196 ps_box_conditions.avg_age
197 FROM (
198 SELECT
199 items_to_proxy_sets.flex_parent_value AS proxy_set_id,
200 STUFF((
201 SELECT
202 ',' + ib.box_condition AS [text()]
203 FROM
204 improeu..item_barcodes ib
205 INNER JOIN item_items
206 ON ib.sage_item_id = item_items.item_id
207 INNER JOIN
208 flex_relationship_values AS items_to_proxy_sets_inner
209 ON items_to_proxy_sets_inner.flex_parent_value = items_to_proxy_sets.flex_parent_value
210 AND items_to_proxy_sets_inner.flex_child_value = item_items.item_id
211 FOR XML PATH('')
212 ), 1, 1, '' ) AS box_conditions,
213 avg_age.avg_age
214 FROM item_items
215 INNER JOIN
216 flex_relationship_values AS items_to_proxy_sets
217 ON
218 items_to_proxy_sets.flex_relationship_id = 2 /*Items to Proxy Sets*/
219 AND items_to_proxy_sets.flex_child_value = item_items.item_id --item_barcodes.sage_item_id
220 INNER JOIN
221 improeu..item_barcodes ib
222 ON
223 ib.sage_item_id = item_items.item_id
224 OUTER APPLY (
225 SELECT
226 items_to_proxy_sets.flex_parent_value AS proxy_set_id,
227 AVG(DATEDIFF(DAY, post_date, ISNULL(sale_date, getdate()))) AS avg_age
228 FROM improeu..item_barcodes
229 WHERE sage_item_id IN (
230 SELECT item_items.item_id
231 FROM item_items
232 INNER JOIN
233 flex_relationship_values AS ps
234 ON ps.flex_child_value = item_items.item_id
235 AND ps.flex_relationship_id = 2 /*Items to Proxy Set*/
236 WHERE ps.flex_parent_value = items_to_proxy_sets.flex_parent_value
237 )
238 ) avg_age
239 GROUP BY
240 items_to_proxy_sets.flex_parent_value,
241 avg_age.avg_age
242 ) ps_box_conditions
243 UNION
244 SELECT
245 0 proxy_set_id,
246 no_ps_box_conditions.item_id,
247 CASE
248 WHEN no_ps_box_conditions.box_conditions LIKE '%C%' OR no_ps_box_conditions.box_conditions LIKE '%D%'
249 THEN 'cd'
250 ELSE
251 CASE
252 WHEN
253 no_ps_box_conditions.box_conditions not like '%A%'
254 AND no_ps_box_conditions.box_conditions not like '%B%'
255 AND no_ps_box_conditions.box_conditions not like '%C%'
256 AND no_ps_box_conditions.box_conditions not like '%D%'
257 THEN 'e'
258 ELSE 'ab'
259 END
260 END AS is_cde,
261 no_ps_box_conditions.avg_age
262 FROM (
263 SELECT
264 item_items.item_id,
265 '' proxy_set_id,
266 STUFF((
267 SELECT
268 ',' + ib.box_condition AS [text()]
269 FROM
270 improeu..item_barcodes ib
271 WHERE
272 ib.sage_item_id = item_items.item_id
273 FOR XML PATH('')
274 ), 1, 1, '' ) AS box_conditions,
275 avg_age.avg_age
276 FROM item_items
277 INNER JOIN
278 improeu..item_barcodes ib
279 ON ib.sage_item_id = item_items.item_id
280 OUTER APPLY (
281 SELECT
282 ii_sub.item_id,
283 AVG(DATEDIFF(DAY, post_date, ISNULL(sale_date, getdate()))) AS avg_age
284 FROM
285 item_items ii_sub
286 INNER JOIN
287 improeu..item_barcodes ib
288 ON ib.sage_item_id = ii_sub.item_id
289 WHERE
290 item_items.item_id = ii_sub.item_id
291 GROUP BY
292 ii_sub.item_id
293 ) avg_age
294 WHERE
295 item_items.item_id NOT IN (
296 SELECT ii.item_id
297 FROM item_items AS ii
298 INNER JOIN
299 flex_relationship_values AS ps
300 ON ps.flex_child_value = ii.item_id
301 AND ps.flex_relationship_id = 2 /*Items to Proxy Set*/
302 )
303 GROUP BY
304 item_items.item_id,
305 avg_age.avg_age
306 ) no_ps_box_conditions
307) is_cde
308ON is_cde.item_id = item_items.item_id
309OR is_cde.proxy_set_id = ps.flex_parent_value
310LEFT JOIN
311 item_price_derivative_rule_exception AS rule_exception
312 ON rule_exception.item_id = item_items.item_id
313 AND rule_exception.sales_interface_id = 106 /*Ebay DE CDE*/
314LEFT JOIN
315 item_prices AS eu_sell
316 ON eu_sell.item_id = item_items.item_id
317 AND eu_sell.sales_interface_id = 57 /*Surplus Sales Price EU*/
318LEFT JOIN
319 item_prices AS item_prices
320 ON item_prices.item_id = item_items.item_id
321 AND item_prices.sales_interface_id = 106 /*Ebay DE CDE*/
322WHERE
323 item_items.is_deleted = 0
324
325--------------------------------------------------------------create new prices where inventory exists
326INSERT INTO
327 item_prices (
328 item_id,
329 item_price,
330 note,
331 created,
332 user_id,
333 sales_interface_id
334 )
335SELECT
336 #tmp_ebay_cde.item_id,
337 #tmp_ebay_cde.item_price,
338 #tmp_ebay_cde.note,
339 #tmp_ebay_cde.created,
340 #tmp_ebay_cde.user_id,
341 #tmp_ebay_cde.sales_interface_id
342FROM #tmp_ebay_cde
343WHERE #tmp_ebay_cde.existing_cde_price IS NULL
344
345--------------------------------------------------------------update existing Ebay DE CDE prices
346UPDATE
347 ip
348SET
349 ip.item_price = new_p.item_price,
350 ip.note = new_p.note,
351 ip.user_id = new_p.user_id,
352 ip.created = new_p.created
353SELECT
354 new_p.item_price,
355 new_p.note,
356 new_p.user_id,
357 new_p.created
358FROM
359 item_prices AS ip
360INNER JOIN (
361 SELECT
362 #tmp_ebay_cde.item_id,
363 #tmp_ebay_cde.item_price,
364 #tmp_ebay_cde.note,
365 #tmp_ebay_cde.created,
366 #tmp_ebay_cde.user_id,
367 #tmp_ebay_cde.sales_interface_id
368 FROM #tmp_ebay_cde
369) new_p
370ON ip.item_id = new_p.item_id
371AND ip.item_price <> new_p.item_price
372WHERE ip.sales_interface_id = 106 /*Ebay DE CDE*/
373--------------------------------------------------------------drop temp table
374IF OBJECT_ID('tempdb..#tmp_ebay_cde') IS NOT NULL
375 DROP TABLE #tmp_ebay_cde