Calcular Valor Por acumulo.

13/07/2012

0

SQL Server

Ola Boa tarde!!!

Eu aqui mais uma vez precisando de ajuda.

Desta vez preciso implementar uma consulta que consegui montar com a ajuda de vcs em meu ultimo topico onde pedi ajuda.

So que desta vez, preciso que os valores sejam acumulados por valor.

No meu topico anterior calculamos os valores agrupamento pelo horario de uma determinada hora ate uma determinada hora.

Agora preciso que alem disto eu apresente um acumulativo, por exemplo:

Horas Total Hora Total Acumulado
Primeira hora 1000.00 1000.00
Segunda Hora 1200.00 2200.00
Terceira Hora 2000.00 4200.00
Quarta Hora 1300.00 5500.00

Nao sei se fui claro.

Alguem poderia me ajudar?

Muito Obrigado.

Abraco.
Alex Lekao

Alex Lekao

Responder

Posts

13/07/2012

James Pedreira

Boa noite!

Não sei se entendi muito bem, mas acredito que você possa utilizar o sum() em conjunto com o group by para resolver seu problema.

select hora,
sum(valor)
from tabela
group by hora


espero ter ajudado.
Responder
Gostei + 0

14/07/2012

Emerson Nascimento

para calcular o acumulado você pode fazer assim:


select t1.hora,
sum(t1.valor) valor,
(select sum(t2.valor) from tabela t2 on t2.hora <= t1.hora) acumulado
from tabela t1
group by t1.hora
Responder
Gostei + 0

17/07/2012

Alex Lekao

Opa e ae... blz???

Desculpae... mas ainda nao consegui testar as sugestoes que me deram.

Obrigado a todos.

Vou postar o meu codigo para ver se fica mais clara a minha duvida.

Vou testar isso que o Emerson sugeriu, nao entendi muito bem... esta fazendo um join correto?

Mas eh isso ae... to meio na correria.

obrigado mais uma vez.

Abraco.
Responder
Gostei + 0

18/07/2012

Alex Lekao

Opa e ae... desculpem a demora.. mas ta maior correria aqui no trampo...

como disse vou postar o meu codigo para vcs me ajudarem mais um pouco... rsrsr

no meu codigo eu coloquei um totgeral para eu ir acompanhando a soma de todas as horas e o totgeral2 seria o acumulado que to querendo.

Emerson, a sugestao que vc me deu eu tentei dar uma ajustada no meu codigo com ela e nao consegui, eu nao entendi muito bem.

eh isso me deem mais essa forca ae...

Obrigadao...

Abraco

Alex - Lekao

SELECT
CASE
WHEN R.CODLOJ = 001 THEN 001 - LAPA
WHEN R.CODLOJ = 002 THEN 002 - FREG
WHEN R.CODLOJ = 003 THEN 003 - PINH
WHEN R.CODLOJ = 004 THEN 004 - JUND
WHEN R.CODLOJ = 005 THEN 005 - TATU
ELSE SEM LOJA/ FALTA LOJA
END AS LOJA,
CASE
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 00:00:00 AND 09:00:00 THEN 01 - Até as 09:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 09:00:01 AND 10:00:00 THEN 02 - De 09:00 as 10:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 10:00:01 AND 11:00:00 THEN 03 - De 10:00 as 11:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 11:00:01 AND 12:00:00 THEN 04 - De 11:00 as 12:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 12:00:01 AND 13:00:00 THEN 05 - De 12:00 as 13:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 13:00:01 AND 14:00:00 THEN 06 - De 13:00 as 14:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 14:00:01 AND 15:00:00 THEN 07 - De 14:00 as 15:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 15:00:01 AND 16:00:00 THEN 08 - De 15:00 as 16:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 16:00:01 AND 17:00:00 THEN 09 - De 16:00 as 17:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 17:00:01 AND 23:59:59 THEN 10 - Após as 17:00
ELSE 00
END AS HORA,
CASE WHEN (R.TIPOREQ=VD)THEN COUNT(R.NUMREQ) ELSE 0 END AS QTDVDA,
CASE WHEN (R.TIPOREQ=VD)THEN SUM(R.TOTGERAL) ELSE 0 END AS VENDA,
(SELECT
SUM(R2.TOTGERAL) FROM REQVDA AS R2
WHERE (R2.TIPOREQ = VD)
AND(R2.DATEMI >= 18.07.2012)
AND(R2.DATEMI <= 18.07.2012)
AND(R2.CODCPV NOT IN (80))
) AS TOTGERAL,
(SELECT
SUM(R3.TOTGERAL)
FROM REQVDA AS R3
WHERE (R3.TIPOREQ = VD)
AND(R3.DATEMI >= 18.07.2012)
AND(R3.DATEMI <= 18.07.2012)
AND(R3.CODCPV NOT IN (80))
) AS TOTGERAL2
FROM REQVDA AS R
WHERE (1=1)
AND(R.TIPOREQ IN (VD))
AND(R.DATEMI >= 18.07.2012)
AND(R.DATEMI <= 18.07.2012)
AND(R.CODCPV NOT IN (80))
GROUP BY R.CODLOJ,R.TIPOREQ,
CASE
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 00:00:00 AND 09:00:00 THEN 01 - Até as 09:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 09:00:01 AND 10:00:00 THEN 02 - De 09:00 as 10:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 10:00:01 AND 11:00:00 THEN 03 - De 10:00 as 11:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 11:00:01 AND 12:00:00 THEN 04 - De 11:00 as 12:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 12:00:01 AND 13:00:00 THEN 05 - De 12:00 as 13:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 13:00:01 AND 14:00:00 THEN 06 - De 13:00 as 14:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 14:00:01 AND 15:00:00 THEN 07 - De 14:00 as 15:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 15:00:01 AND 16:00:00 THEN 08 - De 15:00 as 16:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 16:00:01 AND 17:00:00 THEN 09 - De 16:00 as 17:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 17:00:01 AND 23:59:59 THEN 10 - Após as 17:00
ELSE 00
END
ORDER BY R.CODLOJ,R.TIPOREQ,
CASE
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 00:00:00 AND 09:00:00 THEN 01 - Até as 09:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 09:00:01 AND 10:00:00 THEN 02 - De 09:00 as 10:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 10:00:01 AND 11:00:00 THEN 03 - De 10:00 as 11:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 11:00:01 AND 12:00:00 THEN 04 - De 11:00 as 12:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 12:00:01 AND 13:00:00 THEN 05 - De 12:00 as 13:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 13:00:01 AND 14:00:00 THEN 06 - De 13:00 as 14:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 14:00:01 AND 15:00:00 THEN 07 - De 14:00 as 15:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 15:00:01 AND 16:00:00 THEN 08 - De 15:00 as 16:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 16:00:01 AND 17:00:00 THEN 09 - De 16:00 as 17:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 17:00:01 AND 23:59:59 THEN 10 - Após as 17:00
ELSE 00
END
Responder
Gostei + 0

18/07/2012

Alex Lekao

AE Galera... olha eu aqui novamente...

Muito obrigado pela ajuda....

eu achei um jeito de fazer, nao sei se eh o mais correto mas funcionou...

vou postar o codigo novamente, o que fiz foi colocar no where as condicoes seguindo a ideia que o Emerson posto como sugestao.

se houver um jeito de melhorar ou otimizar, estou aberto a sugestoes.

Obrigado mesmo.

Abraco.

Alex - Lekao

SELECT
CASE
WHEN R.CODLOJ = 001 THEN 001 - LAPA
WHEN R.CODLOJ = 002 THEN 002 - FREG
WHEN R.CODLOJ = 003 THEN 003 - PINH
WHEN R.CODLOJ = 004 THEN 004 - JUND
WHEN R.CODLOJ = 005 THEN 005 - TATU
ELSE SEM LOJA/ FALTA LOJA
END AS LOJA,
CASE
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 00:00:00 AND 09:00:00 THEN 01 - Até as 09:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 09:00:01 AND 10:00:00 THEN 02 - De 09:00 as 10:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 10:00:01 AND 11:00:00 THEN 03 - De 10:00 as 11:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 11:00:01 AND 12:00:00 THEN 04 - De 11:00 as 12:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 12:00:01 AND 13:00:00 THEN 05 - De 12:00 as 13:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 13:00:01 AND 14:00:00 THEN 06 - De 13:00 as 14:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 14:00:01 AND 15:00:00 THEN 07 - De 14:00 as 15:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 15:00:01 AND 16:00:00 THEN 08 - De 15:00 as 16:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 16:00:01 AND 17:00:00 THEN 09 - De 16:00 as 17:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 17:00:01 AND 23:59:59 THEN 10 - Após as 17:00
ELSE 00
END AS HORA,
CASE WHEN (R.TIPOREQ=VD)THEN COUNT(R.NUMREQ) ELSE 0 END AS QTDVDA,
CASE WHEN (R.TIPOREQ=VD)THEN SUM(R.TOTGERAL) ELSE 0 END AS VENDA,
(SELECT
SUM(R2.TOTGERAL) FROM REQVDA AS R2
WHERE (R2.TIPOREQ = VD)
AND(R2.DATEMI >= 18.07.2012)
AND(R2.DATEMI <= 18.07.2012)
AND(R2.CODCPV NOT IN (80))
) AS TOTGERAL,
(SELECT
SUM(R3.TOTGERAL)
FROM REQVDA AS R3
WHERE (R3.TIPOREQ = VD)
AND(R3.DATEMI >= 18.07.2012)
AND(R3.DATEMI <= 18.07.2012)
AND(R3.CODCPV NOT IN (80))
AND((CASE
WHEN CONVERT(VARCHAR,R3.DTHCAD,8) BETWEEN 00:00:00 AND 09:00:00 THEN 01 - Até as 09:00
WHEN CONVERT(VARCHAR,R3.DTHCAD,8) BETWEEN 09:00:01 AND 10:00:00 THEN 02 - De 09:00 as 10:00
WHEN CONVERT(VARCHAR,R3.DTHCAD,8) BETWEEN 10:00:01 AND 11:00:00 THEN 03 - De 10:00 as 11:00
WHEN CONVERT(VARCHAR,R3.DTHCAD,8) BETWEEN 11:00:01 AND 12:00:00 THEN 04 - De 11:00 as 12:00
WHEN CONVERT(VARCHAR,R3.DTHCAD,8) BETWEEN 12:00:01 AND 13:00:00 THEN 05 - De 12:00 as 13:00
WHEN CONVERT(VARCHAR,R3.DTHCAD,8) BETWEEN 13:00:01 AND 14:00:00 THEN 06 - De 13:00 as 14:00
WHEN CONVERT(VARCHAR,R3.DTHCAD,8) BETWEEN 14:00:01 AND 15:00:00 THEN 07 - De 14:00 as 15:00
WHEN CONVERT(VARCHAR,R3.DTHCAD,8) BETWEEN 15:00:01 AND 16:00:00 THEN 08 - De 15:00 as 16:00
WHEN CONVERT(VARCHAR,R3.DTHCAD,8) BETWEEN 16:00:01 AND 17:00:00 THEN 09 - De 16:00 as 17:00
WHEN CONVERT(VARCHAR,R3.DTHCAD,8) BETWEEN 17:00:01 AND 23:59:59 THEN 10 - Após as 17:00
ELSE 00
END) <= (CASE
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 00:00:00 AND 09:00:00 THEN 01 - Até as 09:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 09:00:01 AND 10:00:00 THEN 02 - De 09:00 as 10:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 10:00:01 AND 11:00:00 THEN 03 - De 10:00 as 11:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 11:00:01 AND 12:00:00 THEN 04 - De 11:00 as 12:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 12:00:01 AND 13:00:00 THEN 05 - De 12:00 as 13:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 13:00:01 AND 14:00:00 THEN 06 - De 13:00 as 14:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 14:00:01 AND 15:00:00 THEN 07 - De 14:00 as 15:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 15:00:01 AND 16:00:00 THEN 08 - De 15:00 as 16:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 16:00:01 AND 17:00:00 THEN 09 - De 16:00 as 17:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 17:00:01 AND 23:59:59 THEN 10 - Após as 17:00
ELSE 00
END))
) AS TOTGERAL2
FROM REQVDA AS R
WHERE (1=1)
AND(R.TIPOREQ IN (VD))
AND(R.DATEMI >= 18.07.2012)
AND(R.DATEMI <= 18.07.2012)
AND(R.CODCPV NOT IN (80))
GROUP BY R.CODLOJ,R.TIPOREQ,
CASE
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 00:00:00 AND 09:00:00 THEN 01 - Até as 09:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 09:00:01 AND 10:00:00 THEN 02 - De 09:00 as 10:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 10:00:01 AND 11:00:00 THEN 03 - De 10:00 as 11:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 11:00:01 AND 12:00:00 THEN 04 - De 11:00 as 12:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 12:00:01 AND 13:00:00 THEN 05 - De 12:00 as 13:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 13:00:01 AND 14:00:00 THEN 06 - De 13:00 as 14:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 14:00:01 AND 15:00:00 THEN 07 - De 14:00 as 15:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 15:00:01 AND 16:00:00 THEN 08 - De 15:00 as 16:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 16:00:01 AND 17:00:00 THEN 09 - De 16:00 as 17:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 17:00:01 AND 23:59:59 THEN 10 - Após as 17:00
ELSE 00
END
ORDER BY R.CODLOJ,R.TIPOREQ,
CASE
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 00:00:00 AND 09:00:00 THEN 01 - Até as 09:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 09:00:01 AND 10:00:00 THEN 02 - De 09:00 as 10:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 10:00:01 AND 11:00:00 THEN 03 - De 10:00 as 11:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 11:00:01 AND 12:00:00 THEN 04 - De 11:00 as 12:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 12:00:01 AND 13:00:00 THEN 05 - De 12:00 as 13:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 13:00:01 AND 14:00:00 THEN 06 - De 13:00 as 14:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 14:00:01 AND 15:00:00 THEN 07 - De 14:00 as 15:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 15:00:01 AND 16:00:00 THEN 08 - De 15:00 as 16:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 16:00:01 AND 17:00:00 THEN 09 - De 16:00 as 17:00
WHEN CONVERT(VARCHAR,R.DTHCAD,8) BETWEEN 17:00:01 AND 23:59:59 THEN 10 - Após as 17:00
ELSE 00
END
Responder
Gostei + 0

Assista grátis a nossa aula inaugural

Assitir aula

Saiba por que programar é uma questão de
sobrevivência e como aprender sem riscos

Assistir agora

Utilizamos cookies para fornecer uma melhor experiência para nossos usuários, consulte nossa política de privacidade.

Aceitar