Sql-server – Can’t get the SQL query to filter by dates like I want

You can calculate a running total in SQL 2008 R2 by using the ROW_NUMBER() window function along with a correlated sub-query to calculate the running total column.

Here is an example using some data I have available. You would need to amend the query accordingly to suit your tables/data and would likely need to use a PARTITION BY within the ROW_NUMBER function so you get a running total for the different projects/record types.

If you are able to post some sample data I can provide you with an example using your data structures.

WITH ContactMonthOrdered AS
(
    SELECT
            ContactMonthStartDate
        ,   COUNT(*) AS Total
        ,   ROW_NUMBER() OVER(ORDER BY ContactMonthStartDate) AS MonthOrder

    FROM
        Clinical.ContactDetail

    WHERE
        ContactFiscalYearLongName = '2014/2015'

    GROUP BY
        ContactMonthStartDate
)

SELECT
        ContactMonthStartDate
    ,   Total
    ,   MonthOrder
    ,   (
            SELECT  SUM(Total)
            FROM    ContactMonthOrdered
            WHERE   MonthOrder <= c.MonthOrder
        ) AS RunningTotal

FROM
    ContactMonthOrdered AS c

ORDER BY
    c.MonthOrder

UPDATED The query below is derived from the example query you posted in your question. Provided I understand your schema correctly, this will give you the total for each period for a project and also a running total across the periods for a project. The assumption is that the sequence of PeriodId is meaningful.

;WITH CommitmentPeriodCost AS
(
    SELECT
        Commitments.ProjectId
        , CommitmentDetails.PeriodId
        , SUM(CommitmentDetails.TotalCost) AS TotalCost

    FROM
        CostManagement_Commitments AS Commitments
        LEFT JOIN CostManagement_CommitmentDetails AS CommitmentDetails
            ON  Commitments.Id = CommitmentDetails.CommitmentId

    WHERE
        Commitments.ProjectId = 1

    GROUP BY
        Commitments.ProjectId
        , CommitmentDetails.PeriodId
)

SELECT
    ProjectId
    , PeriodId
    , TotalCost
    , (
            SELECT  SUM(TotalCost)
            FROM    CommitmentPeriodCost AS RunningCost
            WHERE   ProjectId = CommitmentPeriodCost.ProjectId
                    AND PeriodId <= CommitmentPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    CommitmentPeriodCost

ORDER BY
    ProjectId
    , PeriodId

It looks like you're on the right path. Your datebit pattern idea is an interesting option, I'd like to hear if you make any more progress towards that design.

My suggestion is a pretty simple one. Why not split the ItemOfType out to their own tables and reference them with a surrogate id?. I know it isn't always a popular idea if you have good natural keys available, but in terms of space saving it could reduce your storage requirement a bit.

This was too much for a comment, please don't crucify me.

create table dbo.ItemOfType1 (
    ItemOfType1Id int identity(1,1) not null
  , ItemOfType1Code char(12) not null
  , ReferenceType bit not null
  , constraint pkc_ItemOfType1 primary key clustered (ItemOfType1Id)
  , constraint uq_ItemOfType1_ItemOfType1Code_ReferenceType unique nonclustered (ItemOfType1Code, ReferenceType)
);
create table dbo.ItemOfType2 (
    ItemOfType2Id int identity(1,1) not null
  , ItemOfType2Code char(7) not null
  , ReferenceType bit not null
  , constraint pkc_ItemOfType2 primary key clustered (ItemOfType2Id)
  , constraint uq_ItemOfType2_ItemOfType2Code_ReferenceType unique nonclustered (ItemOfType2Code, ReferenceType)
);
create table dbo.ItemOfType1Dates (
    ItemOfType1Id int  not null
  , ReferenceDate date not null
  , constraint pkc_ItemOfType1Dates primary key clustered (ReferenceDate,ItemOfType1Id)
  , constraint fk_ItemOfType1Dates_ItemOfType1_ItemOfType1Id foreign key (ItemOfType1Id) references dbo.ItemOfType1 (ItemOfType1Id)
);
create table dbo.ItemOfType2Dates (
    ItemOfType2Id int  not null
  , ReferenceDate date not null
  , constraint pkc_ItemOfType2Dates primary key clustered (ReferenceDate,ItemOfType2Id)
  , constraint fk_ItemOfType2Dates_ItemOfType2_ItemOfType2Id foreign key (ItemOfType2Id) references dbo.ItemOfType2 (ItemOfType2Id)
);
/*  -- date ranges alternate
create table dbo.ItemOfType1DateRanges (
    ItemOfType1Id int  not null
  , FromBusinessDate date not null
  , ToBusinessDate date not null     
  , constraint pkc_ItemOfType1DateRanges primary key clustered (FromBusinessDate,ItemOfType1Id)
  , constraint fk_ItemOfType1DateRanges_ItemOfType1_ItemOfType1Id foreign key (ItemOfType1Id) references dbo.ItemOfType1 (ItemOfType1Id)
);
create table dbo.ItemOfType2DateRanges (
    ItemOfType2Id int  not null
  , FromBusinessDate date not null
  , ToBusinessDate date not null     
  , constraint pkc_ItemOfType2DateRanges primary key clustered (FromBusinessDate,ItemOfType2Id)
  , constraint fk_ItemOfType2DateRanges_ItemOfType2_ItemOfType2Id foreign key (ItemOfType2Id) references dbo.ItemOfType2 (ItemOfType2Id)
);    
*/