1. 1
2. 2
3. 3
4. 4
5. 5
6. 6
7. 7
8. 8
9. 9
10. 10
11. 11
12. 12
13. 13
14. 14
15. 15
16. 16
17. 17
18. 18
19. 19
20. 20
21. 21
22. 22
23. 23
24. 24
25. 25
26. 26
27. 27
28. 28
29. 29
30. 30
31. 31
32. 32
33. 33
34. 34
35. 35
36. 36
37. 37
38. 38
39. 39
40. 40
41. 41
42. 42
43. 43
44. 44
45. 45
46. 46
47. 47
48. 48
49. 49
50. 50
51. 51
52. 52
53. 53
54. 54
55. 55
56. 56
57. 57
58. 58
59. 59
60. 60
61. 61
62. 62
63. 63
64. 64
65. 65
66. 66
67. 67
68. 68
69. 69
70. 70
71. 71
72. 72
73. 73
74. 74
75. 75
76. 76
77. 77
78. 78
79. 79
80. 80
81. 81
82. 82
83. 83
84. 84
85. 85
86. 86
87. 87
88. 88
89. 89
90. 90
91. 91
92. 92
93. 93
94. 94
95. 95
96. 96
97. 97
98. 98
99. 99
100. 100

SELECT
DISTINCT customer_contracts_ref._Description						AS Номер_договора,

CAST(dateadd(year, -2000, customer_contracts_ref._Fld418) as date)	AS Дата_договора,

TSBO_ref._Description											AS ЦБО,

trade_for_service._Number										AS Номер_сделки,

CAST(dateadd(year, -2000, customer_contracts_ref._Fld419) as date)	AS Дата_сделки,

service_ref._Description											AS Вид_услуги,

ISNULL(trade_for_service._Fld7941, 0)	AS Сумма_сделки,

ISNULL(STUFF(
	(SELECT ', ', _Number as [text()]
	FROM MTCKCenter.dbo._Document129	implementation_service_2
	WHERE implementation_service._Fld2613RRef = implementation_service_2._Fld2613RRef
	FOR XML PATH ('')), 1, 1, ''), 'ОТСУТСТВУЕТ')																		AS Номер_реализации,

ISNULL(STUFF(	
	(SELECT ', ',CAST(dateadd(year, -2000, trade_for_service._Fld16346) as date) as [text()]
	FROM MTCKCenter.dbo._Document129		implementation_service_2
	WHERE implementation_service._Fld2613RRef = implementation_service_2._Fld2613RRef
	FOR XML PATH ('')), 1, 1, ''), '-')																					AS Дата_реализации,

CAST(customer_ref._Description as varchar(50))			AS Контрагент,

CASE trade_for_service._Fld9233
	WHEN 0
		THEN 'нет'
	ELSE 'да'
		END																									AS УТП_сделка,

CASE implementation_service._Fld8224
	WHEN 0
		THEN 'да'
	ELSE 'нет'
		END																				AS УТП_реализация,

CASE
	WHEN implementation_service._Fld8224 = 0
		THEN SUM(ISNULL(CAST(implementation_service._Fld2632 as money), 0)) 
	ELSE '-'
		END																				AS Сумма_УТП,
	
ISNULL(shopping._Fld1772,0)																	AS Сумма_оплаты_плюс,

FROM
MTCKCenter.dbo._Document7939		trade_for_service

JOIN MTCKCenter.dbo._Reference29	  customer_contracts_ref
ON trade_for_service._Fld7945RRef =   customer_contracts_ref._IDRRef

JOIN MTCKCenter.dbo._Reference90	  service_ref
ON trade_for_service._Fld7942RRef =   service_ref._IDRRef

JOIN MTCKCenter.dbo._Reference93	  TSBO_ref
ON trade_for_service._Fld7947RRef =   TSBO_ref._IDRRef

LEFT JOIN MTCKCenter.dbo._Document129 implementation_service
ON trade_for_service._Fld7945RRef =   implementation_service._Fld2613RRef

JOIN MTCKCenter.dbo._Reference44	  customer_ref
ON trade_for_service._Fld7944RRef =   customer_ref._IDRRef

LEFT JOIN MTCKCenter.dbo._Document115	  shopping
ON trade_for_service._Fld7945RRef =   shopping._Fld1749RRef

LEFT JOIN MTCKCenter.dbo._Document104_VT1460 corrections1
ON trade_for_service._Fld7945RRef =	corrections1._Fld1463RRef

LEFT JOIN MTCKCenter.dbo._Document104_VT1476 corrections2
ON trade_for_service._Fld7945RRef = corrections2._Fld1479RRef

LEFT JOIN MTCKCenter.dbo._Document98  refunds
ON trade_for_service._Fld7944RRef =   refunds._Fld1199RRef

LEFT JOIN MTCKCenter.dbo._Document133_VT2880 write_off_to_
ON trade_for_service._Fld7945RRef = write_off_to_._Fld5602_RRRef

WHERE
	(_Fld7942RRef=0x9C8F02DA550767D34576572C765E712A -- уменьшение стоимости авто
OR	 _Fld7942RRef=0xB999001DD8B71C2811EA5143E3D901D3) -- доплата за а/м

AND	service_ref._Marked = 0x00
AND trade_for_service._Posted = 0x01

GROUP BY customer_contracts_ref._Description, trade_for_service._Posted,
customer_contracts_ref._Fld418, customer_contracts_ref._Fld419,
TSBO_ref._Description, service_ref._Description,
shopping._Fld1772, trade_for_service._Fld7941,
implementation_service._Fld2613RRef,
trade_for_service._Fld16346, customer_ref._Description,
trade_for_service._Fld9233, implementation_service._Fld8224,
implementation_service._Fld2632, trade_for_service._Fld8413,
corrections1._Fld8389, corrections2._Fld8390,
refunds._Fld1221, write_off_to_._Fld2887,
write_off_to_._Fld2888, trade_for_service._Number