Различия

Показаны различия между двумя версиями страницы.

--- external:pro_bonus:98_dop:sql_script [2026/04/29 12:11] – создано Алексей Коробов
+++ external:pro_bonus:98_dop:sql_script [2026/04/29 12:15] (текущий) – Алексей Коробов
@@ Строка 15: / Строка 15: @@
   AND telegram_fail_count > 0;
 </code>
+==== Скрипт, позволяющий посчитать через заказы какого официанта было большее начисление бонусов за период ====
+<code>
+WITH EmployeeBonuses AS (
+    SELECT
+        c.[id] as check_id,
+        EmployeeNode.value('@name', 'NVARCHAR(255)') AS EmployeeName,
+        EmployeeNode.value('@code', 'NVARCHAR(50)') AS EmployeeCode,
+        EmployeeNode.value('@role', 'NVARCHAR(10)') AS EmployeeRole,
+        EmployeeNode.value('@id', 'NVARCHAR(100)') AS EmployeeId,
+        (SELECT SUM(BonusNode.value('@sum', 'DECIMAL(18,2)'))
+         FROM c.xml_native.nodes('/CHECK/CHECKDATA/CHECKBONUSES/BONUS') AS T(BonusNode)
+        ) AS CheckBonusSum
+    FROM
+        [dbo].[check] c
+    CROSS APPLY
+        c.xml_native.nodes('/CHECK/CHECKDATA/CHECKPERSONS/PERSON') AS T(EmployeeNode)
+)
+SELECT
+    EmployeeName,
+    EmployeeCode,
+    EmployeeRole,
+    SUM(CheckBonusSum) AS TotalBonus,
+    COUNT(DISTINCT check_id) AS CheckCount
+FROM
+    EmployeeBonuses
+WHERE
+    CheckBonusSum IS NOT NULL
+GROUP BY
+    EmployeeName, EmployeeCode, EmployeeRole
+ORDER BY
+    TotalBonus DESC;
+</code>