ALTER PROCEDURE CalculateTicker 
    @Ticker nvarchar(10)
AS
BEGIN
    SET NOCOUNT ON;
    declare @result int;

    insert into [akkumulator] ([ticker], [dimenstion], [year], [quarterly], [datequarterly])
        select DISTINCT q.[ticker], 'ARQ', year(q.[reportperiod]),  (month(q.[reportperiod])-1) / 3 + 1, min(q.[reportperiod])
        from [vw_quarterly] q
        where q.[ticker] = @Ticker and q.[reportperiod] is not null
        group by q.[ticker], year(q.[reportperiod]),  (month(q.[reportperiod])-1) / 3 + 1;
-- на один квартал может приходиться несколько записей, но работаем только с одной
    select @result = @@ROWCOUNT;

    with rev_group_3q (revdev, netinc, netincdev, tangiblesdev, ticker, reportperiod)
    as
    (
        select iif(count(qsum.[revenue]) != 0 and SUM(qsum.[revenue]) != 0,
                    qmain.[revenue] / (cast(SUM(qsum.[revenue]) as decimal(20,3)) / count(qsum.[revenue])),
                    null) as revdev
                , iif(count(qsum.[netincmn]) != 0,
                     (cast(SUM(qsum.[netincmn]) as decimal(20,3)) / count(qsum.[netincmn])),
                    null) as netinc                
                , iif(count(qsum.[netincmn]) != 0 and SUM(qsum.[netincmn]) != 0,
                    qmain.[netincmn] / (cast(SUM(qsum.[netincmn]) as decimal(20,3)) / count(qsum.[netincmn])),
                    null) as netincdev                
                , iif(count(qsum.[tangibles]) != 0 and SUM(qsum.[tangibles]) != 0,
                    qmain.[tangibles] / (cast(SUM(qsum.[tangibles]) as decimal(20,3)) / count(qsum.[tangibles])),
                    null) as netincdev                

                , qsum.[ticker]
                , qmain.[reportperiod]
            from [vw_quarterly] qsum
                inner join [vw_quarterly] qmain
                    on qsum.[ticker] =qmain.[ticker]
                        and qsum.dimension = [qmain].[dimension]
            where qsum.[reportperiod] >= dateadd(MONTH, -9, qmain.[reportperiod]) -- агрегирование за 9 месяцев
                and qsum.[reportperiod] < qmain.[reportperiod]
            group by qsum.[ticker],qmain.[reportperiod],qmain.[revenue],qmain.[netincmn],qmain.[tangibles]
    ),
     rev_group_4q (revdev, netinc, netincdev, tangiblesdev, ticker, reportperiod)
    as
    (
        select iif(count(qsum.[revenue]) != 0 and SUM(qsum.[revenue]) != 0,
                    qmain.[revenue] / (cast(SUM(qsum.[revenue]) as decimal(20,3)) / count(qsum.[revenue])),
                    null) as revdev
                , iif(count(qsum.[netincmn]) != 0,
                     (cast(SUM(qsum.[netincmn]) as decimal(20,3)) / count(qsum.[netincmn])),
                    null) as netinc                
                , iif(count(qsum.[netincmn]) != 0 and SUM(qsum.[netincmn]) != 0,
                    qmain.[netincmn] / (cast(SUM(qsum.[netincmn]) as decimal(20,3)) / count(qsum.[netincmn])),
                    null) as netincdev                
                , iif(count(qsum.[tangibles]) != 0 and SUM(qsum.[tangibles]) != 0,
                    qmain.[tangibles] / (cast(SUM(qsum.[tangibles]) as decimal(20,3)) / count(qsum.[tangibles])),
                    null) as netincdev                

                , qsum.[ticker]
                , qmain.[reportperiod]
            from [vw_quarterly] qsum
                inner join [vw_quarterly] qmain
                    on qsum.[ticker] =qmain.[ticker]
                        and qsum.dimension = [qmain].[dimension]
            where qsum.[reportperiod] >= dateadd(MONTH, -12, qmain.[reportperiod]) -- агрегирование за 12 месяцев
                and qsum.[reportperiod] < qmain.[reportperiod]
            group by qsum.[ticker],qmain.[reportperiod],qmain.[revenue],qmain.[netincmn],qmain.[tangibles]
    ),
     rev_group_8q (revdev, netinc, netincdev, tangiblesdev, ticker, reportperiod)
    as
    (
        select iif(count(qsum.[revenue]) != 0 and SUM(qsum.[revenue]) != 0,
                    qmain.[revenue] / (cast(SUM(qsum.[revenue]) as decimal(20,3)) / count(qsum.[revenue])),
                    null) as revdev
                , iif(count(qsum.[netincmn]) != 0,
                     (cast(SUM(qsum.[netincmn]) as decimal(20,3)) / count(qsum.[netincmn])),
                    null) as netinc                
                , iif(count(qsum.[netincmn]) != 0 and SUM(qsum.[netincmn]) != 0,
                    qmain.[netincmn] / (cast(SUM(qsum.[netincmn]) as decimal(20,3)) / count(qsum.[netincmn])),
                    null) as netincdev                
                , iif(count(qsum.[tangibles]) != 0 and SUM(qsum.[tangibles]) != 0,
                    qmain.[tangibles] / (cast(SUM(qsum.[tangibles]) as decimal(20,3)) / count(qsum.[tangibles])),
                    null) as netincdev                

                , qsum.[ticker]
                , qmain.[reportperiod]
            from [vw_quarterly] qsum
                inner join [vw_quarterly] qmain
                    on qsum.[ticker] =qmain.[ticker]
                        and qsum.dimension = [qmain].[dimension]
            where qsum.[reportperiod] >= dateadd(MONTH, -24, qmain.[reportperiod]) -- агрегирование за 24 месяца
                and qsum.[reportperiod] < qmain.[reportperiod]
            group by qsum.[ticker],qmain.[reportperiod],qmain.[revenue],qmain.[netincmn],qmain.[tangibles]
    )
    update [akkumulator]
        set [revenue] = q.[revenue] * .000001,
            [netincomecommon] = [netincmn] * .000001,
            [netincome] = q.[netinc] * .000001,
            [tangiblebookvalue] = [tangibles] * .000001,
            [ordinarysharesnumber] = [sharesbas] * .000001,
            [datereport] = [datekey],
            [p] = [sharesbas] * .000001 / [close],
            [pe] = [sharesbas] / [close] / q.netinc,
            [revenue_3q] = rev_group_3q.[revdev],
            [netinccom_3q] = rev_group_3q.netinc,
            [netinccomdev_3q] = rev_group_3q.netincdev,
            [tan_3q] = rev_group_3q.tangiblesdev,
            [revenue_4q] = rev_group_4q.[revdev],
            [netinccom_4q] = rev_group_4q.netinc,
            [netinccomdev_4q] = rev_group_4q.netincdev,
            [tan_4q] = rev_group_4q.tangiblesdev,
            [revenue_8q] = rev_group_8q.[revdev],
            [netinccom_8q] = rev_group_8q.netinc,
            [netinccomdev_8q] = rev_group_8q.netincdev,
            [tan_8q] = rev_group_8q.tangiblesdev
        from [akkumulator] a 
            inner join [vw_quarterly] q on a.[ticker] = q.[ticker] and a.[datequarterly] = q.[reportperiod]
            inner join rev_group_3q on rev_group_3q.[ticker] = q.[ticker] and rev_group_3q.[reportperiod] = q.[reportperiod]
            inner join rev_group_4q on rev_group_4q.[ticker] = q.[ticker] and rev_group_4q.[reportperiod] = q.[reportperiod]
            inner join rev_group_8q on rev_group_8q.[ticker] = q.[ticker] and rev_group_8q.[reportperiod] = q.[reportperiod]
            left join [ya_historydata] h on q.[ticker] = h.[ticker] and convert(date, h.[date]) = convert(date, q.[datekey])
        where q.[ticker] = @Ticker and q.[reportperiod] is not null

    insert into [akkreport]
        ([ticker], [dimenstion], [quarterly],[year],[date],[close],[delta])

    select DISTINCT [ya_historydata].[ticker],
            'ARQ',
            [akkumulator].[quarterly],
            [akkumulator].[year],
            [ya_historydata].[date],
            [ya_historydata].[close],
            hc.[close] / [ya_historydata].[close]
        from [ya_historydata] inner join [akkumulator]
            on [ya_historydata].[ticker] = [akkumulator].[ticker] and ([ya_historydata].[date] >= DATEADD(day,-100, [akkumulator].[datereport]) and [ya_historydata].[date] <= DATEADD(day,100,[akkumulator].[datereport]))
            inner join [ya_historydata] hc
            on hc.ticker = [akkumulator].ticker and cast(hc.[date] as date) = cast([akkumulator].[datereport] as date)
        where [akkumulator].[ticker] = @Ticker and [ya_historydata].[close] is not null and [ya_historydata].[close] != 0

    return @result

END
GO