SQLMAIN, here is a temp table solution. It works by determining the previous ndt date for each record in the group, then keeping a running sum of the interval between the ndt and the previous ndt. It is powered by Jeff Moden's running sum solution using the "quirky update". Admitedly, I don't fully grasp how it works exactly, so when I am using it, I have to add columns to the temp table for each of the variables I use, and populate the columns with the current value of each variable at various point for each record. That way, I can move stuff around, and see how it affects the value of each variable, and when in the code it does it. Anyhow, I think this works as per your specs, but there are no guarantees of any data beyond that. Also, I tried to to all the updates in one fell swoop, but I could not get it to work.

I can't find the link to Jeff's article, I have it on my work pc, but I am at home. Maybe someone could post it?

IF OBJECT_ID('TempDB..#multrecs','u') IS NOT NULL

DROP TABLE #multrecs

SELECT

ID = IDENTITY(INT,1,1),

acct,

cdt,

val,

ndt

INTO #multrecs

FROM multrecs

ORDER BY acct,cdt,val,ndt

--Add columns for later use

ALTER TABLE #multrecs

ADD MIN_ndt BIT,

PREV_NDT SMALLDATETIME,

INTERVAL INT,

INTERVAL_RUN INT

--Start by setting the MIN_ndt flag for the lowest ndt for each acct

UPDATE #multrecs

SET MIN_ndt = CASE WHEN t1.acct IS NOT NULL THEN 1 ELSE 0 END

FROM #multrecs m LEFT OUTER JOIN

(

SELECT

acct,

cdt,

val,

MIN_ndt = MIN(ndt)

FROM multrecs

GROUP BY acct,cdt,val

) t1

ON m.acct = t1.acct

AND m.cdt = t1.cdt

AND m.val = t1.val

AND m.ndt = t1.MIN_ndt

--Set the prev_ndt field = MIN_ndt for the first of each group

--I am assuming if the ndt is lowest, there are no previous.

--It will be use later for a datediff calculation to check for

--the 38 day interval.

UPDATE m

SET PREV_NDT = ndt

FROM #multrecs m

WHERE MIN_ndt = 1

--Declare the local variables for the updates

DECLARE @acct VARCHAR(20)

DECLARE @cdt SMALLDATETIME

DECLARE @ndt SMALLDATETIME

DECLARE @val VARCHAR(20)

DECLARE @prev_ndt SMALLDATETIME

DECLARE @interval_run INT

--First, populate the prev_ndt field for the rest of the records

UPDATE #multrecs

SET @prev_ndt =

CASE WHEN @acct = acct

AND @cdt = cdt

AND @val = val

AND prev_ndt IS NULL

THEN @ndt

ELSE prev_ndt

END,

prev_ndt = @prev_ndt,

@acct = acct,

@cdt = cdt,

@val = val,

@ndt = ndt

FROM #multrecs

--Now populate the interval field, which will tell you the interval

--between the ndt and the previous ndt

UPDATE m

SET interval = DATEDIFF(d,prev_ndt,ndt),

interval_run = 0

FROM #multrecs m

--Finally, update the interval_run field. It will show 0 for the first record in each

--acct. Then it will keep a running sum of the interval, and reset when it gets

--to be >= 38

UPDATE m

SET @interval_run = interval_run =

CASE WHEN acct = @acct AND @interval_run = 38

ORDER BY acct,cdt,val,ndt