Adding Future Dates

SSAS and SSRS
1

I have a project where we need to schedule visits for clients 25 months ahead of time. The issue is that we don't know exactly when the client will show up for the visit. Furthermore, the scheduled visits for the 25 months are based on their initial visit. These scheduled visits are based on 1 month intervals.

For example, if Client A shows up for their initial visit on 3/15, their scheduled Month1 visit will be 4/15. If Client B shows up for their initial visit on 3/20, their scheduled Month 1 visit will be on 4/20.

Then if Client A shows up for their Month1 visit on 4/17, their Month2 scheduled visit now moves to 5/17. Same with Client B if they show up on 4/19, their Month2 visit is now scheduled for 5/19.

Any help would be appreciated.

2

you can have

... the initial visits as parameters ..and then do the calculation for schedule visit

perhaps a stored procedure

3

For SSRS you should think about something like this, for SSAS you can use calculations to do that kind of stuff / there will be beter solutions then this.

DECLARE @TableClientVisit AS TABLE (Client nvarchar(250), Visit DateTime)
INSERT INTO @TableClientVisit
SELECT 'Client A', DATEFROMPARTS(2021,3,15)
UNION
SELECT 'Client B', DATEFROMPARTS(2021,3,20)
UNION
SELECT 'Client C', NULL
UNION
SELECT 'Client A', DATEFROMPARTS(2021,4,17)
UNION
SELECT 'Client B', DATEFROMPARTS(2021,4,19)
UNION
SELECT 'Client C', DATEFROMPARTS(2021,4,13)
UNION
SELECT 'Client A', DATEFROMPARTS(2021,4,17)
UNION
SELECT 'Client A', DATEFROMPARTS(2021,5,30)
UNION
SELECT 'Client A', DATEFROMPARTS(2021,6,17)

SELECT
Client,
MAX(MaxVisit) AS MaxVisit,
MAX(MaxVisitNumber) AS MaxVisitNumber,
MAX([1]) AS Month1,
MAX(ISNULL([2], DATEADD(m,2-MaxVisitNumber, MaxVisit))) AS Month2,
MAX(ISNULL([3], DATEADD(m,3-MaxVisitNumber, MaxVisit))) AS Month3,
MAX(ISNULL([4], DATEADD(m,4-MaxVisitNumber, MaxVisit))) AS Month4,
MAX(ISNULL([5], DATEADD(m,5-MaxVisitNumber, MaxVisit))) AS Month5,
MAX(ISNULL([6], DATEADD(m,6-MaxVisitNumber, MaxVisit))) AS Month6
FROM
(
SELECT
Client,
MAX(MaxVisit) AS MaxVisit,
MAX(MaxVisitNumber) AS MaxVisitNumber,
MAX([1]) AS [1],
MAX([2]) AS [2],
MAX([3]) AS [3],
MAX([4]) AS [4],
MAX([5]) AS [5],
MAX([6]) AS [6]
FROM
(
SELECT
Client,
Visit,
MAX(Visit) AS MaxVisit,
VisitNumber,
MAX(VisitNumber) AS MaxVisitNumber
FROM
(
SELECT
Client,
Visit,
ROW_NUMBER() OVER (PARTITION BY Client ORDER BY Visit) AS VisitNumber
FROM
@TableClientVisit
WHERE Visit IS NOT NULL
) tbl_
GROUP BY
Client,
Visit,
VisitNumber
) tbl
PIVOT
(
MAX(Visit)
FOR VisitNumber
IN ([1],[2],[3],[4],[5],[6])
) PivotTable
GROUP BY
Client
) tbl
GROUP BY
Client