I have the below table ordered by clientID, contractID and effectiveDate. One Client has multiple contractID and it's respective effectiveDate.

the desired output is as below, where the new FYStartDate column should add the missing FYStartDate between 2 dates of subsequent contractIDs of a clientID(in this scenario, Fiscal Year starts on 01June of every year)

I would be appreciate if you could share the required SQL code.

I'm attaching the SQL code to generate the first table

CREATE TABLE [client] (
[clientid] [int] NULL,
[contractid] [int] NULL,
[effectivedate] [date] NULL
) ON [PRIMARY]
GO 

insert into [client] values
('228','2','6/1/2003'),('228','136','6/1/2004'),('228','242','6/1/2008'), 
('228','337','12/1/2012'),('228','584','6/1/2017'),('14216','319','5/1/2013'), 
('14216','355','6/1/2013'),('14216','739','6/1/2020'),('14216','10','3/1/2021'), 
('14216','1009','6/1/2021')

UPDATE: Also, as recommended by @Matbailie, below is the link to Excel file containing DimDate table with FY related columns.

https://1drv.ms/x/s!AsvLF4iggeEss3FU28wuczc7wBZB?e=om5qS2

CodePudding user response：

This is a bit convoluted without as @MatBailie suggest about having more structured data. To accomplish what you ask, each record needs to know when the contract before it and after it comes into effect. I think you need to play with the ordering because I didn't quite get how to order the results... by clientid, contractid, dates, etc?

UPDATED: see comments. Changed some CTEs, JOINS and ORDER BY for better partitioning by clientid.

CREATE TABLE [client] (
[clientid] [int] NULL,
[contractid] [int] NULL,
[effectivedate] [date] NULL
) ON [PRIMARY]
;

insert into [client] values
('228','2','6/1/2003'),('228','136','6/1/2004'),('228','242','6/1/2008'), 
('228','337','12/1/2012'),('228','584','6/1/2017'),('14216','319','5/1/2013'), 
('14216','355','6/1/2013'),('14216','739','6/1/2020'),('14216','10','3/1/2021'), 
('14216','1009','6/1/2021')
;

--Need a sequence of numbers to create a sequence of fiscal years.
WITH x AS (
  SELECT * FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as x(a)
), y as (
  SELECT ROW_NUMBER() OVER(ORDER BY tens.a, ones.a) as row_num
  FROM x as ones, x as tens
), fiscYears as (
  SELECT
    fyStart = DATEFROMPARTS(2000   y.row_num -1, 6, 1)
    , fyEnd = DATEFROMPARTS(2000   y.row_num, 5, 31)
  FROM y

--Need to order the client records by effective date.
--From updated question... looks like we are reporting by clientid.
), clientOrd as (
  SELECT c2.*, ROW_NUMBER() OVER(PARTITION BY c2.clientid ORDER BY c2.effectivedate) as row_num
      FROM client c2

--For each contract, get the previous and next contracts by effective date.
), clientWNext as (
  SELECT c.*
    , cNext.effectivedate as nextEffectiveDate
    , cPrev.effectivedate as prevEffectiveDate
  FROM clientOrd as c
    LEFT JOIN clientOrd as cNext
      ON cNext.clientid = c.clientid
      AND cNext.row_num = c.row_num   1
    LEFT JOIN clientOrd as cPrev
      ON cPrev.clientid = c.clientid
      AND cPrev.row_num = c.row_num - 1
)
SELECT
  c.clientid
  , cwn.contractid
  , CASE WHEN cwn.effectiveDate >= fy.fyStart AND cwn.effectiveDate <= fy.fyEnd 
      THEN  cwn.effectivedate
      ELSE null
    END as effectivedate
  , fy.fyStart
FROM fiscYears as fy
  --To get a full FY range for each client, we join to a distinct list of clients.
  JOIN (
    SELECT DISTINCT clientid FROM client
  ) as c
    ON 1=1

  --Need to join the list of contracts.
  INNER JOIN clientWNext as cwn
    ON cwn.clientid = c.clientid
  
    --This is the main join criteria where the effective date is within the fy year start/end.
    AND ((
      cwn.effectivedate >= fy.fyStart
      AND cwn.effectivedate <= fy.fyEnd
    ) 

    --This is the "alternate" join criteria where the previous contrat is still in effect
    --but there is no new contract to supercede the previous.
    OR (
      cwn.prevEffectiveDate < fy.fyStart
      AND cwn.effectiveDate < fy.fyStart
      AND (cwn.nextEffectiveDate > fy.fyEnd OR cwn.nextEffectiveDate IS NULL)
    ))

--Limiting fiscal year date range.
WHERE fy.fyStart >= '1/1/2003'
  AND fy.fyStart < '1/1/2024'
ORDER BY c.clientid, fy.fyStart, cwn.effectivedate

fiddle