Query to find Cumulative while subtracting other counts

Question

Here is my table structure

Id     INT
RecId  INT
Dated  DATETIME
Status INT

and here is my data.

Status table (contains different statuses)

Id   Status
1    Created
2    Assigned

Log table (contains logs for the different statuses that a record went through (RecId))

Id   RecId     Dated                              Status  

1     1       2013-12-09 14:16:31.930               1
2     7       2013-12-09 14:27:26.620               1
3     1       2013-12-09 14:27:26.620               2
3     8       2013-12-10 11:14:13.747               1
3     9       2013-12-10 11:14:13.747               1
3     8       2013-12-10 11:14:13.747               2

What I need to generate a report from this data in the following format.

Dated                     Created                   Assigned
2013-12-09                 2                         1
2013-12-10                 3                         1

Here the rows data is calculated date wise. The Created is calculated as (previous record (date) Created count - Previous date Assigned count) + Todays Created count.

For example if on date 2013-12-10 three entries were made to log table out of which two have the status Created while one has the status assigned. So in the desired view that I want to build for report, For date 2013-12-10, the view will return Created as 2 + 1 = 3 where 2 is newly inserted records in log table and 1 is the previous day remaining record count (Created - Assigned) 2 - 1.

I hope the scenario is clear. Please ask me if further information is required.

Please help me with the sql to construct the above view.

Answer 1

This matches the expected result for the provided sample, but may require more testing.

with CTE as (
        select
                  *
                , row_number() over(order by dt ASC) as rn
        from (
              select
                      cast(created.dated as date) as dt
                    , count(created.status)       as Created
                    , count(Assigned.status)      as Assigned
                    , count(created.status) 
                      - count(Assigned.status)    as Delta
              from LogTable created
              left join LogTable assigned
                           on created.RecId = assigned.RecId
                          and created.status = 1
                          and assigned.Status = 2
                          and created.Dated <= assigned.Dated
              where created.status = 1
              group by
                      cast(created.dated as date)
          ) x
     )
select
    dt.dt
  , dt.created + coalesce(nxt.delta,0) as created
  , dt.assigned
from CTE dt
left join CTE nxt on dt.rn = nxt.rn+1
;

Result:

|         DT | CREATED | ASSIGNED |
|------------|---------|----------|
| 2013-12-09 |       2 |        1 |
| 2013-12-10 |       3 |        1 |

See this SQLFiddle demo