Comment répartir le montant annuel puis ajouter par mois en SQL

Question 1

Actuellement je travaille avec un tableau qui ressemble à ceci:

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

l' amount sur cette table est le montant total mensuel (ex. J'ai 100 clients qui ont payé 15 $pour l'abonnement annuel, donc mon total montant mensuel serait de $1500).

Cependant, ce que je voudrais faire (et je n'ai aucune idée de comment) est de diviser le montant par 12 et de le répandre dans l'avenir, afin d'avoir un revenu mensuel par mois. Comme un exemple pour 2021-09-01 je voudrais obtenir le suivant:


$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.

Je me demandais si quelqu'un pouvait me donner une idée de comment faire cela dans une requête SQL/CTE?

Question 2

En supposant que tous les mois vous vous souciez existent dans votre tableau, je dirais quelque chose comme:

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

Pour chaque mois qui existe dans la table, cette somme de 1/12e du montant actuel et 11 mois (en utilisant le add_months fonction). Je pense que c'est ce que vous voulez.

Quelques remarques/réflexions:

Je suis en supposant (basé sur le nom de la colonne) que toutes les dates de la month colonne fin le 1er, donc on n'a pas besoin de s'inquiéter sur la correspondance de jours ou d'avoir l' group by retourner plusieurs lignes pour le même mois.
Vous pouvez arrondir le SUMs je l'ai fait, car dans certains cas, en divisant par 12 peut vous donner plus de chiffres après la virgule que vous souhaitez pour de l'argent (bien que, dans ce cas, vous pourriez aussi avoir à envisager de restes).
Si vous avez vraiment seulement une transaction par mois (comme dans votre exemple), vous n'avez pas besoin de faire la group by.
Si le mois vous vous souciez de n'existent pas dans votre tableau, puis cela ne fonctionne pas, mais on pourrait faire la même chose que la génération d'un tableau de mois. par exemple, Si vous avez un montant à la 2020-01-01 mais rien dans 2020-02-01, alors ce ne sera pas de retour une ligne pour 2021-02-01.

Question 3

CTE = mettre en place dataset

CTE_2 = pro-taux de dataset

FINALE DE 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

En supposant que tous les mois vous vous souciez existent dans votre tableau, je dirais quelque chose comme:

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

Pour chaque mois qui existe dans la table, cette somme de 1/12e du montant actuel et 11 mois (en utilisant le add_months fonction). Je pense que c'est ce que vous voulez.

Quelques remarques/réflexions:

Je suis en supposant (basé sur le nom de la colonne) que toutes les dates de la month colonne fin le 1er, donc on n'a pas besoin de s'inquiéter sur la correspondance de jours ou d'avoir l' group by retourner plusieurs lignes pour le même mois.
Vous pouvez arrondir le SUMs je l'ai fait, car dans certains cas, en divisant par 12 peut vous donner plus de chiffres après la virgule que vous souhaitez pour de l'argent (bien que, dans ce cas, vous pourriez aussi avoir à envisager de restes).
Si vous avez vraiment seulement une transaction par mois (comme dans votre exemple), vous n'avez pas besoin de faire la group by.
Si le mois vous vous souciez de n'existent pas dans votre tableau, puis cela ne fonctionne pas, mais on pourrait faire la même chose que la génération d'un tableau de mois. par exemple, Si vous avez un montant à la 2020-01-01 mais rien dans 2020-02-01, alors ce ne sera pas de retour une ligne pour 2021-02-01.

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

CTE = mettre en place dataset

CTE_2 = pro-taux de dataset

FINALE DE 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

Comment répartir le montant annuel puis ajouter par mois en SQL

La question

La meilleure réponse

Dans d'autres langues

Cette page est dans d'autres langues

Populaires dans cette catégorie

Questions populaires dans cette catégorie