Advertisement
bytecoded

TempDB Top 1 Query (1C)

Mar 28th, 2019
306
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 3.81 KB | None | 0 0
  1. SELECT
  2. Limits.LimitType AS LimitType,
  3. Limits.Amount AS Amount,
  4. Limits.CurrencyLimit AS CurrencyLimit,
  5. SUM(ISNULL(CASE
  6. WHEN Limits.Contract = VALUE(Catalog.CompanyContracts.EmptyRef)
  7. THEN CAST(ARWithCheques.AmountCurBalance * Rates.ExchangeRate * Limits.MultiplicityLimit / (Limits.RateLimit * ISNULL(Rates.Multiplicity, 1)) AS NUMBER(15, 2))
  8. ELSE ARWithCheques.AmountCurBalance
  9. END, 0)) AS AmountBalance,
  10. Limits.Contract AS Contract
  11. INTO LimitBalance
  12. FROM
  13. Limits AS Limits
  14. LEFT JOIN ARWithCheques AS ARWithCheques
  15. LEFT JOIN Rates AS Rates
  16. ON (ARWithCheques.Contract.AccountsCurrency = Rates.Currency)
  17. ON (CASE
  18. WHEN Limits.Contract = VALUE(Catalog.CompanyContracts.EmptyRef)
  19. THEN TRUE
  20. ELSE Limits.Contract = ARWithCheques.Contract
  21. END)
  22. WHERE
  23. Limits.LimitType = VALUE(Enum.LimitType.Debit)
  24.  
  25. GROUP BY
  26. Limits.LimitType,
  27. Limits.Amount,
  28. Limits.Contract,
  29. Limits.CurrencyLimit
  30.  
  31. UNION ALL
  32.  
  33. SELECT
  34. Limits.LimitType,
  35. Limits.Amount,
  36. Limits.CurrencyLimit,
  37. SUM(ISNULL(CASE
  38. WHEN Limits.Contract = VALUE(Catalog.CompanyContracts.EmptyRef)
  39. THEN CAST(VendorAccountsBalance.AmountCurBalance * Rates.ExchangeRate * Limits.MultiplicityLimit / (Limits.RateLimit * ISNULL(Rates.Multiplicity, 1)) AS NUMBER(15, 2))
  40. ELSE VendorAccountsBalance.AmountCurBalance
  41. END, 0)),
  42. Limits.Contract
  43. FROM
  44. Limits AS Limits
  45. LEFT JOIN AccumulationRegister.VendorAccounts.Balance(
  46. &Period,
  47. Entity = &Entity
  48. AND Company = &Company) AS VendorAccountsBalance
  49. LEFT JOIN Rates AS Rates
  50. ON VendorAccountsBalance.Contract.AccountsCurrency = Rates.Currency
  51. ON (CASE
  52. WHEN Limits.Contract = VALUE(Catalog.CompanyContracts.EmptyRef)
  53. THEN TRUE
  54. ELSE Limits.Contract = VendorAccountsBalance.Contract
  55. END)
  56. WHERE
  57. Limits.LimitType = VALUE(Enum.LimitType.Credit)
  58.  
  59. GROUP BY
  60. Limits.LimitType,
  61. Limits.Amount,
  62. Limits.Contract,
  63. Limits.CurrencyLimit
  64.  
  65. UNION ALL
  66.  
  67. SELECT
  68. Limits.LimitType,
  69. Limits.Amount,
  70. Limits.CurrencyLimit,
  71. SUM(ISNULL(CASE
  72. WHEN Limits.Contract = VALUE(Catalog.CompanyContracts.EmptyRef)
  73. THEN CAST(SalesByCustomerOrdersTurnovers.AmountCurTurnover * Rates.ExchangeRate * Limits.MultiplicityLimit / (Limits.RateLimit * ISNULL(Rates.Multiplicity, 1)) AS NUMBER(15, 2))
  74. ELSE SalesByCustomerOrdersTurnovers.AmountCurTurnover
  75. END, 0)),
  76. Limits.Contract
  77. FROM
  78. Limits AS Limits
  79. LEFT JOIN AccumulationRegister.SalesByCustomerOrders.Turnovers(
  80. ,
  81. &Period,
  82. ,
  83. Entity = &Entity
  84. AND Company = &Company) AS SalesByCustomerOrdersTurnovers
  85. LEFT JOIN Rates AS Rates
  86. ON SalesByCustomerOrdersTurnovers.Contract.AccountsCurrency = Rates.Currency
  87. ON (CASE
  88. WHEN Limits.Contract = VALUE(Catalog.CompanyContracts.EmptyRef)
  89. THEN TRUE
  90. ELSE Limits.Contract = SalesByCustomerOrdersTurnovers.Contract
  91. END)
  92. WHERE
  93. Limits.LimitType = VALUE(enum.limittype.income)
  94.  
  95. GROUP BY
  96. Limits.LimitType,
  97. Limits.Amount,
  98. Limits.Contract,
  99. Limits.CurrencyLimit
  100.  
  101. UNION ALL
  102.  
  103. SELECT
  104. Limits.LimitType,
  105. Limits.Amount,
  106. Limits.CurrencyLimit,
  107. SUM(ISNULL(CAST(PurchasingTurnovers.AmountTurnover * Rates.ExchangeRate * Limits.MultiplicityLimit / (Limits.RateLimit * ISNULL(Rates.Multiplicity, 1)) AS NUMBER(15, 2)), 0)),
  108. Limits.Contract
  109. FROM
  110. Limits AS Limits
  111. LEFT JOIN AccumulationRegister.Purchasing.Turnovers(
  112. ,
  113. &Period,
  114. ,
  115. Entity = &Entity
  116. AND Document.Company = &Company) AS PurchasingTurnovers
  117. LEFT JOIN Rates AS Rates
  118. ON (Rates.Currency = &RegCurr)
  119. ON (CASE
  120. WHEN Limits.Contract = VALUE(Catalog.CompanyContracts.EmptyRef)
  121. THEN TRUE
  122. ELSE Limits.Contract = PurchasingTurnovers.Document.Contract
  123. END)
  124. WHERE
  125. Limits.LimitType = VALUE(Enum.LimitType.Expense)
  126.  
  127. GROUP BY
  128. Limits.LimitType,
  129. Limits.Amount,
  130. Limits.Contract,
  131. Limits.CurrencyLimit
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement