· 7 years ago · Dec 21, 2018, 02:46 AM
1IF EXISTS ( SELECT *
2 FROM sys.objects
3 WHERE type = 'P'
4 AND name = 'DMS_pp_RangeSM')
5 DROP PROCEDURE DMS_pp_RangeSM;
6GO
7SET ANSI_NULLS ON;
8GO
9SET QUOTED_IDENTIFIER ON;
10GO
11/********************************************************************
12* Procedure: sp_DMS_RPT_ProcessOrderStatus
13* Description:
14* DATE Author Description
15* -------------------------------------------------------------------
16* 2018-20-12 Chieu Pham Create
17********************************************************************/
18
19--[dbo].[DMS_pp_RangeSM] 11,2018,3
20CREATE PROCEDURE [dbo].[DMS_pp_RangeSM] --12,2018,3
21 @Month INT,
22 @Year INT,
23 @CompanyID INT,
24 @DistributorID VARCHAR(MAX) = NULL
25AS
26BEGIN
27 SET NOCOUNT ON;
28 SET FMTONLY OFF;
29 SET NO_BROWSETABLE OFF;
30 DECLARE @PeriodID NVARCHAR(7);
31 DECLARE @Effdate DATE = CONVERT(DATE, GETDATE());
32 DECLARE @expiredate DATE = DATEADD(DAY, -1, DATEADD(MONTH, 1, @Effdate));
33 SET @PeriodID = N'M' + RIGHT(CONVERT(NVARCHAR(2), @Month), 2) + CONVERT(NVARCHAR(4), @Year);
34
35 DECLARE @_CompanyID INT = NULLIF(@CompanyID, 0);
36
37
38 DECLARE @_DistributorID VARCHAR(MAX) = NULLIF(LTRIM(RTRIM(@DistributorID)), '');
39 SELECT *
40 INTO #ListDistributor
41 FROM dbo.fn_DMS_ConvertIDListToTable(@_DistributorID);
42 --------------------------------------------------------------- 1: Lấy Range của SM -------------------------------------------------------
43 SELECT s.RefNbr,
44 'SLM' AS ROLE,
45 c.EmployeeCD,
46 s.Salary,
47 l1.Range,
48 @Effdate AS Effdate,
49 @expiredate AS expiredate
50 INTO #RSSLM
51 FROM
52 (
53 SELECT s.*
54 FROM DMSUploadSalaryLine s
55 JOIN dbo.DMSUploadSalaryHeader B
56 ON s.RefNbr = B.RefNbr
57 AND s.DistributorID = B.DistributorID
58 AND s.CompanyID = B.CompanyID
59 WHERE B.Status = 'R'
60 AND s.RefNbr = @PeriodID
61 AND s.CompanyID = @_CompanyID
62 ) s
63 JOIN
64 (
65 SELECT s.*
66 FROM DMSRangeHammerLine s
67 JOIN dbo.DMSRangeHammerHeader B
68 ON s.RefNbr = B.RefNbr
69 AND s.DistributorID = B.DistributorID
70 AND s.CompanyID = B.CompanyID
71 WHERE B.Status = 'R'
72 AND s.RefNbr = @PeriodID
73 AND s.CompanyID = @_CompanyID
74 ) l1
75 ON l1.CompanyID = s.CompanyID
76 AND l1.Condition = '>='
77 AND s.Salary >= l1.Value
78 JOIN dbo.DMSSalesForce c
79 ON c.EmployeeID = s.SalesForceID
80 AND c.CompanyID = s.CompanyID
81 AND c.CompanyID = @_CompanyID
82 WHERE NOT EXISTS
83 (
84 SELECT *
85 FROM DMSRangeHammerLine l2
86 WHERE s.Salary >= l2.Value
87 AND l2.Value > l1.Value
88 )
89 AND l1.RefNbr = @PeriodID
90 AND l1.CompanyID = @_CompanyID
91 UNION ALL
92 SELECT s.RefNbr,
93 'SLM' AS ROLE,
94 c.EmployeeCD,
95 s.Salary,
96 l1.Range,
97 @Effdate AS Effdate,
98 @expiredate AS expiredate
99 FROM
100 (
101 SELECT s.*
102 FROM DMSUploadSalaryLine s
103 JOIN dbo.DMSUploadSalaryHeader B
104 ON s.RefNbr = B.RefNbr
105 AND s.DistributorID = B.DistributorID
106 AND s.CompanyID = B.CompanyID
107 WHERE B.Status = 'R'
108 AND s.RefNbr = @PeriodID
109 AND s.CompanyID = @_CompanyID
110 ) s
111 JOIN
112 (
113 SELECT s.*
114 FROM DMSRangeHammerLine s
115 JOIN dbo.DMSRangeHammerHeader B
116 ON s.RefNbr = B.RefNbr
117 AND s.DistributorID = B.DistributorID
118 AND s.CompanyID = B.CompanyID
119 WHERE B.Status = 'R'
120 AND s.RefNbr = @PeriodID
121 AND s.CompanyID = @_CompanyID
122 ) l1
123 ON l1.CompanyID = s.CompanyID
124 AND l1.Condition = '<'
125 AND s.Salary < l1.Value
126 JOIN dbo.DMSSalesForce c
127 ON c.EmployeeID = s.SalesForceID
128 AND c.CompanyID = s.CompanyID
129 WHERE NOT EXISTS
130 (
131 SELECT *
132 FROM DMSRangeHammerLine l2
133 WHERE s.Salary <= l2.Value
134 AND l2.Value < l1.Value
135 )
136 AND l1.RefNbr = @PeriodID
137 AND l1.CompanyID = @_CompanyID; -- return;
138 --------------------------------------------------------------------------------------------------2: Lấy range của SCS-----------------------------------------------------------------------------
139 ---------------------------- 2.1: đếm số SM của Salesup quản lý, tổng SL SM của Salesup quản lý-----------------------------------
140 SELECT DISTINCT
141 B.SaleSupID,
142 B.i,
143 B.Weak,
144 B.g,
145 B.e,
146 B.p,
147 SL = i + B.Weak + g + e + p
148 INTO #tmpResult
149 FROM
150 (
151 SELECT B.SaleSupID,
152 SUM(B.i) i,
153 SUM(B.Weak) Weak,
154 SUM(B.g) g,
155 SUM(B.e) e,
156 SUM(B.p) p
157 FROM
158 (
159 SELECT a.SaleSupID,
160 COUNT(B.Range) AS E,
161 '' G,
162 '' I,
163 '' Weak,
164 '' P
165 FROM [STAGING].THPDMSMasterData2.dbo.DMSBLSalesForce a
166 JOIN #RSSLM B
167 ON a.SalePersonCD = B.EmployeeCD
168 WHERE a.BaselineDate = '2018-11-29 00:00:00.000'
169 AND B.Range = 'E' --CONVERT(DATE,GETDATE()) )
170 GROUP BY a.SaleSupID
171 UNION ALL
172 SELECT a.SaleSupID,
173 '' AS E,
174 COUNT(B.Range) G,
175 '' I,
176 '' Weak,
177 '' P
178 FROM [STAGING].THPDMSMasterData2.dbo.DMSBLSalesForce a
179 JOIN #RSSLM B
180 ON a.SalePersonCD = B.EmployeeCD
181 WHERE a.BaselineDate = '2018-11-29 00:00:00.000'
182 AND B.Range = 'G' --CONVERT(DATE,GETDATE()) )
183 GROUP BY a.SaleSupID
184 UNION ALL
185 SELECT a.SaleSupID,
186 '' AS E,
187 '' G,
188 COUNT(B.Range) I,
189 '' Weak,
190 '' P
191 FROM [STAGING].THPDMSMasterData2.dbo.DMSBLSalesForce a
192 JOIN #RSSLM B
193 ON a.SalePersonCD = B.EmployeeCD
194 WHERE a.BaselineDate = '2018-11-29 00:00:00.000'
195 AND B.Range = 'I' --CONVERT(DATE,GETDATE()) )
196 GROUP BY a.SaleSupID
197 UNION ALL
198 SELECT a.SaleSupID,
199 '' AS E,
200 '' G,
201 '' I,
202 COUNT(B.Range) Weak,
203 '' P
204 FROM [STAGING].THPDMSMasterData2.dbo.DMSBLSalesForce a
205 JOIN #RSSLM B
206 ON a.SalePersonCD = B.EmployeeCD
207 WHERE a.BaselineDate = '2018-11-29 00:00:00.000'
208 AND B.Range = 'Weak' --CONVERT(DATE,GETDATE()) )
209 GROUP BY a.SaleSupID
210 UNION ALL
211 SELECT a.SaleSupID,
212 '' AS E,
213 -- COUNT(B.Range) G,
214 '' G,
215 '' I,
216 '' Weak,
217 COUNT(B.Range) P
218 FROM [STAGING].THPDMSMasterData2.dbo.DMSBLSalesForce a
219 JOIN #RSSLM B
220 ON a.SalePersonCD = B.EmployeeCD
221 WHERE a.BaselineDate = '2018-11-29 00:00:00.000'
222 AND B.Range = 'P' --CONVERT(DATE,GETDATE()) )
223 GROUP BY a.SaleSupID
224 ) B
225 GROUP BY B.SaleSupID
226 ) B;
227 --RETURN;
228
229 ----------------------------------------------------2.2: Lấy range của SaleSup-------------------------------------------------------------------
230 SELECT DISTINCT
231 b.RefNbr,
232 'SCS' Role,
233 b.SaleSupID AS EmployeeCD,
234 0 AS Salary,
235 b.range,
236 @Effdate AS Effdate,
237 @expiredate AS expiredate -- CASE WHEN b.Weak>0 OR b.Weak IS null THEN 'Weak' ELSE b.Range END range
238 INTO #RSSCS
239 FROM
240 (
241 SELECT a.RefNbr,
242 a.SaleSupID,
243 CASE
244 WHEN b.range IS NULL
245 OR b.range = '' THEN
246 'Weak'
247 ELSE
248 b.range
249 END range
250 FROM
251 (
252 SELECT *
253 FROM #tmpResult T1
254 JOIN
255 (
256 SELECT s.*
257 FROM DMSRangeHammerLineSCS s
258 JOIN dbo.DMSRangeHammerHeader b
259 ON s.RefNbr = b.RefNbr
260 AND s.DistributorID = b.DistributorID
261 WHERE b.Status = 'R'
262 AND s.RefNbr = @PeriodID
263 ) T2
264 ON T2.RefNbr = @PeriodID
265 ) a
266 LEFT JOIN
267 (
268 SELECT *
269 FROM #tmpResult T1
270 JOIN
271 (
272 SELECT s.*
273 FROM DMSRangeHammerLineSCS s
274 JOIN dbo.DMSRangeHammerHeader b
275 ON s.RefNbr = b.RefNbr
276 AND s.DistributorID = b.DistributorID
277 WHERE b.Status = 'R'
278 AND s.RefNbr = @PeriodID
279 ) T2
280 ON T2.RefNbr = @PeriodID
281 AND
282 (
283 T2.PassNeed = 1
284 AND ISNULL(T1.Weak, 0) = 0
285 AND ISNULL(T1.i, 0) = 0
286 AND T1.SL - T1.g <> 0
287 AND T1.p > 1 --AND (T1.SL)-T1.P=0 -- ALL SM ÄỀU LÀ PASS
288
289 AND
290 (
291 T2.ExcellentNeed IS NULL
292 AND T2.GoodNeed IS NULL
293 AND T2.PassNeed = 1
294 -- OR T2.ExcellentEnough <= T1.e
295 )
296 AND
297 (
298 T2.ExcellentEnough IS NULL
299 OR T2.ExcellentEnough <= T1.e
300 )
301 AND
302 (
303 T2.GoodEnough IS NULL
304 OR T2.GoodEnough <= T1.g
305 )
306 AND (T2.PassEnough IS NULL
307 --OR T2.PassEnough>=1
308 )
309 AND (T2.ImproveEnough IS NULL
310 -- OR T2.ImproveEnough <= T1.i
311 )
312 AND
313 (
314 T2.WeakEnough IS NULL
315 AND T2.WeakNeed IS NULL
316 -- OR T2.WeakEnough <= T1.Weak
317 )
318 OR
319 (
320 T2.PassNeed = 1
321 AND T1.SL - T1.p = 0
322 OR
323 (
324 T1.SL - (T1.p + T1.e + T1.g) = 0
325 AND T2.PassNeed = 1
326 AND T1.p > 1
327 )
328 )
329 ) -- PASS
330 UNION ALL
331 SELECT *
332 FROM #tmpResult T1
333 JOIN
334 (
335 SELECT s.*
336 FROM DMSRangeHammerLineSCS s
337 JOIN dbo.DMSRangeHammerHeader b
338 ON s.RefNbr = b.RefNbr
339 AND s.DistributorID = b.DistributorID
340 WHERE b.Status = 'R'
341 AND s.RefNbr = @PeriodID
342 ) T2
343 ON T2.RefNbr = @PeriodID
344 AND
345 (
346 T2.ExcellentNeed = 1
347 AND T1.i = 0
348 AND T1.Weak = 0
349 AND T1.p = 0
350 AND (T1.SL) - T1.e <= (ISNULL(T2.WeakEnough, 0) + ISNULL(T2.ImproveEnough, 0)
351 + ISNULL(T2.GoodEnough, 0) + ISNULL(T2.PassEnough, 0)
352 )
353 AND
354 (
355 T2.ExcellentEnough IS NULL
356 OR T2.ExcellentEnough <= T1.e
357 )
358 AND
359 (
360 T2.GoodEnough IS NULL
361 OR T2.GoodEnough <= T1.g
362 )
363 AND (T2.PassEnough IS NULL
364 -- OR T2.PassEnough <= T1.p
365 )
366 AND (T2.ImproveEnough IS NULL
367 -- OR T2.ImproveEnough <= T1.i
368 )
369 AND (T2.WeakEnough IS NULL
370 -- OR T2.WeakEnough <= T1.Weak
371 )
372 OR
373 (
374 T2.ExcellentNeed = 1
375 AND T1.SL - T1.e = 0
376 )
377 ) --Excellent
378
379
380 UNION ALL
381 SELECT *
382 FROM #tmpResult T1
383 JOIN
384 (
385 SELECT s.*
386 FROM DMSRangeHammerLineSCS s
387 JOIN dbo.DMSRangeHammerHeader b
388 ON s.RefNbr = b.RefNbr
389 AND s.DistributorID = b.DistributorID
390 WHERE b.Status = 'R'
391 AND s.RefNbr = @PeriodID
392 ) T2
393 ON T2.RefNbr = @PeriodID
394 AND
395 (
396 T2.GoodNeed = 1
397
398 AND T1.e + T1.g + T1.p = T1.SL
399 AND T1.p <= 1
400 AND T1.i = 0
401 AND T1.Weak = 0
402 AND ISNULL(T2.PassEnough, 0) <= 1
403 AND
404 (
405 T2.ExcellentEnough IS NULL
406 OR T2.ExcellentEnough <= T1.e
407 )
408 AND
409 (
410 T2.GoodEnough IS NULL
411 OR T2.GoodEnough <= T1.g
412 )
413 AND
414 (
415 T2.PassEnough IS NULL
416 AND T1.p <= 1
417 OR T2.PassEnough <= T1.p
418 AND T1.p <= 1
419 )
420 AND
421 (
422 T2.ImproveEnough IS NULL
423 AND T2.ImproveNeed IS NULL
424 -- OR T2.ImproveEnough <= T1.i
425 )
426 AND
427 (
428 T2.WeakEnough IS NULL
429 AND T2.WeakNeed IS NULL
430 -- OR T2.WeakEnough <= T1.Weak
431 )
432 OR
433 (
434 T2.GoodNeed = 1
435 AND T1.SL - T1.g = 0
436 OR
437 (
438 T2.GoodNeed = 1
439 AND T1.SL - T1.g - T1.e = 0
440 ) --and t1.p<=1
441 )
442 ) --Good
443 UNION ALL
444 SELECT *
445 FROM #tmpResult T1
446 JOIN
447 (
448 SELECT s.*
449 FROM DMSRangeHammerLineSCS s
450 JOIN dbo.DMSRangeHammerHeader b
451 ON s.RefNbr = b.RefNbr
452 AND s.DistributorID = b.DistributorID
453 WHERE b.Status = 'R'
454 AND s.RefNbr = @PeriodID
455 ) T2
456 ON T2.RefNbr = @PeriodID
457 AND
458 (
459 T2.ImproveEnough >= 1
460 AND ISNULL(T2.WeakEnough, 0) = 0 --or ((T1.SL) - (T1.e + T1.g + T1.i + T1.p) =0)
461 AND T2.ImproveNeed IS NULL
462 AND T1.p + T1.i + T1.e + T1.g = T1.SL
463 AND T1.Weak = 0
464 AND T1.i <> 0
465 AND
466 (
467 T2.ExcellentEnough IS NULL
468 OR T2.ExcellentEnough <= T1.e
469 )
470 AND
471 (
472 T2.GoodEnough IS NULL
473 OR T2.GoodEnough <= T1.g
474 )
475 AND
476 (
477 T2.PassEnough IS NULL
478 OR T2.PassEnough <= T1.p
479 )
480 AND (T2.WeakEnough IS NULL
481 -- OR T2.WeakEnough <= T1.Weak
482 )
483 OR
484 (
485 T2.ImproveEnough = 1
486 AND T1.SL - T1.i = 0
487 )
488 )
489
490
491 -- ) --Improve)
492 ) b
493 ON b.RefNbr = a.RefNbr
494 AND b.SaleSupID = a.SaleSupID
495 ) b;
496 --return;
497 ------RSASCM------
498 --------------------------------------------------------------------------------------------------3: Lấy range của RSASCM-----------------------------------------------------------------------------
499 ---------------------------- 3.1: đếm số SaleSup của RSASCM quản lý, tổng SL SaleSup của RSASCM quản lý-----------------------------------
500 SELECT DISTINCT
501 B.ASMID,
502 B.i,
503 B.Weak,
504 B.g,
505 B.e,
506 B.p,
507 SL = i + B.Weak + g + e + p
508 INTO #tmpResultASCM
509 FROM
510 (
511 SELECT B.ASMID,
512 SUM(B.i) i,
513 SUM(B.Weak) Weak,
514 SUM(B.g) g,
515 SUM(B.e) e,
516 SUM(B.p) p
517 FROM
518 (
519 SELECT a.ASMID,
520 COUNT(B.range) AS E,
521 '' G,
522 '' I,
523 '' Weak,
524 '' P
525 FROM [STAGING].THPDMSMasterData2.dbo.DMSBLSalesForce a
526 JOIN #RSSCS B
527 ON a.SaleSupID = B.EmployeeCD
528 WHERE a.BaselineDate = '2018-11-29 00:00:00.000'
529 AND B.range = 'E' --CONVERT(DATE,GETDATE()) )
530 GROUP BY a.ASMID
531 UNION ALL
532 SELECT a.ASMID,
533 '' AS E,
534 COUNT(B.range) G,
535 '' I,
536 '' Weak,
537 '' P
538 FROM [STAGING].THPDMSMasterData2.dbo.DMSBLSalesForce a
539 JOIN #RSSCS B
540 ON a.SaleSupID = B.EmployeeCD
541 WHERE a.BaselineDate = '2018-11-29 00:00:00.000'
542 AND B.range = 'G' --CONVERT(DATE,GETDATE()) )
543 GROUP BY a.ASMID
544 UNION ALL
545 SELECT a.ASMID,
546 '' AS E,
547 '' G,
548 COUNT(B.range) I,
549 '' Weak,
550 '' P
551 FROM [STAGING].THPDMSMasterData2.dbo.DMSBLSalesForce a
552 JOIN #RSSCS B
553 ON a.SaleSupID = B.EmployeeCD
554 WHERE a.BaselineDate = '2018-11-29 00:00:00.000'
555 AND B.range = 'I' --CONVERT(DATE,GETDATE()) )
556 GROUP BY a.ASMID
557 UNION ALL
558 SELECT a.ASMID,
559 '' AS E,
560 '' G,
561 '' I,
562 COUNT(B.range) Weak,
563 '' P
564 FROM [STAGING].THPDMSMasterData2.dbo.DMSBLSalesForce a
565 JOIN #RSSCS B
566 ON a.SaleSupID = B.EmployeeCD
567 WHERE a.BaselineDate = '2018-11-29 00:00:00.000'
568 AND B.range = 'Weak' --CONVERT(DATE,GETDATE()) )
569 GROUP BY a.ASMID
570 UNION ALL
571 SELECT a.ASMID,
572 '' AS E,
573 -- COUNT(B.range) G,
574 '' g,
575 '' I,
576 '' Weak,
577 COUNT(B.range) P
578 FROM [STAGING].THPDMSMasterData2.dbo.DMSBLSalesForce a
579 JOIN #RSSCS B
580 ON a.SaleSupID = B.EmployeeCD
581 WHERE a.BaselineDate = '2018-11-29 00:00:00.000'
582 AND B.range = 'P' --CONVERT(DATE,GETDATE()) )
583 GROUP BY a.ASMID
584 ) B
585 GROUP BY B.ASMID
586 ) B; --return
587 ----------------------------------------------------3.2: Lấy range của RSASCM-------------------------------------------------------------------
588 SELECT DISTINCT
589 b.RefNbr,
590 'ASCM' Role,
591 b.ASMID AS EmployeeCD,
592 0 AS Salary,
593 range,
594 @Effdate AS Effdate,
595 @expiredate AS expiredate --CASE WHEN b.Weak>0 THEN 'Weak' ELSE b.Range END
596 INTO #RSASCM
597 FROM
598 (
599 SELECT a.RefNbr,
600 a.ASMID,
601 CASE
602 WHEN b.range IS NULL
603 OR b.range = '' THEN
604 'Weak'
605 ELSE
606 b.range
607 END range
608 FROM
609 (
610 SELECT *
611 FROM #tmpResultASCM T1
612 JOIN
613 (
614 SELECT s.*
615 FROM DMSRangeHammerLineASCM s
616 JOIN dbo.DMSRangeHammerHeader b
617 ON s.RefNbr = b.RefNbr
618 AND s.DistributorID = b.DistributorID
619 WHERE b.Status = 'R'
620 AND s.RefNbr = @PeriodID
621 ) T2
622 ON T2.RefNbr = @PeriodID
623 ) a
624 LEFT JOIN
625 (
626 SELECT *
627 FROM #tmpResultASCM T1
628 JOIN
629 (
630 SELECT s.*
631 FROM DMSRangeHammerLineASCM s
632 JOIN dbo.DMSRangeHammerHeader b
633 ON s.RefNbr = b.RefNbr
634 AND s.DistributorID = b.DistributorID
635 WHERE b.Status = 'R'
636 AND s.RefNbr = @PeriodID
637 ) T2
638 ON T2.RefNbr = @PeriodID
639 AND
640 (
641 T2.PassNeed = 1
642 AND ISNULL(T1.Weak, 0) = 0
643 AND ISNULL(T1.i, 0) = 0
644 AND T1.SL - T1.g <> 0
645 AND T1.p > 1 --AND (T1.SL)-T1.P=0 -- ALL SM ÄỀU LÀ PASS
646
647 AND
648 (
649 T2.ExcellentNeed IS NULL
650 AND T2.GoodNeed IS NULL
651 AND T2.PassNeed = 1
652 -- OR T2.ExcellentEnough <= T1.e
653 )
654 AND
655 (
656 T2.ExcellentEnough IS NULL
657 OR ISNULL(T2.ExcellentEnough, 0) <= T1.e
658 )
659 AND
660 (
661 T2.GoodEnough IS NULL
662 OR ISNULL(T2.GoodEnough, 0) <= T1.g
663 )
664 AND
665 (
666 T2.PassEnough IS NULL
667 OR ISNULL(T2.PassEnough, 0) >= 1
668 )
669 AND
670 (
671 T2.ImproveEnough IS NULL
672 AND T2.ImproveNeed IS NULL
673 -- OR T2.ImproveEnough <= T1.i
674 )
675 AND
676 (
677 T2.WeakEnough IS NULL
678 AND T2.WeakNeed IS NULL
679 -- OR T2.WeakEnough <= T1.Weak
680 )
681 OR
682 (
683 T2.PassNeed = 1
684 AND T1.SL - T1.p = 0
685 OR
686 (
687 T1.SL - (T1.p + T1.e + T1.g) = 0
688 AND T2.PassNeed = 1
689 AND T1.p > 1
690 AND T1.i = 0
691 AND T1.Weak = 0
692 )
693 )
694 ) -- PASS
695 UNION ALL
696 SELECT *
697 FROM #tmpResultASCM T1
698 JOIN
699 (
700 SELECT s.*
701 FROM DMSRangeHammerLineASCM s
702 JOIN dbo.DMSRangeHammerHeader b
703 ON s.RefNbr = b.RefNbr
704 AND s.DistributorID = b.DistributorID
705 WHERE b.Status = 'R'
706 AND s.RefNbr = @PeriodID
707 ) T2
708 ON T2.RefNbr = @PeriodID
709 AND
710 (
711 T2.ExcellentNeed = 1
712 AND T1.i = 0
713 AND T1.Weak = 0
714 AND T1.p = 0
715 AND (T1.SL) - T1.e <= (ISNULL(T2.WeakEnough, 0) + ISNULL(T2.ImproveEnough, 0)
716 + ISNULL(T2.GoodEnough, 0) + ISNULL(T2.PassEnough, 0)
717 )
718 AND
719 (
720 T2.ExcellentEnough IS NULL
721 OR T2.ExcellentEnough <= T1.e
722 )
723 AND
724 (
725 T2.GoodEnough IS NULL
726 OR T2.GoodEnough <= T1.g
727 )
728 AND (T2.PassEnough IS NULL
729 -- OR T2.PassEnough <= T1.p
730 )
731 AND (T2.ImproveEnough IS NULL
732 -- OR T2.ImproveEnough <= T1.i
733 )
734 AND (T2.WeakEnough IS NULL
735 -- OR T2.WeakEnough <= T1.Weak
736 )
737 OR
738 (
739 T2.ExcellentNeed = 1
740 AND T1.SL - T1.e = 0
741 )
742 ) --Excellent
743
744 UNION ALL
745 SELECT *
746 FROM #tmpResultASCM T1
747 JOIN
748 (
749 SELECT s.*
750 FROM DMSRangeHammerLineASCM s
751 JOIN dbo.DMSRangeHammerHeader b
752 ON s.RefNbr = b.RefNbr
753 AND s.DistributorID = b.DistributorID
754 WHERE b.Status = 'R'
755 AND s.RefNbr = @PeriodID
756 ) T2
757 ON T2.RefNbr = @PeriodID
758 AND
759 (
760 T2.GoodNeed = 1
761 AND (T1.SL) - (T1.e + T1.g) <= (ISNULL(T2.WeakEnough, 0) + ISNULL(T2.ImproveEnough, 0)
762 + ISNULL(T2.PassEnough, 0)
763 )
764 AND
765 (
766 T2.ExcellentEnough IS NULL
767 OR T2.ExcellentEnough <= T1.e
768 )
769 AND
770 (
771 T2.GoodEnough IS NULL
772 OR T2.GoodEnough <= T1.g
773 )
774 AND
775 (
776 T2.PassEnough IS NULL
777 OR T2.PassEnough <= T1.p
778 )
779 AND
780 (
781 T2.ImproveEnough IS NULL
782 OR T2.ImproveEnough <= T1.i
783 )
784 AND
785 (
786 T2.WeakEnough IS NULL
787 OR T2.WeakEnough <= T1.Weak
788 )
789 OR
790 (
791 T2.GoodNeed = 1
792 AND T1.SL - T1.g = 0
793 )
794 ) --Good
795 UNION ALL
796 SELECT *
797 FROM #tmpResultASCM T1
798 JOIN
799 (
800 SELECT s.*
801 FROM DMSRangeHammerLineASCM s
802 JOIN dbo.DMSRangeHammerHeader b
803 ON s.RefNbr = b.RefNbr
804 AND s.DistributorID = b.DistributorID
805 WHERE b.Status = 'R'
806 AND s.RefNbr = @PeriodID
807 ) T2
808 ON T2.RefNbr = @PeriodID
809 AND
810 (
811 T2.ImproveEnough >= 1
812 AND ISNULL(T2.WeakEnough, 0) = 0
813 AND T2.ImproveNeed IS NULL
814 AND T1.p + T1.i + T1.e + T1.g = T1.SL
815 AND T1.Weak = 0
816 AND T1.i > 0
817 AND
818 (
819 T2.ExcellentEnough IS NULL
820 OR T2.ExcellentEnough <= T1.e
821 )
822 AND
823 (
824 T2.GoodEnough IS NULL
825 OR T2.GoodEnough <= T1.g
826 )
827 AND
828 (
829 T2.PassEnough IS NULL
830 OR T2.PassEnough <= T1.p
831 )
832 AND (T2.WeakEnough IS NULL
833 -- OR T2.WeakEnough <= T1.Weak
834 )
835 OR
836 (
837 T2.ImproveEnough = 1
838 AND T1.SL - T1.i = 0
839 )
840 )
841
842
843 -- ) --Improve)
844
845 ) b
846 ON a.ASMID = b.ASMID
847 AND a.RefNbr = b.RefNbr
848 ) b;
849 --return
850
851 ------RSRSCM------
852 --------------------------------------------------------------------------------------------------4: Lấy range của RSRSCM-----------------------------------------------------------------------------
853 ---------------------------- 4.1: đếm số SaleSup của RSRSCM quản lý, tổng SL ASCM của RSRSCM quản lý-----------------------------------
854 SELECT DISTINCT
855 B.RSMID,
856 B.i,
857 B.Weak,
858 B.g,
859 B.e,
860 B.p,
861 SL = i + B.Weak + g + e + p
862 INTO #tmpResultRSCM
863 FROM
864 (
865 SELECT B.RSMID,
866 SUM(B.i) i,
867 SUM(B.Weak) Weak,
868 SUM(B.g) g,
869 SUM(B.e) e,
870 SUM(B.p) p
871 FROM
872 (
873 SELECT a.RSMID,
874 COUNT(B.range) AS E,
875 '' G,
876 '' I,
877 '' Weak,
878 '' P
879 FROM [STAGING].THPDMSMasterData2.dbo.DMSBLSalesForce a
880 JOIN #RSASCM B
881 ON a.ASMID = B.EmployeeCD
882 WHERE a.BaselineDate = '2018-11-29 00:00:00.000'
883 AND B.range = 'E' --CONVERT(DATE,GETDATE()) )
884 GROUP BY a.RSMID
885 UNION ALL
886 SELECT a.RSMID,
887 '' AS E,
888 COUNT(B.range) G,
889 '' I,
890 '' Weak,
891 '' P
892 FROM [STAGING].THPDMSMasterData2.dbo.DMSBLSalesForce a
893 JOIN #RSASCM B
894 ON a.ASMID = B.EmployeeCD
895 WHERE a.BaselineDate = '2018-11-29 00:00:00.000'
896 AND B.range = 'G' --CONVERT(DATE,GETDATE()) )
897 GROUP BY a.RSMID
898 UNION ALL
899 SELECT a.RSMID,
900 '' AS E,
901 '' G,
902 COUNT(B.range) I,
903 '' Weak,
904 '' P
905 FROM [STAGING].THPDMSMasterData2.dbo.DMSBLSalesForce a
906 JOIN #RSASCM B
907 ON a.ASMID = B.EmployeeCD
908 WHERE a.BaselineDate = '2018-11-29 00:00:00.000'
909 AND B.range = 'I' --CONVERT(DATE,GETDATE()) )
910 GROUP BY a.RSMID
911 UNION ALL
912 SELECT a.RSMID,
913 '' AS E,
914 '' G,
915 '' I,
916 COUNT(B.range) Weak,
917 '' P
918 FROM [STAGING].THPDMSMasterData2.dbo.DMSBLSalesForce a
919 JOIN #RSASCM B
920 ON a.ASMID = B.EmployeeCD
921 WHERE a.BaselineDate = '2018-11-29 00:00:00.000'
922 AND B.range = 'Weak' --CONVERT(DATE,GETDATE()) )
923 GROUP BY a.RSMID
924 UNION ALL
925 SELECT a.RSMID,
926 '' AS E,
927 '' g,
928 '' I,
929 '' Weak,
930 COUNT(B.range) P
931 FROM [STAGING].THPDMSMasterData2.dbo.DMSBLSalesForce a
932 JOIN #RSASCM B
933 ON a.ASMID = B.EmployeeCD
934 WHERE a.BaselineDate = '2018-11-29 00:00:00.000'
935 AND B.range = 'P' --CONVERT(DATE,GETDATE()) )
936 GROUP BY a.RSMID
937 ) B
938 GROUP BY B.RSMID
939 ) B;
940 ----------------------------------------------------4.2: Lấy range của RSRSCM-------------------------------------------------------------------
941 SELECT DISTINCT
942 b.RefNbr,
943 'RSCM' Role,
944 b.RSMID AS EmployeeCD,
945 0 AS Salary,
946 range,
947 @Effdate AS Effdate,
948 @expiredate AS expiredate --CASE WHEN b.Weak>0 THEN 'Weak' ELSE b.Range END
949 INTO #RSRSCM
950 FROM
951 (
952 SELECT a.RefNbr,
953 a.RSMID,
954 CASE
955 WHEN b.range IS NULL
956 OR b.range = '' THEN
957 'Weak'
958 ELSE
959 b.range
960 END range
961 FROM
962 (
963 SELECT *
964 FROM #tmpResultRSCM T1
965 JOIN
966 (
967 SELECT s.*
968 FROM DMSRangeHammerLineRSCM s
969 JOIN dbo.DMSRangeHammerHeader b
970 ON s.RefNbr = b.RefNbr
971 AND s.DistributorID = b.DistributorID
972 WHERE b.Status = 'R'
973 AND s.RefNbr = @PeriodID
974 ) T2
975 ON T2.RefNbr = @PeriodID
976 ) a
977 LEFT JOIN
978 (
979 SELECT *
980 FROM #tmpResultRSCM T1
981 JOIN
982 (
983 SELECT s.*
984 FROM DMSRangeHammerLineRSCM s
985 JOIN dbo.DMSRangeHammerHeader b
986 ON s.RefNbr = b.RefNbr
987 AND s.DistributorID = b.DistributorID
988 WHERE b.Status = 'R'
989 AND s.RefNbr = @PeriodID
990 ) T2
991 ON T2.RefNbr = @PeriodID
992 AND
993 (
994 T2.PassNeed = 1
995 AND ISNULL(T1.Weak, 0) = 0
996 AND ISNULL(T1.i, 0) = 0
997 AND T1.SL - T1.g <> 0
998 AND T1.p > 1 --AND (T1.SL)-T1.P=0 -- ALL SM ÄỀU LÀ PASS
999 --AND (T1.SL) - (T1.g + T1.p + T1.e) <= (ISNULL(T2.WeakEnough, 0)
1000 -- + ISNULL(T2.ImproveEnough, 0)
1001 -- )
1002 -- and ((t1.sl-(t1.p+t1.e+t1.g)=0 and T2.PassEnough is null and t1.p >=1))
1003 AND
1004 (
1005 T2.ExcellentNeed IS NULL
1006 AND T2.GoodNeed IS NULL
1007 AND T2.PassNeed = 1
1008 -- OR T2.ExcellentEnough <= T1.e
1009 )
1010 AND
1011 (
1012 T2.ExcellentEnough IS NULL
1013 OR ISNULL(T2.ExcellentEnough, 0) <= T1.e
1014 )
1015 AND
1016 (
1017 T2.GoodEnough IS NULL
1018 OR ISNULL(T2.GoodEnough, 0) <= T1.g
1019 )
1020 AND
1021 (
1022 T2.PassEnough IS NULL
1023 OR ISNULL(T2.PassEnough, 0) >= 1
1024 )
1025 AND
1026 (
1027 T2.ImproveEnough IS NULL
1028 AND T2.ImproveNeed IS NULL
1029 -- OR T2.ImproveEnough <= T1.i
1030 )
1031 AND
1032 (
1033 T2.WeakEnough IS NULL
1034 AND T2.WeakNeed IS NULL
1035 -- OR T2.WeakEnough <= T1.Weak
1036 )
1037 OR
1038 (
1039 T2.PassNeed = 1
1040 AND T1.SL - T1.p = 0
1041 OR
1042 (
1043 T1.SL - (T1.p + T1.e + T1.g) = 0
1044 AND T2.PassNeed = 1
1045 AND T1.p > 1
1046 AND T1.i = 0
1047 AND T1.Weak = 0
1048 )
1049 )
1050 ) -- PASS
1051 UNION ALL
1052 SELECT *
1053 FROM #tmpResultRSCM T1
1054 JOIN
1055 (
1056 SELECT s.*
1057 FROM DMSRangeHammerLineRSCM s
1058 JOIN dbo.DMSRangeHammerHeader b
1059 ON s.RefNbr = b.RefNbr
1060 AND s.DistributorID = b.DistributorID
1061 WHERE b.Status = 'R'
1062 AND s.RefNbr = @PeriodID
1063 ) T2
1064 ON T2.RefNbr = @PeriodID
1065 AND
1066 (
1067 T2.ExcellentNeed = 1
1068 AND T1.i = 0
1069 AND T1.Weak = 0
1070 AND T1.p = 0
1071 AND (T1.SL) - T1.e <= (ISNULL(T2.WeakEnough, 0) + ISNULL(T2.ImproveEnough, 0)
1072 + ISNULL(T2.GoodEnough, 0) + ISNULL(T2.PassEnough, 0)
1073 )
1074 AND
1075 (
1076 T2.ExcellentEnough IS NULL
1077 OR T2.ExcellentEnough <= T1.e
1078 )
1079 AND
1080 (
1081 T2.GoodEnough IS NULL
1082 OR T2.GoodEnough <= T1.g
1083 )
1084 AND (T2.PassEnough IS NULL
1085 -- OR T2.PassEnough <= T1.p
1086 )
1087 AND (T2.ImproveEnough IS NULL
1088 -- OR T2.ImproveEnough <= T1.i
1089 )
1090 AND (T2.WeakEnough IS NULL
1091 -- OR T2.WeakEnough <= T1.Weak
1092 )
1093 OR
1094 (
1095 T2.ExcellentNeed = 1
1096 AND T1.SL - T1.e = 0
1097 )
1098 ) --Excellent
1099
1100
1101 UNION ALL
1102 SELECT *
1103 FROM #tmpResultRSCM T1
1104 JOIN
1105 (
1106 SELECT s.*
1107 FROM DMSRangeHammerLineRSCM s
1108 JOIN dbo.DMSRangeHammerHeader b
1109 ON s.RefNbr = b.RefNbr
1110 AND s.DistributorID = b.DistributorID
1111 WHERE b.Status = 'R'
1112 AND s.RefNbr = @PeriodID
1113 ) T2
1114 ON T2.RefNbr = @PeriodID
1115 AND
1116 (
1117 T2.GoodNeed = 1
1118 AND (T1.SL) - (T1.e + T1.g) <= (ISNULL(T2.WeakEnough, 0) + ISNULL(T2.ImproveEnough, 0)
1119 + ISNULL(T2.PassEnough, 0)
1120 )
1121 AND
1122 (
1123 T2.ExcellentEnough IS NULL
1124 OR T2.ExcellentEnough <= T1.e
1125 )
1126 AND
1127 (
1128 T2.GoodEnough IS NULL
1129 OR T2.GoodEnough <= T1.g
1130 )
1131 AND
1132 (
1133 T2.PassEnough IS NULL
1134 OR T2.PassEnough <= T1.p
1135 )
1136 AND
1137 (
1138 T2.ImproveEnough IS NULL
1139 OR T2.ImproveEnough <= T1.i
1140 )
1141 AND
1142 (
1143 T2.WeakEnough IS NULL
1144 OR T2.WeakEnough <= T1.Weak
1145 )
1146 OR
1147 (
1148 T2.GoodNeed = 1
1149 AND T1.SL - T1.g = 0
1150 )
1151 ) --Good
1152 UNION ALL
1153 SELECT *
1154 FROM #tmpResultRSCM T1
1155 JOIN
1156 (
1157 SELECT s.*
1158 FROM DMSRangeHammerLineRSCM s
1159 JOIN dbo.DMSRangeHammerHeader b
1160 ON s.RefNbr = b.RefNbr
1161 AND s.DistributorID = b.DistributorID
1162 WHERE b.Status = 'R'
1163 AND s.RefNbr = @PeriodID
1164 ) T2
1165 ON T2.RefNbr = @PeriodID
1166 AND
1167 (
1168 T2.ImproveEnough >= 1
1169 AND ISNULL(T2.WeakEnough, 0) = 0 --or ((T1.SL) - (T1.e + T1.g + T1.i + T1.p) =0)
1170 AND T2.ImproveNeed IS NULL
1171 AND T1.p + T1.i + T1.e + T1.g = T1.SL
1172 AND T1.Weak = 0
1173 AND T1.i > 0
1174 AND
1175 (
1176 T2.ExcellentEnough IS NULL
1177 OR T2.ExcellentEnough <= T1.e
1178 )
1179 AND
1180 (
1181 T2.GoodEnough IS NULL
1182 OR T2.GoodEnough <= T1.g
1183 )
1184 AND
1185 (
1186 T2.PassEnough IS NULL
1187 OR T2.PassEnough <= T1.p
1188 )
1189 AND (T2.WeakEnough IS NULL
1190 -- OR T2.WeakEnough <= T1.Weak
1191 )
1192 OR
1193 (
1194 T2.ImproveEnough = 1
1195 AND T1.SL - T1.i = 0
1196 )
1197 )
1198
1199
1200 -- ) --Improve)
1201
1202 ) b
1203 ON a.RSMID = b.RSMID
1204 AND a.RefNbr = b.RefNbr
1205 ) b;
1206
1207
1208 DELETE DMSRangeTeamSales
1209 WHERE Refnbr = @PeriodID;
1210 INSERT INTO DMSRangeTeamSales
1211 SELECT *
1212 FROM #RSSLM
1213 UNION ALL
1214 SELECT *
1215 FROM #RSASCM
1216 UNION ALL
1217 SELECT *
1218 FROM #RSSCS
1219 UNION ALL
1220 SELECT *
1221 FROM #RSRSCM;
1222
1223 DROP TABLE #RSSLM;
1224 DROP TABLE #RSASCM;
1225 DROP TABLE #RSSCS;
1226 DROP TABLE #RSRSCM;
1227
1228END;
1229GO