• 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