GD Star Rating
loading...

Добрый день. Есть кто знаком с Октелл? Или в крайнем случае кто может помочь с МMS SQL? Нужно сделать автоматическую выгрузку диаграмм статистики Октелла в строго определенное время. Суть в следующем. Была диаграмма со статистикой и возможностью выбора даты запроса. Потом наш администратор решил, что это слишком вешает систему и убрал возможность выбора даты. Теперь статистика отображается с 00-00 сегодня по текущий момент. Подскажите, возможно как-то автоматизировать запуск расчета статистики в 23-55, после чего ее сохранять в таблицу или отправлять по почте? Неделю мучаюсь, думал через служебную задачу выполнить-не получилось…

Если с Октелл никто не работает-подскажите, как это реализовать в MS SQL, либо пните куда и что почитать? А то с SQL знаком только поверхностно.
С меня пиво, соки или чего еще.

Админы и сочувствующие посетители hardblog.net посчитали злободневным:еще один блог treeblogfree.com

6 Responses to Добрый день.

  1. Axoon:

    Я понятия не имею, что такое Октелл. А MS SQL много чего может, что конкретно нужно сделать?

  2. EniSm:

    : Нужно автоматически в 23-55 автоматически получать статистику по следующему запросу.

    declare @harit1 as table (a nvarchar(150),b int,c int,d float,e int,f int,g float,h int,i int,k float,l int,m int,n float)
    declare @harittemp as table (a nvarchar(50), b float)
    declare @harittemp1 as table (a nvarchar(50), b float)
    declare @harittemp2 as table (a nvarchar(50), b float)
    declare @harittemp3 as table (a nvarchar(50), b float)

    declare @x as datetime,@x1 as datetime, @count as nvarchar(50), @y as int,@y1 as int, @y2 as float,
    @z as int,@z1 as int, @z2 as float,@w as int, @w1 as int, @w2 as float,@a as int, @b as int, @c as int, @d as int, @e as int,
    @v as int, @v1 as int, @v2 as float, @f as int, @g as int

    set @x=convert(nvarchar(10),getdate(),120)+’ 00:00:00.000′
    set @x1=dateadd(mi,+60,@x)

    set @y:select count(distinct idchain)
    from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_КО1′) and idchain is not null)
    set @y1:select count(distinct datetimestart)
    from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_КО1′) and callresult between ’12’ and ’20’)

    insert into @harittemp (a,b)
    select distinct idchain,lenqueue from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_КО1′)

    set @y2:select round(sum(b)/count(*),1) from @harittemp)

    set @z:select count(distinct idchain)
    from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_КО2′) and idchain is not null)
    set @z1:select count(distinct datetimestart)
    from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_КО2′) and callresult between ’12’ and ’20’)

    insert into @harittemp1 (a,b)
    select distinct idchain,lenqueue from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_КО2′)

    set @z2:select round(sum(b)/count(*),1) from @harittemp1)

    set @w:select count(distinct idchain)
    from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_ТО’) and idchain is not null)

    set @w1:select count(distinct datetimestart)
    from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_ТО’) and callresult between ’12’ and ’20’)

    insert into @harittemp2 (a,b)
    select distinct idchain,lenqueue from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_ТО’)

    set @w2:select round(sum(b)/count(*),1) from @harittemp2)

    set @v:select count(distinct idchain)
    from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_ОП’) and idchain is not null)
    set @v1:select count(distinct datetimestart)
    from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_ОП’) and callresult between ’12’ and ’20’)

    insert into @harittemp3 (a,b)
    select distinct idchain,lenqueue from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_ОП’)

    set @v2:select round(sum(b)/count(*),1) from @harittemp3)

    insert into @harit1 (a,b,c,d,e,f,g,h,i,k,l,m,n) values (‘c 00 по 01:00’,@y,@y1,@y2,@z,@z1,@z2,@w,@w1,@w2, @v,@v1,@v2)
    delete from @harittemp
    delete from @harittemp1
    delete from @harittemp2
    delete from @harittemp3

    set @count = 1

    while @count < 24
    begin

    set @[email protected]
    set @x1=dateadd(ss,+3600,@x)
    set @y:select count(distinct idchain)
    from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_КО1′) and idchain is not null)
    set @y1:select count(distinct datetimestart)
    from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_КО1′) and callresult between ’12’ and ’20’)

    insert into @harittemp (a,b)
    select distinct idchain,lenqueue from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_КО1′)

    set @y2:select round(sum(b)/count(*),1) from @harittemp)

    set @z:select count(distinct idchain)
    from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_КО2′) and idchain is not null)
    set @z1:select count(distinct datetimestart)
    from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_КО2′) and callresult between ’12’ and ’20’)

    insert into @harittemp1 (a,b)
    select distinct idchain,lenqueue from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_КО2′)

    set @z2:select round(sum(b)/count(*),1) from @harittemp1)

    set @w:select count(distinct idchain)
    from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_ТО’) and idchain is not null)

    set @w1:select count(distinct datetimestart)
    from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_ТО’) and callresult between ’12’ and ’20’)

    insert into @harittemp2 (a,b)
    select distinct idchain,lenqueue from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_ТО’)

    set @w2:select round(sum(b)/count(*),1) from @harittemp2)

    set @v:select count(distinct idchain)
    from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_ОП’) and idchain is not null)
    set @v1:select count(distinct datetimestart)
    from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_ОП’) and callresult between ’12’ and ’20’)

    insert into @harittemp3 (a,b)
    select distinct idchain,lenqueue from oktell_cc_Temp.dbo.a_cube_cc_effortconne ctions as a
    left join oktell.dbo.a_users as b on b.id = a.idoperator
    left join oktell_cc_Temp.dbo.a_cube_cc_Cat_Task as c on c.id = a.idtask
    where datetimestart between @x and @x1 and idtask:select id from
    oktell_cc_temp.dbo.a_cube_cc_Cat_Task where name =’Простор_ОП’)

    set @v2:select round(sum(b)/count(*),1) from @harittemp3)

    insert into @harit1 (a,b,c,d,e,f,g,h,i,k,l,m,n) values (‘c ‘[email protected]+’ по ‘[email protected]+’:59′,@y,@y1,@y2,@z,@z1,@z2,
    @w,@w1,@w2,@v,@v1,@v2)
    delete from @harittemp
    delete from @harittemp1
    delete from @harittemp2
    delete from @harittemp3

    set @count = @count + 1
    if @count < 0
    break else continue end

    update @harit1 set d=’0′ where d is null
    update @harit1 set g=’0′ where g is null
    update @harit1 set k=’0′ where k is null
    update @harit1 set n=’0′ where n is null
    set @count = (select sum(b) from @harit1)
    set @a:select sum(d)/24 from @harit1)
    set @b:select sum(e) from @harit1)
    set @c:select sum(g)/24 from @harit1)
    set @d:select sum(h) from @harit1)
    set @e:select sum(k)/24 from @harit1)
    set @f:select sum(l) from @harit1)
    set @g:select sum(n)/24 from @harit1)

    insert into @harit1 (a) values (”)
    insert into @harit1 (a,b,d,e,g,h,k,l,n) values (‘ИТОГО’,@count,@a,@b,@c,@d,@e,@f,@g)

    select a,b,c,d,e,f,g,l,m,n,h,i,k from @harit1

  3. Axoon:

    Нукак вариант, читать про это http://msdn.microsoft.com/en-us/library/&#133;

    И ещё про http://www.microsoft.com/sqlserver/en/us&#133; если данные нужно тянуть откуда-то “слева” или нужно трансформировать в какую-то свою структуру.

    Ещё надо иметь в виду, что не вовсех редакциях все фичи доступны и, что в разных версиях sql server это можно сделать по-разному.

    Какя точно версия и редакция sql server?

  4. Axoon:

    : Во второй ссылке запятую нужно убрать, парсер её прихватил.

  5. EniSm:

    : В документах установленного на него ПО значится следующее:
    Microsoft SQL Server 2005 (64-bit)
    Microsoft SQL Server 2005 Backward compatibility
    Microsoft SQL Server 2005 Books Online (English)
    Microsoft SQL Server 2005 Native Client
    Microsoft SQL Server 2005 Setup Support Files (English)
    Microsoft SQL Server 2005 VSS Writer

    Спасибо. Буду читать.

  6. Axoon:

    : Блин, 2005-й не самый свежий и в этом списке нет редакции. Бывает Express – самый хилый и, например, Enterprise, который сильный.

    Но тем не менее, в 2005-ом тоже есть Integration Services и Server Agent.

Добавить комментарий