Помогите пожалуйста с запросом

ainura · 21.03.2008

Я столкнулась с такой проблемой. Мне нужно разобраться в запросе который составляла не я. Я вроде как разобрала но у меня выходит ошибка может кто не будь поймет это по запросу. Буду безумно признательна.

[codebox](этот запрос уже переделанный мною)
DECLARE @F1 varchar(50), @F2 varchar(50), @F3 varchar(50), @F4 varchar(50), @F5 varchar(50), @F6 varchar(50), @Dep int, @Ind int, @FT varchar(1000), @F7 varchar(50), @F8 varchar(50), @F9 varchar(50)
SET @F1='=RC[2]+RC[4]+RC[6]+RC[8]+RC[10]+RC[12]'
SET @F2='=IF(RC[-1]=0,100,RC[-1]/RC[-1]*100)'
SET @F3='=IF(R[-1]C[-1]=0,0,RC[-1]/R[-1]C[-1]*100)'
SET @F4='=IF(R[-2]C[-1]=0,0,RC[-1]/R[-2]C[-1]*100)'
SET @F5='=IF(R[-3]C[-1]=0,0,RC[-1]/R[-3]C[-1]*100)'
SET @F6='=R[-12]C-R[-9]C-R[-6]C-R[-5]C-R[-4]C'
SET @F7='=R[1]C+R[2]C'
SET @F8=@F7+'+R[3]C'
SET @F9='=R[-12]C-R[-9]C-R[-6]C-R[-5]C-R[-4]C'

SET @Dep =
(SELECT Count(*) FROM
(SELECT ev.ElementNameRU
FROM Documents d LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU) tmp)

SET @FT=''
SET @Ind=1
WHILE @Ind<=@Dep
BEGIN
IF @FT=''
SET @FT='='
ELSE
SET @FT=@FT+'+'

SET @FT=@FT+'R[-'+CAST(@Ind*14 as varchar(5))+']C'
SET @Ind=@Ind + 1
END

SELECT h1,h3,c1,c3,c5,c7,c9,c11,c13
FROM
(
SELECT
ev.ElementNameRU as h1,
1 as h2,
'Количество поручений на контроле всего' as h3,
@F1 as c1,@F7 as c3,@F7 as c5,@F7 as c7,@F7 as c9,@F7 as c11,@F7 as c13

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
2,
'в т.ч. в качестве головного исполнителя',
@F1,@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW' AND d.ExecutionStatusCode='SpecialAdmControl'
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID NOT IN ('ALUO-66TEZW', 'ALUO-66TF2Y', 'ALUO-66TF2X') AND d.ExecutionStatusCode<>'SpecialAdmControl'
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID IN ('ALUO-66TF2Y', 'ALUO-66TF2X')
AND DocumentTypeUID IN ('ADMR-6LY96V', 'DADR-66RG7Y', 'ALUO-66TD6D', 'ALUO-66TD6C', 'ALUO-66TD3W')
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID = d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
3,
'в т.ч. в качестве соисполнителя',
@F1,@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW' AND d.ExecutionStatusCode='SpecialAdmControl'
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID NOT IN ('ALUO-66TEZW', 'ALUO-66TF2Y', 'ALUO-66TF2X') AND d.ExecutionStatusCode<>'SpecialAdmControl'
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID IN ('ALUO-66TF2Y', 'ALUO-66TF2X')
AND DocumentTypeUID IN ('ADMR-6LY96V', 'DADR-66RG7Y', 'ALUO-66TD6D', 'ALUO-66TD6C', 'ALUO-66TD3W')
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
4,
'Поручения, исполненные с нарушением сроков, всего',
@F1,@F7,@F7,@F7,@F7,@F7,@F7

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
5,
'в т.ч. в качестве головного исполнителя',
@F1,@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW' AND d.ExecutionStatusCode='SpecialAdmControl'
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID NOT IN ('ALUO-66TEZW', 'ALUO-66TF2Y', 'ALUO-66TF2X') AND d.ExecutionStatusCode<>'SpecialAdmControl'
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID IN ('ALUO-66TF2Y', 'ALUO-66TF2X')
AND DocumentTypeUID IN ('ADMR-6LY96V', 'DADR-66RG7Y', 'ALUO-66TD6D', 'ALUO-66TD6C', 'ALUO-66TD3W')
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
6,
'в т.ч. в качестве соисполнителя',
@F1,@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW' AND d.ExecutionStatusCode='SpecialAdmControl'
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID NOT IN ('ALUO-66TEZW', 'ALUO-66TF2Y', 'ALUO-66TF2X') AND d.ExecutionStatusCode<>'SpecialAdmControl'
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID IN ('ALUO-66TF2Y', 'ALUO-66TF2X')
AND DocumentTypeUID IN ('ADMR-6LY96V', 'DADR-66RG7Y', 'ALUO-66TD6D', 'ALUO-66TD6C', 'ALUO-66TD3W')
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
7,
'Несогласие АП с Правительством',
@F1,@F2,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW' AND d.ExecutionStatusCode='SpecialAdmControl'
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F2,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID NOT IN ('ALUO-66TEZW', 'ALUO-66TF2Y', 'ALUO-66TF2X') AND d.ExecutionStatusCode<>'SpecialAdmControl'
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F2,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID IN ('ALUO-66TF2Y', 'ALUO-66TF2X')
AND DocumentTypeUID IN ('ADMR-6LY96V', 'DADR-66RG7Y', 'ALUO-66TD6D', 'ALUO-66TD6C', 'ALUO-66TD3W')
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F2

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
8,
'Исполнено некачественно',
@F1,@F2,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW' AND d.ExecutionStatusCode='SpecialAdmControl'
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F2,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID NOT IN ('ALUO-66TEZW', 'ALUO-66TF2Y', 'ALUO-66TF2X') AND d.ExecutionStatusCode<>'SpecialAdmControl'
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F2,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID IN ('ALUO-66TF2Y', 'ALUO-66TF2X')
AND DocumentTypeUID IN ('ADMR-6LY96V', 'DADR-66RG7Y', 'ALUO-66TD6D', 'ALUO-66TD6C', 'ALUO-66TD3W')
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F2

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
9,
'Всего поручений, по которым продлены сроки исполнения',
@F1,@F8,@F8,@F8,@F8,@F8,@F8

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
10,
'в т.ч. 1 раз',
@F1,@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW' AND d.ExecutionStatusCode='SpecialAdmControl'
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID NOT IN ('ALUO-66TEZW', 'ALUO-66TF2Y', 'ALUO-66TF2X') AND d.ExecutionStatusCode<>'SpecialAdmControl'
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID IN ('ALUO-66TF2Y', 'ALUO-66TF2X')
AND DocumentTypeUID IN ('ADMR-6LY96V', 'DADR-66RG7Y', 'ALUO-66TD6D', 'ALUO-66TD6C', 'ALUO-66TD3W')
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
11,
'2 раза',
@F1,@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW' AND d.ExecutionStatusCode='SpecialAdmControl'
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID NOT IN ('ALUO-66TEZW', 'ALUO-66TF2Y', 'ALUO-66TF2X') AND d.ExecutionStatusCode<>'SpecialAdmControl'
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID IN ('ALUO-66TF2Y', 'ALUO-66TF2X')
AND DocumentTypeUID IN ('ADMR-6LY96V', 'DADR-66RG7Y', 'ALUO-66TD6D', 'ALUO-66TD6C', 'ALUO-66TD3W')
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
12,
'3 раза и более',
@F1,@F5,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW' AND d.ExecutionStatusCode='SpecialAdmControl'
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F5,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID NOT IN ('ALUO-66TEZW', 'ALUO-66TF2Y', 'ALUO-66TF2X') AND d.ExecutionStatusCode<>'SpecialAdmControl'
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F5,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID IN ('ALUO-66TF2Y', 'ALUO-66TF2X')
AND DocumentTypeUID IN ('ADMR-6LY96V', 'DADR-66RG7Y', 'ALUO-66TD6D', 'ALUO-66TD6C', 'ALUO-66TD3W')
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F5

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
13,
'Поручения, исполненные в установленном порядке',
@F1,@F9,@F9,@F9,@F9,@F9,@F9

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT 'Итого','1','=R[-14]C',@F1,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2
UNION ALL SELECT 'Итого','2','=R[-14]C',@F1,@F3,@FT,@F3,@FT,@F3,@FT,@F3,@FT,@F3,@FT,@F3,@FT,@F3
UNION ALL SELECT 'Итого','3','=R[-14]C',@F1,@F4,@FT,@F4,@FT,@F4,@FT,@F4,@FT,@F4,@FT,@F4,@FT,@F4
UNION ALL SELECT 'Итого','4','=R[-14]C',@F1,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2
UNION ALL SELECT 'Итого','5','=R[-14]C',@F1,@F3,@FT,@F3,@FT,@F3,@FT,@F3,@FT,@F3,@FT,@F3,@FT,@F3
UNION ALL SELECT 'Итого','6','=R[-14]C',@F1,@F4,@FT,@F4,@FT,@F4,@FT,@F4,@FT,@F4,@FT,@F4,@FT,@F4
UNION ALL SELECT 'Итого','7','=R[-14]C',@F1,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2
UNION ALL SELECT 'Итого','8','=R[-14]C',@F1,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2
UNION ALL SELECT 'Итого','9','=R[-14]C',@F1,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2
UNION ALL SELECT 'Итого','10','=R[-14]C',@F1,@F3,@FT,@F3,@FT,@F3,@FT,@F3,@FT,@F3,@FT,@F3,@FT,@F3
UNION ALL SELECT 'Итого','11','=R[-14]C',@F1,@F4,@FT,@F4,@FT,@F4,@FT,@F4,@FT,@F4,@FT,@F4,@FT,@F4
UNION ALL SELECT 'Итого','12','=R[-14]C',@F1,@F5,@FT,@F5,@FT,@F5,@FT,@F5,@FT,@F5,@FT,@F5,@FT,@F5
UNION ALL SELECT 'Итого','13','=R[-14]C',@F1,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2
) tmp

ORDER BY CASE h1 when 'Итого' then 'ЮЮЮ' else h1 end, h2
[/codebox]

(этот запрос в оригенале)
[codebox]DECLARE @F1 varchar(50), @F2 varchar(50), @F3 varchar(50), @F4 varchar(50), @F5 varchar(50), @F6 varchar(50), @Dep int, @Ind int, @FT varchar(1000), @F7 varchar(50), @F8 varchar(50), @F9 varchar(50)
SET @F1='=RC[2]+RC[4]+RC[6]+RC[8]+RC[10]+RC[12]'
SET @F2='=IF(RC[-1]=0,100,RC[-1]/RC[-1]*100)'
SET @F3='=IF(R[-1]C[-1]=0,0,RC[-1]/R[-1]C[-1]*100)'
SET @F4='=IF(R[-2]C[-1]=0,0,RC[-1]/R[-2]C[-1]*100)'
SET @F5='=IF(R[-3]C[-1]=0,0,RC[-1]/R[-3]C[-1]*100)'
SET @F6='=R[-12]C-R[-9]C-R[-6]C-R[-5]C-R[-4]C'
SET @F7='=R[1]C+R[2]C'
SET @F8=@F7+'+R[3]C'
SET @F9='=R[-12]C-R[-9]C-R[-6]C-R[-5]C-R[-4]C'

SET @Dep =
(SELECT Count(*) FROM
(SELECT ev.ElementNameRU
FROM Documents d LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
WHERE @WHERE GROUP BY ev.ElementNameRU) tmp)

SET @FT=''
SET @Ind=1
WHILE @Ind<=@Dep
BEGIN
IF @FT=''
SET @FT='='
ELSE
SET @FT=@FT+'+'

SET @FT=@FT+'R[-'+CAST(@Ind*14 as varchar(5))+']C'
SET @Ind=@Ind + 1
END

SELECT h1,h3,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14 FROM
(
SELECT
ev.ElementNameRU as h1,
1 as h2,
'Количество поручений на контроле всего' as h3,
@F1 as c1,@F2 as c2,@F7 as c3,@F2 as c4,@F7 as c5,@F2 as c6,@F7 as c7,@F2 as c8,
@F7 as c9,@F2 as c10,@F7 as c11,@F2 as c12,@F7 as c13,@F2 as c14

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
2,
'в т.ч. в качестве головного исполнителя',
@F1,@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW'
AND ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '004%'))
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW'
AND d.DocumentTypeUID NOT IN ('ALUO-66TD2F','ALUO-66TD5D','DADR-66LJKC','DADR-66LJKD')
AND NOT ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%')
OR (d.DocumentINNumber LIKE '004%') OR (d.DocumentINNumber LIKE '005%') OR (d.DocumentINNumber LIKE '006%')
OR (d.DocumentINNumber LIKE '007%'))
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND ((d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%') OR (d.DocumentINNumber LIKE '005%'))
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND ((d.DocumentINNumber LIKE '006%') OR (d.DocumentINNumber LIKE '007%'))
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND d.DocumentTypeUID NOT IN ('ALUO-66TD5D','ALUO-66TD88','DADR-66K8HU','DADR-66RG7T','DADR-66RG8J')
AND d.ExecutionPerson2UID=ev.ExecutorUID
AND NOT ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%')
OR (d.DocumentINNumber LIKE '004%') OR (d.DocumentINNumber LIKE '005%') OR (d.DocumentINNumber LIKE '006%')
OR (d.DocumentINNumber LIKE '007%'))
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3,

CAST(SUM(CASE WHEN ((d.DocumentCorrespondentUID='ALUO-66TF2Y' AND d.DocumentTypeUID='ADMR-6LY96V')
OR (d.DocumentCorrespondentUID='ALUO-66TF2X' AND d.DocumentTypeUID='DADR-66RG7Y'))
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID = d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
3,
'в т.ч. в качестве соисполнителя',
@F1,@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW'
AND ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '004%'))
AND d.ExecutionPerson2UID<>ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW'
AND d.DocumentTypeUID NOT IN ('ALUO-66TD2F','ALUO-66TD5D','DADR-66LJKC','DADR-66LJKD')
AND NOT ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%')
OR (d.DocumentINNumber LIKE '004%') OR (d.DocumentINNumber LIKE '005%') OR (d.DocumentINNumber LIKE '006%')
OR (d.DocumentINNumber LIKE '007%'))
AND d.ExecutionPerson2UID<>ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND ((d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%') OR (d.DocumentINNumber LIKE '005%'))
AND d.ExecutionPerson2UID<>ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND ((d.DocumentINNumber LIKE '006%') OR (d.DocumentINNumber LIKE '007%'))
AND d.ExecutionPerson2UID<>ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND d.DocumentTypeUID NOT IN ('ALUO-66TD5D','ALUO-66TD88','DADR-66K8HU','DADR-66RG7T','DADR-66RG8J')
AND d.ExecutionPerson2UID<>ev.ExecutorUID
AND NOT ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%')
OR (d.DocumentINNumber LIKE '004%') OR (d.DocumentINNumber LIKE '005%') OR (d.DocumentINNumber LIKE '006%')
OR (d.DocumentINNumber LIKE '007%'))
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4,

CAST(SUM(CASE WHEN ((d.DocumentCorrespondentUID='ALUO-66TF2Y' AND d.DocumentTypeUID='ADMR-6LY96V')
OR (d.DocumentCorrespondentUID='ALUO-66TF2X' AND d.DocumentTypeUID='DADR-66RG7Y'))
AND d.ExecutionPerson2UID<>ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
WHERE @WHERE
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
4,
'Поручения, исполненные с нарушением сроков, всего',
@F1,@F2,@F7,@F2,@F7,@F2,@F7,@F2,@F7,@F2,@F7,@F2,@F7,@F2

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
5,
'в т.ч. в качестве головного исполнителя',
@F1,@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW'
AND IsNotTime='1'
AND ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '004%'))
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW'
AND d.DocumentTypeUID NOT IN ('ALUO-66TD2F','ALUO-66TD5D','DADR-66LJKC','DADR-66LJKD')
AND IsNotTime='1'
AND NOT ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%')
OR (d.DocumentINNumber LIKE '004%') OR (d.DocumentINNumber LIKE '005%') OR (d.DocumentINNumber LIKE '006%')
OR (d.DocumentINNumber LIKE '007%'))
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND IsNotTime='1'
AND ((d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%') OR (d.DocumentINNumber LIKE '005%'))
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND IsNotTime='1'
AND ((d.DocumentINNumber LIKE '006%') OR (d.DocumentINNumber LIKE '007%'))
AND d.ExecutionPerson2UID=ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND d.DocumentTypeUID NOT IN ('ALUO-66TD5D','ALUO-66TD88','DADR-66K8HU','DADR-66RG7T','DADR-66RG8J')
AND IsNotTime='1'
AND d.ExecutionPerson2UID=ev.ExecutorUID
AND NOT ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%')
OR (d.DocumentINNumber LIKE '004%') OR (d.DocumentINNumber LIKE '005%') OR (d.DocumentINNumber LIKE '006%')
OR (d.DocumentINNumber LIKE '007%'))
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3,

CAST(SUM(CASE WHEN ((d.DocumentCorrespondentUID='ALUO-66TF2Y' AND d.DocumentTypeUID='ADMR-6LY96V')
OR (d.DocumentCorrespondentUID='ALUO-66TF2X' AND d.DocumentTypeUID='DADR-66RG7Y'))
AND d.ExecutionPerson2UID=ev.ExecutorUID
AND IsNotTime='1'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
6,
'в т.ч. в качестве соисполнителя',
@F1,@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW'
AND IsNotTime='1'
AND ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '004%'))
AND d.ExecutionPerson2UID<>ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW'
AND d.DocumentTypeUID NOT IN ('ALUO-66TD2F','ALUO-66TD5D','DADR-66LJKC','DADR-66LJKD')
AND IsNotTime='1'
AND NOT ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%')
OR (d.DocumentINNumber LIKE '004%') OR (d.DocumentINNumber LIKE '005%') OR (d.DocumentINNumber LIKE '006%')
OR (d.DocumentINNumber LIKE '007%'))
AND d.ExecutionPerson2UID<>ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND IsNotTime='1'
AND ((d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%') OR (d.DocumentINNumber LIKE '005%'))
AND d.ExecutionPerson2UID<>ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND IsNotTime='1'
AND ((d.DocumentINNumber LIKE '006%') OR (d.DocumentINNumber LIKE '007%'))
AND d.ExecutionPerson2UID<>ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND d.DocumentTypeUID NOT IN ('ALUO-66TD5D','ALUO-66TD88','DADR-66K8HU','DADR-66RG7T','DADR-66RG8J')
AND IsNotTime='1'
AND d.ExecutionPerson2UID<>ev.ExecutorUID
AND NOT ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%')
OR (d.DocumentINNumber LIKE '004%') OR (d.DocumentINNumber LIKE '005%') OR (d.DocumentINNumber LIKE '006%')
OR (d.DocumentINNumber LIKE '007%'))
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4,

CAST(SUM(CASE WHEN ((d.DocumentCorrespondentUID='ALUO-66TF2Y' AND d.DocumentTypeUID='ADMR-6LY96V')
OR (d.DocumentCorrespondentUID='ALUO-66TF2X' AND d.DocumentTypeUID='DADR-66RG7Y'))
AND IsNotTime='1'
AND d.ExecutionPerson2UID<>ev.ExecutorUID
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
7,
'Несогласие АП с Правительством',
@F1,@F2,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW'
AND ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '004%'))
AND d.IsNotAcceptedAPRK='1'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F2,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW'
AND d.DocumentTypeUID NOT IN ('ALUO-66TD2F','ALUO-66TD5D','DADR-66LJKC','DADR-66LJKD')
AND NOT ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%')
OR (d.DocumentINNumber LIKE '004%') OR (d.DocumentINNumber LIKE '005%') OR (d.DocumentINNumber LIKE '006%')
OR (d.DocumentINNumber LIKE '007%'))
AND d.IsNotAcceptedAPRK='1'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F2,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND ((d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%') OR (d.DocumentINNumber LIKE '005%'))
AND d.IsNotAcceptedAPRK='1'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F2,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND ((d.DocumentINNumber LIKE '006%') OR (d.DocumentINNumber LIKE '007%'))
AND d.IsNotAcceptedAPRK='1'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F2,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND d.DocumentTypeUID NOT IN ('ALUO-66TD5D','ALUO-66TD88','DADR-66K8HU','DADR-66RG7T','DADR-66RG8J')
AND NOT ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%')
OR (d.DocumentINNumber LIKE '004%') OR (d.DocumentINNumber LIKE '005%') OR (d.DocumentINNumber LIKE '006%')
OR (d.DocumentINNumber LIKE '007%'))
AND d.IsNotAcceptedAPRK='1'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F2,

CAST(SUM(CASE WHEN ((d.DocumentCorrespondentUID='ALUO-66TF2Y' AND d.DocumentTypeUID='ADMR-6LY96V')
OR (d.DocumentCorrespondentUID='ALUO-66TF2X' AND d.DocumentTypeUID='DADR-66RG7Y'))
AND d.IsNotAcceptedAPRK='1'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F2

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
8,
'Исполнено некачественно',
@F1,@F2,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW'
AND ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '004%'))
AND d.IsBadDocument='1'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F2,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW'
AND d.DocumentTypeUID NOT IN ('ALUO-66TD2F','ALUO-66TD5D','DADR-66LJKC','DADR-66LJKD')
AND NOT ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%')
OR (d.DocumentINNumber LIKE '004%') OR (d.DocumentINNumber LIKE '005%') OR (d.DocumentINNumber LIKE '006%')
OR (d.DocumentINNumber LIKE '007%'))
AND d.IsBadDocument='1'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F2,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND ((d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%') OR (d.DocumentINNumber LIKE '005%'))
AND d.IsBadDocument='1'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F2,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND ((d.DocumentINNumber LIKE '006%') OR (d.DocumentINNumber LIKE '007%'))
AND d.IsBadDocument='1'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F2,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND d.DocumentTypeUID NOT IN ('ALUO-66TD5D','ALUO-66TD88','DADR-66K8HU','DADR-66RG7T','DADR-66RG8J')
AND NOT ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%')
OR (d.DocumentINNumber LIKE '004%') OR (d.DocumentINNumber LIKE '005%') OR (d.DocumentINNumber LIKE '006%')
OR (d.DocumentINNumber LIKE '007%'))
AND d.IsBadDocument='1'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F2,

CAST(SUM(CASE WHEN ((d.DocumentCorrespondentUID='ALUO-66TF2Y' AND d.DocumentTypeUID='ADMR-6LY96V')
OR (d.DocumentCorrespondentUID='ALUO-66TF2X' AND d.DocumentTypeUID='DADR-66RG7Y'))
AND d.IsBadDocument='1'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F2

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
9,
'Всего поручений, по которым продлены сроки исполнения',
@F1,@F2,@F8,@F2,@F8,@F2,@F8,@F2,@F8,@F2,@F8,@F2,@F8,@F2

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
10,
'в т.ч. 1 раз',
@F1,@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW'
AND ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '004%'))
AND d.Prolongation='3'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW'
AND d.DocumentTypeUID NOT IN ('ALUO-66TD2F','ALUO-66TD5D','DADR-66LJKC','DADR-66LJKD')
AND NOT ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%')
OR (d.DocumentINNumber LIKE '004%') OR (d.DocumentINNumber LIKE '005%') OR (d.DocumentINNumber LIKE '006%')
OR (d.DocumentINNumber LIKE '007%'))
AND d.Prolongation='3'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND ((d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%') OR (d.DocumentINNumber LIKE '005%'))
AND d.Prolongation='3'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND ((d.DocumentINNumber LIKE '006%') OR (d.DocumentINNumber LIKE '007%'))
AND d.Prolongation='3'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND d.DocumentTypeUID NOT IN ('ALUO-66TD5D','ALUO-66TD88','DADR-66K8HU','DADR-66RG7T','DADR-66RG8J')
AND NOT ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%')
OR (d.DocumentINNumber LIKE '004%') OR (d.DocumentINNumber LIKE '005%') OR (d.DocumentINNumber LIKE '006%')
OR (d.DocumentINNumber LIKE '007%'))
AND d.Prolongation='3'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3,

CAST(SUM(CASE WHEN ((d.DocumentCorrespondentUID='ALUO-66TF2Y' AND d.DocumentTypeUID='ADMR-6LY96V')
OR (d.DocumentCorrespondentUID='ALUO-66TF2X' AND d.DocumentTypeUID='DADR-66RG7Y'))
AND d.Prolongation='3'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F3

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
11,
'2 раза',
@F1,@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW'
AND ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '004%'))
AND d.Prolongation='1'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW'
AND d.DocumentTypeUID NOT IN ('ALUO-66TD2F','ALUO-66TD5D','DADR-66LJKC','DADR-66LJKD')
AND NOT ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%')
OR (d.DocumentINNumber LIKE '004%') OR (d.DocumentINNumber LIKE '005%') OR (d.DocumentINNumber LIKE '006%')
OR (d.DocumentINNumber LIKE '007%'))
AND d.Prolongation='1'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND ((d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%') OR (d.DocumentINNumber LIKE '005%'))
AND d.Prolongation='1'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND ((d.DocumentINNumber LIKE '006%') OR (d.DocumentINNumber LIKE '007%'))
AND d.Prolongation='1'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND d.DocumentTypeUID NOT IN ('ALUO-66TD5D','ALUO-66TD88','DADR-66K8HU','DADR-66RG7T','DADR-66RG8J')
AND NOT ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%')
OR (d.DocumentINNumber LIKE '004%') OR (d.DocumentINNumber LIKE '005%') OR (d.DocumentINNumber LIKE '006%')
OR (d.DocumentINNumber LIKE '007%'))
AND d.Prolongation='1'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4,

CAST(SUM(CASE WHEN ((d.DocumentCorrespondentUID='ALUO-66TF2Y' AND d.DocumentTypeUID='ADMR-6LY96V')
OR (d.DocumentCorrespondentUID='ALUO-66TF2X' AND d.DocumentTypeUID='DADR-66RG7Y'))
AND d.Prolongation='1'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F4

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
12,
'3 раза и более',
@F1,@F5,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW'
AND ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '004%'))
AND d.Prolongation='2'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F5,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ALUO-66TEZW'
AND d.DocumentTypeUID NOT IN ('ALUO-66TD2F','ALUO-66TD5D','DADR-66LJKC','DADR-66LJKD')
AND NOT ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%')
OR (d.DocumentINNumber LIKE '004%') OR (d.DocumentINNumber LIKE '005%') OR (d.DocumentINNumber LIKE '006%')
OR (d.DocumentINNumber LIKE '007%'))
AND d.Prolongation='2'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F5,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND ((d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%') OR (d.DocumentINNumber LIKE '005%'))
AND d.Prolongation='2'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F5,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND ((d.DocumentINNumber LIKE '006%') OR (d.DocumentINNumber LIKE '007%'))
AND d.Prolongation='2'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F5,

CAST(SUM(CASE WHEN d.DocumentCorrespondentUID='ADMR-6MWEU4'
AND d.DocumentTypeUID NOT IN ('ALUO-66TD5D','ALUO-66TD88','DADR-66K8HU','DADR-66RG7T','DADR-66RG8J')
AND NOT ((d.DocumentINNumber LIKE '001%') OR (d.DocumentINNumber LIKE '002%') OR (d.DocumentINNumber LIKE '003%')
OR (d.DocumentINNumber LIKE '004%') OR (d.DocumentINNumber LIKE '005%') OR (d.DocumentINNumber LIKE '006%')
OR (d.DocumentINNumber LIKE '007%'))
AND d.Prolongation='2'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F5,

CAST(SUM(CASE WHEN ((d.DocumentCorrespondentUID='ALUO-66TF2Y' AND d.DocumentTypeUID='ADMR-6LY96V')
OR (d.DocumentCorrespondentUID='ALUO-66TF2X' AND d.DocumentTypeUID='DADR-66RG7Y'))
AND d.Prolongation='2'
THEN 1 ELSE 0 END) AS varchar(1000)),
@F5

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT
ev.ElementNameRU,
13,
'Поручения, исполненные в установленном порядке',
@F1,@F2,@F9,@F2,@F9,@F2,@F9,@F2,@F9,@F2,@F9,@F2,@F9,@F2

FROM Documents d
LEFT OUTER JOIN EXECUTORS_VIEW_INFO ev ON ev.DocumentUID=d.DocumentUID
GROUP BY ev.ElementNameRU

UNION ALL SELECT 'Итого','1','=R[-14]C',@F1,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2
UNION ALL SELECT 'Итого','2','=R[-14]C',@F1,@F3,@FT,@F3,@FT,@F3,@FT,@F3,@FT,@F3,@FT,@F3,@FT,@F3
UNION ALL SELECT 'Итого','3','=R[-14]C',@F1,@F4,@FT,@F4,@FT,@F4,@FT,@F4,@FT,@F4,@FT,@F4,@FT,@F4
UNION ALL SELECT 'Итого','4','=R[-14]C',@F1,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2
UNION ALL SELECT 'Итого','5','=R[-14]C',@F1,@F3,@FT,@F3,@FT,@F3,@FT,@F3,@FT,@F3,@FT,@F3,@FT,@F3
UNION ALL SELECT 'Итого','6','=R[-14]C',@F1,@F4,@FT,@F4,@FT,@F4,@FT,@F4,@FT,@F4,@FT,@F4,@FT,@F4
UNION ALL SELECT 'Итого','7','=R[-14]C',@F1,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2
UNION ALL SELECT 'Итого','8','=R[-14]C',@F1,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2
UNION ALL SELECT 'Итого','9','=R[-14]C',@F1,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2
UNION ALL SELECT 'Итого','10','=R[-14]C',@F1,@F3,@FT,@F3,@FT,@F3,@FT,@F3,@FT,@F3,@FT,@F3,@FT,@F3
UNION ALL SELECT 'Итого','11','=R[-14]C',@F1,@F4,@FT,@F4,@FT,@F4,@FT,@F4,@FT,@F4,@FT,@F4,@FT,@F4
UNION ALL SELECT 'Итого','12','=R[-14]C',@F1,@F5,@FT,@F5,@FT,@F5,@FT,@F5,@FT,@F5,@FT,@F5,@FT,@F5
UNION ALL SELECT 'Итого','13','=R[-14]C',@F1,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2,@FT,@F2
) tmp

ORDER BY CASE h1 when 'Итого' then 'ЮЮЮ' else h1 end, h2[/codebox]

European · 21.03.2008

ainura сказал(а):
Мне нужно разобраться в запросе который составляла не я

Ой мля, без литра пива тут не разобраться... Девушка, врядли бесплатно кто-то Вам поможет

ainura · 21.03.2008

sax_ol сказал(а):
"Мама мия"
Вы бы хоть обрисовали проблему, чего есть, чего хотите, чего не получается, а то так и не знаешь на что сразу смотреть то.

При формировании того запроса который я переделала у меня выдает следующую ошибку:
All the queries in a query expression containing a UNION operator must have the same number of expressions in their select lists.

ainura · 21.03.2008

sax_ol сказал(а):
ainura
Правильно говорит. При использовании UNION-ов надо чтобы все запросы выдавали одинаковое количество и тип данных в своих наборах.
Ну т.е.
select 1, 2, 3, 4, 5
UNION
select 1, 2, 3, 4, 5
правильно, а так
select 1, 2, 3, 5
UNION
select 1, 2, 3, 4, 5
нет
У вас там так и есть где-то 10 где-то больше ...

Спасибо Вам кажется я поняла где у меня косяк

Все сервисы Codeby

Поиск

Поиск

Помогите пожалуйста с запросом

ainura

European

ainura

ainura