Cum să se răspândească sumă anuală și apoi se adaugă pe lună în SQL

Question 1

În prezent lucrez cu un tabel care arata ca aceasta:

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

la amount pe masă este totalul lunar (ex. Am 100 de clienți care au plătit 15 dolari pentru un abonament anual, deci totalul lunar ar fi de $1500).

Cu toate acestea ceea ce aș vrea să fac (și nu am nici o idee cum) este de a împărți suma de 12 și răspândirea-l în viitor, în scopul de a avea un venit lunar pe luna. Ca un exemplu pentru 2021-09-01 mi-ar lua următoarele:


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

Mă întrebam dacă cineva ar putea da-mi o idee de cum de a efectua acest lucru într-o interogare SQL/CTE?

Question 2

Presupunând că toate lunile îți pasă există în masă, aș sugera ceva de genul:

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

Pentru fiecare lună, că există în masă, această sume 1/12 valoarea curentă plus anterioară de 11 luni (folosind funcția add_months). Cred că asta e ceea ce vrei.

Câteva note/gânduri:

Presupun (bazat pe numele de coloană) că toate datele din month coloana termina pe 1, deci nu trebuie să vă faceți griji despre potrivire zile sau având group by reveni mai multe rânduri pentru aceeași lună.
Ați putea dori pentru a rotunji SUMs-am făcut, deoarece, în unele cazuri, împărțirea la 12 s-ar putea da mai multe cifre după virgulă decât vrei tu pentru bani (deși, în acest caz, s-ar putea, de asemenea, trebuie să ia în considerare resturi).
Dacă într-adevăr doar o singură tranzacție pe lună (ca în exemplu), nu aveți nevoie pentru a face group by.
Dacă luni-ți pasă nu există în tabel, atunci acest lucru nu va funcționa, dar ai putea face același lucru generează un tabel de luni. de exemplu, Dacă aveți o sumă pe 2020-01-01 dar nimic în 2020-02-01, atunci acest lucru nu va reveni-un rând pentru 2021-02-01.

Question 3

CTE = înființat de date

CTE_2 = pro-rata de date

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

Presupunând că toate lunile îți pasă există în masă, aș sugera ceva de genul:

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

Pentru fiecare lună, că există în masă, această sume 1/12 valoarea curentă plus anterioară de 11 luni (folosind funcția add_months). Cred că asta e ceea ce vrei.

Câteva note/gânduri:

Presupun (bazat pe numele de coloană) că toate datele din month coloana termina pe 1, deci nu trebuie să vă faceți griji despre potrivire zile sau având group by reveni mai multe rânduri pentru aceeași lună.
Ați putea dori pentru a rotunji SUMs-am făcut, deoarece, în unele cazuri, împărțirea la 12 s-ar putea da mai multe cifre după virgulă decât vrei tu pentru bani (deși, în acest caz, s-ar putea, de asemenea, trebuie să ia în considerare resturi).
Dacă într-adevăr doar o singură tranzacție pe lună (ca în exemplu), nu aveți nevoie pentru a face group by.
Dacă luni-ți pasă nu există în tabel, atunci acest lucru nu va funcționa, dar ai putea face același lucru generează un tabel de luni. de exemplu, Dacă aveți o sumă pe 2020-01-01 dar nimic în 2020-02-01, atunci acest lucru nu va reveni-un rând pentru 2021-02-01.

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

CTE = înființat de date

CTE_2 = pro-rata de date

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

Cum să se răspândească sumă anuală și apoi se adaugă pe lună în SQL

Problema

Cel mai bun răspuns

În alte limbi

Această pagină este în alte limbi

Popular în această categorie

Întrebări frecvente în această categorie