How can I populate a table in one month increments based on a date

Question

I need to load a table based on the entry date from another table being between the 1st day of a month and the last day of the month. I need to decrement the load to be 1 month's worth of records 1 month from the current month, then 2 months from the current month and so so.

I've tried CTE, using a cursor, and manually changing the datediff parameters. This last method works, but I would like to load the table dynamically

Declare @StartDt as datetime = (SELECT FORMAT(DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,GETDATE())-1,0)),'MM/dd/yyyy'));

Declare @endDt as datetime = EOMONTH(@StartDt);

SELECT  useruid, 
    userfullname,
    sum(totaltime)SumTime,
    @StartDt as StartDate, 
    @EndDt as EndDate

FROM Table_1.tbl
WHERE ENTRYDATE between @StartDt  and @EndDt
group by useruid, USERFULLNAME;

I am inserting this into another table. I don't have an issue doing that by manually changing -1 in the Startdt variable to -2 and so on. I want to know if I can have it change dynamically while loading the other table.

Again, I have no issues loading the table. I just want to know if there is a more efficient way to do so as I have to go back 100 months.

Answer 1

You can use dynamic SQL to write your @StartDt declare statement on the fly.

I put your code in the while loop which will run 100 times. (go back 100 months):

declare @int int = 0
while @int > -100
begin
set @int= @int -1
declare @str varchar(max)
set @str = 'declare @StartDt as datetime = (SELECT FORMAT(DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,GETDATE())'+cast(@int as varchar(10))+',0)),''MM/dd/yyyy''))'
execute (@str)

Declare @endDt as datetime = EOMONTH(@StartDt)

SELECT  useruid, 
    userfullname,
    sum(totaltime)SumTime,
    @StartDt as StartDate, 
    @EndDt as EndDate

FROM Table_1.tbl
WHERE ENTRYDATE between @StartDt  and @EndDt
group by useruid, USERFULLNAME
end