Yet another LEFT OUTER JOIN fail in SQL Server 2012 Express

Question

I'm wondering if someone might be able to troubleshoot my query. I have a simple table that has project savings per month. There are always 12 consecutive months worth or savings, but the first month can vary (e.g.: start from January for 12 months, start from March for 12 months, etc).

I need a report that gets me all savings (by month) for a given year. This means that for some project savings, if the start month is not January, then some of that project savings will fall in a different report year.

So I need a query that will return all months for the current report year, and have zero haves for where a project doesn't have saving values for that month.

I have some projects starting in July, and I'm only getting back those 6 months with their value. That is, the left join back to the date WITH is not outer joining properly. Can someone tell me where I'm going wrong please?

See code below:

DECLARE @MonthEndSnapshot SMALLDATETIME;
SELECT @MonthEndSnapshot =  getdate()

DECLARE @StartDate SMALLDATETIME, @EndDate SMALLDATETIME;
SELECT @StartDate = FORMAT(@MonthEndSnapshot, 'yyyy') + '0101', @EndDate = FORMAT(@MonthEndSnapshot, 'yyyy') + '1231';

;WITH d(d) AS 
(
    SELECT 
        DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0))
    FROM 
        (SELECT TOP 
             (DATEDIFF(MONTH, @StartDate, @EndDate) + 1) 
             n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
         FROM 
             sys.all_objects 
         ORDER BY [object_id]) AS n
)
select  
    left(datename(month, d.d), 3) as xAxisValueMon,
    datepart(mm, d.d) as xAxisValue,
    a.ProjectId as ProjectId, 
    ISNULL(SUM(a.Saving), 0) as yAxisValue
from 
    d 
LEFT OUTER JOIN 
    (SELECT 
         mes.ProjectId, mes.Saving, mes.SavingMonth
     FROM 
         dbo.sf_SnapshotMonthEndSaving() mes) AS a ON d.d = a.SavingMonth
group by 
    a.ProjectId,  datename(month, d.d), datepart(mm, d.d)
order by   
    a.ProjectId, datepart(mm, d.d)  

The WITH d(d) part works, and returns 12 month dates (1st month from Jan to Dec).

I also tried the following structure as the query:

;WITH d(d) AS 
(
  SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0))
  FROM ( SELECT TOP (DATEDIFF(MONTH, @StartDate, @EndDate) + 1) 
    n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
    FROM sys.all_objects ORDER BY [object_id] ) AS n
)
select  left(datename(month, d.d), 3) as xAxisValueMon,
    datepart(mm, d.d) as xAxisValue,
    mes.ProjectId as ProjectId, 
    ISNULL(SUM(mes.Saving), 0) as yAxisValue
from d LEFT OUTER JOIN 
    dbo.sf_SnapshotMonthEndSaving() mes 
    ON d.d = mes.SavingMonth
group by mes.ProjectId,  datename(month, d.d), datepart(mm, d.d)
order by   mes.ProjectId, datepart(mm, d.d) 

But same results. The MonthEndSaving table is as follows:

CREATE TABLE [dbo].[MonthEndSaving]
(
    [MonthEndSavingId] [int] IDENTITY(1,1) NOT NULL,
    [MonthEndSnapshot] [datetime] NOT NULL,   
    [ProjectId] [int] NOT NULL,
    [SavingMonth] [datetime] NOT NULL,
    [Saving] [money] NOT NULL,
    [DateCreated] [datetime] NOT NULL,

    PRIMARY KEY CLUSTERED (MonthEndSavingId)
)
GO

ALTER TABLE MonthEndSaving
    ADD CONSTRAINT [ProjectMonthEndSaving] 
    FOREIGN KEY (ProjectId) REFERENCES [dbo].[Project](ProjectId) 
GO

Answer 1

This code should do what you need:

DECLARE @MonthEndSnapshot SMALLDATETIME;
SELECT @MonthEndSnapshot =  getdate()



DECLARE @StartDate SMALLDATETIME, @EndDate SMALLDATETIME;
SELECT @StartDate = FORMAT(@MonthEndSnapshot, 'yyyy') + '0101', @EndDate = FORMAT(@MonthEndSnapshot, 'yyyy') + '1231';

;WITH d(d) AS 
(
  SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0))
  FROM ( SELECT TOP (DATEDIFF(MONTH, @StartDate, @EndDate) + 1) 
    n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
    FROM sys.all_objects ORDER BY [object_id] ) AS n
)
select  left(datename(month, d.d), 3) as xAxisValueMon,
    datepart(mm, d.d) as xAxisValue,
    prj.ProjectId as ProjectId, 
    ISNULL(SUM(a.Saving), 0) as yAxisValue
from d 
CROSS JOIN 
    (
        SELECT DISTINCT mes.ProjectId
        FROM dbo.sf_SnapshotMonthEndSaving() mes
    ) as prj
LEFT OUTER JOIN 
    (
        SELECT mes.ProjectId, mes.Saving, mes.SavingMonth
        FROM dbo.sf_SnapshotMonthEndSaving() mes 
    ) as a
    ON d.d = a.SavingMonth
    AND prj.ProjectID = a.ProjectID
group by prj.ProjectId,  datename(month, d.d), datepart(mm, d.d)
order by prj.ProjectId,  datepart(mm, d.d)   

Answer 2

Dang, Laughing Vergil seems to be a faster typist =) Anyway, the idea is pretty much the same. Your 'error' was that you join each month to ALL the projects in dbo.sf_SnapshotMonthEndSaving(). If one fits, it gets returned for that one only, if two fit, it will show those two etc... but it will NOT repeat for EVERY project. This should.

DECLARE @StartDate datetime = '1 jan 2016',
        @EndDate datetime = '1 dec 2016'

;WITH d(FirstDayOfMonth) AS 
(
  SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0))
  FROM ( SELECT TOP (DATEDIFF(MONTH, @StartDate, @EndDate) + 1) 
    n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
    FROM sys.all_objects ORDER BY [object_id] ) AS n
),
RelevantProjects AS
(
   SELECT DISTINCT ProjectId
     FROM dbo.sf_SnapshotMonthEndSaving() mes
    WHERE mes.SavingMonth BETWEEN @StartDate AND @EndDate -- you could also join to d but I think this is faster
),
ProjectsAndDates AS
(
    SELECT ProjectID,
           FirstDayOfMonth
      FROM d
     CROSS JOIN RelevantProjects
)
select  left(datename(month, d.FirstDayOfMonth), 3) as xAxisValueMon,
        datepart(mm, d.FirstDayOfMonth) as xAxisValue,
        d.ProjectId as ProjectId, 
        ISNULL(SUM(mes.Saving), 0) as yAxisValue
   from ProjectsAndDates d 
    LEFT OUTER JOIN [MonthEndSaving] mes  -- dbo.sf_SnapshotMonthEndSaving() mes 
                 ON mes.SavingMonth = d.FirstDayOfMonth
                AND mes.Project_id  = d.ProjectID
   group by d.ProjectId, datename(month, d.FirstDayOfMonth), datepart(mm, d.FirstDayOfMonth)
   order by d.ProjectId, datepart(mm, d.FirstDayOfMonth)