· 8 years ago · May 23, 2018, 08:12 AM
1IF EXISTS ( SELECT *
2 FROM sys.objects
3 WHERE type = 'P'
4 AND name = 'DMS_pp_GenResultEmployees' )
5 DROP PROCEDURE [dbo].[DMS_pp_GenResultEmployees];
6GO
7/*
82016-05-19: Apply dieu kien xep loai theo tung vung, khu vuc, NPP
9*/
10CREATE PROCEDURE [dbo].[DMS_pp_GenResultEmployees]
11 @CompanyID INT ,
12 @GenType CHAR(1) ,
13 @Year NVARCHAR(4) ,
14 @Week INT
15AS
16 BEGIN
17 IF @GenType = 'A'
18 BEGIN
19 -- Tinh ngay bat dau:
20 DECLARE @StartDate DATETIME;
21 DECLARE @EndDate DATETIME;
22 SELECT @EndDate = GETDATE()
23 - ( SELECT NumDayPrevGetBaseline + 1
24 FROM DMSHamConfigGen
25 WHERE ID = 1
26 AND GenType = 'A'
27 AND CompanyID = @CompanyID
28 );
29
30 SELECT @EndDate = @EndDate
31 - ( SELECT THPDMS2.dbo.[pp_GetNumberExcludeSunDay](@EndDate,
32 GETDATE())
33 )
34 - ( SELECT COUNT(HolidayID)
35 FROM THPDMS2.dbo.DMSHoliday
36 WHERE FromDate >= ( GETDATE()
37 - ( SELECT NumDayPrevGetBaseline
38 + 1
39 FROM DMSHamConfigGen
40 WHERE ID = 1
41 AND GenType = 'A'
42 AND CompanyID = @CompanyID
43 ) )
44 AND ToDate <= GETDATE()
45 );
46
47 SELECT @StartDate = @EndDate - ( SELECT NumDayPrevGetData
48 FROM DMSHamConfigGen
49 WHERE ID = 1
50 AND GenType = 'A'
51 AND CompanyID = @CompanyID
52 );
53 --SELECT @StartDate,@EndDate
54 -----------LUU DU LIEU KET QUA TAM
55 SELECT detail.RefNbr ,
56 header.EffectDate ,
57 detail.RefResult ,
58 detail.RegionID ,
59 detail.AreaID ,
60 detail.DistributorID ,
61 detail.PCMin ,
62 detail.PCMax ,
63 detail.PercentPhotoMin ,
64 detail.PercentPhotoMax ,
65 detail.PercentDisplayMin ,
66 detail.PercentDisplayMax ,
67 detail.NumHLMin ,
68 detail.NumHLMax ,
69 header.Type
70 INTO #temp
71 FROM DMSHamResultsDetailSM detail
72 LEFT JOIN ( SELECT TOP 1
73 RefNbr ,
74 EffectDate ,
75 Status ,
76 Type
77 FROM DMSHamResultsHeader
78 WHERE Status = 'R'
79 AND CAST(EffectDate AS DATE) <= CAST(GETDATE() AS DATE)
80 ORDER BY RefNbr DESC
81 ) header ON detail.RefNbr = header.RefNbr
82 WHERE header.Status = 'R'
83 AND CAST(header.EffectDate AS DATE) <= CAST(GETDATE() AS DATE)
84 AND detail.CompanyID = @CompanyID;
85
86 --------- tinh TB PC trong BL.
87 SELECT *
88 INTO #ExportAuto
89 FROM dbo.DMSHamResults
90 WHERE 1 = 0;
91-------------------------luu bang tam:
92 SELECT Vi.* ,
93 ROW_NUMBER() OVER ( PARTITION BY Vi.ss ORDER BY Vi.RefResult ASC ) AS SortID
94 INTO #RouteRSAuto
95 FROM ( SELECT v1.RefNbr ,
96 v1.Year ,
97 v1.Week ,
98 v1.RouteCD AS RouteID ,
99 v1.SS ,
100 ISNULL(v1.RefResult, 0) AS RefResult ,
101 v1.[AvgPC] ,
102 v1.AvgHinhChup AS [AvgPercentPhoto] ,
103 v1.AvgTrungBay AS [AvgPercentDisplay] ,
104 @StartDate AS StartDate ,
105 @EndDate AS EndDate ,
106 ISNULL(smResult.NumHLMin, 0) AS NumHLMin ,
107 ISNULL(smResult.NumHLMax, 0) AS NumHLMax ,
108 header.PercentSSMin AS PercentSSMin ,
109 header.PercentASMMin AS PercentASMMin ,
110 header.PercentRSMMin AS PercentRSMMin ,
111 v1.IsNew ,
112 GETDATE() AS CreatedDateTime ,
113 IsWWNext = CASE ( SELECT DISTINCT TOP 1
114 ValueCD
115 FROM THPDMS2.dbo.DMSSFAssignment sfa
116 LEFT JOIN dbo.DMSSalesOrgValue sog ON sog.CompanyID = sfa.CompanyID
117 AND sog.ValueID = sfa.SalesOrgID
118 LEFT JOIN dbo.DMSSalesForce sf ON sf.CompanyID = sfa.CompanyID
119 AND sf.EmployeeID = sfa.EmployeeID
120 WHERE sf.EmployeeCD = v1.SS
121 AND sfa.CompanyID = @CompanyID
122 AND sfa.IsBaseAssignment = 1
123 )
124 WHEN 6 THEN 0
125 WHEN 7 THEN 0
126 ELSE 0
127 END ,
128 IsValidate = CASE ( SELECT DISTINCT TOP 1
129 ValueCD
130 FROM THPDMS2.dbo.DMSSFAssignment sfa
131 LEFT JOIN dbo.DMSSalesOrgValue sog ON sog.CompanyID = sfa.CompanyID
132 AND sog.ValueID = sfa.SalesOrgID
133 LEFT JOIN dbo.DMSSalesForce sf ON sf.CompanyID = sfa.CompanyID
134 AND sf.EmployeeID = sfa.EmployeeID
135 WHERE sf.EmployeeCD = v1.SS
136 AND sfa.CompanyID = @CompanyID
137 AND sfa.IsBaseAssignment = 1
138 )
139 WHEN 6 THEN 0
140 WHEN 7 THEN 0
141 ELSE 0
142 END
143 FROM ( SELECT DISTINCT
144 sorg.RegionID ,
145 sorg.AreaID ,
146 --dis.DistributorID ,
147 distroute.RouteCD ,
148 --distroute.RouteName ,
149 sp.EmployeeCD AS SS ,
150 ISNULL(BLFCS.AvgPC, 0) AS AvgPC ,
151 ISNULL(display.AVGHinhChup, 0) AS AvgHinhChup ,
152 ISNULL(display2.AVGTrungBay, 0) AS AvgTrungBay ,
153 ( SELECT TOP 1
154 t.RefResult
155 FROM #temp t
156 WHERE (
157 --DK KEM
158 ( ( ISNULL(BLFCS.AvgPC,
159 0) >= t.PCMin
160 AND ISNULL(BLFCS.AvgPC,
161 0) < t.PCMax
162 AND t.RefResult = 'A'
163 AND ( ( t.[Type] = 'R'
164 AND t.RegionID = sorg.RegionID
165 )
166 OR ( t.[Type] = 'A'
167 AND t.RegionID = sorg.RegionID
168 )
169 OR ( t.[Type] = 'D'
170 AND t.RegionID = sorg.RegionID
171 )
172 OR ( t.[Type] = 'N'
173 AND 1 = 1
174 )
175 )
176 )
177 OR ( ISNULL(display.AVGHinhChup,
178 0) >= t.PercentPhotoMin
179 AND ISNULL(display.AVGHinhChup,
180 0) < t.PercentPhotoMax
181 AND t.RefResult = 'A'
182 AND ( ( t.[Type] = 'R'
183 AND t.RegionID = sorg.RegionID
184 )
185 OR ( t.[Type] = 'A'
186 AND t.RegionID = sorg.RegionID
187 )
188 OR ( t.[Type] = 'D'
189 AND t.RegionID = sorg.RegionID
190 )
191 OR ( t.[Type] = 'N'
192 AND 1 = 1
193 )
194 )
195 )
196 OR ( ISNULL(display2.AVGTrungBay,
197 0) >= t.PercentDisplayMin
198 AND ISNULL(display2.AVGTrungBay,
199 0) < t.PercentDisplayMax
200 AND t.RefResult = 'A'
201 AND ( ( t.[Type] = 'R'
202 AND t.RegionID = sorg.RegionID
203 )
204 OR ( t.[Type] = 'A'
205 AND t.RegionID = sorg.RegionID
206 )
207 OR ( t.[Type] = 'D'
208 AND t.RegionID = sorg.RegionID
209 )
210 OR ( t.[Type] = 'N'
211 AND 1 = 1
212 )
213 )
214 )
215 )
216 --dk TRUNG BINH
217 OR ( ( ISNULL(BLFCS.AvgPC,
218 0) >= t.PCMin
219 AND ISNULL(BLFCS.AvgPC,
220 0) < t.PCMax
221 AND t.RefResult = 'C'
222 AND ( ( t.[Type] = 'R'
223 AND t.RegionID = sorg.RegionID
224 )
225 OR ( t.[Type] = 'A'
226 AND t.RegionID = sorg.RegionID
227 )
228 OR ( t.[Type] = 'D'
229 AND t.RegionID = sorg.RegionID
230 )
231 OR ( t.[Type] = 'N'
232 AND 1 = 1
233 )
234 )
235 AND ISNULL(display.AVGHinhChup,
236 0) >= t.PercentPhotoMin
237 AND ISNULL(display2.AVGTrungBay,
238 0) >= t.PercentDisplayMin
239 )
240 OR ( ISNULL(BLFCS.AvgPC,
241 0) >= t.PCMax
242 AND t.RefResult = 'C'
243 AND ( ( t.[Type] = 'R'
244 AND t.RegionID = sorg.RegionID
245 )
246 OR ( t.[Type] = 'A'
247 AND t.RegionID = sorg.RegionID
248 )
249 OR ( t.[Type] = 'D'
250 AND t.RegionID = sorg.RegionID
251 )
252 OR ( t.[Type] = 'N'
253 AND 1 = 1
254 )
255 )
256 AND ISNULL(display.AVGHinhChup,
257 0) >= t.PercentPhotoMin
258 AND ISNULL(display2.AVGTrungBay,
259 0) >= t.PercentDisplayMin
260 AND ISNULL(display2.AVGTrungBay,
261 0) < t.PercentDisplayMax
262 )
263 )
264 OR ( ISNULL(BLFCS.AvgPC,
265 0) >= t.PCMin
266 AND t.RefResult = 'D'
267 AND ( ( t.[Type] = 'R'
268 AND t.RegionID = sorg.RegionID
269 )
270 OR ( t.[Type] = 'A'
271 AND t.RegionID = sorg.RegionID
272 )
273 OR ( t.[Type] = 'D'
274 AND t.RegionID = sorg.RegionID
275 )
276 OR ( t.[Type] = 'N'
277 AND 1 = 1
278 )
279 )
280 AND ISNULL(display.AVGHinhChup,
281 0) >= t.PercentPhotoMin
282 AND ISNULL(display2.AVGTrungBay,
283 0) >= t.PercentDisplayMin
284 )
285 )
286 ) AS RefResult ,
287 ( SELECT TOP 1
288 A.RefNbr
289 FROM #temp A
290 ) AS RefNbr ,
291 ( CASE WHEN DATEPART(wk,
292 GETDATE()) >= 52
293 THEN YEAR(GETDATE())
294 + 1
295 ELSE YEAR(GETDATE())
296 END ) AS [Year] ,
297 ( CASE WHEN DATEPART(wk,
298 GETDATE()) >= 52
299 THEN 1
300 ELSE DATEPART(wk,
301 GETDATE()) + 1
302 END ) AS [Week] ,
303 CASE WHEN sp.StartDate IS NOT NULL
304 THEN CASE
305 WHEN DATEDIFF(DAY,
306 sp.StartDate,
307 GETDATE()) > ISNULL(( SELECT DISTINCT
308 Number
309 FROM
310 THPDMS2.dbo.[DMSHamerViewGetSetting]
311 WHERE
312 ID = 'SMM'
313 ), 0) THEN 0
314 ELSE 1
315 END
316 ELSE 0
317 END AS IsNew
318 FROM THPDMS2.dbo.DMSRouteSetting AS distroute
319 LEFT JOIN dbo.DMSViewTHPSalesOrg sorg ON sorg.CompanyID = distroute.CompanyID
320 AND sorg.AreaID = distroute.SalesAreaID
321 AND distroute.Active=1
322 -- INNER JOIN THPDMS2.dbo.DMSViewDistributorLocation
323 -- AS dis ON dis.CompanyID = distroute.CompanyID
324 -- AND dis.DistributorID = distroute.DistributorID
325 -- AND dis.IsActive = 1
326 -- AND distroute.SalesAreaID = dis.AreaID
327 --AND distroute.CompanyID = @CompanyID
328 INNER JOIN THPDMS2.dbo.DMSSalesForce sp ON sp.EmployeeID = distroute.SalesForceID
329 AND sp.Active = 1
330 AND sp.CompanyID = distroute.CompanyID
331 --INNER JOIN thpdms2.dbo.DMSHORouteMaster rm ON rm.RouteCD = distroute.RouteCD
332 -- AND rm.Active = 1
333 LEFT JOIN ( SELECT
334 RouteID ,
335 AVG(PC) AS AvgPC
336 FROM
337 THPBaseline2..DMSBLFCS
338 WHERE
339 TranDate >= CAST(@StartDate AS DATE)
340 AND TranDate <= CAST(@EndDate AS DATE)
341 AND PC > 0
342 GROUP BY RouteID
343 ) AS BLFCS ON distroute.RouteCD = BLFCS.RouteID
344 LEFT JOIN ( SELECT
345 RouteID ,
346 SUM(SMLastPass)
347 * 100 / SUM(MCP) AS AVGHinhChup
348 FROM
349 ( SELECT
350 a.RouteID ,
351 a.ImageLastCount ,
352 a.ImageLastPass ,
353 a.SMLastPass ,
354 MCP_Daily = 33 ,
355 a.VisitDate ,
356 b.MCP ,
357 b.OutletVisited
358 FROM
359 THPVisibility2.dbo.BLImageLast a
360 LEFT JOIN THPVisibility2.[dbo].[BLSalesKPI] b ON a.RouteID = b.RouteID
361 AND a.SalesmanID = b.SalesmanID
362 AND a.VisitDate = b.VisitDate
363 AND a.DisplayID = b.DisplayID
364 WHERE
365 a.DisplayID = 1
366 AND a.VisitDate >= CAST(@StartDate AS DATE)
367 AND a.VisitDate <= CAST(@EndDate AS DATE)
368 AND b.OutletVisited > 0
369 ) TB
370 GROUP BY RouteID
371 ) AS display ON distroute.RouteCD = display.RouteID
372 LEFT JOIN ( SELECT
373 RouteID ,
374 SUM(ImageLastPass)
375 * 100
376 / SUM(MCP_Daily
377 * 0.95) AS AVGTrungBay
378 FROM
379 ( SELECT
380 a.RouteID ,
381 a.ImageLastCount ,
382 a.ImageLastPass ,
383 a.SMLastPass ,
384 MCP_Daily = 33 ,
385 a.VisitDate ,
386 b.MCP ,
387 b.OutletVisited
388 FROM
389 THPVisibility2.[dbo].[BLImageLast] a
390 LEFT JOIN THPVisibility2.[dbo].[BLSalesKPI] b ON a.RouteID = b.RouteID
391 AND a.SalesmanID = b.SalesmanID
392 AND a.VisitDate = b.VisitDate
393 AND a.DisplayID = b.DisplayID
394 WHERE
395 a.DisplayID = 1
396 AND a.VisitDate >= CAST(@StartDate AS DATE)
397 AND a.VisitDate <= CAST(@EndDate AS DATE)
398 AND b.OutletVisited > 0
399 ) TB
400 GROUP BY RouteID
401 ) AS display2 ON distroute.RouteCD = display2.RouteID
402 ) AS v1
403 LEFT JOIN DMSHamResultsHeader AS header ON header.RefNbr = v1.RefNbr
404 AND header.CompanyID = @CompanyID
405 LEFT JOIN DMSHamResultsDetailSM AS smResult ON v1.RefResult = smResult.RefResult
406 AND v1.RefNbr = smResult.RefNbr
407 AND smResult.CompanyID = header.CompanyID
408 AND (( header.[Type] = 'R'
409 AND v1.RegionID = smResult.RegionID
410 )
411 OR ( header.[Type] = 'A'
412 AND v1.RegionID = smResult.RegionID
413 )
414 OR ( header.[Type] = 'D'
415 AND v1.RegionID = smResult.RegionID
416 )
417 OR ( header.[Type] = 'N'
418 AND 1 = 1
419 ))
420 WHERE v1.RefNbr IS NOT NULL
421 AND v1.Year = YEAR(GETDATE())
422 AND v1.Week = ( DATEPART(wk, GETDATE())
423 + 1 ) --and rm.Active = 1
424 ) AS Vi;
425----- SELECT UPDATED OR NOT INSERTED FROM ACU TO Hammer
426
427 INSERT INTO #ExportAuto
428 (CompanyID,
429 [RefNbr] ,
430 [Year] ,
431 [Week] ,
432 [RouteID] ,
433 SS ,
434 [RefResult] ,
435 [AvgPC] ,
436 [AvgPercentPhoto] ,
437 [AvgPercentDisplay] ,
438 StartDate ,
439 EndDate ,
440 NumHLMin ,
441 NumHLMax ,
442 PercentSSMin ,
443 PercentASMMin ,
444 PercentRSMMin ,
445 IsNew ,
446 CreatedDateTime ,
447 IsWWNext ,
448 IsValidate ,
449 SortID
450 )
451 ( SELECT @CompanyID,*
452 FROM #RouteRSAuto
453 );
454 WITH TARGET
455 AS ( SELECT CompanyID,
456 [RefNbr] ,
457 [Year] ,
458 [Week] ,
459 [RouteID] ,
460 SS ,
461 [RefResult] ,
462 [AvgPC] ,
463 [AvgPercentPhoto] ,
464 [AvgPercentDisplay] ,
465 StartDate ,
466 EndDate ,
467 NumHLMin ,
468 NumHLMax ,
469 PercentSSMin ,
470 PercentASMMin ,
471 PercentRSMMin ,
472 IsNew ,
473 CreatedDateTime ,
474 IsWWNext ,
475 IsValidate ,
476 SortID
477 FROM [dbo].DMSHamResults
478 WHERE Year = YEAR(GETDATE())
479 AND Week = ( DATEPART(wk, GETDATE())
480 + 1 )
481 AND CompanyID = @CompanyID
482 )
483 MERGE INTO TARGET
484 USING #ExportAuto AS SOURCE
485 ON ( TARGET.RefNbr = ISNULL(SOURCE.RefNbr, '')
486 AND TARGET.[Year] = ISNULL(SOURCE.[Year], '')
487 AND TARGET.[Week] = ISNULL(SOURCE.[Week], 0)
488 AND TARGET.CompanyID = SOURCE.CompanyID
489 AND TARGET.RouteID = ISNULL(SOURCE.RouteID, '')
490 AND TARGET.SS = SOURCE.SS
491 )
492
493--When records are matched, update
494--the records if there is any change
495 WHEN MATCHED AND
496 TARGET.RefResult <> SOURCE.RefResult
497 OR TARGET.AvgPC <> SOURCE.AvgPC
498 OR TARGET.AvgPercentPhoto <> SOURCE.AvgPercentPhoto
499 OR TARGET.AvgPercentDisplay <> SOURCE.AvgPercentDisplay
500 OR TARGET.StartDate <> SOURCE.StartDate
501 OR TARGET.EndDate <> SOURCE.EndDate
502 OR ISNULL(TARGET.SS, '') <> ISNULL(SOURCE.SS, '')
503 OR ISNULL(TARGET.NumHLMin, 0) <> ISNULL(SOURCE.NumHLMin,
504 0)
505 OR ISNULL(TARGET.NumHLMax, 0) <> ISNULL(SOURCE.NumHLMax,
506 0)
507 OR ISNULL(TARGET.PercentSSMin, 0) <> ISNULL(SOURCE.PercentSSMin,
508 0)
509 OR ISNULL(TARGET.PercentASMMin, 0) <> ISNULL(SOURCE.PercentASMMin,
510 0)
511 OR ISNULL(TARGET.PercentRSMMin, 0) <> ISNULL(SOURCE.PercentRSMMin,
512 0)
513 OR ISNULL(TARGET.IsNew, 0) <> ISNULL(SOURCE.IsNew, 0)
514 OR TARGET.CreatedDateTime <> SOURCE.CreatedDateTime
515 OR ISNULL(TARGET.IsWWNext, 0) <> ISNULL(SOURCE.IsWWNext,
516 0)
517 OR ISNULL(TARGET.IsValidate, 0) <> ISNULL(SOURCE.IsValidate,
518 0)
519 OR ISNULL(TARGET.SortID, 0) <> ISNULL(SOURCE.SortID, 0)
520 THEN
521 UPDATE SET TARGET.RefResult = SOURCE.RefResult ,
522 TARGET.AvgPC = SOURCE.AvgPC ,
523 TARGET.AvgPercentPhoto = SOURCE.AvgPercentPhoto ,
524 TARGET.AvgPercentDisplay = SOURCE.AvgPercentDisplay ,
525 TARGET.StartDate = SOURCE.StartDate ,
526 TARGET.EndDate = SOURCE.EndDate ,
527 TARGET.SS = SOURCE.SS ,
528 TARGET.NumHLMin = SOURCE.NumHLMin ,
529 TARGET.NumHLMax = SOURCE.NumHLMax ,
530 TARGET.PercentSSMin = SOURCE.PercentSSMin ,
531 TARGET.PercentASMMin = SOURCE.PercentASMMin ,
532 TARGET.PercentRSMMin = SOURCE.PercentRSMMin ,
533 TARGET.IsNew = SOURCE.IsNew ,
534 TARGET.CreatedDateTime = SOURCE.CreatedDateTime ,
535 TARGET.IsWWNext = SOURCE.IsWWNext ,
536 TARGET.IsValidate = SOURCE.IsValidate ,
537 TARGET.SortID = SOURCE.SortID
538--When no records are matched, insert
539--the incoming records from source
540--table to target table
541 WHEN NOT MATCHED BY TARGET THEN
542 INSERT (CompanyID,
543 [RefNbr] ,
544 [Year] ,
545 [Week] ,
546 [RouteID] ,
547 SS ,
548 [RefResult] ,
549 [AvgPC] ,
550 [AvgPercentPhoto] ,
551 [AvgPercentDisplay] ,
552 StartDate ,
553 EndDate ,
554 NumHLMin ,
555 NumHLMax ,
556 PercentSSMin ,
557 PercentASMMin ,
558 PercentRSMMin ,
559 IsNew ,
560 CreatedDateTime ,
561 IsWWNext ,
562 IsValidate ,
563 SortID
564 )
565 VALUES (SOURCE.CompanyID,
566 SOURCE.[RefNbr] ,
567 SOURCE.[Year] ,
568 SOURCE.[Week] ,
569 SOURCE.[RouteID] ,
570 SOURCE.SS ,
571 SOURCE.[RefResult] ,
572 SOURCE.[AvgPC] ,
573 SOURCE.[AvgPercentPhoto] ,
574 SOURCE.[AvgPercentDisplay] ,
575 SOURCE.StartDate ,
576 SOURCE.EndDate ,
577 SOURCE.NumHLMin ,
578 SOURCE.NumHLMax ,
579 SOURCE.PercentSSMin ,
580 SOURCE.PercentASMMin ,
581 SOURCE.PercentRSMMin ,
582 SOURCE.IsNew ,
583 SOURCE.CreatedDateTime ,
584 SOURCE.IsWWNext ,
585 SOURCE.IsValidate ,
586 SOURCE.SortID
587 ) --When there is a row that exists in target table and
588--same record does not exist in source table
589--then delete this record from target table
590 ;
591 SELECT @@ROWCOUNT;
592 DROP TABLE #ExportAuto;
593 DROP TABLE #temp;
594----------------------------Ss Auto
595--------LUU DU LIEU KET QUA TAM
596 SELECT detail.RefNbr ,
597 header.EffectDate ,
598 detail.RefResult ,
599 detail.RegionID ,
600 detail.AreaID ,
601 detail.DistributorID ,
602 detail.PercentSMLowMin ,
603 detail.PercentSMLowMax ,
604 detail.PercentSMStardardMin ,
605 detail.PercentSMStardardMax ,
606 detail.PercentSMGoodMin ,
607 detail.PercentSMGoodMax ,
608 detail.NumHLMin ,
609 detail.NumHLMax ,
610 header.Type
611 INTO #tempSSAuto
612 FROM DMSHamResultsDetailSS detail
613 LEFT JOIN ( SELECT TOP 1
614 RefNbr ,
615 EffectDate ,
616 Status ,
617 Type
618 FROM DMSHamResultsHeader
619 WHERE Status = 'R'
620 AND CAST(EffectDate AS DATE) <= CAST(GETDATE() AS DATE)
621 ORDER BY RefNbr DESC
622 ) header ON detail.RefNbr = header.RefNbr
623 WHERE Status = 'R'
624 AND CAST(EffectDate AS DATE) <= CAST(GETDATE() AS DATE)
625 AND detail.CompanyID =@CompanyID;
626
627 SELECT *
628 INTO #ExportSSAuto
629 FROM dbo.DMSHamResultsSS
630 WHERE 1 = 0;
631---------------------luu tam
632 SELECT ViewSS.* ,
633 ROW_NUMBER() OVER ( PARTITION BY ViewSS.ASM ORDER BY ViewSS.RefResult ASC ) AS SortID
634 INTO #SSRSAuto
635 FROM ( SELECT rs.[RefNbr] ,
636 rs.[Year] ,
637 rs.[Week] ,
638 rs.[SS] ,
639 rs.ASM ,
640 rs.[SumRoute] ,
641 rs.[Kem] AS SMKem ,
642 rs.AvgKem AS [AvgSMKem] ,
643 rs.TB AS [SMTB] ,
644 rs.AvgTB AS [AvgSMTB] ,
645 rs.Tot AS [SMTot] ,
646 rs.AvgTot AS [AvgSMTot] ,
647 ViewSS.NumHLMin AS [NumHLMin] ,
648 ViewSS.NumHLMax AS [NumHLMax] ,
649 rs.RefResult ,
650 CASE WHEN sf.StartDate IS NOT NULL
651 THEN CASE WHEN DATEDIFF(DAY,
652 sf.StartDate,
653 GETDATE()) > ISNULL(( SELECT DISTINCT
654 Number
655 FROM
656 THPDMS2.dbo.[DMSHamerViewGetSetting]
657 WHERE
658 ID = 'SMM'
659 ), 0) THEN 0
660 ELSE 1
661 END
662 ELSE 0
663 END AS IsNew ,
664 IsWWNext = CASE ( SELECT DISTINCT TOP 1
665 ValueCD
666 FROM THPDMS2.dbo.DMSSFAssignment sfa
667 LEFT JOIN dbo.DMSSalesOrgValue sog ON sog.CompanyID = sfa.CompanyID
668 AND sog.ValueID = sfa.SalesOrgID
669 LEFT JOIN dbo.DMSSalesForce sf ON sf.CompanyID = sfa.CompanyID
670 AND sf.EmployeeID = sfa.EmployeeID
671 WHERE sf.EmployeeCD = rs.SS
672 AND sfa.CompanyID = @CompanyID
673 AND sfa.IsBaseAssignment = 1
674 )
675 WHEN 6 THEN 0
676 WHEN 7 THEN 0
677 ELSE 0
678 END ,
679 IsValidate = CASE ( SELECT DISTINCT TOP 1
680 ValueCD
681 FROM THPDMS2.dbo.DMSSFAssignment sfa
682 LEFT JOIN dbo.DMSSalesOrgValue sog ON sog.CompanyID = sfa.CompanyID
683 AND sog.ValueID = sfa.SalesOrgID
684 LEFT JOIN dbo.DMSSalesForce sf ON sf.CompanyID = sfa.CompanyID
685 AND sf.EmployeeID = sfa.EmployeeID
686 WHERE sf.EmployeeCD = rs.SS
687 AND sfa.CompanyID = @CompanyID
688 AND sfa.IsBaseAssignment = 1
689 )
690 WHEN 6 THEN 0
691 WHEN 7 THEN 0
692 ELSE 0
693 END ,
694 PercentSSMin = ( SELECT DISTINCT TOP 1
695 PercentSSMin
696 FROM DMSHamResultsHeader
697 WHERE RefNbr = rs.[RefNbr]
698 )
699 FROM ( SELECT v1.* ,
700 getASM.* ,
701 ISNULL(( SELECT TOP 1
702 t.RefResult
703 FROM #tempSSAuto t
704 WHERE
705 (
706 --DK KEM
707 ( ( ISNULL(v1.AvgKem,
708 0) >= t.PercentSMLowMin
709 AND ISNULL(v1.AvgKem,
710 0) < t.PercentSMLowMax
711 AND t.RefResult = 'A'
712 AND ( ( t.[Type] = 'R'
713 AND t.RegionID = sfa.RegionID
714 )
715 OR ( t.[Type] = 'A'
716 AND t.RegionID = sfa.RegionID
717 )
718 OR ( t.[Type] = 'D'
719 AND t.RegionID = sfa.RegionID
720 )
721 OR ( t.[Type] = 'N'
722 AND 1 = 1
723 )
724 )
725 )
726 AND ( ISNULL(v1.AvgTB,
727 0) >= t.PercentSMStardardMin
728 AND ISNULL(v1.AvgTB,
729 0) < t.PercentSMStardardMax
730 AND t.RefResult = 'A'
731 AND ( ( t.[Type] = 'R'
732 AND t.RegionID = sfa.RegionID
733 )
734 OR ( t.[Type] = 'A'
735 AND t.RegionID = sfa.RegionID
736 )
737 OR ( t.[Type] = 'D'
738 AND t.RegionID = sfa.RegionID
739 )
740 OR ( t.[Type] = 'N'
741 AND 1 = 1
742 )
743 )
744 )
745 AND ( ISNULL(v1.AvgTot,
746 0) >= t.PercentSMGoodMin
747 AND ISNULL(v1.AvgTot,
748 0) < t.PercentSMGoodMax
749 AND t.RefResult = 'A'
750 AND ( ( t.[Type] = 'R'
751 AND t.RegionID = sfa.RegionID
752 )
753 OR ( t.[Type] = 'A'
754 AND t.RegionID = sfa.RegionID
755 )
756 OR ( t.[Type] = 'D'
757 AND t.RegionID = sfa.RegionID
758 )
759 OR ( t.[Type] = 'N'
760 AND 1 = 1
761 )
762 )
763 )
764 )
765 --dk TRUNG BINH
766 OR ( ( ISNULL(v1.AvgKem,
767 0) >= t.PercentSMLowMin
768 AND ISNULL(v1.AvgKem,
769 0) < t.PercentSMLowMax
770 AND t.RefResult = 'C'
771 AND ( ( t.[Type] = 'R'
772 AND t.RegionID = sfa.RegionID
773 )
774 OR ( t.[Type] = 'A'
775 AND t.RegionID = sfa.RegionID
776 )
777 OR ( t.[Type] = 'D'
778 AND t.RegionID = sfa.RegionID
779 )
780 OR ( t.[Type] = 'N'
781 AND 1 = 1
782 )
783 )
784 )
785 AND ( ISNULL(v1.AvgTB,
786 0) >= t.PercentSMStardardMin
787 AND ISNULL(v1.AvgTB,
788 0) < t.PercentSMStardardMax
789 AND t.RefResult = 'C'
790 AND ( ( t.[Type] = 'R'
791 AND t.RegionID = sfa.RegionID
792 )
793 OR ( t.[Type] = 'A'
794 AND t.RegionID = sfa.RegionID
795 )
796 OR ( t.[Type] = 'D'
797 AND t.RegionID = sfa.RegionID
798 )
799 OR ( t.[Type] = 'N'
800 AND 1 = 1
801 )
802 )
803 )
804 AND ( ISNULL(v1.AvgTot,
805 0) >= t.PercentSMGoodMin
806 AND ISNULL(v1.AvgTot,
807 0) < t.PercentSMGoodMax
808 AND t.RefResult = 'C'
809 AND ( ( t.[Type] = 'R'
810 AND t.RegionID = sfa.RegionID
811 )
812 OR ( t.[Type] = 'A'
813 AND t.RegionID = sfa.RegionID
814 )
815 OR ( t.[Type] = 'D'
816 AND t.RegionID = sfa.RegionID
817 )
818 OR ( t.[Type] = 'N'
819 AND 1 = 1
820 )
821 )
822 )
823 )
824 OR ( ( ISNULL(v1.AvgKem,
825 0) >= t.PercentSMLowMin
826 AND ISNULL(v1.AvgKem,
827 0) < t.PercentSMLowMax
828 AND t.RefResult = 'D'
829 AND ( ( t.[Type] = 'R'
830 AND t.RegionID = sfa.RegionID
831 )
832 OR ( t.[Type] = 'A'
833 AND t.RegionID = sfa.RegionID
834 )
835 OR ( t.[Type] = 'D'
836 AND t.RegionID = sfa.RegionID
837 )
838 OR ( t.[Type] = 'N'
839 AND 1 = 1
840 )
841 )
842 )
843 AND ( ISNULL(v1.AvgTB,
844 0) >= t.PercentSMStardardMin
845 AND ISNULL(v1.AvgTB,
846 0) < t.PercentSMStardardMax
847 AND t.RefResult = 'D'
848 AND ( ( t.[Type] = 'R'
849 AND t.RegionID = sfa.RegionID
850 )
851 OR ( t.[Type] = 'A'
852 AND t.RegionID = sfa.RegionID
853 )
854 OR ( t.[Type] = 'D'
855 AND t.RegionID = sfa.RegionID
856 )
857 OR ( t.[Type] = 'N'
858 AND 1 = 1
859 )
860 )
861 )
862 AND ( ISNULL(v1.AvgTot,
863 0) >= t.PercentSMGoodMin
864 AND ISNULL(v1.AvgTot,
865 0) < t.PercentSMGoodMax
866 AND t.RefResult = 'D'
867 AND ( ( t.[Type] = 'R'
868 AND t.RegionID = sfa.RegionID
869 )
870 OR ( t.[Type] = 'A'
871 AND t.RegionID = sfa.RegionID
872 )
873 OR ( t.[Type] = 'D'
874 AND t.RegionID = sfa.RegionID
875 )
876 OR ( t.[Type] = 'N'
877 AND 1 = 1
878 )
879 )
880 )
881 )
882 )
883 ), 0) AS RefResult
884 FROM ( SELECT v1.RefNbr ,
885 v1.SS ,
886 v1.Year ,
887 v1.Week ,
888 ISNULL(v1.SumRoute,
889 0) AS SumRoute ,
890 ISNULL(v2.CountRouteKem,
891 0) AS Kem ,
892 ISNULL(( v2.CountRouteKem
893 * 100 )
894 / v1.SumRoute, 0) AS AvgKem ,
895 ISNULL(v3.CountRouteTB,
896 0) AS TB ,
897 ISNULL(( v3.CountRouteTB
898 * 100 )
899 / v1.SumRoute, 0) AS AvgTB ,
900 ISNULL(v4.CountRouteTot,
901 0) AS Tot ,
902 ISNULL(( v4.CountRouteTot
903 * 100 )
904 / v1.SumRoute, 0) AS AvgTot
905 FROM ( SELECT
906 temrs.RefNbr ,
907 temrs.Year ,
908 temrs.Week ,
909 temrs.SS ,
910 ISNULL(tsum.SumRoute,
911 0) AS SumRoute
912 FROM
913 ( SELECT DISTINCT
914 rs.RefNbr ,
915 rs.Year ,
916 rs.Week ,
917 rs.SS
918 FROM
919 DMSHamResults AS rs WHERE rs.CompanyID=@CompanyID
920 ) AS temrs
921 LEFT JOIN ( SELECT
922 rs.RefNbr ,
923 rs.Year ,
924 rs.Week ,
925 rs.SS ,
926 COUNT(rs.RouteID) AS SumRoute
927 FROM
928 DMSHamResults AS rs WHERE rs.CompanyID=@CompanyID
929 GROUP BY rs.RefNbr ,
930 rs.Year ,
931 rs.Week ,
932 rs.SS
933 ) AS tsum ON temrs.RefNbr = tsum.RefNbr
934 AND temrs.Year = tsum.[Year]
935 AND temrs.Week = tsum.Week
936 AND temrs.SS = tsum.SS
937 ) AS v1
938 LEFT JOIN ( SELECT
939 rsk.RefNbr ,
940 rsk.Year ,
941 rsk.Week ,
942 rsk.SS ,
943 COUNT(rsk.RouteID) AS CountRouteKem
944 FROM
945 DMSHamResults AS rsk
946 WHERE
947 rsk.RefResult = 'A'
948 AND rsk.IsNew = 0
949 AND rsk.AvgPC > 0
950 AND rsk.CompanyID =@CompanyID
951 GROUP BY rsk.RefNbr ,
952 rsk.Year ,
953 rsk.Week ,
954 rsk.SS
955 ) AS v2 ON v1.SS = v2.SS
956 AND v1.RefNbr = v2.RefNbr
957 AND v1.Week = v2.Week
958 AND v1.Year = v2.Year
959 LEFT JOIN ( SELECT
960 rsk.RefNbr ,
961 rsk.Year ,
962 rsk.Week ,
963 rsk.SS ,
964 COUNT(rsk.RouteID) AS CountRouteTB
965 FROM
966 DMSHamResults AS rsk
967 WHERE
968 rsk.RefResult = 'C'
969 AND rsk.IsNew = 0
970 AND rsk.AvgPC > 0
971 AND rsk.CompanyID =@CompanyID
972 GROUP BY rsk.RefNbr ,
973 rsk.Year ,
974 rsk.Week ,
975 rsk.SS
976 ) AS v3 ON v1.SS = v3.SS
977 AND v1.RefNbr = v3.RefNbr
978 AND v1.Week = v3.Week
979 AND v1.Year = v3.Year
980 LEFT JOIN ( SELECT
981 rsk.RefNbr ,
982 rsk.Year ,
983 rsk.Week ,
984 rsk.SS ,
985 COUNT(rsk.RouteID) AS CountRouteTot
986 FROM
987 DMSHamResults AS rsk
988 WHERE
989 rsk.RefResult = 'D'
990 AND rsk.IsNew = 0
991 AND rsk.CompanyID =@CompanyID
992 AND rsk.AvgPC > 0
993 GROUP BY rsk.RefNbr ,
994 rsk.Year ,
995 rsk.Week ,
996 rsk.SS
997 ) AS v4 ON v1.SS = v4.SS
998 AND v1.RefNbr = v4.RefNbr
999 AND v1.Week = v4.Week
1000 AND v1.Year = v4.Year
1001 ) AS v1
1002
1003 LEFT JOIN THPDMS2.dbo.[DMSHamerViewSSASM]
1004 AS getASM ON v1.SS = getASM.EmployeeID
1005 AND getASM.companyID = @CompanyID
1006 LEFT JOIN ( SELECT DISTINCT
1007 sf.EmployeeCD ,
1008 RegionID = sa.SalesOrgID
1009 FROM
1010 dbo.DMSSFAssignment sa
1011 LEFT JOIN dbo.DMSSalesForce sf ON sf.CompanyID = sa.companyid
1012 AND sf.EmployeeID = sa.EmployeeID
1013 LEFT JOIN dbo.DMSSFHierachy sh ON sh.CompanyID = sf.CompanyID
1014 AND sh.SFHierachyID = sf.CompanyID
1015 WHERE sh.TerritoryType='R'
1016 AND sa.CompanyID =@CompanyID
1017 ) sfa ON sfa.EmployeeCD = v1.SS
1018 ) AS rs
1019 LEFT JOIN dbo.DMSSalesForce sf ON rs.SS = sf.EmployeeCD
1020 AND sf.CompanyID =@CompanyID
1021 LEFT JOIN DMSHamResultsHeader AS header ON header.RefNbr = rs.RefNbr
1022 AND header.CompanyID =@CompanyID
1023 LEFT JOIN DMSHamResultsDetailSS AS ViewSS ON rs.RefResult = ViewSS.RefResult
1024 AND rs.RefNbr = ViewSS.RefNbr
1025 AND ViewSS.CompanyID =@CompanyID
1026 AND (( header.[Type] = 'R'
1027 AND rs.RegionID = ViewSS.RegionID
1028 )
1029 OR ( header.[Type] = 'A'
1030 AND rs.RegionID = ViewSS.RegionID
1031 )
1032 OR ( header.[Type] = 'D'
1033 AND rs.RegionID = ViewSS.RegionID
1034 )
1035 OR ( header.[Type] = 'N'
1036 AND 1 = 1
1037 ))
1038 WHERE ( rs.SS IS NOT NULL
1039 OR rs.SS = 0
1040 )
1041 AND rs.Year = YEAR(GETDATE())
1042 AND rs.Week = ( DATEPART(wk, GETDATE())
1043 + 1 )
1044 AND ASM IS NOT NULL
1045 ) AS ViewSS;
1046----- SELECT UPDATED OR NOT INSERTED FROM ACU TO Hammer
1047 INSERT INTO #ExportSSAuto
1048 (CompanyID,
1049 [RefNbr] ,
1050 [Year] ,
1051 [Week] ,
1052 [SS] ,
1053 [ASM] ,
1054 [SumRoute] ,
1055 [SMKem] ,
1056 [AvgSMKem] ,
1057 [SMTB] ,
1058 [AvgSMTB] ,
1059 [SMTot] ,
1060 [AvgSMTot] ,
1061 [NumHLMin] ,
1062 [NumHLMax] ,
1063 [RefResult] ,
1064 IsNew ,
1065 IsWWNext ,
1066 IsValidate ,
1067 PercentSSMin ,
1068 SortID
1069
1070 )
1071 ( SELECT @CompanyID, *
1072 FROM #SSRSAuto
1073 );
1074 WITH TARGET
1075 AS ( SELECT CompanyID,
1076 [RefNbr] ,
1077 [Year] ,
1078 [Week] ,
1079 [SS] ,
1080 [ASM] ,
1081 [SumRoute] ,
1082 [SMKem] ,
1083 [AvgSMKem] ,
1084 [SMTB] ,
1085 [AvgSMTB] ,
1086 [SMTot] ,
1087 [AvgSMTot] ,
1088 [NumHLMin] ,
1089 [NumHLMax] ,
1090 [RefResult] ,
1091 IsNew ,
1092 IsWWNext ,
1093 IsValidate ,
1094 PercentSSMin ,
1095 SortID
1096 FROM [dbo].DMSHamResultsSS
1097 WHERE Year = YEAR(GETDATE())
1098 AND Week = ( DATEPART(wk, GETDATE())
1099 + 1 )
1100 AND CompanyID = @CompanyID
1101 )
1102 MERGE INTO TARGET
1103 USING #ExportSSAuto AS SOURCE
1104 ON ( TARGET.RefNbr = ISNULL(SOURCE.RefNbr, '')
1105 AND TARGET.[Year] = ISNULL(SOURCE.[Year], '')
1106 AND TARGET.[Week] = ISNULL(SOURCE.[Week], 0)
1107 AND TARGET.SS = ISNULL(SOURCE.SS, '')
1108 AND TARGET.ASM = ISNULL(SOURCE.ASM, '')
1109 AND TARGET.CompanyID=SOURCE.CompanyID
1110 )
1111
1112
1113--When records are matched, update
1114--the records if there is any change
1115 WHEN MATCHED AND
1116 TARGET.RefResult <> SOURCE.RefResult
1117 OR ISNULL(TARGET.SumRoute, 0) <> ISNULL(SOURCE.SumRoute,
1118 0)
1119 OR ISNULL(TARGET.SMKem, 0) < ISNULL(SOURCE.SMKem, 0)
1120 OR ISNULL(TARGET.[AvgSMKem], 0) < ISNULL(SOURCE.[AvgSMKem],
1121 0)
1122 OR ISNULL(TARGET.[SMTB], 0) < ISNULL(SOURCE.[SMTB], 0)
1123 OR ISNULL(TARGET.[AvgSMTB], 0) < ISNULL(SOURCE.[AvgSMTB],
1124 0)
1125 OR ISNULL(TARGET.[SMTot], 0) < ISNULL(SOURCE.[SMTot],
1126 0)
1127 OR ISNULL(TARGET.[AvgSMTot], 0) < ISNULL(SOURCE.[AvgSMTot],
1128 0)
1129 OR ISNULL(TARGET.ASM, '') <> ISNULL(SOURCE.ASM, '')
1130 OR ISNULL(TARGET.NumHLMin, 0) <> ISNULL(SOURCE.NumHLMin,
1131 0)
1132 OR ISNULL(TARGET.NumHLMax, 0) <> ISNULL(SOURCE.NumHLMax,
1133 0)
1134 OR ISNULL(TARGET.IsNew, 0) <> ISNULL(SOURCE.IsNew, 0)
1135 OR ISNULL(TARGET.IsWWNext, 0) <> ISNULL(SOURCE.IsWWNext,
1136 0)
1137 OR ISNULL(TARGET.IsValidate, 0) <> ISNULL(SOURCE.IsValidate,
1138 0)
1139 OR ISNULL(TARGET.PercentSSMin, 0) <> ISNULL(SOURCE.PercentSSMin,
1140 0)
1141 OR ISNULL(TARGET.SortID, 0) <> ISNULL(SOURCE.SortID, 0)
1142 THEN
1143 UPDATE SET TARGET.RefResult = SOURCE.RefResult ,
1144 TARGET.SumRoute = SOURCE.SumRoute ,
1145 TARGET.SMKem = SOURCE.SMKem ,
1146 TARGET.[AvgSMKem] = SOURCE.[AvgSMKem] ,
1147 TARGET.[SMTB] = SOURCE.[SMTB] ,
1148 TARGET.[AvgSMTB] = SOURCE.[AvgSMTB] ,
1149 TARGET.[SMTot] = SOURCE.[SMTot] ,
1150 TARGET.[AvgSMTot] = SOURCE.[AvgSMTot] ,
1151 TARGET.ASM = SOURCE.ASM ,
1152 TARGET.NumHLMin = SOURCE.NumHLMin ,
1153 TARGET.NumHLMax = SOURCE.NumHLMax ,
1154 TARGET.IsNew = SOURCE.IsNew ,
1155 TARGET.IsWWNext = SOURCE.IsWWNext ,
1156 TARGET.IsValidate = SOURCE.IsValidate ,
1157 TARGET.PercentSSMin = SOURCE.PercentSSMin ,
1158 TARGET.SortID = SOURCE.SortID
1159
1160
1161--When no records are matched, insert
1162--the incoming records from source
1163--table to target table
1164 WHEN NOT MATCHED BY TARGET THEN
1165 INSERT ( CompanyID,
1166 [RefNbr] ,
1167 [Year] ,
1168 [Week] ,
1169 [SS] ,
1170 [ASM] ,
1171 [SumRoute] ,
1172 [SMKem] ,
1173 [AvgSMKem] ,
1174 [SMTB] ,
1175 [AvgSMTB] ,
1176 [SMTot] ,
1177 [AvgSMTot] ,
1178 [NumHLMin] ,
1179 [NumHLMax] ,
1180 [RefResult] ,
1181 IsNew ,
1182 IsWWNext ,
1183 IsValidate ,
1184 PercentSSMin ,
1185 SortID
1186 )
1187 VALUES (SOURCE.CompanyID,
1188 SOURCE.[RefNbr] ,
1189 SOURCE.[Year] ,
1190 SOURCE.[Week] ,
1191 SOURCE.[SS] ,
1192 SOURCE.[ASM] ,
1193 SOURCE.[SumRoute] ,
1194 SOURCE.[SMKem] ,
1195 SOURCE.[AvgSMKem] ,
1196 SOURCE.[SMTB] ,
1197 SOURCE.[AvgSMTB] ,
1198 SOURCE.[SMTot] ,
1199 SOURCE.[AvgSMTot] ,
1200 SOURCE.NumHLMin ,
1201 SOURCE.NumHLMax ,
1202 SOURCE.[RefResult] ,
1203 SOURCE.IsNew ,
1204 SOURCE.IsWWNext ,
1205 SOURCE.IsValidate ,
1206 PercentSSMin ,
1207 SOURCE.SortID
1208 ) --When there is a row that exists in target table and
1209--same record does not exist in source table
1210--then delete this record from target table
1211 ;
1212 SELECT @@ROWCOUNT;
1213 DROP TABLE #ExportSSAuto;
1214 DROP TABLE #tempSSAuto;
1215----------------------------------------
1216------------------------------ASM Auto
1217------------LUU DU LIEU KET QUA TAM
1218 SELECT detail.RefNbr ,
1219 header.EffectDate ,
1220 detail.RefResult ,
1221 detail.RegionID ,
1222 detail.AreaID ,
1223 detail.DistributorID ,
1224 detail.PercentSSLowMin ,
1225 detail.PercentSSLowMax ,
1226 detail.PercentSSStardardMin ,
1227 detail.PercentSSStardardMax ,
1228 detail.PercentSSGoodMin ,
1229 detail.PercentSSGoodMax ,
1230 detail.NumHLMin ,
1231 detail.NumHLMax ,
1232 header.Type
1233 INTO #tempASMAuto
1234 FROM DMSHamResultsDetailASM detail
1235 LEFT JOIN ( SELECT TOP 1
1236 RefNbr ,
1237 EffectDate ,
1238 Status ,
1239 Type
1240 FROM DMSHamResultsHeader
1241 WHERE Status = 'R'
1242 AND CAST(EffectDate AS DATE) <= CAST(GETDATE() AS DATE)
1243 ORDER BY RefNbr DESC
1244 ) header ON detail.RefNbr = header.RefNbr
1245 WHERE Status = 'R'
1246 AND CAST(EffectDate AS DATE) <= CAST(GETDATE() AS DATE)
1247 AND detail.companyid=@CompanyID;
1248
1249----------------------------- Tinh so lieu vao bang ASM
1250 SELECT *
1251 INTO #ExportASMAuto
1252 FROM dbo.DMSHamResultsASM
1253 WHERE 1 = 0;
1254-------------------------Luu bang tam:
1255 SELECT ViewASM.* ,
1256 ROW_NUMBER() OVER ( PARTITION BY ViewASM.RSM ORDER BY ViewASM.RefResult ASC ) AS SortID
1257 INTO #ASMRSAuto
1258 FROM ( SELECT rs1.[RefNbr] ,
1259 rs1.[Year] ,
1260 rs1.[Week] ,
1261 rs1.[ASM] ,
1262 rs1.RSM ,
1263 rs1.[SumSS] ,
1264 rs1.[Kem] AS SMKem ,
1265 rs1.AvgKem AS [AvgSMKem] ,
1266 rs1.TB AS [SMTB] ,
1267 rs1.AvgTB AS [AvgSMTB] ,
1268 rs1.Tot AS [SMTot] ,
1269 rs1.AvgTot AS [AvgSMTot] ,
1270 ViewASM.NumHLMin AS [NumHLMin] ,
1271 ViewASM.NumHLMax AS [NumHLMax] ,
1272 rs1.RefResult ,
1273 IsWWNext = CASE ( SELECT DISTINCT TOP 1
1274 ValueCD
1275 FROM THPDMS2.dbo.DMSSFAssignment sfa
1276 LEFT JOIN dbo.DMSSalesOrgValue sog ON sog.CompanyID = sfa.CompanyID
1277 AND sog.ValueID = sfa.SalesOrgID
1278 LEFT JOIN dbo.DMSSalesForce sf ON sf.CompanyID = sfa.CompanyID
1279 AND sf.EmployeeID = sfa.EmployeeID
1280 WHERE sf.EmployeeCD = rs1.ASM
1281 AND sfa.CompanyID = @CompanyID
1282 AND sfa.IsBaseAssignment = 1
1283 )
1284 WHEN 6 THEN 0
1285 WHEN 7 THEN 0
1286 ELSE 0
1287 END ,
1288 IsValidate = CASE ( SELECT DISTINCT TOP 1
1289 ValueCD
1290 FROM THPDMS2.dbo.DMSSFAssignment sfa
1291 LEFT JOIN dbo.DMSSalesOrgValue sog ON sog.CompanyID = sfa.CompanyID
1292 AND sog.ValueID = sfa.SalesOrgID
1293 LEFT JOIN dbo.DMSSalesForce sf ON sf.CompanyID = sfa.CompanyID
1294 AND sf.EmployeeID = sfa.EmployeeID
1295 WHERE sf.EmployeeCD = rs1.ASM
1296 AND sfa.CompanyID = @CompanyID
1297 AND sfa.IsBaseAssignment = 1
1298 )
1299 WHEN 6 THEN 0
1300 WHEN 7 THEN 0
1301 ELSE 0
1302 END ,
1303 NumberSSMin = ( SELECT [Number]
1304 FROM [THPHammer2].[dbo].[SystemSetting]
1305 WHERE ID = 'MinSS'
1306 ) ,
1307 PercentASMMin = ( SELECT DISTINCT TOP 1
1308 PercentASMMin
1309 FROM DMSHamResultsHeader
1310 WHERE RefNbr = rs1.[RefNbr]
1311 )
1312 FROM ( SELECT tem1.* ,
1313 getRSM.RSM ,
1314 getRSM.RegionID ,
1315 ISNULL(( SELECT TOP 1
1316 t.RefResult
1317 FROM #tempASMAuto t
1318 WHERE
1319 (
1320 --DK KEM
1321 ( ( ISNULL(tem1.AvgKem,
1322 0) >= t.PercentSSLowMin
1323 AND ISNULL(tem1.AvgKem,
1324 0) < t.PercentSSLowMax
1325 AND t.RefResult = 'A'
1326 AND ( ( t.[Type] = 'R'
1327 AND t.RegionID = getRSM.RegionID
1328 )
1329 OR ( t.[Type] = 'A'
1330 AND t.RegionID = getRSM.RegionID
1331 )
1332 OR ( t.[Type] = 'D'
1333 AND t.RegionID = getRSM.RegionID
1334 )
1335 OR ( t.[Type] = 'N'
1336 AND 1 = 1
1337 )
1338 )
1339 )
1340 AND ( ISNULL(tem1.AvgTB,
1341 0) >= t.PercentSSStardardMin
1342 AND ISNULL(tem1.AvgTB,
1343 0) < t.PercentSSStardardMax
1344 AND t.RefResult = 'A'
1345 AND ( ( t.[Type] = 'R'
1346 AND t.RegionID = getRSM.RegionID
1347 )
1348 OR ( t.[Type] = 'A'
1349 AND t.RegionID = getRSM.RegionID
1350 )
1351 OR ( t.[Type] = 'D'
1352 AND t.RegionID = getRSM.RegionID
1353 )
1354 OR ( t.[Type] = 'N'
1355 AND 1 = 1
1356 )
1357 )
1358 )
1359 AND ( ISNULL(tem1.AvgTot,
1360 0) >= t.PercentSSGoodMin
1361 AND ISNULL(tem1.AvgTot,
1362 0) < t.PercentSSGoodMax
1363 AND t.RefResult = 'A'
1364 AND ( ( t.[Type] = 'R'
1365 AND t.RegionID = getRSM.RegionID
1366 )
1367 OR ( t.[Type] = 'A'
1368 AND t.RegionID = getRSM.RegionID
1369 )
1370 OR ( t.[Type] = 'D'
1371 AND t.RegionID = getRSM.RegionID
1372 )
1373 OR ( t.[Type] = 'N'
1374 AND 1 = 1
1375 )
1376 )
1377 )
1378 )
1379 --dk TRUNG BINH
1380 OR ( ( ISNULL(tem1.AvgKem,
1381 0) >= t.PercentSSLowMin
1382 AND ISNULL(tem1.AvgKem,
1383 0) < t.PercentSSLowMax
1384 AND t.RefResult = 'C'
1385 AND ( ( t.[Type] = 'R'
1386 AND t.RegionID = getRSM.RegionID
1387 )
1388 OR ( t.[Type] = 'A'
1389 AND t.RegionID = getRSM.RegionID
1390 )
1391 OR ( t.[Type] = 'D'
1392 AND t.RegionID = getRSM.RegionID
1393 )
1394 OR ( t.[Type] = 'N'
1395 AND 1 = 1
1396 )
1397 )
1398 )
1399 AND ( ISNULL(tem1.AvgTB,
1400 0) >= t.PercentSSStardardMin
1401 AND ISNULL(tem1.AvgTB,
1402 0) < t.PercentSSStardardMax
1403 AND t.RefResult = 'C'
1404 AND ( ( t.[Type] = 'R'
1405 AND t.RegionID = getRSM.RegionID
1406 )
1407 OR ( t.[Type] = 'A'
1408 AND t.RegionID = getRSM.RegionID
1409 )
1410 OR ( t.[Type] = 'D'
1411 AND t.RegionID = getRSM.RegionID
1412 )
1413 OR ( t.[Type] = 'N'
1414 AND 1 = 1
1415 )
1416 )
1417 )
1418 AND ( ISNULL(tem1.AvgTot,
1419 0) >= t.PercentSSGoodMin
1420 AND ISNULL(tem1.AvgTot,
1421 0) < t.PercentSSGoodMax
1422 AND t.RefResult = 'C'
1423 AND ( ( t.[Type] = 'R'
1424 AND t.RegionID = getRSM.RegionID
1425 )
1426 OR ( t.[Type] = 'A'
1427 AND t.RegionID = getRSM.RegionID
1428 )
1429 OR ( t.[Type] = 'D'
1430 AND t.RegionID = getRSM.RegionID
1431 )
1432 OR ( t.[Type] = 'N'
1433 AND 1 = 1
1434 )
1435 )
1436 )
1437 )
1438 OR ( ( ISNULL(tem1.AvgKem,
1439 0) >= t.PercentSSLowMin
1440 AND ISNULL(tem1.AvgKem,
1441 0) < t.PercentSSLowMax
1442 AND t.RefResult = 'D'
1443 AND ( ( t.[Type] = 'R'
1444 AND t.RegionID = getRSM.RegionID
1445 )
1446 OR ( t.[Type] = 'A'
1447 AND t.RegionID = getRSM.RegionID
1448 )
1449 OR ( t.[Type] = 'D'
1450 AND t.RegionID = getRSM.RegionID
1451 )
1452 OR ( t.[Type] = 'N'
1453 AND 1 = 1
1454 )
1455 )
1456 )
1457 AND ( ISNULL(tem1.AvgTB,
1458 0) >= t.PercentSSStardardMin
1459 AND ISNULL(tem1.AvgTB,
1460 0) < t.PercentSSStardardMax
1461 AND t.RefResult = 'D'
1462 AND ( ( t.[Type] = 'R'
1463 AND t.RegionID = getRSM.RegionID
1464 )
1465 OR ( t.[Type] = 'A'
1466 AND t.RegionID = getRSM.RegionID
1467 )
1468 OR ( t.[Type] = 'D'
1469 AND t.RegionID = getRSM.RegionID
1470 )
1471 OR ( t.[Type] = 'N'
1472 AND 1 = 1
1473 )
1474 )
1475 )
1476 AND ( ISNULL(tem1.AvgTot,
1477 0) >= t.PercentSSGoodMin
1478 AND ISNULL(tem1.AvgTot,
1479 0) < t.PercentSSGoodMax
1480 AND t.RefResult = 'D'
1481 AND ( ( t.[Type] = 'R'
1482 AND t.RegionID = getRSM.RegionID
1483 )
1484 OR ( t.[Type] = 'A'
1485 AND t.RegionID = getRSM.RegionID
1486 )
1487 OR ( t.[Type] = 'D'
1488 AND t.RegionID = getRSM.RegionID
1489 )
1490 OR ( t.[Type] = 'N'
1491 AND 1 = 1
1492 )
1493 )
1494 )
1495 )
1496 )
1497 ), 0) AS RefResult
1498 FROM ( SELECT v1.RefNbr ,
1499 v1.ASM ,
1500 v1.Year ,
1501 v1.Week ,
1502 ISNULL(v1.SumSS, 0) AS SumSS ,
1503 ISNULL(v2.SumSSKem,
1504 0) AS Kem ,
1505 ISNULL(( v2.SumSSKem
1506 * 100 )
1507 / v1.SumSS, 0) AS AvgKem ,
1508 ISNULL(v3.SumSSTB,
1509 0) AS TB ,
1510 ISNULL(( v3.SumSSTB
1511 * 100 )
1512 / v1.SumSS, 0) AS AvgTB ,
1513 ISNULL(v4.SumSSTot,
1514 0) AS Tot ,
1515 ISNULL(( v4.SumSSTot
1516 * 100 )
1517 / v1.SumSS, 0) AS AvgTot
1518 FROM ( SELECT
1519 rs.RefNbr ,
1520 rs.Year ,
1521 rs.Week ,
1522 rs.ASM ,
1523 COUNT(rs.SS) AS SumSS
1524 FROM
1525 DMSHamResultsSS
1526 AS rs
1527 WHERE
1528 rs.IsNew = 0
1529 AND rs.CompanyID = @CompanyID
1530 GROUP BY rs.RefNbr ,
1531 rs.Year ,
1532 rs.Week ,
1533 rs.ASM
1534 ) AS v1
1535 LEFT JOIN ( SELECT
1536 rs.RefNbr ,
1537 rs.Year ,
1538 rs.Week ,
1539 rs.ASM ,
1540 COUNT(rs.SS) AS SumSSKem
1541 FROM
1542 DMSHamResultsSS
1543 AS rs
1544 WHERE
1545 rs.RefResult = 'A'
1546 AND rs.IsNew = 0
1547 AND rs.CompanyID = @CompanyID
1548 GROUP BY rs.RefNbr ,
1549 rs.Year ,
1550 rs.Week ,
1551 rs.ASM
1552 ) AS v2 ON v1.ASM = v2.ASM
1553 AND v1.RefNbr = v2.RefNbr
1554 AND v1.Week = v2.Week
1555 AND v1.Year = v2.Year
1556 LEFT JOIN ( SELECT
1557 rs.RefNbr ,
1558 rs.Year ,
1559 rs.Week ,
1560 rs.ASM ,
1561 COUNT(rs.SS) AS SumSSTB
1562 FROM
1563 DMSHamResultsSS
1564 AS rs
1565 WHERE
1566 rs.RefResult = 'C'
1567 AND rs.CompanyID = @CompanyID
1568 AND rs.IsNew = 0
1569 GROUP BY rs.RefNbr ,
1570 rs.Year ,
1571 rs.Week ,
1572 rs.ASM
1573 ) AS v3 ON v1.ASM = v3.ASM
1574 AND v1.RefNbr = v3.RefNbr
1575 AND v1.Week = v3.Week
1576 AND v1.Year = v3.Year
1577 LEFT JOIN ( SELECT
1578 rs.RefNbr ,
1579 rs.Year ,
1580 rs.Week ,
1581 rs.ASM ,
1582 COUNT(rs.SS) AS SumSSTot
1583 FROM
1584 DMSHamResultsSS
1585 AS rs
1586 WHERE
1587 rs.RefResult = 'D'
1588 AND rs.IsNew = 0
1589 AND rs.CompanyID = @CompanyID
1590 GROUP BY rs.RefNbr ,
1591 rs.Year ,
1592 rs.Week ,
1593 rs.ASM
1594 ) AS v4 ON v1.ASM = v4.ASM
1595 AND v1.RefNbr = v4.RefNbr
1596 AND v1.Week = v4.Week
1597 AND v1.Year = v4.Year
1598 ) AS tem1
1599 LEFT JOIN ( SELECT DISTINCT
1600 ASM ,
1601 RSM ,
1602 RegionID
1603 FROM
1604 THPDMS2.dbo.DMSHamerViewSSASM
1605 ) AS getRSM ON tem1.ASM = getRSM.ASM
1606 ) AS rs1
1607 LEFT JOIN DMSHamResultsHeader AS header ON header.RefNbr = rs1.RefNbr
1608 AND header.CompanyID = @CompanyID
1609 LEFT JOIN DMSHamResultsDetailASM AS ViewASM ON rs1.RefResult = ViewASM.RefResult
1610 AND rs1.RefNbr = ViewASM.RefNbr
1611 AND ViewASM.CompanyID=@CompanyID
1612 AND (( header.[Type] = 'R'
1613 AND rs1.RegionID = ViewASM.RegionID
1614 )
1615 OR ( header.[Type] = 'A'
1616 AND rs1.RegionID = ViewASM.RegionID
1617 )
1618 OR ( header.[Type] = 'D'
1619 AND rs1.RegionID = ViewASM.RegionID
1620 )
1621 OR ( header.[Type] = 'N'
1622 AND 1 = 1
1623 ))
1624 WHERE rs1.ASM IS NOT NULL
1625 AND rs1.RSM IS NOT NULL
1626 AND rs1.Year = YEAR(GETDATE())
1627 AND rs1.Week = ( DATEPART(wk, GETDATE())
1628 + 1 )
1629 ) AS ViewASM;
1630
1631
1632--- SELECT UPDATED OR NOT INSERTED FROM ACU TO Hammer
1633 INSERT INTO #ExportASMAuto
1634 ( CompanyID,
1635 [RefNbr] ,
1636 [Year] ,
1637 [Week] ,
1638 [ASM] ,
1639 [RSM] ,
1640 [SumSS] ,
1641 [SSKem] ,
1642 [AvgSSKem] ,
1643 [SSTB] ,
1644 [AvgSSTB] ,
1645 [SSTot] ,
1646 [AvgSSTot] ,
1647 [NumHLMin] ,
1648 [NumHLMax] ,
1649 [RefResult] ,
1650 IsWWNext ,
1651 IsValidate ,
1652 NumberSSMin ,
1653 PercentASMMin ,
1654 SortID
1655
1656 )
1657 ( SELECT @CompanyID, *
1658 FROM #ASMRSAuto
1659 );
1660 WITH TARGET
1661 AS ( SELECT CompanyID
1662 , [RefNbr] ,
1663 [Year] ,
1664 [Week] ,
1665 [ASM] ,
1666 [RSM] ,
1667 [SumSS] ,
1668 [SSKem] ,
1669 [AvgSSKem] ,
1670 [SSTB] ,
1671 [AvgSSTB] ,
1672 [SSTot] ,
1673 [AvgSSTot] ,
1674 [NumHLMin] ,
1675 [NumHLMax] ,
1676 [RefResult] ,
1677 IsWWNext ,
1678 IsValidate ,
1679 NumberSSMin ,
1680 PercentASMMin ,
1681 SortID
1682 FROM [dbo].DMSHamResultsASM
1683 WHERE Year = YEAR(GETDATE())
1684 AND Week = ( DATEPART(wk, GETDATE())
1685 + 1 )
1686 AND CompanyID = @CompanyID
1687 )
1688 MERGE INTO TARGET
1689 USING #ExportASMAuto AS SOURCE
1690 ON ( TARGET.RefNbr = ISNULL(SOURCE.RefNbr, '')
1691 AND TARGET.[Year] = ISNULL(SOURCE.[Year], '')
1692 AND TARGET.[Week] = ISNULL(SOURCE.[Week], 0)
1693 AND TARGET.ASM = ISNULL(SOURCE.ASM, '')
1694 AND TARGET.CompanyID = SOURCE.CompanyID
1695 )
1696
1697----When records are matched, update
1698----the records if there is any change
1699 WHEN MATCHED AND
1700 TARGET.RefResult <> SOURCE.RefResult
1701 OR ISNULL(TARGET.[SumSS], 0) <> ISNULL(SOURCE.[SumSS],
1702 0)
1703 OR ISNULL(TARGET.SSKem, 0) < ISNULL(SOURCE.SSKem, 0)
1704 OR ISNULL(TARGET.[AvgSSKem], 0) < ISNULL(SOURCE.[AvgSSKem],
1705 0)
1706 OR ISNULL(TARGET.[SSTB], 0) < ISNULL(SOURCE.[SSTB], 0)
1707 OR ISNULL(TARGET.[AvgSSTB], 0) < ISNULL(SOURCE.[AvgSSTB],
1708 0)
1709 OR ISNULL(TARGET.[SSTot], 0) < ISNULL(SOURCE.[SSTot],
1710 0)
1711 OR ISNULL(TARGET.[AvgSSTot], 0) < ISNULL(SOURCE.[AvgSSTot],
1712 0)
1713 OR ISNULL(TARGET.RSM, '') <> ISNULL(SOURCE.RSM, '')
1714 OR ISNULL(TARGET.NumHLMin, 0) <> ISNULL(SOURCE.NumHLMin,
1715 0)
1716 OR ISNULL(TARGET.NumHLMax, 0) <> ISNULL(SOURCE.NumHLMax,
1717 0)
1718 OR ISNULL(TARGET.IsWWNext, 0) <> ISNULL(SOURCE.IsWWNext,
1719 0)
1720 OR ISNULL(TARGET.IsValidate, 0) <> ISNULL(SOURCE.IsValidate,
1721 0)
1722 OR ISNULL(TARGET.SortID, 0) <> ISNULL(SOURCE.SortID, 0)
1723 OR ISNULL(TARGET.NumberSSMin, 0) <> ISNULL(SOURCE.NumberSSMin,
1724 0)
1725 OR ISNULL(TARGET.PercentASMMin, 0) <> ISNULL(SOURCE.PercentASMMin,
1726 0) THEN
1727 UPDATE SET TARGET.RefResult = SOURCE.RefResult ,
1728 TARGET.SumSS = SOURCE.SumSS ,
1729 TARGET.SSKem = SOURCE.SSKem ,
1730 TARGET.[AvgSSKem] = SOURCE.[AvgSSKem] ,
1731 TARGET.[SSTB] = SOURCE.[SSTB] ,
1732 TARGET.[AvgSSTB] = SOURCE.[AvgSSTB] ,
1733 TARGET.[SSTot] = SOURCE.[SSTot] ,
1734 TARGET.[AvgSSTot] = SOURCE.[AvgSSTot] ,
1735 TARGET.RSM = SOURCE.RSM ,
1736 TARGET.NumHLMin = SOURCE.NumHLMin ,
1737 TARGET.NumHLMax = SOURCE.NumHLMax ,
1738 TARGET.IsWWNext = SOURCE.IsWWNext ,
1739 TARGET.IsValidate = SOURCE.IsValidate ,
1740 TARGET.NumberSSMin = SOURCE.NumberSSMin ,
1741 TARGET.PercentASMMin = SOURCE.PercentASMMin ,
1742 TARGET.SortID = SOURCE.SortID
1743
1744
1745
1746----When no records are matched, insert
1747----the incoming records from source
1748----table to target table
1749 WHEN NOT MATCHED BY TARGET THEN
1750 INSERT ( CompanyID,
1751 [RefNbr] ,
1752 [Year] ,
1753 [Week] ,
1754 [ASM] ,
1755 [RSM] ,
1756 [SumSS] ,
1757 [SSKem] ,
1758 [AvgSSKem] ,
1759 [SSTB] ,
1760 [AvgSSTB] ,
1761 [SSTot] ,
1762 [AvgSSTot] ,
1763 [NumHLMin] ,
1764 [NumHLMax] ,
1765 [RefResult] ,
1766 IsWWNext ,
1767 IsValidate ,
1768 NumberSSMin ,
1769 PercentASMMin ,
1770 SortID
1771 )
1772 VALUES ( SOURCE.CompanyID ,SOURCE.[RefNbr] ,
1773 SOURCE.[Year] ,
1774 SOURCE.[Week] ,
1775 SOURCE.[ASM] ,
1776 SOURCE.[RSM] ,
1777 SOURCE.[SumSS] ,
1778 SOURCE.[SSKem] ,
1779 SOURCE.[AvgSSKem] ,
1780 SOURCE.[SSTB] ,
1781 SOURCE.[AvgSSTB] ,
1782 SOURCE.[SSTot] ,
1783 SOURCE.[AvgSSTot] ,
1784 SOURCE.NumHLMin ,
1785 SOURCE.NumHLMax ,
1786 SOURCE.[RefResult] ,
1787 SOURCE.IsWWNext ,
1788 SOURCE.IsValidate ,
1789 SOURCE.NumberSSMin ,
1790 SOURCE.PercentASMMin ,
1791 SOURCE.SortID
1792 ) --When there is a row that exists in target table and
1793--same record does not exist in source table
1794--then delete this record from target table
1795 ;
1796 SELECT @@ROWCOUNT;
1797 DROP TABLE #ExportASMAuto;
1798 DROP TABLE #tempASMAuto;
1799-------------------------------------
1800 END;
1801 --ELSE -- process manual
1802-- BEGIN
1803-- DECLARE @StartOfWeek DATETIME;
1804-- DECLARE @EndOfWeek DATETIME;
1805-- DECLARE @Start DATETIME;
1806-- SELECT @StartOfWeek = DATEADD(wk,
1807-- DATEDIFF(wk, 7, '1/1/' + @Year)
1808-- + ( @Week - 1 ), 7);
1809-- SELECT @EndOfWeek = DATEADD(wk,
1810-- DATEDIFF(wk, 7, '1/1/' + @Year)
1811-- + ( @Week - 1 ), 7) + 6;
1812-- SELECT @Start = @StartOfWeek
1813-- - ( SELECT ISNULL(NumDayPrevGetData, 0)
1814-- FROM DMSHamConfigGen
1815-- WHERE ID = 1
1816-- AND GenType = 'M'
1817-- );
1818
1819
1820-- -----------LUU DU LIEU KET QUA TAM
1821
1822-- SELECT detail.RefNbr ,
1823-- header.EffectDate ,
1824-- detail.RefResult ,
1825-- detail.RegionID ,
1826-- detail.PCMin ,
1827-- detail.PCMax ,
1828-- detail.PercentPhotoMin ,
1829-- detail.PercentPhotoMax ,
1830-- detail.PercentDisplayMin ,
1831-- detail.PercentDisplayMax ,
1832-- detail.NumHLMin ,
1833-- detail.NumHLMax ,
1834-- header.[Type]
1835-- INTO #temp1
1836-- FROM DMSHamResultsDetailSM detail
1837-- LEFT JOIN ( SELECT TOP 1
1838-- RefNbr ,
1839-- EffectDate ,
1840-- Status ,
1841-- Type
1842-- FROM DMSHamResultsHeader
1843-- WHERE Status = 'R'
1844-- AND CAST(EffectDate AS DATE) <= CAST(@StartOfWeek AS DATE)
1845-- ORDER BY RefNbr DESC
1846-- ) header ON detail.RefNbr = header.RefNbr
1847-- WHERE header.Status = 'R'
1848-- AND CAST(EffectDate AS DATE) <= CAST(@StartOfWeek AS DATE);
1849
1850-- --------- tinh TB PC trong BL.
1851-- SELECT [RefNbr] ,
1852-- [Year] ,
1853-- [Week] ,
1854-- [RouteID] ,
1855-- SS ,
1856-- [RefResult] ,
1857-- [AvgPC] ,
1858-- [AvgPercentPhoto] ,
1859-- [AvgPercentDisplay] ,
1860-- StartDate ,
1861-- EndDate ,
1862-- NumHLMin ,
1863-- NumHLMax ,
1864-- PercentSSMin ,
1865-- PercentASMMin ,
1866-- PercentRSMMin ,
1867-- IsNew ,
1868-- CreatedDateTime ,
1869-- IsWWNext ,
1870-- IsValidate ,
1871-- SortID
1872-- INTO #Export
1873-- FROM dbo.DMSHamResults
1874-- WHERE 1 = 0;
1875------ Get data into tem truoc khi long
1876-- SELECT Vi.* ,
1877-- ROW_NUMBER() OVER ( PARTITION BY Vi.ss ORDER BY Vi.RefResult ASC ) AS SortID
1878-- INTO #RouteRS
1879-- FROM ( SELECT v1.RefNbr ,
1880-- v1.Year ,
1881-- v1.Week ,
1882-- v1.RouteCD AS RouteID ,
1883-- v1.SS ,
1884-- ISNULL(v1.RefResult, 0) AS RefResult ,
1885-- v1.[AvgPC] ,
1886-- v1.AvgHinhChup AS [AvgPercentPhoto] ,
1887-- v1.AvgTrungBay AS [AvgPercentDisplay] ,
1888-- @Start AS StartDate ,
1889-- @StartOfWeek AS EndDate ,
1890-- smResult.NumHLMin AS NumHLMin ,
1891-- smResult.NumHLMax AS NumHLMax ,
1892-- header.PercentSSMin AS PercentSSMin ,
1893-- header.PercentASMMin AS PercentASMMin ,
1894-- header.PercentRSMMin AS PercentRSMMin ,
1895-- v1.IsNew ,
1896-- GETDATE() AS CreatedDateTime ,
1897-- IsWWNext = CASE ( SELECT DISTINCT TOP 1
1898-- RegionID
1899-- FROM THPDMS2.dbo.DMSSFAssignment
1900-- WHERE EmployeeID = v1.SS
1901-- AND IsActive = 1
1902-- )
1903-- WHEN 6 THEN 0
1904-- WHEN 7 THEN 0
1905-- ELSE 0
1906-- END ,
1907-- IsValidate = CASE ( SELECT DISTINCT TOP 1
1908-- RegionID
1909-- FROM THPDMS2.dbo.DMSSFAssignment
1910-- WHERE EmployeeID = v1.SS
1911-- AND IsActive = 1
1912-- )
1913-- WHEN 6 THEN 0
1914-- WHEN 7 THEN 0
1915-- ELSE 0
1916-- END
1917-- FROM ( SELECT DISTINCT
1918-- dis.RegionID ,
1919-- distroute.RouteCD ,
1920-- distroute.RouteName ,
1921-- distroute.SalesSupID AS SS ,
1922-- ISNULL(BLFCS.AvgPC, 0) AS AvgPC ,
1923-- ISNULL(display.AVGHinhChup, 0) AS AvgHinhChup ,
1924-- ISNULL(display2.AVGTrungBay, 0) AS AvgTrungBay ,
1925-- ( SELECT TOP 1
1926-- t.RefResult
1927-- FROM #temp1 t
1928-- WHERE (
1929-- --DK KEM
1930-- ( ( ISNULL(BLFCS.AvgPC,
1931-- 0) >= t.PCMin
1932-- AND ISNULL(BLFCS.AvgPC,
1933-- 0) < t.PCMax
1934-- AND t.RefResult = 'A'
1935-- AND ( ( t.[Type] = 'R'
1936-- AND t.RegionID = dis.RegionID
1937-- )
1938-- OR ( t.[Type] = 'A'
1939-- AND t.RegionID = dis.RegionID
1940-- )
1941-- OR ( t.[Type] = 'D'
1942-- AND t.RegionID = dis.RegionID
1943-- )
1944-- OR ( t.[Type] = 'N'
1945-- AND 1 = 1
1946-- )
1947-- )
1948-- )
1949-- OR ( ISNULL(display.AVGHinhChup,
1950-- 0) >= t.PercentPhotoMin
1951-- AND ISNULL(display.AVGHinhChup,
1952-- 0) < t.PercentPhotoMax
1953-- AND t.RefResult = 'A'
1954-- AND ( ( t.[Type] = 'R'
1955-- AND t.RegionID = dis.RegionID
1956-- )
1957-- OR ( t.[Type] = 'A'
1958-- AND t.RegionID = dis.RegionID
1959-- )
1960-- OR ( t.[Type] = 'D'
1961-- AND t.RegionID = dis.RegionID
1962-- )
1963-- OR ( t.[Type] = 'N'
1964-- AND 1 = 1
1965-- )
1966-- )
1967-- OR ( ISNULL(display2.AVGTrungBay,
1968-- 0) >= t.PercentDisplayMin
1969-- AND ISNULL(display2.AVGTrungBay,
1970-- 0) < t.PercentDisplayMax
1971-- AND t.RefResult = 'A'
1972-- AND ( t.[Type] = 'R'
1973-- AND t.RegionID = dis.RegionID
1974-- )
1975-- OR ( t.[Type] = 'A'
1976-- AND t.RegionID = dis.RegionID
1977-- )
1978-- OR ( t.[Type] = 'D'
1979-- AND t.RegionID = dis.RegionID
1980-- )
1981-- OR ( t.[Type] = 'N'
1982-- AND 1 = 1
1983-- )
1984-- )
1985-- )
1986-- )
1987-- --dk TRUNG BINH
1988-- OR ( ( ISNULL(BLFCS.AvgPC,
1989-- 0) >= t.PCMin
1990-- AND ISNULL(BLFCS.AvgPC,
1991-- 0) < t.PCMax
1992-- AND t.RefResult = 'C'
1993-- AND ( ( t.[Type] = 'R'
1994-- AND t.RegionID = dis.RegionID
1995-- )
1996-- OR ( t.[Type] = 'A'
1997-- AND t.RegionID = dis.RegionID
1998-- )
1999-- OR ( t.[Type] = 'D'
2000-- AND t.RegionID = dis.RegionID
2001-- )
2002-- OR ( t.[Type] = 'N'
2003-- AND 1 = 1
2004-- )
2005-- )
2006-- AND ISNULL(display.AVGHinhChup,
2007-- 0) >= t.PercentPhotoMin
2008-- AND ISNULL(display2.AVGTrungBay,
2009-- 0) >= t.PercentDisplayMin
2010-- )
2011-- OR ( ISNULL(BLFCS.AvgPC,
2012-- 0) >= t.PCMax
2013-- AND t.RefResult = 'C'
2014-- AND ( ( t.[Type] = 'R'
2015-- AND t.RegionID = dis.RegionID
2016-- )
2017-- OR ( t.[Type] = 'A'
2018-- AND t.RegionID = dis.RegionID
2019-- )
2020-- OR ( t.[Type] = 'D'
2021-- AND t.RegionID = dis.RegionID
2022-- )
2023-- OR ( t.[Type] = 'N'
2024-- AND 1 = 1
2025-- )
2026-- )
2027-- AND ISNULL(display.AVGHinhChup,
2028-- 0) >= t.PercentPhotoMin
2029-- AND ISNULL(display2.AVGTrungBay,
2030-- 0) >= t.PercentDisplayMin
2031-- AND ISNULL(display2.AVGTrungBay,
2032-- 0) < t.PercentDisplayMax
2033-- )
2034-- )
2035-- OR ( ISNULL(BLFCS.AvgPC,
2036-- 0) >= t.PCMin
2037-- AND t.RefResult = 'D'
2038-- AND ( ( t.[Type] = 'R'
2039-- AND t.RegionID = dis.RegionID
2040-- )
2041-- OR ( t.[Type] = 'A'
2042-- AND t.RegionID = dis.RegionID
2043-- )
2044-- OR ( t.[Type] = 'D'
2045-- AND t.RegionID = dis.RegionID
2046-- )
2047-- OR ( t.[Type] = 'N'
2048-- AND 1 = 1
2049-- )
2050-- )
2051-- AND ISNULL(display.AVGHinhChup,
2052-- 0) >= t.PercentPhotoMin
2053-- AND ISNULL(display2.AVGTrungBay,
2054-- 0) >= t.PercentDisplayMin
2055-- )
2056-- )
2057-- ) AS RefResult ,
2058-- ( SELECT TOP 1
2059-- A.RefNbr
2060-- FROM #temp1 A
2061-- ) AS RefNbr ,
2062-- ( SELECT TOP 1
2063-- YEAR(@StartOfWeek)
2064-- FROM #temp1 A
2065-- ) AS [Year] ,
2066-- ( SELECT TOP 1
2067-- DATEPART(wk,
2068-- @StartOfWeek)
2069-- FROM #temp1 A
2070-- ) AS [Week] ,
2071-- CASE WHEN sp.UsrInitDate IS NOT NULL
2072-- THEN CASE
2073-- WHEN DATEDIFF(DAY,
2074-- sp.UsrInitDate,
2075-- GETDATE()) > ISNULL(( SELECT DISTINCT
2076-- Number
2077-- FROM
2078-- THPDMS2.dbo.[DMSHamerViewGetSetting]
2079-- WHERE
2080-- ID = 'SMM'
2081-- ), 0) THEN 0
2082-- ELSE 1
2083-- END
2084-- ELSE 0
2085-- END AS IsNew
2086-- FROM THPDMS2.dbo.DMSDistributorRouteAssignment
2087-- AS distroute
2088-- INNER JOIN THPDMS2.dbo.DMSDistributors
2089-- AS dis ON dis.SystemID = distroute.CompanyID
2090-- AND dis.Active = 1
2091-- INNER JOIN THPDMS2.dbo.DMSHORouteMaster rm ON rm.RouteCD = distroute.RouteCD
2092-- AND rm.Active = 1
2093-- INNER JOIN THPDMS2.dbo.SalesPerson sp ON sp.SalespersonID = distroute.SalespersonID
2094-- AND sp.CompanyID > 2
2095-- AND sp.IsActive = 1
2096-- AND sp.CompanyID = distroute.CompanyID
2097-- LEFT JOIN ( SELECT
2098-- RouteID ,
2099-- AVG(PC) AS AvgPC
2100-- FROM
2101-- THPBaseline2..DMSBLFCS
2102-- WHERE
2103-- TranDate >= CAST(@Start AS DATE)
2104-- AND TranDate <= CAST(@StartOfWeek AS DATE)
2105-- AND PC > 0
2106-- GROUP BY RouteID
2107-- ) AS BLFCS ON distroute.RouteCD = BLFCS.RouteID
2108-- LEFT JOIN ( SELECT
2109-- RouteID ,
2110-- SUM(SMLastPass)
2111-- * 100 / SUM(MCP) AS AVGHinhChup
2112-- FROM
2113-- ( SELECT
2114-- a.RouteID ,
2115-- a.ImageLastCount ,
2116-- a.ImageLastPass ,
2117-- a.SMLastPass ,
2118-- MCP_Daily = 33 ,
2119-- a.VisitDate ,
2120-- b.MCP ,
2121-- b.OutletVisited
2122-- FROM
2123-- [192.168.1.16].[DisplayProgramVersion2].[dbo].[BLImageLast] a
2124-- LEFT JOIN [192.168.1.16].[DisplayProgramVersion2].[dbo].[BLSalesKPI] b ON a.RouteID = b.RouteID
2125-- AND a.SalesmanID = b.SalesmanID
2126-- AND a.VisitDate = b.VisitDate
2127-- AND a.DisplayID = b.DisplayID
2128-- WHERE
2129-- a.DisplayID = 1
2130-- AND a.VisitDate >= CAST(@Start AS DATE)
2131-- AND a.VisitDate <= CAST(@StartOfWeek AS DATE)
2132-- AND b.OutletVisited > 0
2133-- ) TB
2134-- GROUP BY RouteID
2135-- ) AS display ON distroute.RouteCD = display.RouteID
2136-- LEFT JOIN ( SELECT
2137-- RouteID ,
2138-- SUM(ImageLastPass)
2139-- * 100
2140-- / SUM(MCP_Daily
2141-- * 0.95) AS AVGTrungBay
2142-- FROM
2143-- ( SELECT
2144-- a.RouteID ,
2145-- a.ImageLastCount ,
2146-- a.ImageLastPass ,
2147-- a.SMLastPass ,
2148-- MCP_Daily = 33 ,
2149-- a.VisitDate ,
2150-- b.MCP ,
2151-- b.OutletVisited
2152-- FROM
2153-- [192.168.1.16].[DisplayProgramVersion2].[dbo].[BLImageLast] a
2154-- LEFT JOIN [192.168.1.16].[DisplayProgramVersion2].[dbo].[BLSalesKPI] b ON a.RouteID = b.RouteID
2155-- AND a.SalesmanID = b.SalesmanID
2156-- AND a.VisitDate = b.VisitDate
2157-- AND a.DisplayID = b.DisplayID
2158-- WHERE
2159-- a.DisplayID = 1
2160-- AND a.VisitDate >= CAST(@Start AS DATE)
2161-- AND a.VisitDate <= CAST(@StartOfWeek AS DATE)
2162-- AND b.OutletVisited > 0
2163-- ) TB
2164-- GROUP BY RouteID
2165-- ) AS display2 ON distroute.RouteCD = display2.RouteID
2166-- WHERE distroute.CompanyID > 2
2167-- ) AS v1
2168-- LEFT JOIN DMSHamResultsHeader AS header ON header.RefNbr = v1.RefNbr
2169-- LEFT JOIN DMSHamResultsDetailSM AS smResult ON v1.RefResult = smResult.RefResult
2170-- AND v1.RefNbr = smResult.RefNbr
2171-- AND ( header.[Type] = 'R'
2172-- AND v1.RegionID = smResult.RegionID
2173-- )
2174-- OR ( header.[Type] = 'A'
2175-- AND v1.RegionID = smResult.RegionID
2176-- )
2177-- OR ( header.[Type] = 'D'
2178-- AND v1.RegionID = smResult.RegionID
2179-- )
2180-- OR ( header.[Type] = 'N'
2181-- AND 1 = 1
2182-- )
2183-- WHERE v1.RefNbr IS NOT NULL
2184-- AND v1.SS IS NOT NULL
2185-- AND v1.Year = @Year
2186-- AND v1.Week = @Week --and rm.Active = 1
2187-- ) AS Vi;
2188----- SELECT UPDATED OR NOT INSERTED FROM ACU TO Hammer
2189-- INSERT INTO #Export
2190-- ( [RefNbr] ,
2191-- [Year] ,
2192-- [Week] ,
2193-- [RouteID] ,
2194-- SS ,
2195-- [RefResult] ,
2196-- [AvgPC] ,
2197-- [AvgPercentPhoto] ,
2198-- [AvgPercentDisplay] ,
2199-- StartDate ,
2200-- EndDate ,
2201-- NumHLMin ,
2202-- NumHLMax ,
2203-- PercentSSMin ,
2204-- PercentASMMin ,
2205-- PercentRSMMin ,
2206-- IsNew ,
2207-- CreatedDateTime ,
2208-- IsWWNext ,
2209-- IsValidate ,
2210-- SortID
2211-- )
2212-- ( SELECT *
2213-- FROM #RouteRS
2214-- );
2215-- WITH TARGET
2216-- AS ( SELECT [RefNbr] ,
2217-- [Year] ,
2218-- [Week] ,
2219-- [RouteID] ,
2220-- SS ,
2221-- [RefResult] ,
2222-- [AvgPC] ,
2223-- [AvgPercentPhoto] ,
2224-- [AvgPercentDisplay] ,
2225-- StartDate ,
2226-- EndDate ,
2227-- NumHLMin ,
2228-- NumHLMax ,
2229-- PercentSSMin ,
2230-- PercentASMMin ,
2231-- PercentRSMMin ,
2232-- IsNew ,
2233-- CreatedDateTime ,
2234-- IsWWNext ,
2235-- IsValidate ,
2236-- SortID
2237-- FROM [dbo].DMSHamResults
2238-- WHERE Year = @Year
2239-- AND Week = @Week --and rm.Active =
2240-- )
2241-- MERGE INTO TARGET
2242-- USING #Export AS SOURCE
2243-- ON ( TARGET.RefNbr = ISNULL(SOURCE.RefNbr, '')
2244-- AND TARGET.[Year] = ISNULL(SOURCE.[Year], '')
2245-- AND TARGET.[Week] = ISNULL(SOURCE.[Week], 0)
2246-- AND TARGET.RouteID = ISNULL(SOURCE.RouteID, '')
2247-- )
2248
2249----When records are matched, update
2250----the records if there is any change
2251-- WHEN MATCHED AND
2252-- TARGET.RefResult <> SOURCE.RefResult
2253-- OR ISNULL(TARGET.AvgPC, 0) <> ISNULL(SOURCE.AvgPC, 0)
2254-- OR ISNULL(TARGET.AvgPercentPhoto, 0) < ISNULL(SOURCE.AvgPercentPhoto,
2255-- 0)
2256-- OR TARGET.AvgPercentDisplay <> SOURCE.AvgPercentDisplay
2257-- OR TARGET.StartDate <> SOURCE.StartDate
2258-- OR TARGET.EndDate <> SOURCE.EndDate
2259-- OR ISNULL(TARGET.SS, '') <> ISNULL(SOURCE.SS, '')
2260-- OR ISNULL(TARGET.NumHLMin, 0) <> ISNULL(SOURCE.NumHLMin,
2261-- 0)
2262-- OR ISNULL(TARGET.NumHLMax, 0) <> ISNULL(SOURCE.NumHLMax,
2263-- 0)
2264-- OR ISNULL(TARGET.PercentSSMin, 0) <> ISNULL(SOURCE.PercentSSMin,
2265-- 0)
2266-- OR ISNULL(TARGET.PercentASMMin, 0) <> ISNULL(SOURCE.PercentASMMin,
2267-- 0)
2268-- OR ISNULL(TARGET.PercentRSMMin, 0) <> ISNULL(SOURCE.PercentRSMMin,
2269-- 0)
2270-- OR ISNULL(TARGET.IsNew, 0) <> ISNULL(SOURCE.IsNew, 0)
2271-- OR TARGET.CreatedDateTime <> SOURCE.CreatedDateTime
2272-- OR ISNULL(TARGET.IsWWNext, 0) <> ISNULL(SOURCE.IsWWNext,
2273-- 0)
2274-- OR ISNULL(TARGET.IsValidate, 0) <> ISNULL(SOURCE.IsValidate,
2275-- 0)
2276-- OR ISNULL(TARGET.SortID, 0) <> ISNULL(SOURCE.SortID, 0)
2277-- THEN
2278-- UPDATE SET TARGET.RefResult = SOURCE.RefResult ,
2279-- TARGET.AvgPC = SOURCE.AvgPC ,
2280-- TARGET.AvgPercentPhoto = SOURCE.AvgPercentPhoto ,
2281-- TARGET.AvgPercentDisplay = SOURCE.AvgPercentDisplay ,
2282-- TARGET.StartDate = SOURCE.StartDate ,
2283-- TARGET.EndDate = SOURCE.EndDate ,
2284-- TARGET.SS = SOURCE.SS ,
2285-- TARGET.NumHLMin = SOURCE.NumHLMin ,
2286-- TARGET.NumHLMax = SOURCE.NumHLMax ,
2287-- TARGET.PercentSSMin = SOURCE.PercentSSMin ,
2288-- TARGET.PercentASMMin = SOURCE.PercentASMMin ,
2289-- TARGET.PercentRSMMin = SOURCE.PercentRSMMin ,
2290-- TARGET.IsNew = SOURCE.IsNew ,
2291-- TARGET.CreatedDateTime = SOURCE.CreatedDateTime ,
2292-- TARGET.IsWWNext = SOURCE.IsWWNext ,
2293-- TARGET.IsValidate = SOURCE.IsValidate ,
2294-- TARGET.SortID = SOURCE.SortID
2295----When no records are matched, insert
2296----the incoming records from source
2297----table to target table
2298-- WHEN NOT MATCHED BY TARGET THEN
2299-- INSERT ( [RefNbr] ,
2300-- [Year] ,
2301-- [Week] ,
2302-- [RouteID] ,
2303-- SS ,
2304-- [RefResult] ,
2305-- [AvgPC] ,
2306-- [AvgPercentPhoto] ,
2307-- [AvgPercentDisplay] ,
2308-- StartDate ,
2309-- EndDate ,
2310-- NumHLMin ,
2311-- NumHLMax ,
2312-- PercentSSMin ,
2313-- PercentASMMin ,
2314-- PercentRSMMin ,
2315-- IsNew ,
2316-- CreatedDateTime ,
2317-- IsWWNext ,
2318-- IsValidate ,
2319-- SortID
2320-- )
2321-- VALUES ( SOURCE.[RefNbr] ,
2322-- SOURCE.[Year] ,
2323-- SOURCE.[Week] ,
2324-- SOURCE.[RouteID] ,
2325-- SOURCE.SS ,
2326-- SOURCE.[RefResult] ,
2327-- SOURCE.[AvgPC] ,
2328-- SOURCE.[AvgPercentPhoto] ,
2329-- SOURCE.[AvgPercentDisplay] ,
2330-- SOURCE.StartDate ,
2331-- SOURCE.EndDate ,
2332-- SOURCE.NumHLMin ,
2333-- SOURCE.NumHLMax ,
2334-- SOURCE.PercentSSMin ,
2335-- SOURCE.PercentASMMin ,
2336-- SOURCE.PercentRSMMin ,
2337-- SOURCE.IsNew ,
2338-- SOURCE.CreatedDateTime ,
2339-- SOURCE.IsWWNext ,
2340-- SOURCE.IsValidate ,
2341-- SOURCE.SortID
2342-- ) --When there is a row that exists in target table and
2343----same record does not exist in source table
2344----then delete this record from target table
2345-- ;
2346-- SELECT @@ROWCOUNT;
2347-- DROP TABLE #Export;
2348-- DROP TABLE #temp1;
2349--------------------------Tinh du lieu cho SS
2350
2351-------------LUU DU LIEU KET QUA TAM
2352-- SELECT detail.RefNbr ,
2353-- header.EffectDate ,
2354-- detail.RefResult ,
2355-- detail.RegionID ,
2356-- detail.PercentSMLowMin ,
2357-- detail.PercentSMLowMax ,
2358-- detail.PercentSMStardardMin ,
2359-- detail.PercentSMStardardMax ,
2360-- detail.PercentSMGoodMin ,
2361-- detail.PercentSMGoodMax ,
2362-- detail.NumHLMin ,
2363-- detail.NumHLMax ,
2364-- header.[Type]
2365-- INTO #tempSS
2366-- FROM DMSHamResultsDetailSS detail
2367-- LEFT JOIN ( SELECT TOP 1
2368-- RefNbr ,
2369-- EffectDate ,
2370-- Status ,
2371-- Type
2372-- FROM DMSHamResultsHeader
2373-- WHERE Status = 'R'
2374-- AND CAST(EffectDate AS DATE) <= CAST(@StartOfWeek AS DATE)
2375-- ORDER BY RefNbr DESC
2376-- ) header ON detail.RefNbr = header.RefNbr
2377-- WHERE header.Status = 'R'
2378-- AND CAST(EffectDate AS DATE) <= CAST(@StartOfWeek AS DATE);
2379
2380------------------------------- Tinh so lieu vao bang SS
2381-- SELECT [RefNbr] ,
2382-- [Year] ,
2383-- [Week] ,
2384-- [SS] ,
2385-- [ASM] ,
2386-- [SumRoute] ,
2387-- [SMKem] ,
2388-- [AvgSMKem] ,
2389-- [SMTB] ,
2390-- [AvgSMTB] ,
2391-- [SMTot] ,
2392-- [AvgSMTot] ,
2393-- [NumHLMin] ,
2394-- [NumHLMax] ,
2395-- [RefResult] ,
2396-- IsNew ,
2397-- IsWWNext ,
2398-- IsValidate ,
2399-- PercentSSMin ,
2400-- SortID
2401-- INTO #ExportSS
2402-- FROM dbo.DMSHamResultsSS
2403-- WHERE 1 = 0;
2404------------------------ add vao bang tam
2405-- SELECT ViewSS.* ,
2406-- ROW_NUMBER() OVER ( PARTITION BY ViewSS.ASM ORDER BY ViewSS.RefResult ASC ) AS SortID
2407-- INTO #SSRS
2408-- FROM ( SELECT rs.[RefNbr] ,
2409-- rs.[Year] ,
2410-- rs.[Week] ,
2411-- rs.[SS] ,
2412-- rs.ASM ,
2413-- rs.[SumRoute] ,
2414-- rs.[Kem] AS SMKem ,
2415-- rs.AvgKem AS [AvgSMKem] ,
2416-- rs.TB AS [SMTB] ,
2417-- rs.AvgTB AS [AvgSMTB] ,
2418-- rs.Tot AS [SMTot] ,
2419-- rs.AvgTot AS [AvgSMTot] ,
2420-- ViewSS.NumHLMin AS [NumHLMin] ,
2421-- ViewSS.NumHLMin AS [NumHLMax] ,
2422-- rs.RefResult ,
2423-- CASE WHEN sf.CreatedDateTime IS NOT NULL
2424-- THEN CASE WHEN DATEDIFF(DAY,
2425-- sf.CreatedDateTime,
2426-- GETDATE()) > ISNULL(( SELECT DISTINCT
2427-- Number
2428-- FROM
2429-- THPDMS2.dbo.[DMSHamerViewGetSetting]
2430-- WHERE
2431-- ID = 'SMM'
2432-- ), 0) THEN 0
2433-- ELSE 1
2434-- END
2435-- ELSE 0
2436-- END AS IsNew ,
2437-- IsWWNext = CASE ( SELECT DISTINCT TOP 1
2438-- RegionID
2439-- FROM THPDMS2.dbo.DMSSFAssignment
2440-- WHERE EmployeeID = rs.SS
2441-- AND IsActive = 1
2442-- )
2443-- WHEN 6 THEN 0
2444-- WHEN 7 THEN 0
2445-- ELSE 0
2446-- END ,
2447-- IsValidate = CASE ( SELECT DISTINCT TOP 1
2448-- RegionID
2449-- FROM THPDMS2.dbo.DMSSFAssignment
2450-- WHERE EmployeeID = rs.SS
2451-- AND IsActive = 1
2452-- )
2453-- WHEN 6 THEN 0
2454-- WHEN 7 THEN 0
2455-- ELSE 0
2456-- END ,
2457-- PercentSSMin = ( SELECT DISTINCT TOP 1
2458-- PercentSSMin
2459-- FROM dbo.DMSHamResultsHeader
2460-- WHERE RefNbr = rs.[RefNbr]
2461-- )
2462-- FROM ( SELECT v1.* ,
2463-- getASM.* ,
2464-- ISNULL(( SELECT TOP 1
2465-- t.RefResult
2466-- FROM #tempSS t
2467-- WHERE
2468-- (
2469-- --DK KEM
2470-- ( ( ISNULL(v1.AvgKem,
2471-- 0) >= t.PercentSMLowMin
2472-- AND ISNULL(v1.AvgKem,
2473-- 0) < t.PercentSMLowMax
2474-- AND t.RefResult = 'A'
2475-- AND ( ( t.[Type] = 'R'
2476-- AND t.RegionID = sfa.RegionID
2477-- )
2478-- OR ( t.[Type] = 'A'
2479-- AND t.RegionID = sfa.RegionID
2480-- )
2481-- OR ( t.[Type] = 'D'
2482-- AND t.RegionID = sfa.RegionID
2483-- )
2484-- OR ( t.[Type] = 'N'
2485-- AND 1 = 1
2486-- )
2487-- )
2488-- )
2489-- AND ( ISNULL(v1.AvgTB,
2490-- 0) >= t.PercentSMStardardMin
2491-- AND ISNULL(v1.AvgTB,
2492-- 0) < t.PercentSMStardardMax
2493-- AND t.RefResult = 'A'
2494-- AND ( ( t.[Type] = 'R'
2495-- AND t.RegionID = sfa.RegionID
2496-- )
2497-- OR ( t.[Type] = 'A'
2498-- AND t.RegionID = sfa.RegionID
2499-- )
2500-- OR ( t.[Type] = 'D'
2501-- AND t.RegionID = sfa.RegionID
2502-- )
2503-- OR ( t.[Type] = 'N'
2504-- AND 1 = 1
2505-- )
2506-- )
2507-- )
2508-- AND ( ISNULL(v1.AvgTot,
2509-- 0) >= t.PercentSMGoodMin
2510-- AND ISNULL(v1.AvgTot,
2511-- 0) < t.PercentSMGoodMax
2512-- AND t.RefResult = 'A'
2513-- AND ( ( t.[Type] = 'R'
2514-- AND t.RegionID = sfa.RegionID
2515-- )
2516-- OR ( t.[Type] = 'A'
2517-- AND t.RegionID = sfa.RegionID
2518-- )
2519-- OR ( t.[Type] = 'D'
2520-- AND t.RegionID = sfa.RegionID
2521-- )
2522-- OR ( t.[Type] = 'N'
2523-- AND 1 = 1
2524-- )
2525-- )
2526-- )
2527-- )
2528-- --dk TRUNG BINH
2529-- OR ( ( ISNULL(v1.AvgKem,
2530-- 0) >= t.PercentSMLowMin
2531-- AND ISNULL(v1.AvgKem,
2532-- 0) < t.PercentSMLowMax
2533-- AND t.RefResult = 'C'
2534-- AND ( ( t.[Type] = 'R'
2535-- AND t.RegionID = sfa.RegionID
2536-- )
2537-- OR ( t.[Type] = 'A'
2538-- AND t.RegionID = sfa.RegionID
2539-- )
2540-- OR ( t.[Type] = 'D'
2541-- AND t.RegionID = sfa.RegionID
2542-- )
2543-- OR ( t.[Type] = 'N'
2544-- AND 1 = 1
2545-- )
2546-- )
2547-- )
2548-- AND ( ISNULL(v1.AvgTB,
2549-- 0) >= t.PercentSMStardardMin
2550-- AND ISNULL(v1.AvgTB,
2551-- 0) < t.PercentSMStardardMax
2552-- AND t.RefResult = 'C'
2553-- AND ( ( t.[Type] = 'R'
2554-- AND t.RegionID = sfa.RegionID
2555-- )
2556-- OR ( t.[Type] = 'A'
2557-- AND t.RegionID = sfa.RegionID
2558-- )
2559-- OR ( t.[Type] = 'D'
2560-- AND t.RegionID = sfa.RegionID
2561-- )
2562-- OR ( t.[Type] = 'N'
2563-- AND 1 = 1
2564-- )
2565-- )
2566-- )
2567-- AND ( ISNULL(v1.AvgTot,
2568-- 0) >= t.PercentSMGoodMin
2569-- AND ISNULL(v1.AvgTot,
2570-- 0) < t.PercentSMGoodMax
2571-- AND t.RefResult = 'C'
2572-- AND ( ( t.[Type] = 'R'
2573-- AND t.RegionID = sfa.RegionID
2574-- )
2575-- OR ( t.[Type] = 'A'
2576-- AND t.RegionID = sfa.RegionID
2577-- )
2578-- OR ( t.[Type] = 'D'
2579-- AND t.RegionID = sfa.RegionID
2580-- )
2581-- OR ( t.[Type] = 'N'
2582-- AND 1 = 1
2583-- )
2584-- )
2585-- )
2586-- )
2587-- OR ( ( ISNULL(v1.AvgKem,
2588-- 0) >= t.PercentSMLowMin
2589-- AND ISNULL(v1.AvgKem,
2590-- 0) < t.PercentSMLowMax
2591-- AND t.RefResult = 'D'
2592-- AND ( ( t.[Type] = 'R'
2593-- AND t.RegionID = sfa.RegionID
2594-- )
2595-- OR ( t.[Type] = 'A'
2596-- AND t.RegionID = sfa.RegionID
2597-- )
2598-- OR ( t.[Type] = 'D'
2599-- AND t.RegionID = sfa.RegionID
2600-- )
2601-- OR ( t.[Type] = 'N'
2602-- AND 1 = 1
2603-- )
2604-- )
2605-- )
2606-- AND ( ISNULL(v1.AvgTB,
2607-- 0) >= t.PercentSMStardardMin
2608-- AND ISNULL(v1.AvgTB,
2609-- 0) < t.PercentSMStardardMax
2610-- AND t.RefResult = 'D'
2611-- AND ( ( t.[Type] = 'R'
2612-- AND t.RegionID = sfa.RegionID
2613-- )
2614-- OR ( t.[Type] = 'A'
2615-- AND t.RegionID = sfa.RegionID
2616-- )
2617-- OR ( t.[Type] = 'D'
2618-- AND t.RegionID = sfa.RegionID
2619-- )
2620-- OR ( t.[Type] = 'N'
2621-- AND 1 = 1
2622-- )
2623-- )
2624-- )
2625-- AND ( ISNULL(v1.AvgTot,
2626-- 0) >= t.PercentSMGoodMin
2627-- AND ISNULL(v1.AvgTot,
2628-- 0) < t.PercentSMGoodMax
2629-- AND t.RefResult = 'D'
2630-- AND ( ( t.[Type] = 'R'
2631-- AND t.RegionID = sfa.RegionID
2632-- )
2633-- OR ( t.[Type] = 'A'
2634-- AND t.RegionID = sfa.RegionID
2635-- )
2636-- OR ( t.[Type] = 'D'
2637-- AND t.RegionID = sfa.RegionID
2638-- )
2639-- OR ( t.[Type] = 'N'
2640-- AND 1 = 1
2641-- )
2642-- )
2643-- )
2644-- )
2645-- )
2646-- ), 0) AS RefResult
2647-- FROM ( SELECT v1.RefNbr ,
2648-- v1.SS ,
2649-- v1.Year ,
2650-- v1.Week ,
2651-- ISNULL(v1.SumRoute,
2652-- 0) AS SumRoute ,
2653-- ISNULL(v2.CountRouteKem,
2654-- 0) AS Kem ,
2655-- ISNULL(( v2.CountRouteKem
2656-- * 100 )
2657-- / v1.SumRoute, 0) AS AvgKem ,
2658-- ISNULL(v3.CountRouteTB,
2659-- 0) AS TB ,
2660-- ISNULL(( v3.CountRouteTB
2661-- * 100 )
2662-- / v1.SumRoute, 0) AS AvgTB ,
2663-- ISNULL(v4.CountRouteTot,
2664-- 0) AS Tot ,
2665-- ISNULL(( v4.CountRouteTot
2666-- * 100 )
2667-- / v1.SumRoute, 0) AS AvgTot
2668-- FROM ( SELECT
2669-- temrs.RefNbr ,
2670-- temrs.Year ,
2671-- temrs.Week ,
2672-- temrs.SS ,
2673-- ISNULL(tsum.SumRoute,
2674-- 0) AS SumRoute
2675-- FROM
2676-- ( SELECT DISTINCT
2677-- rs.RefNbr ,
2678-- rs.Year ,
2679-- rs.Week ,
2680-- rs.SS
2681-- FROM
2682-- DMSHamResults AS rs
2683-- ) AS temrs
2684-- LEFT JOIN ( SELECT
2685-- rs.RefNbr ,
2686-- rs.Year ,
2687-- rs.Week ,
2688-- rs.SS ,
2689-- COUNT(rs.RouteID) AS SumRoute
2690-- FROM
2691-- DMSHamResults AS rs
2692-- GROUP BY rs.RefNbr ,
2693-- rs.Year ,
2694-- rs.Week ,
2695-- rs.SS
2696-- ) AS tsum ON temrs.RefNbr = tsum.RefNbr
2697-- AND temrs.Year = tsum.[Year]
2698-- AND temrs.Week = tsum.Week
2699-- AND temrs.SS = tsum.SS
2700-- ) AS v1
2701-- LEFT JOIN ( SELECT
2702-- rsk.RefNbr ,
2703-- rsk.Year ,
2704-- rsk.Week ,
2705-- rsk.SS ,
2706-- COUNT(rsk.RouteID) AS CountRouteKem
2707-- FROM
2708-- DMSHamResults AS rsk
2709-- WHERE
2710-- rsk.RefResult = 'A'
2711-- AND rsk.IsNew = 0
2712-- AND rsk.AvgPC > 0
2713-- GROUP BY rsk.RefNbr ,
2714-- rsk.Year ,
2715-- rsk.Week ,
2716-- rsk.SS
2717-- ) AS v2 ON v1.SS = v2.SS
2718-- AND v1.RefNbr = v2.RefNbr
2719-- AND v1.Week = v2.Week
2720-- AND v1.Year = v2.Year
2721-- LEFT JOIN ( SELECT
2722-- rsk.RefNbr ,
2723-- rsk.Year ,
2724-- rsk.Week ,
2725-- rsk.SS ,
2726-- COUNT(rsk.RouteID) AS CountRouteTB
2727-- FROM
2728-- DMSHamResults AS rsk
2729-- WHERE
2730-- rsk.RefResult = 'C'
2731-- AND rsk.IsNew = 0
2732-- AND rsk.AvgPC > 0
2733-- GROUP BY rsk.RefNbr ,
2734-- rsk.Year ,
2735-- rsk.Week ,
2736-- rsk.SS
2737-- ) AS v3 ON v1.SS = v3.SS
2738-- AND v1.RefNbr = v3.RefNbr
2739-- AND v1.Week = v3.Week
2740-- AND v1.Year = v3.Year
2741-- LEFT JOIN ( SELECT
2742-- rsk.RefNbr ,
2743-- rsk.Year ,
2744-- rsk.Week ,
2745-- rsk.SS ,
2746-- COUNT(rsk.RouteID) AS CountRouteTot
2747-- FROM
2748-- DMSHamResults AS rsk
2749-- WHERE
2750-- rsk.RefResult = 'D'
2751-- AND rsk.IsNew = 0
2752-- AND rsk.AvgPC > 0
2753-- GROUP BY rsk.RefNbr ,
2754-- rsk.Year ,
2755-- rsk.Week ,
2756-- rsk.SS
2757-- ) AS v4 ON v1.SS = v4.SS
2758-- AND v1.RefNbr = v4.RefNbr
2759-- AND v1.Week = v4.Week
2760-- AND v1.Year = v4.Year
2761-- ) AS v1
2762-- LEFT JOIN THPDMS2.dbo.[DMSHamerViewSSASM]
2763-- AS getASM ON v1.SS = getASM.EmployeeID
2764-- LEFT JOIN ( SELECT DISTINCT
2765-- RegionID ,
2766-- EmployeeID
2767-- FROM
2768-- THPDMS2.dbo.DMSSFAssignment
2769-- WHERE
2770-- IsActive = 1
2771-- ) sfa ON sfa.EmployeeID = v1.SS
2772-- ) AS rs
2773-- LEFT JOIN THPDMS2.dbo.DMSSalesForces sf ON rs.SS = sf.EmployeeID
2774-- LEFT JOIN DMSHamResultsHeader AS header ON header.RefNbr = rs.RefNbr
2775-- LEFT JOIN DMSHamResultsDetailSS AS ViewSS ON rs.RefResult = ViewSS.RefResult
2776-- AND rs.RefNbr = ViewSS.RefNbr
2777-- AND ( header.[Type] = 'R'
2778-- AND rs.RegionID = ViewSS.RegionID
2779-- )
2780-- OR ( header.[Type] = 'A'
2781-- AND rs.RegionID = ViewSS.RegionID
2782-- )
2783-- OR ( header.[Type] = 'D'
2784-- AND rs.RegionID = ViewSS.RegionID
2785-- )
2786-- OR ( header.[Type] = 'N'
2787-- AND 1 = 1
2788-- )
2789-- WHERE ( rs.SS IS NOT NULL
2790-- OR rs.SS = 0
2791-- )
2792-- AND rs.Year = @Year
2793-- AND rs.Week = @Week
2794-- AND rs.ASM IS NOT NULL
2795-- AND rs.AreaID IS NOT NULL
2796-- ) AS ViewSS;
2797----- SELECT UPDATED OR NOT INSERTED FROM ACU TO Hammer
2798-- INSERT INTO #ExportSS
2799-- ( [RefNbr] ,
2800-- [Year] ,
2801-- [Week] ,
2802-- [SS] ,
2803-- [ASM] ,
2804-- [SumRoute] ,
2805-- [SMKem] ,
2806-- [AvgSMKem] ,
2807-- [SMTB] ,
2808-- [AvgSMTB] ,
2809-- [SMTot] ,
2810-- [AvgSMTot] ,
2811-- [NumHLMin] ,
2812-- [NumHLMax] ,
2813-- [RefResult] ,
2814-- IsNew ,
2815-- IsWWNext ,
2816-- IsValidate ,
2817-- PercentSSMin ,
2818-- SortID
2819
2820-- )
2821-- ( SELECT *
2822-- FROM #SSRS
2823-- );
2824-- WITH TARGET
2825-- AS ( SELECT [RefNbr] ,
2826-- [Year] ,
2827-- [Week] ,
2828-- [SS] ,
2829-- [ASM] ,
2830-- [SumRoute] ,
2831-- [SMKem] ,
2832-- [AvgSMKem] ,
2833-- [SMTB] ,
2834-- [AvgSMTB] ,
2835-- [SMTot] ,
2836-- [AvgSMTot] ,
2837-- [NumHLMin] ,
2838-- [NumHLMax] ,
2839-- [RefResult] ,
2840-- IsNew ,
2841-- IsWWNext ,
2842-- IsValidate ,
2843-- PercentSSMin ,
2844-- SortID
2845-- FROM [dbo].DMSHamResultsSS
2846-- WHERE Year = @Year
2847-- AND Week = @Week
2848-- )
2849-- MERGE INTO TARGET
2850-- USING #ExportSS AS SOURCE
2851-- ON ( TARGET.RefNbr = ISNULL(SOURCE.RefNbr, '')
2852-- AND TARGET.[Year] = ISNULL(SOURCE.[Year], '')
2853-- AND TARGET.[Week] = ISNULL(SOURCE.[Week], 0)
2854-- AND TARGET.SS = ISNULL(SOURCE.SS, '')
2855-- AND TARGET.ASM = ISNULL(SOURCE.ASM, '')
2856-- )
2857
2858
2859----When records are matched, update
2860----the records if there is any change
2861-- WHEN MATCHED AND
2862-- TARGET.RefResult <> SOURCE.RefResult
2863-- OR ISNULL(TARGET.SumRoute, 0) <> ISNULL(SOURCE.SumRoute,
2864-- 0)
2865-- OR ISNULL(TARGET.SMKem, 0) < ISNULL(SOURCE.SMKem, 0)
2866-- OR ISNULL(TARGET.[AvgSMKem], 0) < ISNULL(SOURCE.[AvgSMKem],
2867-- 0)
2868-- OR ISNULL(TARGET.[SMTB], 0) < ISNULL(SOURCE.[SMTB], 0)
2869-- OR ISNULL(TARGET.[AvgSMTB], 0) < ISNULL(SOURCE.[AvgSMTB],
2870-- 0)
2871-- OR ISNULL(TARGET.[SMTot], 0) < ISNULL(SOURCE.[SMTot],
2872-- 0)
2873-- OR ISNULL(TARGET.[AvgSMTot], 0) < ISNULL(SOURCE.[AvgSMTot],
2874-- 0)
2875-- OR ISNULL(TARGET.ASM, '') <> ISNULL(SOURCE.ASM, '')
2876-- OR ISNULL(TARGET.NumHLMin, 0) <> ISNULL(SOURCE.NumHLMin,
2877-- 0)
2878-- OR ISNULL(TARGET.NumHLMax, 0) <> ISNULL(SOURCE.NumHLMax,
2879-- 0)
2880-- OR ISNULL(TARGET.IsNew, 0) <> ISNULL(SOURCE.IsNew, 0)
2881-- OR ISNULL(TARGET.IsWWNext, 0) <> ISNULL(SOURCE.IsWWNext,
2882-- 0)
2883-- OR ISNULL(TARGET.IsValidate, 0) <> ISNULL(SOURCE.IsValidate,
2884-- 0)
2885-- OR ISNULL(TARGET.PercentSSMin, 0) <> ISNULL(SOURCE.PercentSSMin,
2886-- 0)
2887-- OR ISNULL(TARGET.SortID, 0) <> ISNULL(SOURCE.SortID, 0)
2888-- THEN
2889-- UPDATE SET TARGET.RefResult = SOURCE.RefResult ,
2890-- TARGET.SumRoute = SOURCE.SumRoute ,
2891-- TARGET.SMKem = SOURCE.SMKem ,
2892-- TARGET.[AvgSMKem] = SOURCE.[AvgSMKem] ,
2893-- TARGET.[SMTB] = SOURCE.[SMTB] ,
2894-- TARGET.[AvgSMTB] = SOURCE.[AvgSMTB] ,
2895-- TARGET.[SMTot] = SOURCE.[SMTot] ,
2896-- TARGET.[AvgSMTot] = SOURCE.[AvgSMTot] ,
2897-- TARGET.ASM = SOURCE.ASM ,
2898-- TARGET.NumHLMin = SOURCE.NumHLMin ,
2899-- TARGET.NumHLMax = SOURCE.NumHLMax ,
2900-- TARGET.IsNew = SOURCE.IsNew ,
2901-- TARGET.IsWWNext = SOURCE.IsWWNext ,
2902-- TARGET.IsValidate = SOURCE.IsValidate ,
2903-- TARGET.PercentSSMin = SOURCE.PercentSSMin ,
2904-- TARGET.SortID = SOURCE.SortID
2905----When no records are matched, insert
2906----the incoming records from source
2907----table to target table
2908-- WHEN NOT MATCHED BY TARGET THEN
2909-- INSERT ( [RefNbr] ,
2910-- [Year] ,
2911-- [Week] ,
2912-- [SS] ,
2913-- [ASM] ,
2914-- [SumRoute] ,
2915-- [SMKem] ,
2916-- [AvgSMKem] ,
2917-- [SMTB] ,
2918-- [AvgSMTB] ,
2919-- [SMTot] ,
2920-- [AvgSMTot] ,
2921-- [NumHLMin] ,
2922-- [NumHLMax] ,
2923-- [RefResult] ,
2924-- IsNew ,
2925-- IsWWNext ,
2926-- IsValidate ,
2927-- PercentSSMin ,
2928-- SortID
2929-- )
2930-- VALUES ( SOURCE.[RefNbr] ,
2931-- SOURCE.[Year] ,
2932-- SOURCE.[Week] ,
2933-- SOURCE.[SS] ,
2934-- SOURCE.[ASM] ,
2935-- SOURCE.[SumRoute] ,
2936-- SOURCE.[SMKem] ,
2937-- SOURCE.[AvgSMKem] ,
2938-- SOURCE.[SMTB] ,
2939-- SOURCE.[AvgSMTB] ,
2940-- SOURCE.[SMTot] ,
2941-- SOURCE.[AvgSMTot] ,
2942-- SOURCE.NumHLMin ,
2943-- SOURCE.NumHLMax ,
2944-- SOURCE.[RefResult] ,
2945-- SOURCE.IsNew ,
2946-- SOURCE.IsWWNext ,
2947-- SOURCE.IsValidate ,
2948-- SOURCE.PercentSSMin ,
2949-- SOURCE.SortID
2950-- ) --When there is a row that exists in target table and
2951----same record does not exist in source table
2952----then delete this record from target table
2953-- ;
2954-- SELECT @@ROWCOUNT;
2955-- DROP TABLE #ExportSS;
2956-- DROP TABLE #tempSS;
2957
2958
2959--------------------------------- Tinh so lieu vao bang ASM
2960-------------LUU DU LIEU KET QUA TAM
2961-- SELECT detail.RefNbr ,
2962-- header.EffectDate ,
2963-- detail.RefResult ,
2964-- detail.RegionID ,
2965-- detail.PercentSSLowMin ,
2966-- detail.PercentSSLowMax ,
2967-- detail.PercentSSStardardMin ,
2968-- detail.PercentSSStardardMax ,
2969-- detail.PercentSSGoodMin ,
2970-- detail.PercentSSGoodMax ,
2971-- detail.NumHLMin ,
2972-- detail.NumHLMax ,
2973-- header.[Type]
2974-- INTO #tempASM
2975-- FROM DMSHamResultsDetailASM detail
2976-- LEFT JOIN ( SELECT TOP 1
2977-- RefNbr ,
2978-- EffectDate ,
2979-- Status ,
2980-- Type
2981-- FROM DMSHamResultsHeader
2982-- WHERE Status = 'R'
2983-- AND CAST(EffectDate AS DATE) <= CAST(@StartOfWeek AS DATE)
2984-- ORDER BY RefNbr DESC
2985-- ) header ON detail.RefNbr = header.RefNbr
2986-- WHERE header.Status = 'R'
2987-- AND CAST(EffectDate AS DATE) <= CAST(@StartOfWeek AS DATE);
2988
2989------------------------------- Tinh so lieu vao bang ASM
2990
2991
2992-- SELECT *
2993-- INTO #ExportASM
2994-- FROM dbo.DMSHamResultsASM
2995-- WHERE 1 = 0;
2996--------------------Them du lieu vao bang tam
2997-- SELECT ViewASM.* ,
2998-- ROW_NUMBER() OVER ( PARTITION BY ViewASM.RSM ORDER BY ViewASM.RefResult ASC ) AS SortID
2999-- INTO #ASMRS
3000-- FROM ( SELECT rs1.[RefNbr] ,
3001-- rs1.[Year] ,
3002-- rs1.[Week] ,
3003-- rs1.[ASM] ,
3004-- rs1.RSM ,
3005-- rs1.[SumSS] ,
3006-- rs1.[Kem] AS SMKem ,
3007-- rs1.AvgKem AS [AvgSMKem] ,
3008-- rs1.TB AS [SMTB] ,
3009-- rs1.AvgTB AS [AvgSMTB] ,
3010-- rs1.Tot AS [SMTot] ,
3011-- rs1.AvgTot AS [AvgSMTot] ,
3012-- ViewASM.NumHLMin AS [NumHLMin] ,
3013-- ViewASM.NumHLMax AS [NumHLMax] ,
3014-- rs1.RefResult ,
3015-- IsWWNext = CASE ( SELECT DISTINCT TOP 1
3016-- RegionID
3017-- FROM THPDMS2.dbo.DMSSFAssignment
3018-- WHERE EmployeeID = rs1.[ASM]
3019-- AND IsActive = 1
3020-- )
3021-- WHEN 6 THEN 0
3022-- WHEN 7 THEN 0
3023-- ELSE 0
3024-- END ,
3025-- IsValidate = CASE ( SELECT DISTINCT TOP 1
3026-- RegionID
3027-- FROM THPDMS2.dbo.DMSSFAssignment
3028-- WHERE EmployeeID = rs1.[ASM]
3029-- AND IsActive = 1
3030-- )
3031-- WHEN 6 THEN 0
3032-- WHEN 7 THEN 0
3033-- ELSE 0
3034-- END ,
3035-- NumberSSMin = ( SELECT [Number]
3036-- FROM [192.168.1.14].[Hammer].[dbo].[SystemSetting]
3037-- WHERE ID = 'MinSS'
3038-- ) ,
3039-- PercentASMMin = ( SELECT DISTINCT TOP 1
3040-- PercentASMMin
3041-- FROM dbo.DMSHamResultsHeader
3042-- WHERE RefNbr = rs1.[RefNbr]
3043-- )
3044-- FROM ( SELECT tem1.* ,
3045-- getRSM.RSM ,
3046-- getRSM.RegionID ,
3047-- ISNULL(( SELECT TOP 1
3048-- t.RefResult
3049-- FROM #tempASM t
3050-- WHERE
3051-- (
3052-- --DK KEM
3053-- ( ( ISNULL(tem1.AvgKem,
3054-- 0) >= t.PercentSSLowMin
3055-- AND ISNULL(tem1.AvgKem,
3056-- 0) < t.PercentSSLowMax
3057-- AND t.RefResult = 'A'
3058-- AND ( ( t.[Type] = 'R'
3059-- AND t.RegionID = getRSM.RegionID
3060-- )
3061-- OR ( t.[Type] = 'A'
3062-- AND t.RegionID = getRSM.RegionID
3063-- )
3064-- OR ( t.[Type] = 'D'
3065-- AND t.RegionID = getRSM.RegionID
3066-- )
3067-- OR ( t.[Type] = 'N'
3068-- AND 1 = 1
3069-- )
3070-- )
3071-- )
3072-- AND ( ISNULL(tem1.AvgTB,
3073-- 0) >= t.PercentSSStardardMin
3074-- AND ISNULL(tem1.AvgTB,
3075-- 0) < t.PercentSSStardardMax
3076-- AND t.RefResult = 'A'
3077-- AND ( ( t.[Type] = 'R'
3078-- AND t.RegionID = getRSM.RegionID
3079-- )
3080-- OR ( t.[Type] = 'A'
3081-- AND t.RegionID = getRSM.RegionID
3082-- )
3083-- OR ( t.[Type] = 'D'
3084-- AND t.RegionID = getRSM.RegionID
3085-- )
3086-- OR ( t.[Type] = 'N'
3087-- AND 1 = 1
3088-- )
3089-- )
3090-- )
3091-- AND ( ISNULL(tem1.AvgTot,
3092-- 0) >= t.PercentSSGoodMin
3093-- AND ISNULL(tem1.AvgTot,
3094-- 0) < t.PercentSSGoodMax
3095-- AND t.RefResult = 'A'
3096-- AND ( ( t.[Type] = 'R'
3097-- AND t.RegionID = getRSM.RegionID
3098-- )
3099-- OR ( t.[Type] = 'A'
3100-- AND t.RegionID = getRSM.RegionID
3101-- )
3102-- OR ( t.[Type] = 'D'
3103-- AND t.RegionID = getRSM.RegionID
3104-- )
3105-- OR ( t.[Type] = 'N'
3106-- AND 1 = 1
3107-- )
3108-- )
3109-- )
3110-- )
3111-- --dk TRUNG BINH
3112-- OR ( ( ISNULL(tem1.AvgKem,
3113-- 0) >= t.PercentSSLowMin
3114-- AND ISNULL(tem1.AvgKem,
3115-- 0) < t.PercentSSLowMax
3116-- AND t.RefResult = 'C'
3117-- AND ( ( t.[Type] = 'R'
3118-- AND t.RegionID = getRSM.RegionID
3119-- )
3120-- OR ( t.[Type] = 'A'
3121-- AND t.RegionID = getRSM.RegionID
3122-- )
3123-- OR ( t.[Type] = 'D'
3124-- AND t.RegionID = getRSM.RegionID
3125-- )
3126-- OR ( t.[Type] = 'N'
3127-- AND 1 = 1
3128-- )
3129-- )
3130-- )
3131-- AND ( ISNULL(tem1.AvgTB,
3132-- 0) >= t.PercentSSStardardMin
3133-- AND ISNULL(tem1.AvgTB,
3134-- 0) < t.PercentSSStardardMax
3135-- AND t.RefResult = 'C'
3136-- AND ( ( t.[Type] = 'R'
3137-- AND t.RegionID = getRSM.RegionID
3138-- )
3139-- OR ( t.[Type] = 'A'
3140-- AND t.RegionID = getRSM.RegionID
3141-- )
3142-- OR ( t.[Type] = 'D'
3143-- AND t.RegionID = getRSM.RegionID
3144-- )
3145-- OR ( t.[Type] = 'N'
3146-- AND 1 = 1
3147-- )
3148-- )
3149-- )
3150-- AND ( ISNULL(tem1.AvgTot,
3151-- 0) >= t.PercentSSGoodMin
3152-- AND ISNULL(tem1.AvgTot,
3153-- 0) < t.PercentSSGoodMax
3154-- AND t.RefResult = 'C'
3155-- AND ( ( t.[Type] = 'R'
3156-- AND t.RegionID = getRSM.RegionID
3157-- )
3158-- OR ( t.[Type] = 'A'
3159-- AND t.RegionID = getRSM.RegionID
3160-- )
3161-- OR ( t.[Type] = 'D'
3162-- AND t.RegionID = getRSM.RegionID
3163-- )
3164-- OR ( t.[Type] = 'N'
3165-- AND 1 = 1
3166-- )
3167-- )
3168-- )
3169-- )
3170-- OR ( ( ISNULL(tem1.AvgKem,
3171-- 0) >= t.PercentSSLowMin
3172-- AND ISNULL(tem1.AvgKem,
3173-- 0) < t.PercentSSLowMax
3174-- AND t.RefResult = 'D'
3175-- AND ( ( t.[Type] = 'R'
3176-- AND t.RegionID = getRSM.RegionID
3177-- )
3178-- OR ( t.[Type] = 'A'
3179-- AND t.RegionID = getRSM.RegionID
3180-- )
3181-- OR ( t.[Type] = 'D'
3182-- AND t.RegionID = getRSM.RegionID
3183-- )
3184-- OR ( t.[Type] = 'N'
3185-- AND 1 = 1
3186-- )
3187-- )
3188-- )
3189-- AND ( ISNULL(tem1.AvgTB,
3190-- 0) >= t.PercentSSStardardMin
3191-- AND ISNULL(tem1.AvgTB,
3192-- 0) < t.PercentSSStardardMax
3193-- AND t.RefResult = 'D'
3194-- AND ( ( t.[Type] = 'R'
3195-- AND t.RegionID = getRSM.RegionID
3196-- )
3197-- OR ( t.[Type] = 'A'
3198-- AND t.RegionID = getRSM.RegionID
3199-- )
3200-- OR ( t.[Type] = 'D'
3201-- AND t.RegionID = getRSM.RegionID
3202-- )
3203-- OR ( t.[Type] = 'N'
3204-- AND 1 = 1
3205-- )
3206-- )
3207-- )
3208-- AND ( ISNULL(tem1.AvgTot,
3209-- 0) >= t.PercentSSGoodMin
3210-- AND ISNULL(tem1.AvgTot,
3211-- 0) < t.PercentSSGoodMax
3212-- AND t.RefResult = 'D'
3213-- AND ( ( t.[Type] = 'R'
3214-- AND t.RegionID = getRSM.RegionID
3215-- )
3216-- OR ( t.[Type] = 'A'
3217-- AND t.RegionID = getRSM.RegionID
3218-- )
3219-- OR ( t.[Type] = 'D'
3220-- AND t.RegionID = getRSM.RegionID
3221-- )
3222-- OR ( t.[Type] = 'N'
3223-- AND 1 = 1
3224-- )
3225-- )
3226-- )
3227-- )
3228-- )
3229-- ), 0) AS RefResult
3230-- FROM ( SELECT v1.RefNbr ,
3231-- v1.ASM ,
3232-- v1.Year ,
3233-- v1.Week ,
3234-- ISNULL(v1.SumSS, 0) AS SumSS ,
3235-- ISNULL(v2.SumSSKem,
3236-- 0) AS Kem ,
3237-- ISNULL(( v2.SumSSKem
3238-- * 100 )
3239-- / v1.SumSS, 0) AS AvgKem ,
3240-- ISNULL(v3.SumSSTB,
3241-- 0) AS TB ,
3242-- ISNULL(( v3.SumSSTB
3243-- * 100 )
3244-- / v1.SumSS, 0) AS AvgTB ,
3245-- ISNULL(v4.SumSSTot,
3246-- 0) AS Tot ,
3247-- ISNULL(( v4.SumSSTot
3248-- * 100 )
3249-- / v1.SumSS, 0) AS AvgTot
3250-- FROM ( SELECT
3251-- rs.RefNbr ,
3252-- rs.Year ,
3253-- rs.Week ,
3254-- rs.ASM ,
3255-- COUNT(rs.SS) AS SumSS
3256-- FROM
3257-- DMSHamResultsSS
3258-- AS rs
3259-- WHERE
3260-- rs.IsNew = 0
3261-- GROUP BY rs.RefNbr ,
3262-- rs.Year ,
3263-- rs.Week ,
3264-- rs.ASM
3265-- ) AS v1
3266-- LEFT JOIN ( SELECT
3267-- rs.RefNbr ,
3268-- rs.Year ,
3269-- rs.Week ,
3270-- rs.ASM ,
3271-- COUNT(rs.SS) AS SumSSKem
3272-- FROM
3273-- DMSHamResultsSS
3274-- AS rs
3275-- WHERE
3276-- rs.RefResult = 'A'
3277-- AND rs.IsNew = 0
3278-- GROUP BY rs.RefNbr ,
3279-- rs.Year ,
3280-- rs.Week ,
3281-- rs.ASM
3282-- ) AS v2 ON v1.ASM = v2.ASM
3283-- AND v1.RefNbr = v2.RefNbr
3284-- AND v1.Week = v2.Week
3285-- AND v1.Year = v2.Year
3286-- LEFT JOIN ( SELECT
3287-- rs.RefNbr ,
3288-- rs.Year ,
3289-- rs.Week ,
3290-- rs.ASM ,
3291-- COUNT(rs.SS) AS SumSSTB
3292-- FROM
3293-- DMSHamResultsSS
3294-- AS rs
3295-- WHERE
3296-- rs.RefResult = 'C'
3297-- AND rs.IsNew = 0
3298-- GROUP BY rs.RefNbr ,
3299-- rs.Year ,
3300-- rs.Week ,
3301-- rs.ASM
3302-- ) AS v3 ON v1.ASM = v3.ASM
3303-- AND v1.RefNbr = v3.RefNbr
3304-- AND v1.Week = v3.Week
3305-- AND v1.Year = v3.Year
3306-- LEFT JOIN ( SELECT
3307-- rs.RefNbr ,
3308-- rs.Year ,
3309-- rs.Week ,
3310-- rs.ASM ,
3311-- COUNT(rs.SS) AS SumSSTot
3312-- FROM
3313-- DMSHamResultsSS
3314-- AS rs
3315-- WHERE
3316-- rs.RefResult = 'D'
3317-- AND rs.IsNew = 0
3318-- GROUP BY rs.RefNbr ,
3319-- rs.Year ,
3320-- rs.Week ,
3321-- rs.ASM
3322-- ) AS v4 ON v1.ASM = v4.ASM
3323-- AND v1.RefNbr = v4.RefNbr
3324-- AND v1.Week = v4.Week
3325-- AND v1.Year = v4.Year
3326-- ) AS tem1
3327-- LEFT JOIN ( SELECT DISTINCT
3328-- ASM ,
3329-- RSM ,
3330-- RegionID
3331-- FROM
3332-- THPDMS2.dbo.DMSHamerViewSSASM
3333-- ) AS getRSM ON tem1.ASM = getRSM.ASM
3334-- ) AS rs1
3335-- LEFT JOIN DMSHamResultsHeader AS header ON header.RefNbr = rs1.RefNbr
3336-- LEFT JOIN DMSHamResultsDetailASM AS ViewASM ON rs1.RefResult = ViewASM.RefResult
3337-- AND rs1.RefNbr = ViewASM.RefNbr
3338-- AND ( header.[Type] = 'R'
3339-- AND rs1.RegionID = ViewASM.RegionID
3340-- )
3341-- OR ( header.[Type] = 'A'
3342-- AND rs1.RegionID = ViewASM.RegionID
3343-- )
3344-- OR ( header.[Type] = 'D'
3345-- AND rs1.RegionID = ViewASM.RegionID
3346-- )
3347-- OR ( header.[Type] = 'N'
3348-- AND 1 = 1
3349-- )
3350-- WHERE rs1.ASM IS NOT NULL
3351-- AND rs1.Year = @Year
3352-- AND rs1.Week = @Week
3353-- ) AS ViewASM;
3354----- SELECT UPDATED OR NOT INSERTED FROM ACU TO Hammer
3355-- INSERT INTO #ExportASM
3356-- ( [RefNbr] ,
3357-- [Year] ,
3358-- [Week] ,
3359-- [ASM] ,
3360-- [RSM] ,
3361-- [SumSS] ,
3362-- [SSKem] ,
3363-- [AvgSSKem] ,
3364-- [SSTB] ,
3365-- [AvgSSTB] ,
3366-- [SSTot] ,
3367-- [AvgSSTot] ,
3368-- [NumHLMin] ,
3369-- [NumHLMax] ,
3370-- [RefResult] ,
3371-- IsWWNext ,
3372-- IsValidate ,
3373-- NumberSSMin ,
3374-- PercentASMMin ,
3375-- SortID
3376-- )
3377-- ( SELECT *
3378-- FROM #ASMRS
3379-- );
3380-- WITH TARGET
3381-- AS ( SELECT [RefNbr] ,
3382-- [Year] ,
3383-- [Week] ,
3384-- [ASM] ,
3385-- [RSM] ,
3386-- [SumSS] ,
3387-- [SSKem] ,
3388-- [AvgSSKem] ,
3389-- [SSTB] ,
3390-- [AvgSSTB] ,
3391-- [SSTot] ,
3392-- [AvgSSTot] ,
3393-- [NumHLMin] ,
3394-- [NumHLMax] ,
3395-- [RefResult] ,
3396-- IsWWNext ,
3397-- IsValidate ,
3398-- NumberSSMin ,
3399-- PercentASMMin ,
3400-- SortID
3401-- FROM [dbo].DMSHamResultsASM
3402-- WHERE Year = @Year
3403-- AND Week = @Week
3404-- )
3405-- MERGE INTO TARGET
3406-- USING #ExportASM AS SOURCE
3407-- ON ( TARGET.RefNbr = ISNULL(SOURCE.RefNbr, '')
3408-- AND TARGET.[Year] = ISNULL(SOURCE.[Year], '')
3409-- AND TARGET.[Week] = ISNULL(SOURCE.[Week], 0)
3410-- AND TARGET.ASM = ISNULL(SOURCE.ASM, '')
3411-- )
3412
3413----When records are matched, update
3414----the records if there is any change
3415-- WHEN MATCHED AND
3416-- TARGET.RefResult <> SOURCE.RefResult
3417-- OR ISNULL(TARGET.[SumSS], 0) <> ISNULL(SOURCE.[SumSS],
3418-- 0)
3419-- OR ISNULL(TARGET.SSKem, 0) < ISNULL(SOURCE.SSKem, 0)
3420-- OR ISNULL(TARGET.[AvgSSKem], 0) < ISNULL(SOURCE.[AvgSSKem],
3421-- 0)
3422-- OR ISNULL(TARGET.[SSTB], 0) < ISNULL(SOURCE.[SSTB], 0)
3423-- OR ISNULL(TARGET.[AvgSSTB], 0) < ISNULL(SOURCE.[AvgSSTB],
3424-- 0)
3425-- OR ISNULL(TARGET.[SSTot], 0) < ISNULL(SOURCE.[SSTot],
3426-- 0)
3427-- OR ISNULL(TARGET.[AvgSSTot], 0) < ISNULL(SOURCE.[AvgSSTot],
3428-- 0)
3429-- OR ISNULL(TARGET.RSM, '') <> ISNULL(SOURCE.RSM, '')
3430-- OR ISNULL(TARGET.NumHLMin, 0) <> ISNULL(SOURCE.NumHLMin,
3431-- 0)
3432-- OR ISNULL(TARGET.NumHLMax, 0) <> ISNULL(SOURCE.NumHLMax,
3433-- 0)
3434-- OR ISNULL(TARGET.IsWWNext, 0) <> ISNULL(SOURCE.IsWWNext,
3435-- 0)
3436-- OR ISNULL(TARGET.IsValidate, 0) <> ISNULL(SOURCE.IsValidate,
3437-- 0)
3438-- OR ISNULL(TARGET.NumberSSMin, 0) <> ISNULL(SOURCE.NumberSSMin,
3439-- 0)
3440-- OR ISNULL(TARGET.PercentASMMin, 0) <> ISNULL(SOURCE.PercentASMMin,
3441-- 0)
3442-- OR ISNULL(TARGET.SortID, 0) <> ISNULL(SOURCE.SortID, 0)
3443-- THEN
3444-- UPDATE SET TARGET.RefResult = SOURCE.RefResult ,
3445-- TARGET.SumSS = SOURCE.SumSS ,
3446-- TARGET.SSKem = SOURCE.SSKem ,
3447-- TARGET.[AvgSSKem] = SOURCE.[AvgSSKem] ,
3448-- TARGET.[SSTB] = SOURCE.[SSTB] ,
3449-- TARGET.[AvgSSTB] = SOURCE.[AvgSSTB] ,
3450-- TARGET.[SSTot] = SOURCE.[SSTot] ,
3451-- TARGET.[AvgSSTot] = SOURCE.[AvgSSTot] ,
3452-- TARGET.RSM = SOURCE.RSM ,
3453-- TARGET.NumHLMin = SOURCE.NumHLMin ,
3454-- TARGET.NumHLMax = SOURCE.NumHLMax ,
3455-- TARGET.IsWWNext = SOURCE.IsWWNext ,
3456-- TARGET.IsValidate = SOURCE.IsValidate ,
3457-- TARGET.SortID = SOURCE.SortID ,
3458-- TARGET.NumberSSMin = SOURCE.NumberSSMin ,
3459-- TARGET.PercentASMMin = SOURCE.PercentASMMin
3460----When no records are matched, insert
3461----the incoming records from source
3462----table to target table
3463-- WHEN NOT MATCHED BY TARGET THEN
3464-- INSERT ( [RefNbr] ,
3465-- [Year] ,
3466-- [Week] ,
3467-- [ASM] ,
3468-- [RSM] ,
3469-- [SumSS] ,
3470-- [SSKem] ,
3471-- [AvgSSKem] ,
3472-- [SSTB] ,
3473-- [AvgSSTB] ,
3474-- [SSTot] ,
3475-- [AvgSSTot] ,
3476-- [NumHLMin] ,
3477-- [NumHLMax] ,
3478-- [RefResult] ,
3479-- IsWWNext ,
3480-- IsValidate ,
3481-- NumberSSMin ,
3482-- PercentASMMin ,
3483-- SortID
3484-- )
3485-- VALUES ( SOURCE.[RefNbr] ,
3486-- SOURCE.[Year] ,
3487-- SOURCE.[Week] ,
3488-- SOURCE.[ASM] ,
3489-- SOURCE.[RSM] ,
3490-- SOURCE.[SumSS] ,
3491-- SOURCE.[SSKem] ,
3492-- SOURCE.[AvgSSKem] ,
3493-- SOURCE.[SSTB] ,
3494-- SOURCE.[AvgSSTB] ,
3495-- SOURCE.[SSTot] ,
3496-- SOURCE.[AvgSSTot] ,
3497-- SOURCE.NumHLMin ,
3498-- SOURCE.NumHLMax ,
3499-- SOURCE.[RefResult] ,
3500-- SOURCE.IsWWNext ,
3501-- SOURCE.IsValidate ,
3502-- SOURCE.NumberSSMin ,
3503-- SOURCE.PercentASMMin ,
3504-- SOURCE.SortID
3505-- ) --When there is a row that exists in target table and
3506----same record does not exist in source table
3507----then delete this record from target table
3508-- ;
3509-- SELECT @@ROWCOUNT;
3510-- DROP TABLE #ExportASM;
3511-- DROP TABLE #tempASM;
3512
3513----------------------------------
3514
3515
3516-- END;
3517
3518 END;
3519GO