daily pastebin goal
35%
SHARE
TWEET

Untitled

a guest Jan 18th, 2019 64 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ROW_NUMBER() OVER
  2.     (PARTITION BY CHECKSUM(BR_ProviderID, PA_AssignmentType, BR_AddressID, BR_AddressTypeID)
  3.     ORDER BY
  4.     PA_RSAssignmentOrder ASC,
  5.     EffectiveDate ASC,
  6.     TerminationDate DESC,
  7.     PA_ReimbursementScheduleID ASC
  8. ) AS RSAssignmentRank
  9.    
  10. RowID   BR_ProviderID   PA_ReimbursementScheduleID  PA_AssignmentType   BR_AddressID    BR_AddressTypeID    PA_RSAssignmentOrder    BR_AddressTerminationDate   EffectiveDate   TerminationDate RSAssignmentRank
  11. 7   D13Z0K6UHH  AJW Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   4/15/2004   12/31/2009  1
  12. 10  D13Z0K6UHH  AJW Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   4/15/2004   12/31/2009  2
  13. 12  D13Z0K6UHH  AJW Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   4/15/2004   12/31/2009  3
  14. 14  D13Z0K6UHH  AJW Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   4/15/2004   12/31/2009  4
  15. 268 D13Z0K6UHH  AJW Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   6/1/2007    12/31/2009  37
  16. 311 D13Z0K6UHH  H15 Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   1/1/2010    7/31/2010   38
  17. 343 D13Z0K6UHH  H15 Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   1/1/2010    7/31/2010   39
  18. 421 D13Z0K6UHH  H15 Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   1/1/2010    7/31/2010   60
  19. 653 D13Z0K6UHH  CZO Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   8/1/2010    3/14/2011   61
  20. 655 D13Z0K6UHH  CZO Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   8/1/2010    3/14/2011   62
  21. 730 D13Z0K6UHH  CZO Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   8/1/2010    3/14/2011   81
  22. 732 D13Z0K6UHH  CZO Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   8/1/2010    3/14/2011   82
  23. 763 D13Z0K6UHH  CZO Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   8/1/2010    3/14/2011   83
  24. 775 D2V50Z1NT5  C11 Standard    D2V50UH142  _REI0PVM65  1   1/1/2500    12/28/2009  12/31/2010  1
  25. 784 D2V50Z1NT5  C11 Standard    D2V50UH142  _REI0PVM65  2   1/1/2500    1/1/2011    5/17/2011   2
  26. 790 D2V50Z1NT5  F11 Standard    D2V50UH142  _REI0PVM65  2   1/1/2500    5/18/2011   9/25/2012   3
  27. 794 D2V50Z1NT5  C11 Standard    D2V50UH142  _REI0PVM65  2   1/1/2500    9/26/2012   1/1/2500    4
  28.    
  29. RowID   BR_ProviderID   PA_ReimbursementScheduleID  PA_AssignmentType   BR_AddressID    BR_AddressTypeID    PA_RSAssignmentOrder    BR_AddressTerminationDate   EffectiveDate   TerminationDate RSAssignmentRank
  30. 7   D13Z0K6UHH  AJW Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   4/15/2004   12/31/2009  1
  31. 311 D13Z0K6UHH  H15 Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   1/1/2010    7/31/2010   38
  32. 653 D13Z0K6UHH  CZO Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   8/1/2010    3/14/2011   61
  33. 775 D2V50Z1NT5  C11 Standard    D2V50UH142  _REI0PVM65  1   1/1/2500    12/28/2009  12/31/2010  1
  34. 784 D2V50Z1NT5  C11 Standard    D2V50UH142  _REI0PVM65  2   1/1/2500    1/1/2011    5/17/2011   2
  35. 790 D2V50Z1NT5  F11 Standard    D2V50UH142  _REI0PVM65  2   1/1/2500    5/18/2011   9/25/2012   3
  36. 794 D2V50Z1NT5  C11 Standard    D2V50UH142  _REI0PVM65  2   1/1/2500    9/26/2012   1/1/2500    4
  37.    
  38. WITH
  39.     cteRSFinder (
  40.         RowID,
  41.         BR_ProviderID,
  42.         PA_ReimbursementScheduleID,
  43.         PA_AssignmentType,
  44.         BR_AddressID,
  45.         BR_AddressTypeID,
  46.         PA_AddressID,
  47.         PA_RSAssignmentOrder,
  48.         PA_AddressTypeID,
  49.         BR_AddressTerminationDate,
  50.         EffectiveDate,
  51.         TerminationDate,
  52.         RSAssignmentRank
  53.     )
  54. AS (
  55.     SELECT
  56.         RowID,
  57.         BR_ProviderID,
  58.         PA_ReimbursementScheduleID,
  59.         PA_AssignmentType,
  60.         BR_AddressID,
  61.         BR_AddressTypeID,
  62.         PA_AddressID,
  63.         PA_RSAssignmentOrder,
  64.         PA_AddressTypeID,
  65.         BR_AddressTerminationDate,
  66.         EffectiveDate,
  67.         TerminationDate,
  68.         @RSAssignmentRank
  69.  
  70.     FROM
  71.         #TempAllPossibleRSAssignments taprsa
  72.  
  73.     WHERE
  74.         RSAssignmentRank = 1
  75.  
  76.     UNION ALL
  77.  
  78.         SELECT TOP 1
  79.             taprsa.RowID,
  80.             taprsa.BR_ProviderID,
  81.             taprsa.PA_ReimbursementScheduleID,
  82.             taprsa.PA_AssignmentType,
  83.             taprsa.BR_AddressID,
  84.             taprsa.BR_AddressTypeID,
  85.             taprsa.PA_AddressID,
  86.             taprsa.PA_RSAssignmentOrder,
  87.             taprsa.PA_AddressTypeID,
  88.             taprsa.BR_AddressTerminationDate,
  89.             taprsa.EffectiveDate,
  90.             taprsa.TerminationDate,
  91.             taprsa.RSAssignmentRank
  92.  
  93.         FROM
  94.             #TempAllPossibleRSAssignments taprsa
  95.  
  96.         INNER JOIN cteRSFinder cteLoop
  97.             ON
  98.             taprsa.BR_ProviderID        =   cteLoop.BR_ProviderID       AND
  99.             taprsa.PA_AssignmentType    =   cteLoop.PA_AssignmentType   AND
  100.             taprsa.BR_AddressID         =   cteLoop.BR_AddressID        AND
  101.             taprsa.BR_AddressTypeID     =   cteLoop.BR_AddressTypeID
  102.  
  103.         WHERE
  104.             taprsa.EffectiveDate = DateAdd( dd, 1, cteLoop.TerminationDate )
  105.             AND taprsa.RSAssignmentRank > cteLoop.RSAssignmentRank
  106. )
  107.  
  108. INSERT INTO #TempFinalRSAssignments
  109.     SELECT DISTINCT
  110.         cteRSFinder.BR_ProviderID               AS ProviderID,
  111.         cteRSFinder.PA_ReimbursementScheduleID  AS ReimbursementScheduleID,
  112.         cteRSFinder.PA_AssignmentType           AS AssignmentType,
  113.         cteRSFinder.BR_AddressID                AS AddressID,
  114.         cteRSFinder.BR_AddressTypeID            AS AddressTypeID,
  115.         cteRSFinder.EffectiveDate,
  116.         cteRSFinder.TerminationDate,
  117.         GetDate()                               AS MaintenanceDate,
  118.         0                                       AS RuleID,
  119.         cteRSFinder.BR_AddressTerminationDate,
  120.         cteRSFinder.RSAssignmentRank
  121.  
  122.     FROM
  123.         cteRSFinder
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top