I would be curious how this solution stacks up performance-wise to the others, although I don't have time at the moment to check.

WITH FancyWayToFindGaps AS

(

SELECT palstkid, extractdate=DATEADD(day, -1, MIN(extractdate))

,[days]=1+DATEDIFF(day, MIN(extractdate), MAX(extractdate))

FROM

(

SELECT palstkid, extractdate

,grp=ROW_NUMBER() OVER (PARTITION BY palstkid ORDER BY extractdate)/2

FROM

(

SELECT palstkid

,gapstart=DATEADD(day, 1, MAX(extractdate))

,gapend=DATEADD(day, -1, MIN(extractdate))

FROM

(

SELECT palstkid, extractdate, grp, rn

FROM

(

SELECT palstkid, extractdate

,grp=CAST(extractdate AS DATETIME)-ROW_NUMBER() OVER (PARTITION BY palstkid ORDER BY extractdate)

,rn=ROW_NUMBER() OVER (PARTITION BY palstkid, extractdate ORDER BY extractdate)

FROM #palletstockhistory

) a

WHERE rn=1

) a

GROUP BY palstkid, grp

) a

CROSS APPLY

(

VALUES (gapstart),(gapend)

) b (extractdate)

) a

GROUP BY palstkid, grp

HAVING COUNT(*) = 2

)

SELECT b.pid, extractdate=DATEADD(day, n, a.extractdate), a.palstkid, b.uomrecqty, b.qty

,b.quantityRunningTotal, daysinstock=daysinstock + n

FROM FancyWayToFindGaps a

JOIN #palletstockhistory b ON a.palstkid = b.palstkid AND a.extractdate = b.extractdate

CROSS APPLY

(

SELECT TOP (a.[days]) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)

CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)

) Tally (n)

UNION ALL

SELECT *

FROM #palletstockhistory;

IO counts:

Table '#palletstockhistory. Scan count 3, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

This uses a rather obscure technique to find islands in the dates, convert those to gaps and then use a Tally table to expand that set out to the rows required to fill in the gaps. Despite its relative obscurity, it does pretty well performance-wise in finding gaps.

The SQL of Gaps and Islands in Sequences [/url]