adding a column to a table with different parameters <p>I am trying to enter 2 tables into one output my problem is that the parameter is pulling from 2 different columns. I need to be able to show what was completed and the work ordered for a months period. I do not get any errors as the script is written but after 3 hours I stopped it from running.</p> <p>If I change the join on the <code>@Ordered</code> to <code>iAuditID</code> from <code>EffectiveDate</code> it takes a few seconds to run, but it does not pull the correct information then. I only need the count of ordered audits to be correct and in the <code>@data</code> table and correct. Work may have been ordered a few months before it was completed, therefore I need to be able see how much work is coming in each month and what is being completed.</p> <p>I am using SQL 2008.</p> <pre><code>DECLARE @StartDate datetime, @EndDate datetime, @iClientID int, @iServiceLevelID int SET @StartDate = '1-1-13' SET @EndDate = '12-30-13' SET DATEFIRST 7 DECLARE @TB1 TABLE (EffectiveDate datetime, iAuditID int, iClientID int, iServiceLevelID int) INSERT INTO @TB1 SELECT dateadd(month, datediff(month, 0, a.dtClosedDate),0) AS EffectiveDate, a.iAuditID, a.iClientID, a.iServiceLevelID FROM vwtblAudit a WHERE (a.dtClosedDate &gt;= @StartDate) AND (a.dtClosedDate &lt;= @EndDate) AND (a.iClientID = @iClientID OR @iClientID is null) AND (a.iServiceLevelID = @iServiceLevelID or @iServiceLevelID is null) GROUP BY dateadd(month, datediff(month, 0, a.dtClosedDate),0), a.iClientID, a.iAuditID, a.iServiceLevelID DECLARE @Ordered TABLE (EffectiveDate datetime, iAuditID int, iClientID int, iServiceLevelID int, dtOpenDate int) INSERT INTO @Ordered SELECT dateadd(month, datediff(month, 0, a.dtOpenDate),0) AS EffectiveDate, a.iAuditID, a.iClientID, a.iServiceLevelID, Count(a.dtOpenDate) as Ordered FROM tblAudit a WHERE (a.dtOpenDate &gt;= @StartDate) AND (a.dtOpenDate &lt;= @EndDate) AND (a.iClientID = @iClientID OR @iClientID is null) AND (a.iServiceLevelID = @iServiceLevelID or @iServiceLevelID is null) GROUP BY dateadd(month, datediff(month, 0, a.dtOpenDate),0), a.iClientID, a.iServiceLevelID, a.iAuditID order by iClientID, EffectiveDate DECLARE @DATA table(iclientID int, sClientCode varchar(8), sClientName varchar(50), iServiceLevelID int, sServiceLevelName varchar(50), EffectiveDate datetime, iAuditCount int, Completed int) SELECT tblClient.iclientID, tblClient.sClientCode, tblClient.sClientName, tblServiceLevel.iServiceLevelID, TB1.EffectiveDate, COUNT(*) AS iAuditCount, COUNT(Ordered.dtOpenDate) as Ordered FROM @TB1 TB1 INNER JOIN tblClient ON TB1.iClientID = tblClient.iClientID INNER JOIN tblServiceLevel ON TB1.iServiceLevelID = tblServiceLevel.iServiceLevelID join @Ordered Ordered on TB1.EffectiveDate=Ordered.EffectiveDate GROUP BY tblClient.iClientID, TB1.iServiceLevelID, tblclient.sClientCode, tblClient.sClientName, tblServiceLevel.iServiceLevelID, tblServiceLevel.sServiceLevelName, TB1.EffectiveDate return SET DATEFIRST 7 </code></pre> <p>Every a from the <code>@data</code> worked like it should, but I was ask to add the work coming in, so I added <code>@ordered</code> table to bring in the correct information. The data is still correct except for the ordered column. See sample below:</p> <pre><code>clientID ClientCode ClientName iServiceLevelID EffectiveDate iAuditCount Ordered 0001 1001 Sample 1 Jan 2014 104 19(this number should be 134) </code></pre>