- 001
- 002
- 003
- 004
- 005
- 006
- 007
- 008
- 009
- 010
- 011
- 012
- 013
- 014
- 015
- 016
- 017
- 018
- 019
- 020
- 021
- 022
- 023
- 024
- 025
- 026
- 027
- 028
- 029
- 030
- 031
- 032
- 033
- 034
- 035
- 036
- 037
- 038
- 039
- 040
- 041
- 042
- 043
- 044
- 045
- 046
- 047
- 048
- 049
- 050
- 051
- 052
- 053
- 054
- 055
- 056
- 057
- 058
- 059
- 060
- 061
- 062
- 063
- 064
- 065
- 066
- 067
- 068
- 069
- 070
- 071
- 072
- 073
- 074
- 075
- 076
- 077
- 078
- 079
- 080
- 081
- 082
- 083
- 084
- 085
- 086
- 087
- 088
- 089
- 090
- 091
- 092
- 093
- 094
- 095
- 096
- 097
- 098
- 099
- 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
Follow us!