TSQL - Replace Cursor

Question

I found in our database a cursor statement and I would like to replace it.

Declare @max_date datetime
Select @max_date = max(finished) From Payments
Declare @begin_date datetime = '2015-02-01'
Declare @end_of_last_month datetime 
While @begin_date <= @max_date
Begin
    SELECT @end_of_last_month = CAST(DATEADD(DAY, -1 , DATEFROMPARTS(YEAR(@begin_date),MONTH(@begin_date),1)) AS DATE) --AS end_of_last_month

    Insert Into @table(Customer, ArticleTypeID, ArticleType, end_of_month, month, year)
    Select Count(distinct (customerId)), prod.ArticleTypeID, at.ArticleType, @end_of_last_month, datepart(month, @end_of_last_month), datepart(year, @end_of_last_month)
    From Customer cust
    Inner join Payments pay ON pay.member_id = m.member_id
    Inner Join Products prod ON prod.product_id = pay.product_id
    Inner Join ArticleType at ON at.ArticleTypeID = prod.ArticleTypeID
    Where  @end_of_last_month between begin_date and expire_date
        and completed = 1
        Group by prod.ArticleTypeID, at.ArticleType
    order by prod.ArticleTypeID, at.ArticleType

    Set @begin_date = DATEADD(month, 1, @begin_date)
    End

It groups all User per Month where the begin- and expire date in the actual Cursormonth.

Notes: The user has different payment types, for e.g. 1 Month, 6 Month and so on.

Is it possible to rewrite the code - my problem is only the identification at the where clause (@end_of_last_month between begin_date and expire_date) How can I handle this with joins or cte's?

Answer 1

What you need first, if not already is a numbers table

Using said Numbers table you can create a dynamic list of dates for "end_of_Last_Month" like so

;WITH ctexAllDates
AS (
    SELECT  end_of_last_month = DATEADD(DAY, -1, DATEADD(MONTH, N.N -1, @begin_date))
    FROM
            dbo.Numbers N
    WHERE
            N.N <= DATEDIFF(MONTH, @begin_date, @max_date) + 1
)
select * FROM ctexAllDates

Then combine with your query like so

;WITH ctexAllDates
AS (
    SELECT  end_of_last_month = DATEADD(DAY, -1, DATEADD(MONTH, N.N -1, @begin_date))
    FROM
            dbo.Numbers N
    WHERE
            N.N <= DATEDIFF(MONTH, @begin_date, @max_date) + 1
)
INSERT INTO @table
(
      Customer
    , ArticleTypeID
    , ArticleType
    , end_of_month
    , month
    , year
)
SELECT
              COUNT(DISTINCT (customerId))
            , prod.ArticleTypeID
            , at.ArticleType
            , A.end_of_last_month
            , DATEPART(MONTH, A.end_of_last_month)
            , DATEPART(YEAR, A.end_of_last_month)
FROM
            Customer    cust
INNER JOIN  Payments    pay ON pay.member_id    = m.member_id
INNER JOIN  Products    prod ON prod.product_id = pay.product_id
INNER JOIN  ArticleType at ON at.ArticleTypeID  = prod.ArticleTypeID
LEFT JOIN ctexAllDates A ON     A.end_of_last_month BETWEEN begin_date AND expire_date
WHERE           completed   = 1
GROUP BY
              prod.ArticleTypeID
            , at.ArticleType
            , A.end_of_last_month
ORDER BY
              prod.ArticleTypeID
            , at.ArticleType;