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

Тема в разделе "SQL", создана пользователем ainura, 21 мар 2008.

  1. ainura

    ainura Гость

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


    [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]
     
  2. European

    Регистрация:
    4 сен 2006
    Сообщения:
    2.580
    Симпатии:
    0
    Ой мля, без литра пива тут не разобраться... Девушка, врядли бесплатно кто-то Вам поможет
     
  3. ainura

    ainura Гость

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

    ainura Гость



    Спасибо Вам кажется я поняла где у меня косяк :)
     
Загрузка...
Похожие Темы - Помогите пожалуйста запросом
  1. МарФа
    Ответов:
    4
    Просмотров:
    1.655
  2. limbra
    Ответов:
    3
    Просмотров:
    51
  3. uxbmw3w
    Ответов:
    0
    Просмотров:
    72
  4. GREED
    Ответов:
    1
    Просмотров:
    542
  5. Рая
    Ответов:
    3
    Просмотров:
    791

Поделиться этой страницей