Como divulgar montante anual e, em seguida, adicione por mês em SQL

Question 1

Atualmente eu estou trabalhando com uma tabela que tem esta aparência:

Month     | Transaction          | amount
2021-07-01| Annual Membership Fee| 45
2021-08-01| Annual Membership Fee| 145
2021-09-01| Annual Membership Fee| 2940
2021-10-01| Annual Membership Fee| 1545

o amount na tabela é mensal total quantidade (ex. Eu tenho 100 clientes que paga r $15 para a filiação anual, de modo a minha total valor mensal seria de r $1500).

No entanto o que eu gostaria de fazer (e eu não tenho nenhuma idéia de como) é dividir o valor por 12 e espalhá-lo para o futuro, a fim de ter uma receita mensal por mês. Como um exemplo para 2021-09-01 eu gostaria de receber o seguinte:


$2490/12 = $207.5 (dollars per month for the next 12 months)

in 2021-09-01 I would only get $207.5 for that specific month.

On 2021-10-01 I would get $1545/12 = $128.75 plus $207.5 from the previous month (total = $336.25 for 2021-10-01)

And the same operation would repeat onwards. The last period that I would collect my $207.5 from 2021-09-01 would be in 2022-08-01.

Eu queria saber se alguém poderia me dar uma idéia de como fazer isso em uma consulta SQL/CTE?

Question 2

Supondo que todos os meses você se preocupa com a existir em sua mesa, eu gostaria de sugerir algo como:

SELECT 
 month, 
 (SELECT SUM(m2.amount/12) FROM mytable m2 WHERE m2.month BETWEEN ADD_MONTHS(m1.month, -11) AND m1.month) as monthlyamount
FROM mytable m1
GROUP BY month
ORDER BY month

Para cada mês que existe na tabela, esta soma 1/12º da quantidade atual mais o anterior período de 11 meses (usando o add_months função). Eu acho que é isso que você quer.

Algumas notas/pensamentos:

Eu estou supondo que (com base no nome da coluna) que todas as datas month coluna final no dia 1º, por isso não precisa de se preocupar com a correspondência de dias ou de ter o group by retornar várias linhas para o mesmo mês.
Você pode querer rodada o SUMs eu fiz, uma vez que, em alguns casos, dividindo por 12, pode dar-lhe mais dígitos após a vírgula do que você deseja para o dinheiro (embora, nesse caso, você pode também temos que considerar os restos).
Se você realmente tiver apenas uma transação por mês (como no seu exemplo), você não precisa fazer o group by.
Se os meses que você se preocupa não existem na sua tabela, então isso não vai funcionar, mas você pode fazer a mesma coisa, gerando uma tabela de meses. por exemplo, Se você tiver uma quantia em 2020-01-01 mas nada 2020-02-01, então isso não irá retornar uma linha para 2021-02-01.

Question 3

CTE = configurar conjunto de dados

CTE_2 = taxa pró-conjunto de dados

FINAL SQL = select future_cal_month,sum(pro_rated_amount) from cte_2 group by 1

with cte as (
select '2021-07-01' cal_month,'Annual Membership Fee' transaction ,45 amount
union all select '2021-08-01' cal_month,'Annual Membership Fee' transaction ,145 amount
union all select '2021-09-01' cal_month,'Annual Membership Fee' transaction ,2940 amount
union all select '2021-10-01' cal_month,'Annual Membership Fee' transaction ,1545 amount) 
, cte_2 as (    
select 
    dateadd('month', row_number() over (partition by cal_month order by 1), cal_month) future_cal_month
    ,amount/12 pro_rated_amount
from 
     cte 
    ,table(generator(rowcount => 12)) v)
select 
  future_cal_month
, sum(pro_rated_amount) 
from 
  cte_2 
group by 
  future_cal_month

EdmCoff · Answer 1 · 2021-11-23T16:06:20

Supondo que todos os meses você se preocupa com a existir em sua mesa, eu gostaria de sugerir algo como:

SELECT 
 month, 
 (SELECT SUM(m2.amount/12) FROM mytable m2 WHERE m2.month BETWEEN ADD_MONTHS(m1.month, -11) AND m1.month) as monthlyamount
FROM mytable m1
GROUP BY month
ORDER BY month

Para cada mês que existe na tabela, esta soma 1/12º da quantidade atual mais o anterior período de 11 meses (usando o add_months função). Eu acho que é isso que você quer.

Algumas notas/pensamentos:

Eu estou supondo que (com base no nome da coluna) que todas as datas month coluna final no dia 1º, por isso não precisa de se preocupar com a correspondência de dias ou de ter o group by retornar várias linhas para o mesmo mês.
Você pode querer rodada o SUMs eu fiz, uma vez que, em alguns casos, dividindo por 12, pode dar-lhe mais dígitos após a vírgula do que você deseja para o dinheiro (embora, nesse caso, você pode também temos que considerar os restos).
Se você realmente tiver apenas uma transação por mês (como no seu exemplo), você não precisa fazer o group by.
Se os meses que você se preocupa não existem na sua tabela, então isso não vai funcionar, mas você pode fazer a mesma coisa, gerando uma tabela de meses. por exemplo, Se você tiver uma quantia em 2020-01-01 mas nada 2020-02-01, então isso não irá retornar uma linha para 2021-02-01.

Adrian White · Answer 2 · 2021-11-23T21:00:56

CTE = configurar conjunto de dados

CTE_2 = taxa pró-conjunto de dados

FINAL SQL = select future_cal_month,sum(pro_rated_amount) from cte_2 group by 1

with cte as (
select '2021-07-01' cal_month,'Annual Membership Fee' transaction ,45 amount
union all select '2021-08-01' cal_month,'Annual Membership Fee' transaction ,145 amount
union all select '2021-09-01' cal_month,'Annual Membership Fee' transaction ,2940 amount
union all select '2021-10-01' cal_month,'Annual Membership Fee' transaction ,1545 amount) 
, cte_2 as (    
select 
    dateadd('month', row_number() over (partition by cal_month order by 1), cal_month) future_cal_month
    ,amount/12 pro_rated_amount
from 
     cte 
    ,table(generator(rowcount => 12)) v)
select 
  future_cal_month
, sum(pro_rated_amount) 
from 
  cte_2 
group by 
  future_cal_month

Como divulgar montante anual e, em seguida, adicione por mês em SQL

Pergunta

Melhor resposta

Em outros idiomas

Esta página está em outros idiomas

Popular nesta categoria

Perguntas populares nesta categoria