SQL Server query scanning more partitions than expected
Reasoning

When comparing the column of the non partitioned table against the partitioned table, sql server will not be able to know what DayOfMonth will hold, even if all of them are 15. 

As a result it would not know which partitions to return to satisfy this filtering when joining the two tables. 

A different example to give some more insight can be found here.



Testing

I was able to recreate your issue, for further questions please add as much information as possible. 
This could be Table definition, indexes, partition scheme, ...

The DDL &amp; DML is at the bottom.

When running the query we can get the same result:



62 partitions returned.

On the seek predicates it tries to filter what it can, this is the two filters on CalendarId that do not have the  + DayOfMonth-1.



This translates to

WHERE ...
    AND (( dc.CalendarId &lt; 20190701)
    OR (dc.CalendarId &gt;= 20270601))
...


When running the query with these filters you will see the same amount of rows returned on accessing the table in the query plan.

Only after getting this data, it can and will be joined to the AccountInfo table on the ID's and the DayOfMonth - 1





To confirm your statement that when we remove the column that only two partitions are scanned:

SELECT
    ai.Name
    ,SUM(dc.Component )
    ,SUM(dc.Value) Value
FROM 
    Warm.DailyCosts dc
    JOIN Warm.AccountInfo ai
        ON dc.AccountInfoId = ai.Id
WHERE
    ai.CorrelationId = '00000000-0000-0000-0000-000000000000'
    AND ((dc.CalendarId &gt;= (20190601  - 1)
        AND dc.CalendarId &lt; 20190701)
    OR (dc.CalendarId &gt;= 20270601
        AND dc.CalendarId &lt; (20270601  - 1)))
GROUP BY            Name


Only two are accessed and used:





Solution

I am going to assume that DayOfMonth only goes up to 31 days. 

If you know these boundaries, you can 'hard code' them so that sql server knows what partitions to look for. After this you can add the extra filtering.

E.G.

WITH CTE
AS
(
SELECT
    ai.Name
    ,dc.Component 
    ,dc.Value 
    ,dc.CalendarId
    ,ai.DayOfMonth
FROM 
    Warm.DailyCosts dc
    JOIN Warm.AccountInfo ai
        ON dc.AccountInfoId = ai.Id
WHERE
    ai.CorrelationId = '00000000-0000-0000-0000-000000000000'
    AND ((dc.CalendarId &gt;= (20190601 )
        AND dc.CalendarId &lt; 20190701)
    OR (dc.CalendarId &gt;= 20270601
        AND dc.CalendarId &lt; (20270701)))

)

SELECT Name,Component,SUM(Value)
FROM CTE 
WHERE ((CalendarId &gt;= (20190601 + DayOfMonth - 1)
        AND CalendarId &lt; 20190701)
    OR (CalendarId &gt;= 20270601
        AND CalendarId &lt; (20270601 + DayOfMonth - 1)))

GROUP BY        
    Name
    ,Component


The sole purpose of the  cte is to let sql server know that it can also filter on dc.CalendarId &gt;= dc.CalendarId &gt;= (20190601 ) OR dc.CalendarId &lt; (20270701)).

Sidenote: Adding a constraint does not work to enforce this.

This query gives us the result we want, with the correct  partition elemination:





Only accessing partitions 19 &amp; 21.

You can probably also use OR / AND logic to get the same result if you prefer. The important part is getting the boundaries known.



Test data

CREATE SCHEMA WARM
GO

CREATE TABLE Warm.DailyCosts(ID INT IDENTITY(1,1) NOT NULL,
                            Component int, 
                             Value int, 
                             CalendarId INT, 
                             AccountInfoId int
                             )


CREATE TABLE Warm.AccountInfo(Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                              Name VARCHAR(25),
                              CorrelationId uniqueidentifier,
                              DayOfMonth int
                              );
USE [master]
GO
ALTER DATABASE [test] ADD FILEGROUP [Partitionfg]
GO
USE [test]
GO
CREATE PARTITION FUNCTION [PF_CostDateByYearMonth](int) AS RANGE RIGHT FOR VALUES (
    20180101,
    20180201,
    20180301,
    20180401,
    20180501,
    20180601,
    20180701,
    20180801,
    20180901,
    20181001,
    20181101,
    20181201,
    20190101,
    20190201,
    20190301,
    20190401,
    20190501,
    20190601,
    20190701,
    20270601,
    20270701,
    20270801,
    20270901,
    20271001,
    20271101,
    20271201,
    20280101,
    20280201,
    20280301,
    20280401,
    20280501,
    20280601,
    20280701,
    20280801,
    20280901,
    20281001,
    20281101,
    20281201,
    20290101,
    20290201,
    20290301,
    20290401,
    20290501,
    20290601,
    20290701,
    20290801,
    20290901,
    20291001,
    20291101,
    20291201,
    20300101,
    20300201,
    20300301,
    20300401,
    20300501,
    20300601,
    20300701,
    20300801,
    20300901,
    20301001,
    20301101,
    20301201)


CREATE PARTITION SCHEME [PS_CostDateByYearMonth]  
AS PARTITION [PF_CostDateByYearMonth]  
ALL TO ( [Partitionfg] );  

CREATE UNIQUE CLUSTERED INDEX ptci ON  Warm.DailyCosts(CalendarId,Id) ON [PS_CostDateByYearMonth](CalendarId) ;
USE [master]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N'TestPartition', FILENAME = N'D:\DATA\TestPartition.ndf' , SIZE = 3072KB , FILEGROWTH = 65536KB ) TO FILEGROUP [Partitionfg]
GO
USE [test]
GO

INSERT INTO  Warm.DailyCosts(Component, 
                             Value, 
                             CalendarId, 
                             AccountInfoId 
                             )
SELECT TOP(1000000) --1M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        20180101 +  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000 % 1000 ,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2 
-- 2018
INSERT INTO  Warm.DailyCosts(Component, 
                             Value, 
                             CalendarId, 
                             AccountInfoId 
                             )
SELECT TOP(1000000) --1M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        20190101 +  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000 % 1000 ,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2 
-- 2019
INSERT INTO  Warm.DailyCosts(Component, 
                             Value, 
                             CalendarId, 
                             AccountInfoId 
                             )

SELECT TOP(1000000) --1M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        20280101 +  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000 % 1000 ,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2 
-- 2028

INSERT INTO Warm.AccountInfo(
                              Name ,
                              CorrelationId,
                              DayOfMonth 
                              )

SELECT TOP(3000000) --3M
        CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS varchar(10)) + 'a',
       '00000000-0000-0000-0000-000000000000',
       15
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2 
CREATE NONCLUSTERED INDEX [IX_Component_Value_DailyCosts] ON Warm.DailyCosts
 (Component,Value) 
ON [PS_CostDateByYearMonth](CalendarId);
GO