Come diffondere importo annuale e quindi aggiungere al mese in SQL

Question 1

Attualmente sto lavorando con una tabella simile a questa:

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

il amount in tale tabella è la totale importo mensile (ex. Ho 100 clienti che hanno pagato 15 $per l'abbonamento annuale, quindi il mio totale importo mensile sarebbe $1500).

Tuttavia quello che vorrei fare (e non ho idea di come) è dividere l'importo da 12 e a diffondere nel futuro, per avere un reddito mensile al mese. Un esempio per 2021-09-01 vorrei ottenere i seguenti:


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

Mi chiedevo se qualcuno potesse darmi un'idea di come eseguire questo in una query SQL/CTE?

Question 2

Supponendo che tutti i mesi si cura di esistere nella tabella, vorrei suggerire qualcosa di simile:

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

Per ogni mese che esiste nella tabella, questo in sintesi 1/12 corrente importo maggiorato del precedente 11 mesi (utilizzando il add_months funzione). Penso che quello che si desidera.

Un paio di note e pensieri:

Sto assumendo (in base al nome della colonna) che tutte le date in month fine colonna 1, quindi non abbiamo bisogno di preoccuparsi di matching giorni o aventi group by ritorno più righe per lo stesso mese.
Si potrebbe desiderare di turno il SUMs ho fatto, dal momento che, in alcuni casi, dividendo per 12 potrebbe dare di più cifre decimali che si desidera per i soldi (anche se, in questo caso, si potrebbe prendere in considerazione anche i residui).
Se è davvero solo una transazione al mese (come nel tuo esempio), non c'è bisogno di fare il group by.
Se i mesi che ti interessano non esiste nella tabella, quindi questo non funziona, ma si potrebbe fare la stessa cosa generare una tabella di mesi. ad esempio, Se si dispone di un importo 2020-01-01, ma nulla in 2020-02-01, quindi questo non restituisce una riga per 2021-02-01.

Question 3

CTE = set up set di dati

CTE_2 = pro-tasso di set di dati

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

Supponendo che tutti i mesi si cura di esistere nella tabella, vorrei suggerire qualcosa di simile:

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

Per ogni mese che esiste nella tabella, questo in sintesi 1/12 corrente importo maggiorato del precedente 11 mesi (utilizzando il add_months funzione). Penso che quello che si desidera.

Un paio di note e pensieri:

Sto assumendo (in base al nome della colonna) che tutte le date in month fine colonna 1, quindi non abbiamo bisogno di preoccuparsi di matching giorni o aventi group by ritorno più righe per lo stesso mese.
Si potrebbe desiderare di turno il SUMs ho fatto, dal momento che, in alcuni casi, dividendo per 12 potrebbe dare di più cifre decimali che si desidera per i soldi (anche se, in questo caso, si potrebbe prendere in considerazione anche i residui).
Se è davvero solo una transazione al mese (come nel tuo esempio), non c'è bisogno di fare il group by.
Se i mesi che ti interessano non esiste nella tabella, quindi questo non funziona, ma si potrebbe fare la stessa cosa generare una tabella di mesi. ad esempio, Se si dispone di un importo 2020-01-01, ma nulla in 2020-02-01, quindi questo non restituisce una riga per 2021-02-01.

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

CTE = set up set di dati

CTE_2 = pro-tasso di set di dati

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

Come diffondere importo annuale e quindi aggiungere al mese in SQL

Domanda

Migliore risposta

In altre lingue

Questa pagina è in altre lingue

Popolare in questa categoria

Domande popolari in questa categoria