The problem with using the quirky update is that it's, well, quirky. It's not guaranteed to work. Here is a method that uses guaranteed methods and performs only slightly slower on this sample data.
;
WITH CTETally AS
(
SELECT n
FROM (VALUES(1), (1), (1), (1), (1), (1)) n(n)
)
, bits AS
(
SELECT TOP(31) POWER(CAST(2 AS BIGINT), ROW_NUMBER() OVER(ORDER BY @@VERSION) - 1) AS bitmask
FROM CTETally A
CROSS JOIN CTETally AS b
)
, bitmasks AS
(
SELECT s.[Date], s.BitValue & b.bitmask AS BitValues
FROM #Sample s
INNER JOIN bits b
ON s.BitValue & b.bitmask = b.bitmask
GROUP BY s.[Date], s.BitValue & b.bitmask
)
SELECT [Date], SUM(BitValues)
FROM bitmasks
GROUP BY [Date]
ORDER BY [Date]
I haven't tested this on a large dataset.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA