Помощник
 |
|
Разработка бизнес сайтов Нужны клиенты? Тогда сюда быстрее... |
Сообщение сайта
Здравствуйте, гость ( Вход | Регистрация )
 Помогите пожалуйста с запросом |
|
|
 21:03:2008, 06:54
 
|
|
Новенький  Группа: Программист Сообщений: 3 Регистрация: 21:03:2008 Пользователь №: 16 046 Специализация: администратор Репутация:  0 
 |
Я столкнулась с такой проблемой. Мне нужно разобраться в запросе который составляла не я. Я вроде как разобрала но у меня выходит ошибка может кто не будь поймет это по запросу. Буду безумно признательна.
CODE (этот запрос уже переделанный мною) 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 (этот запрос в оригенале) CODE 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 Сообщение отредактировал European - 21:03:2008, 07:18
Причина редактирования: Установка тегов кода
|
 |
Сообщение
#1
|
 |
|
|
21:03:2008, 07:14
|
|
============== Группа: Модеры Сообщений: 1 740 Регистрация: 11:01:2007 Пользователь №: 8 750 Специализация: specialist Репутация: 27
|
"Мама мия"
 Вы бы хоть обрисовали проблему, чего есть, чего хотите, чего не получается, а то так и не знаешь на что сразу смотреть то. |
|
|
Сообщение
#2
|
|
|
21:03:2008, 07:25
|
|
Божественный Группа: Модеры Сообщений: 1 814 Регистрация: 4:09:2006 Из: Минск Пользователь №: 6 316 Специализация: C++ Репутация: 31
|
Цитата(ainura @ 21:03:2008 - 09:54)  Мне нужно разобраться в запросе который составляла не я Ой мля, без литра пива тут не разобраться... Девушка, врядли бесплатно кто-то Вам поможет |
|
|
Сообщение
#3
|
|
|
21:03:2008, 08:26
|
|
Новенький Группа: Программист Сообщений: 3 Регистрация: 21:03:2008 Пользователь №: 16 046 Специализация: администратор Репутация: 0
|
Цитата(sax_ol @ 21:03:2008, 14:14 ) "Мама мия" Вы бы хоть обрисовали проблему, чего есть, чего хотите, чего не получается, а то так и не знаешь на что сразу смотреть то. При формировании того запроса который я переделала у меня выдает следующую ошибку: All the queries in a query expression containing a UNION operator must have the same number of expressions in their select lists. |
|
|
Сообщение
#4
|
|
|
21:03:2008, 08:31
|
|
============== Группа: Модеры Сообщений: 1 740 Регистрация: 11:01:2007 Пользователь №: 8 750 Специализация: specialist Репутация: 27
|
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 где-то больше ... |
|
|
Сообщение
#5
|
|
|
 21:03:2008, 10:12
|
|
Новенький Группа: Программист Сообщений: 3 Регистрация: 21:03:2008 Пользователь №: 16 046 Специализация: администратор Репутация: 0
|
Цитата(sax_ol @ 21:03:2008, 15:31 ) 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 где-то больше ... Спасибо Вам кажется я поняла где у меня косяк :-) |
|
|
Сообщение
#6
|
|
|
Текстовая версия | Сейчас: 16:05:2008 - 17:29 |
