API tools faq
paste
Advertisement
zzzzzzzzzzzzzzxz

SQL Code

zzzzzzzzzzzzzzxz
Dec 15th, 2024
29
0
22 days
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 6.63 KB | None | 0 0
raw download clone embed print report
  1. WITH
  2. ko AS
  3. (
  4. SELECT 1 id, 'Север' name FROM dual UNION ALL
  5. SELECT 2 id, 'Запад' name FROM dual UNION ALL
  6. SELECT 3 id, 'Восток' name FROM dual UNION ALL
  7. SELECT 4 id, 'Юг' name FROM dual
  8. ),
  9. ac AS
  10. (
  11. SELECT 1 ko, TO_DATE('01.01.2013','DD.MM.YYYY') oper_date, 100 amount, 1 direction FROM dual UNION ALL
  12. SELECT 1 ko, TO_DATE('11.07.2024','DD.MM.YYYY') oper_date, 30 amount, 0 direction FROM dual UNION ALL
  13. SELECT 1 ko, TO_DATE('01.08.2024','DD.MM.YYYY') oper_date, 230 amount, 1 direction FROM dual UNION ALL
  14. SELECT 1 ko, TO_DATE('11.08.2024','DD.MM.YYYY') oper_date, 40 amount, 0 direction FROM dual UNION ALL
  15. SELECT 1 ko, TO_DATE('12.08.2024','DD.MM.YYYY') oper_date, 100 amount, 1 direction FROM dual UNION ALL
  16. SELECT 1 ko, TO_DATE('13.08.2024','DD.MM.YYYY') oper_date, 40 amount, 0 direction FROM dual UNION ALL
  17. SELECT 1 ko, TO_DATE('14.08.2024','DD.MM.YYYY') oper_date, 90 amount, 1 direction FROM dual UNION ALL
  18. SELECT 1 ko, TO_DATE('01.09.2024','DD.MM.YYYY') oper_date, 180 amount, 0 direction FROM dual UNION ALL
  19. SELECT 1 ko, TO_DATE('02.09.2024','DD.MM.YYYY') oper_date, 90 amount, 1 direction FROM dual UNION ALL
  20. SELECT 1 ko, TO_DATE('11.09.2024','DD.MM.YYYY') oper_date, 180 amount, 0 direction FROM dual UNION ALL
  21. SELECT 1 ko, TO_DATE('01.10.2024','DD.MM.YYYY') oper_date, 400 amount, 1 direction FROM dual UNION ALL
  22. SELECT 1 ko, TO_DATE('11.10.2024','DD.MM.YYYY') oper_date, 100 amount, 0 direction FROM dual UNION ALL
  23. SELECT 1 ko, TO_DATE('13.10.2024','DD.MM.YYYY') oper_date, 400 amount, 1 direction FROM dual UNION ALL
  24. SELECT 1 ko, TO_DATE('01.11.2024','DD.MM.YYYY') oper_date, 120 amount, 1 direction FROM dual UNION ALL
  25. SELECT 1 ko, TO_DATE('11.11.2024','DD.MM.YYYY') oper_date, 310 amount, 0 direction FROM dual UNION ALL
  26. SELECT 1 ko, TO_DATE('12.11.2024','DD.MM.YYYY') oper_date, 100 amount, 0 direction FROM dual UNION ALL
  27. SELECT 1 ko, TO_DATE('21.11.2024','DD.MM.YYYY') oper_date, 120 amount, 1 direction FROM dual UNION ALL
  28. SELECT 1 ko, TO_DATE('22.12.2024','DD.MM.YYYY') oper_date, 120 amount, 1 direction FROM dual
  29. UNION ALL
  30. SELECT 2 ko, TO_DATE('01.02.2013','DD.MM.YYYY') oper_date, 2725 amount, 0 direction FROM dual UNION ALL
  31. SELECT 2 ko, TO_DATE('01.08.2024','DD.MM.YYYY') oper_date, 725 amount, 1 direction FROM dual UNION ALL
  32. SELECT 2 ko, TO_DATE('11.08.2024','DD.MM.YYYY') oper_date, 40 amount, 1 direction FROM dual UNION ALL
  33. SELECT 2 ko, TO_DATE('01.09.2024','DD.MM.YYYY') oper_date, 90 amount, 1 direction FROM dual UNION ALL
  34. SELECT 2 ko, TO_DATE('11.09.2024','DD.MM.YYYY') oper_date, 180 amount, 1 direction FROM dual UNION ALL
  35. SELECT 2 ko, TO_DATE('01.10.2024','DD.MM.YYYY') oper_date, 100 amount, 1 direction FROM dual UNION ALL
  36. SELECT 2 ko, TO_DATE('11.10.2024','DD.MM.YYYY') oper_date, 380 amount, 1 direction FROM dual UNION ALL
  37. SELECT 2 ko, TO_DATE('01.11.2024','DD.MM.YYYY') oper_date, 120 amount, 1 direction FROM dual UNION ALL
  38. SELECT 2 ko, TO_DATE('11.11.2024','DD.MM.YYYY') oper_date, 480 amount, 1 direction FROM dual UNION ALL
  39. SELECT 2 ko, TO_DATE('21.11.2024','DD.MM.YYYY') oper_date, 80 amount, 1 direction FROM dual
  40. UNION ALL
  41. SELECT 3 ko, TO_DATE('01.07.2024','DD.MM.YYYY') oper_date, 125 amount, 1 direction FROM dual UNION ALL
  42. SELECT 3 ko, TO_DATE('11.07.2024','DD.MM.YYYY') oper_date, 80 amount, 0 direction FROM dual UNION ALL
  43. SELECT 3 ko, TO_DATE('01.08.2024','DD.MM.YYYY') oper_date, 100 amount, 1 direction FROM dual UNION ALL
  44. SELECT 3 ko, TO_DATE('11.08.2024','DD.MM.YYYY') oper_date, 40 amount, 0 direction FROM dual UNION ALL
  45. SELECT 3 ko, TO_DATE('01.09.2024','DD.MM.YYYY') oper_date, 90 amount, 1 direction FROM dual UNION ALL
  46. SELECT 3 ko, TO_DATE('11.09.2024','DD.MM.YYYY') oper_date, 180 amount, 0 direction FROM dual UNION ALL
  47. SELECT 3 ko, TO_DATE('12.09.2024','DD.MM.YYYY') oper_date, 10 amount, 0 direction FROM dual UNION ALL
  48. SELECT 3 ko, TO_DATE('01.10.2024','DD.MM.YYYY') oper_date, 120 amount, 1 direction FROM dual UNION ALL
  49. SELECT 3 ko, TO_DATE('01.11.2024','DD.MM.YYYY') oper_date, 80 amount, 1 direction FROM dual UNION ALL
  50. SELECT 3 ko, TO_DATE('01.12.2024','DD.MM.YYYY') oper_date, 80 amount, 0 direction FROM dual
  51. UNION ALL
  52. SELECT 4 ko, TO_DATE('01.07.2024','DD.MM.YYYY') oper_date, 120 amount, 1 direction FROM dual UNION ALL
  53. SELECT 4 ko, TO_DATE('11.07.2024','DD.MM.YYYY') oper_date, 480 amount, 1 direction FROM dual UNION ALL
  54. SELECT 4 ko, TO_DATE('01.08.2024','DD.MM.YYYY') oper_date, 90 amount, 0 direction FROM dual UNION ALL
  55. SELECT 4 ko, TO_DATE('11.08.2024','DD.MM.YYYY') oper_date, 180 amount, 1 direction FROM dual UNION ALL
  56. SELECT 4 ko, TO_DATE('01.11.2024','DD.MM.YYYY') oper_date, 100 amount, 0 direction FROM dual UNION ALL
  57. SELECT 4 ko, TO_DATE('21.11.2024','DD.MM.YYYY') oper_date, 80 amount, 1 direction FROM dual
  58. )
  59. SELECT
  60.   ko.name AS Контрагент,
  61.  
  62.   SUM(CASE WHEN TO_CHAR(ac.oper_date, 'MM.YYYY') = TO_CHAR(ADD_MONTHS(CURRENT_DATE, -5), 'MM.YYYY') AND ac.direction = 1 THEN ac.amount
  63.            WHEN TO_CHAR(ac.oper_date, 'MM.YYYY') = TO_CHAR(ADD_MONTHS(CURRENT_DATE, -5), 'MM.YYYY') AND ac.direction = 0 THEN -ac.amount
  64.            ELSE 0 END) AS Июль,
  65.  
  66.   SUM(CASE WHEN TO_CHAR(ac.oper_date, 'MM.YYYY') = TO_CHAR(ADD_MONTHS(CURRENT_DATE, -4), 'MM.YYYY') AND ac.direction = 1 THEN ac.amount
  67.            WHEN TO_CHAR(ac.oper_date, 'MM.YYYY') = TO_CHAR(ADD_MONTHS(CURRENT_DATE, -4), 'MM.YYYY') AND ac.direction = 0 THEN -ac.amount
  68.            ELSE 0 END) AS Август,
  69.  
  70.   SUM(CASE WHEN TO_CHAR(ac.oper_date, 'MM.YYYY') = TO_CHAR(ADD_MONTHS(CURRENT_DATE, -3), 'MM.YYYY') AND ac.direction = 1 THEN ac.amount
  71.            WHEN TO_CHAR(ac.oper_date, 'MM.YYYY') = TO_CHAR(ADD_MONTHS(CURRENT_DATE, -3), 'MM.YYYY') AND ac.direction = 0 THEN -ac.amount
  72.            ELSE 0 END) AS Сентябрь,
  73.  
  74.   SUM(CASE WHEN TO_CHAR(ac.oper_date, 'MM.YYYY') = TO_CHAR(ADD_MONTHS(CURRENT_DATE, -2), 'MM.YYYY') AND ac.direction = 1 THEN ac.amount
  75.            WHEN TO_CHAR(ac.oper_date, 'MM.YYYY') = TO_CHAR(ADD_MONTHS(CURRENT_DATE, -2), 'MM.YYYY') AND ac.direction = 0 THEN -ac.amount
  76.            ELSE 0 END) AS Октябрь,
  77.  
  78.   SUM(CASE WHEN TO_CHAR(ac.oper_date, 'MM.YYYY') = TO_CHAR(ADD_MONTHS(CURRENT_DATE, -1), 'MM.YYYY') AND ac.direction = 1 THEN ac.amount
  79.            WHEN TO_CHAR(ac.oper_date, 'MM.YYYY') = TO_CHAR(ADD_MONTHS(CURRENT_DATE, -1), 'MM.YYYY') AND ac.direction = 0 THEN -ac.amount
  80.            ELSE 0 END) AS Ноябрь,
  81.  
  82.   SUM(CASE WHEN TO_CHAR(ac.oper_date, 'MM.YYYY') = TO_CHAR(CURRENT_DATE, 'MM.YYYY') AND ac.direction = 1 THEN ac.amount
  83.            WHEN TO_CHAR(ac.oper_date, 'MM.YYYY') = TO_CHAR(CURRENT_DATE, 'MM.YYYY') AND ac.direction = 0 THEN -ac.amount
  84.            ELSE 0 END) AS Декабрь
  85.  
  86. FROM
  87.   ko
  88. LEFT JOIN ac ON ac.ko = ko.id
  89. GROUP BY
  90.   ko.name
  91. ORDER BY
  92.   ko.name;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!