quote:

---

- Why does "11/21/2010 B 2" have a count of 2? "B" only
appears once for that date. Same with most of the B's.

---

quote:

---

- Why does C only appear once? Is that because he first instance of C appears on 11/24/2010? Why doesn't C have a zero count for the other previous days?

---

DECLARE @Table1 TABLE 
(Id INT, [State] CHAR, [TDate] DATETIME)

INSERT @Table1
SELECT 1, 'A', '11/20/2010' UNION
SELECT 1, 'A', '11/21/2010' UNION
SELECT 1, 'B', '11/23/2010' UNION
SELECT 2, 'A', '11/20/2010' UNION
SELECT 2, 'B', '11/21/2010' UNION
SELECT 2, 'B', '11/22/2010' UNION
SELECT 3, 'B', '11/20/2010' UNION
SELECT 3, 'A', '11/23/2010' UNION
SELECT 3, 'C', '11/24/2010' 

DECLARE @Table2 TABLE 
([DDate] DATETIME)
INSERT @Table2
SELECT '11/20/2010' UNION
SELECT '11/21/2010' UNION
SELECT '11/22/2010' UNION
SELECT '11/23/2010' UNION
SELECT '11/24/2010' 

INSERT @Table1 
SELECT Id, [State], DDate
FROM 
(
	SELECT DDate, ID, TDate, State, 
	ROW_NUMBER() OVER (PARTITION BY Id, dDate Order by Id, Diff, dDate) as row
	FROM
	(
		SELECT DDate, t.Id, TDate, t.State, CAST(DATEDIFF(day, TDate, dDate) as int) as Diff
		FROM @Table2 d, @Table1 t
		WHERE CAST(DATEDIFF(DAY, TDate, DDate) as int) >= 1 
			AND	DDate NOT IN (SELECT TDate from @Table1 WHERE Id = t.Id)
	)[x]
)[y]
WHERE row = 1

SELECT TDate, State, COUNT(Id)
FROM @Table1
GROUP BY TDate, State
ORDER BY TDate, State

quote:

---

Originally posted by anilofar

11/21/2010 B has count of 2 because 2 has state B at 11/21/2010 and 3 has state B at 11/20/2010. I need to find accumulating state count where state info is missing for a some id, and dates

---

DECLARE @NumberRecords INT
DECLARE @RowCount INT
DECLARE @tempDate DATETIME

SET @NumberRecords = (SELECT COUNT(*) FROM TableB)
SET @RowCount = 1

IF OBJECT_ID('TempDB..#ResultTable','U') IS NOT NULL
    DROP TABLE #ResultTable
    
CREATE TABLE #ResultTable ([Date] DATETIME, State CHAR(1), ID INT)

WHILE @RowCount <= @NumberRecords
BEGIN
SELECT @tempDate = [Date] from TableB WHERE Id = @RowCount

    INSERT #ResultTable
    SELECT @tempDate AS [Date], State, Count(Id) ID
    FROM
    (
    SELECT A.Id
        , (SELECT TOP 1 State 
        FROM TableA AS B
        WHERE B.[Date] <= @tempDate
        AND A.Id = B.Id
        ORDER BY DATE Desc) AS State
    FROM TableA AS A
    )[X]
    GROUP BY State

SET @RowCount = @RowCount + 1
END

Date                    State ID
----------------------- ----- -----------
2010-11-20 00:00:00.000 A     6
2010-11-20 00:00:00.000 B     3
2010-11-21 00:00:00.000 A     3
2010-11-21 00:00:00.000 B     6
2010-11-22 00:00:00.000 A     3
2010-11-22 00:00:00.000 B     6
2010-11-23 00:00:00.000 A     3
2010-11-23 00:00:00.000 B     6
2010-11-24 00:00:00.000 B     6
2010-11-24 00:00:00.000 C     3

quote:

---

I still do not understand. What is the second 2 in your statement? Is that the ID? If so, how does that play into calculating the count?

---

SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1

WHILE @RowCount <= @NumberRecords
BEGIN
	SELECT @tempDate = Date from Table2 WHERE Id = @RowCount

	INSERT INTO #ResultTable
	SELECT @tempDate, State, Count(Id)
	FROM
	(
		SELECT A.Id
		, (SELECT TOP 1 State 
			FROM Table1 B
			WHERE B.Date <= @tempDate
				AND A.Id = B.Id
			ORDER BY DATE Desc) AS State
		FROM Table3 A
	)[X]
	GROUP BY State

	SET @RowCount = @RowCount + 1
END

SELECT 
    D.[Date], 
    D.State, 
    COUNT(D.ID) AS MyCount
FROM
    (
        SELECT 
            *
        FROM
            (
                SELECT 
                    *
                FROM
                    -- Assumeing these are already distinct
                    (SELECT ID FROM  TableC) AS C
                    CROSS JOIN
                    (SELECT [Date] FROM TableB) AS B
             ) AS Combo
        CROSS APPLY
            (
                SELECT TOP 1 State
                FROM TableA AS A
                WHERE A.[Date] <= Combo.[Date]
                AND A.ID = Combo.ID
                ORDER BY [Date] DESC
                
            ) AS T
    ) AS D
GROUP BY
    D.[Date], 
    D.State
ORDER BY
    D.[Date], 
    D.State