IF OBJECT_ID('TempDB..#multrecs','u') IS NOT NULL DROP TABLE #multrecsSELECT ID = IDENTITY(INT,1,1), acct, cdt, val, ndt INTO #multrecs FROM multrecs ORDER BY acct,cdt,val,ndt--Add columns for later useALTER 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 acctUPDATE #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 updatesDECLARE @acct VARCHAR(20)DECLARE @cdt SMALLDATETIMEDECLARE @ndt SMALLDATETIMEDECLARE @val VARCHAR(20)DECLARE @prev_ndt SMALLDATETIMEDECLARE @interval_run INT--First, populate the prev_ndt field for the rest of the recordsUPDATE #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 ndtUPDATE 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 >= 38UPDATE m SET @interval_run = interval_run = CASE WHEN acct = @acct AND @interval_run < 38 THEN @interval_run + interval ELSE interval END, @acct = acct FROM #multrecs m--See the resultsSELECT acct, cdt, val, ndt FROM #multrecs WHERE interval_run = 0 OR interval_run >= 38 ORDER BY acct,cdt,val,ndt
-------------------- Solution -----------------------------ALTER TABLE #multrecs ADD Display bitCREATE CLUSTERED INDEX Quirky ON #multrecs (acct,cdt,val,ndt) -- <----- Note this clustered index. Very important.DECLARE @PrevAcct bigint, @PrevCDT datetime, @PrevVal varchar(20), @LDNDT datetime, --Last Displayed NDT @Display bit -- Even though you don't use it, this won't function without the 3 part update UPDATE #multrecsSET @Display = Display = CASE WHEN Acct = @PrevAcct AND CDT = @PrevCDT AND Val = @PrevVal AND DATEDIFF(d,@LDNDT,NDT) < 38 THEN 0 ELSE 1 END, @LDNDT = CASE WHEN Acct = @PrevAcct AND CDT = @PrevCDT AND Val = @PrevVal AND DATEDIFF(d,@LDNDT,NDT) < 38 THEN @LDNDT ELSE NDT END, @PrevAcct = Acct, @PrevCDT = CDT, @PrevVal = ValFROM #Multrecs WITH (INDEX(0))SELECT * FROM #MultRecs WHERE Display = 1