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.
::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.