Not sure how to ask this correctly.

I have a few tables for count, revenue and processed transactions.
Typically these recorded daily. So naturally if i wanted to calculate revenue it would be 1st day to the last day of the month.

However whenever we invoice a client its not the first to the last day of the month. It is 22nd of each month to the 21st of the following month.

As all the data is stored on different tables how would i get each table to roll up the counts, revenue and transactions by 22nd to 21st of each month but they would represent a whole month

So e.g.

January 21 = 22/12/2020 - 21/01/2021 February 21 = 22/01/2021 - 21/02/2021 ...

CodePudding user response：

You can subtract 21 days and then add a month. Then, eomonth() or something similar to identify the month. For instance, to count the number of rows in "january"

select eomonth(thedate) as monthendate,
       dateadd(year(thedate), month(thedate), 1) as month_day1
from t cross apply
     (values (dateadd(month, 1, dateadd(day, -21, datecol)))
     ) v(thedate)