SQL Server Find the Date Range based on Start Date and Frequence-CodePudding

I'm trying to create a function where I can give it any date and it can return a billing date range for a specific asset.

E.g. Below I've got an example for AssetBilling table which tells me how frequently an asset should be billed based on the start date.

If I give the function a date: 25/02/2021 for the asset Red Car it should tell me that the billing period it falls under is: FROM 01/02/2021 TO 01/03/2021

I have tried the follow CTE but I'm not entirely sure how to use it as I've not done it before:

DECLARE @InvDate AS DATE = '25/02/2021',
        @ID AS INT = 1 -- Red Car example
;WITH CTE AS (
    SELECT StartDate, DATEADD(MM, Frequency, StartDate) EndDate 
    FROM AssetBilling WHERE ID = @ID
    UNION ALL
    SELECT DATEADD(MM, 1, StartDate), DATEADD(MM, 1, EndDate)
    FROM CTE 
    WHERE
    StartDate >= @InvDate AND EndDate <= @InvDate 
)
SELECT *
FROM 
CTE

This is the result that I get:

CREATE TABLE [dbo].[AssetBilling](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Asset] [varchar] (150) NULL,
    [StartDate] [date] NULL,
    [Frequency] [int] NULL,
    [BillType][varchar](50)NULL
);

INSERT INTO AssetBilling
(Asset, StartDate, Frequency, BillType)
VALUES
('Red Car', '01/01/2021', 1, 'Monthly'),
('Blue Car', '25/05/2021', 3, 'Quarterly')

CodePudding user response：

I don't believe you need a recursive CTE here but to Larnu's point please show what you want to happen when the frequency is not monthly (to modify this query to support that I'd need to understand exactly what you want when it's quarterly for example, and the InvDate provided is in the 2nd or 3rd month of the period).

DECLARE @InvDate date = '20210225', -- avoid regional, ambiguous formats
        @ID      int  = 1;

;WITH src(StartDate, Frequency) AS 
(
  SELECT DATEADD(MONTH, 
    DATEDIFF(MONTH, StartDate, @InvDate), StartDate), Frequency
  FROM dbo.AssetBilling -- always use schema prefix
  WHERE ID = @ID
)
SELECT StartDate, EndDate = DATEADD(MONTH,Frequency,StartDate)
  FROM src;

CodePudding user response：

This is very much a guess, with one vague example and no further example or explanation were given on request, but maybe this:

DECLARE @InvDate date = '20220225',
        @ID int = 1;

SELECT StartDate,
       CASE WHEN @InvDate > EndDate THEN EndDate --I assume you have an end date
            WHEN DATEDIFF(MONTH,StartDate,@InvDate) < Frequency THEN DATEADD(MONTH, Frequency, StartDate)
            WHEN DATEDIFF(MONTH,StartDate,@InvDate) % Frequency != 0 THEN DATEADD(MONTH, ((DATEDIFF(MONTH,StartDate,@InvDate) / Frequency) * Frequency)   Frequency, StartDate)
            ELSE @InvDate
       END
FROM dbo.AssetBilling
WHERE ID = @ID;

db<>fiddle

There may be scenarios I have no covered in the WHEN, but this is hard to know when, again, we have 1 example. Maybe this gives you a big enough clue.