Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
DECLARE @Start_Date DATE 
DECLARE @End_Date DATE

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 
ORDER BY  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,
 CONSTRAINT [PK_Months] PRIMARY KEY CLUSTERED ([MonthID] ASC)
)
GO
INSERT INTO [dbo].[Months]
           ([MonthName])
     VALUES
           (<MonthName, varchar(25),>)
GO

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

share|improve this question
    
Do you have some sample DDL (CREATE TABLE and INSERT INTO commands) for these two tables to test with? –  Thomas Stringer Jun 3 '13 at 14:58
    
Ah sorry, missed the MONTH() function in the JOIN (which probably contributes to a high cost of the remote query, BTW). –  Aaron Bertrand Jun 3 '13 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? –  Aaron Bertrand Jun 3 '13 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 –  RateControl Jun 3 '13 at 17:16

1 Answer 1

up vote 7 down vote accepted

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:

DECLARE 
  @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:
  SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Start_Date), 0))
  FROM n
)
SELECT 
  m.m, 
  MonthName = DATENAME(MONTH, m.m),
  [Widget Count] = COUNT(w.Widget_ID)
FROM m
LEFT OUTER JOIN [Server].[Database].dbo.Widget AS w -- meaningful alias
ON 
  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 + '%'
GROUP BY m.m
ORDER BY m.m;

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.

share|improve this answer
    
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? –  RateControl Jun 3 '13 at 15:15
    
Ha good eyes! +1 –  Thomas Stringer Jun 3 '13 at 15:15
2  
@RateControl Encapsulate your current filters on A in parenthesis and add an OR A.JoinKey IS NULL –  JNK Jun 3 '13 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! –  RateControl Jun 3 '13 at 15:20
    
You just blew my mind with your current edit. Thank so much. –  RateControl Jun 3 '13 at 17:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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