Calculo SQL Server com Windows Function

Ola galera, bom dia(Agora já é tarde, tive que parar pra resolver umas “buchinhas”, voltando após almoço e globo esporte). Blz ?
Hoje iremos fazer uma query usando as windows Function do SQL Server.Esse post  foi originado de uma query que tivemos que criar aqui na empresa. E a maneira mais “elegante” de escrever a  query foi usando as Windows Function.
Tendo as seguintes informações em um arquivo .xls, que me foi passado por um amigo desenvolvedor:
TabelaA

TabelaB

E o mesmo deseja o seguinte resultado as informações:

Vou explicar os calculos:

primeira Coluna “SomaProdutos” é a soma dos produtos das duas tabelas por IdPeriodo
Ex: A tabela A e a Tabela B armazena a informação de Valor para o periodo  1.
A soma dos valores 77 + 55 é  igual a 132 e assim sucessivamente.
A coluna “DiasUteis” é uma informação que esta atrelada a cada IdPeriodo. Em um periodo existe uma
certa quantidade de dias uteis para aquele periodos. No meu caso trasnformei essas    informações em
uma tabela de dias uteis e o IdPeriodo.
A coluna “Multiplicação” é a multiplicação dos DiasUteis por SomaProdutos.
A coluna “SomaMultiplicacao” é a soma total da coluna “Multiplicacao”.
A coluna “SomaDias” é a soma da coluna DiasUteis.
E a ultima coluna “Resultado” é a Divisão da coluna  “SomaMultiplicacao” por “SomaDias”.

Uma das vantagens da Windows Function é que podemos ter acesso aos detalhes de uma informação agrupada.
Mais informações Fabiano Amorim: http://blogs.solidq.com/fabianosqlserver/Post.aspx?ID=58

Vamos aos código (A parte boa….rs).
Primeiro crio as tabelas temporarias conforme arquivo .xls (É possivel fazer apenas com uma tabela de periodo,mas, depois da fejuca. Vamos que vamos). Crio a tabela de dias uteis e insiro as informações do meu arquivo .xls.
use tempdb
go
create table #tmp_produto_um(id_periodo int, id_produto int, valor decimal(19,2))
create table #tmp_produto_dois(id_periodo int, id_produto int, valor decimal(19,2))
create table #tmp_dias_uteis(id_periodo int, qtdeDias int)

insert into #tmp_produto_um(id_periodo,id_produto,valor) values(1,18,55.00),(2,18,44.00),(3,18,33.00),(4,18,22.00)
insert into #tmp_produto_dois(id_periodo,id_produto,valor) values(1,19,77.00),(2,19,66.00),(3,19,44.00),(4,19,33.00)
insert into #tmp_dias_uteis(id_periodo,qtdeDias)values(1,22),(2,21),(3,22),(4,23)

SELECT * FROM #tmp_produto_um
SELECT * FROM #tmp_produto_dois
SELECT * FROM #tmp_dias_uteis

Crio uma CTE com a soma dos Valores que no .xls é: SomaProdutos.A multiplicação que no .xls é:DiasUteis * SomaProdutos.

;WITH cte AS
(
SELECT    p.id_periodo                                AS IdPeriodo
        ,SUM(d.valor) + SUM(p.valor)                 AS ValorTotal
        ,u.qtdeDias                                    AS QtdeDias
        ,(SUM(d.valor) + SUM(p.valor)) * u.qtdeDias    AS ProdutVsDiasUteis
        FROM #tmp_produto_um p
        JOIN #tmp_produto_dois d
        ON p.id_periodo = d.id_periodo
        JOIN #tmp_dias_uteis u
        ON u.id_periodo = p.id_periodo
        GROUP BY p.id_periodo,u.qtdeDias
)
Abaixo realizo a query chamando a CTE e usando o SUM com o OVER() para que possa ser realizado a soma das colunas, sem a necessidade de agregação. E dentro da SubQuery crio a soma para ser usada como a coluna resultado. Código total abaixo:

;WITH cte AS
(
SELECT    p.id_periodo                                AS IdPeriodo
        ,SUM(d.valor) + SUM(p.valor)                 AS ValorTotal
        ,u.qtdeDias                                    AS QtdeDias
        ,(SUM(d.valor) + SUM(p.valor)) * u.qtdeDias    AS ProdutVsDiasUteis
        FROM #tmp_produto_um p
        JOIN #tmp_produto_dois d
        ON p.id_periodo = d.id_periodo
        JOIN #tmp_dias_uteis u
        ON u.id_periodo = p.id_periodo
        GROUP BY p.id_periodo,u.qtdeDias
)
SELECT  
        c.IdPeriodo,
        c.ValorTotal,
        c.QtdeDias,
        c.ProdutVsDiasUteis
        ,SUM(c.ProdutVsDiasUteis)        OVER()    AS SomaProdutVsDiasUteis
        ,SUM(c.QtdeDias)                OVER()    AS SomaTotalDias
        ,ROUND(D.SomaDiasUteis / D.SomaDias,2)  AS GeralCalculado
        FROM cte c
        JOIN (    SELECT    a.IdPeriodo
                        ,SUM(a.ProdutVsDiasUteis)        OVER()    AS SomaDiasUteis
                        ,SUM(a.QtdeDias)                OVER()    AS SomaDias
                FROM  cte a
                ) as D
        ON c.IdPeriodo = D.IdPeriodo

Como pode ser visto o resultado do SQLServer é o mesmo que o arquivo .xls que foi colocada no print anteriormente.
Resultado SQL Server:

Espero ter ajudado.
Abs a todos…e tenham uma ótima semana.