Below is a stored proc to count the number of widgets made in a month. If no widgets are made, no records will exists.

The execution plan shows an INNER JOIN of the M and A tables, where in the statement I am doing a LEFT OUTER JOIN.

I would like to count the number of widgets made in a time frame, join that to a month table (Jan-Dec), and have the results shown in an SSRS report. The join is because I cannot COUNT data that isn't there. Currently I am getting:

MonthName   Widget Count
February    2
March       3
April       4
May         6
June        5
July        4 
August      6
September   2
October     4
November    1
December    2

I would like to have the months where no widgets were made included in the list.

Here is the code:

DECLARE @OName varchar(50)

SET @OName = 'John'
SET @Start_Date = '01/01/2012'
SET @End_Date   = '12/31/2012'

SELECT   M.[MonthName]
    ,COUNT(A.[Widget_ID]) AS 'Widget Count'
FROM [Connector].dbo.[Months] AS M 
        LEFT OUTER JOIN  [SERVER].[DATABASE].[dbo].[Widget] AS A
        ON MONTH(A.[Widget_Date]) = M.[MonthID]  
WHERE     (A.[Operator_Name] LIKE '%'+ @OName +'%')
          AND A.[PlantID] = '00000001'
          AND (
               (A.Widget_Date >= @Start_Date AND @End_Date IS NULL)
            OR (A.Widget_Datet <= @End_Date AND @Start_Date IS NULL)
            OR (A.Widget_Date >= @Start_Date AND A.Widget_Date <= @End_Date)
            OR (@Start_Date IS NULL AND @End_Date IS NULL)

GROUP BY  M.[MonthName], M.MonthID 

Below is the execution plan for this query.Execution Plan

::UPDATE1:: I just inserted the names of the months into this table. I cannot change the remote table.

CREATE TABLE [dbo].[Months](
    [MonthID] [smallint] IDENTITY(1,1) NOT NULL,
    [MonthName] [varchar](25) NOT NULL,
INSERT INTO [dbo].[Months]
           (<MonthName, varchar(25),>)

The "widget" table has over 250 fields, that will take me much too long to anonymize.

  • Do you have some sample DDL (CREATE TABLE and INSERT INTO commands) for these two tables to test with? Jun 3, 2013 at 14:58
  • Ah sorry, missed the MONTH() function in the JOIN (which probably contributes to a high cost of the remote query, BTW). Jun 3, 2013 at 15:55
  • 1
    How do you control / validate that start date / end date are within the same year and/or are less than 13 months apart? When start date is May 2012 and end date is null, do you expect 8 rows of output, or more? Jun 3, 2013 at 16:19
  • The start and end date are arbitrary. The report can be run from the beginning of time to a date, or to a date til the end of time. If they run it for 9 years, I will expect 9 years of results to show. There are business rules that the user is following while running this report Jun 3, 2013 at 17:16

1 Answer 1


Your code says you are doing a LEFT OUTER JOIN, but are you really? Your very first WHERE clause (as well as each that follow) filters rows for inclusion from the outer table:

WHERE     (A.[Operator_Name] LIKE '%'+ @OName +'%')

This turns your LEFT OUTER JOIN into an INNER JOIN, whether you meant it or not. Perhaps you meant to move those filters to the ON clause.

Of course I would probably remove the non-sargeable MONTH() function from the JOIN, since this is going to force a complete scan on the remote table, and write it this way instead - also eliminating the join to your local months table:

  @OName      VARCHAR(50) = 'John', 
  @Start_Date DATE = NULL,--'20120101', -- use safe date formats 
  @End_Date   DATE = '20121231'; -- use semi-colons

-- deal with NULLs here so the query is simpler:

SELECT @Start_Date = COALESCE(@Start_Date, '20010101'),
       @End_Date   = COALESCE(@End_Date, CURRENT_TIMESTAMP);

;WITH n(n) AS 
  -- get the # of months you need instead of relying on your Connector table:
  SELECT TOP (DATEDIFF(MONTH, @Start_Date, @End_Date)+1) Number
    FROM master..spt_values
    WHERE [type] = N'P' AND Number >= 0
    ORDER BY Number
), m(m) AS
  -- convert those to months:
  FROM n
  MonthName = DATENAME(MONTH, m.m),
  [Widget Count] = COUNT(w.Widget_ID)
LEFT OUTER JOIN [Server].[Database].dbo.Widget AS w -- meaningful alias
  w.Widget_Date >= m.m
  AND w.Widget_Date < DATEADD(MONTH, 1, m.m) -- open-ended date range query
  AND w.Operator_Name LIKE '%' + @OName + '%'

Just ignore the m column in the output (you can't order by it without including it in the output, and it doesn't make sense to order by the name).

Another suggestion: don't use 'single quotes' as alias delimiters. Forms of this are deprecated and it also makes column alias look like string literals. Use [square brackets] instead.

  • Thank you! That is what I was looking for! I had thought something was going on, just blamed my lack of knowledge. Your answer to my question is correct, now, can you help me achieve the desired output? Jun 3, 2013 at 15:15
  • Ha good eyes! +1 Jun 3, 2013 at 15:15
  • 2
    @RateControl Encapsulate your current filters on A in parenthesis and add an OR A.JoinKey IS NULL
    – JNK
    Jun 3, 2013 at 15:16
  • Ha! Perfect. I haven't run into this before, and didn't know how to deal with it. Thanks so much for your help! Jun 3, 2013 at 15:20
  • You just blew my mind with your current edit. Thank so much. Jun 3, 2013 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.