CODEWITHSUNDEEP
sql-serverdaterevenue
Luis A
Luis A

Reputation: 21

MSSQL Query to defer income by dividing over the next 12 months

hope you are staying safe ! . I have a requirement to defer income from our memberships sales over 12 months. my table has 2 columns :

1. SaleDate.
2. Revenue

I need to split the revenue into 12 months . for example if the membership cost $1200 and is sold on june 17 then each month would get $100 and the date needs to look like this

Date           Revenue
June           100
july           100
August         100
September      100

and so on until May .

So it's basically a running total of all the revenue we make divided by the next 12 months . and we would do the same thing every month. Any help would be appreciated.

Upvotes: -2

Views: 200

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81970

Perhaps with a CROSS JOIN

Example

Declare @YourTable Table ([SaleDate] date,[Revenue] money)  
Insert Into @YourTable Values 
 ('2020-06-17',1200)
 
Select [Date]    =dateadd(month,n,[SaleDate])
      ,[Revenue] =A.Revenue/12
 from @YourTable A
 Cross Join ( values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11) ) B(n)

Returns

Date        Revenue
2020-06-17  100.00
2020-07-17  100.00
2020-08-17  100.00
2020-09-17  100.00
2020-10-17  100.00
2020-11-17  100.00
2020-12-17  100.00
2021-01-17  100.00
2021-02-17  100.00
2021-03-17  100.00
2021-04-17  100.00
2021-05-17  100.00

Upvotes: 1

Related Questions