SQL Server Performance

Need help with my stored proc.

Discussion in 'SQL Server 2008 General Developer Questions' started by kss113, Apr 6, 2011.

  1. kss113 New Member

    Hello All,
    I am getting a: Null Value is eliminaed by aggregate or other set operation message.
    Basically it's a select into a temp table that I select into another temp table.
    I've been looking at this for 2 days now, so thanks for any help
    KS----------------------------------------------------------------------------------------- DECLARE
    @BusinessUnitID nvarchar(7) SET @BusinessUnitID = '2518' DECLARE
    @EmployeeID nvarchar(20) SET @EmployeeID = '30060'--'30060'--'13666' DECLARE
    @CustomerID nvarchar(20) SET @CustomerID = '' DECLARE
    @Party nvarchar(35) SET @Party = '' DECLARE
    @PartyAddress nvarchar(130) SET @PartyAddress = '' DECLARE
    @StartDate nvarchar(10) SET @StartDate ='1/1/2011' --'1/1/2011' DECLARE
    @EndDate nvarchar(10) SET @EndDate ='4/06/2011' --'3/25/2011' DECLARE
    @DocumentStatusID nvarchar(10) SET @DocumentStatusID = '' --SELECT * from BusinessUnits WHERE Description LIKE '%carroll%'
    --2518, |2950, ~2943, 2730
    -----------------------------------------------------------------------------------------IF
    @BusinessUnitID = '' SET @BusinessUnitID = NULL IF
    (@BusinessUnitID IS NULL) BEGIN
    SET @BusinessUnitID = (SELECT TOP 1 BusinessUnitID FROM Employees WHERE EmployeeID = @EmployeeID) ENDIF
    @EmployeeID = '' SET @EmployeeID = NULL IF
    @CustomerID = '' SET @CustomerID = NULL IF
    @Party = '' SET @Party = NULL IF
    @PartyAddress = '' SET @PartyAddress = NULL IF
    @StartDate = '' SET @StartDate = NULL IF
    @EndDate = '' SET @EndDate = NULL IF
    @DocumentStatusID = '' SET @DocumentStatusID = NULL IF
    (@StartDate IS NOT NULL) BEGIN
    DECLARE @StartDT datetime
    SET @StartDT = @StartDate
    DECLARE @EndDT datetime
    SET @EndDT = DATEADD(day, 1, @EndDate) ENDSET
    NOCOUNT ON DECLARE
    @TempTable TABLE(TaskAssignedEmployeeID nvarchar(20)) IF
    (@EmployeeID IS NOT NULL) BEGIN
    INSERT INTO @TempTable
    SELECT @EmployeeID ENDELSEBEGIN
    IF (CHARINDEX('~', @BusinessUnitID) = 1)
    BEGIN
    INSERT INTO @TempTable
    SELECT DISTINCT EmployeeID
    FROM Employees
    WHERE BusinessUnitID IN (SELECT BusinessUnitID FROM BusinessUnits WHERE BusinessUnitID3 = REPLACE(@BusinessUnitID, '~', ''))
    AND Active = 'Y'
    END
    ELSE IF (CHARINDEX('|', @BusinessUnitID) = 1)
    BEGIN
    INSERT INTO @TempTable
    SELECT DISTINCT EmployeeID
    FROM Employees
    WHERE BusinessUnitID IN (SELECT BusinessUnitID FROM BusinessUnits WHERE BusinessUnitID4 = REPLACE(@BusinessUnitID, '|', ''))
    AND Active = 'Y'
    END
    ELSE
    BEGIN
    INSERT INTO @TempTable
    SELECT DISTINCT EmployeeID
    FROM Employees
    WHERE BusinessUnitID = @BusinessUnitID
    AND Active = 'Y'
    END
    DECLARE @TempTable2 TABLE(TaskBusinessUnitID nvarchar(7))
    IF (CHARINDEX('~', @BusinessUnitID) = 1)
    BEGIN
    INSERT INTO @TempTable2
    SELECT DISTINCT BusinessUnitID FROM BusinessUnits WHERE BusinessUnitID3 = REPLACE(@BusinessUnitID, '~', '') AND Active = 'Y'
    END
    ELSE IF (CHARINDEX('|', @BusinessUnitID) = 1)
    BEGIN
    INSERT INTO @TempTable2
    SELECT DISTINCT BusinessUnitID FROM BusinessUnits WHERE BusinessUnitID4 = REPLACE(@BusinessUnitID, '|', '') AND Active = 'Y'
    END
    ELSE
    BEGIN
    INSERT INTO @TempTable2
    SELECT @BusinessUnitID
    END ENDDECLARE
    @ParentActivities TABLE (ActivityID nvarchar(20)) IF
    (@EmployeeID IS NOT NULL) BEGIN
    --Work Orders, Sales Cales, Call Back Requests
    INSERT INTO @ParentActivities
    SELECT A.ActivityID
    FROM Activities A
    INNER JOIN @TempTable TT ON TT.TaskAssignedEmployeeID = A.TaskAssignedEmployeeID
    WHERE A.ActivityTypeID IN ('WOI', 'SLC', 'CBR') AND ((A.TaskScheduledToDT >= @StartDT AND A.TaskScheduledToDT < @EndDT) OR (@StartDT IS NULL)) ENDELSEBEGIN
    --Work Orders, Sales Cales, Call Back Requests, Non-Assigned Work Orders (NULL TaskAssignedEmployeeID)
    INSERT INTO @ParentActivities
    SELECT A.ActivityID
    FROM Activities A
    INNER JOIN @TempTable TT ON TT.TaskAssignedEmployeeID = A.TaskAssignedEmployeeID
    WHERE A.ActivityTypeID IN ('WOI', 'SLC', 'CBR') AND ((A.TaskScheduledToDT >= @StartDT AND A.TaskScheduledToDT < @EndDT) OR (@StartDT IS NULL))
    UNION
    SELECT A.ActivityID
    FROM Activities A
    INNER JOIN @TempTable2 TT ON TT.TaskBusinessUnitID = A.TaskBusinessUnitID
    WHERE A.ActivityTypeID IN ('WOI', 'SLC', 'CBR') AND ((A.TaskScheduledToDT >= @StartDT AND A.TaskScheduledToDT < @EndDT) OR (@StartDT IS NULL))
    AND A.TaskAssignedEmployeeID IS NULL END--Selecting into a temp table #MyTempTableSELECT
    DISTINCT BU
    .BusinessUnitID3, BU
    .BusinessUnitID4, BU
    .BusinessUnitID5, A
    .ActivityID, A
    .ServiceTypeID AS ActivityServiceType, A
    .ParentActivityID, ACS
    .[Description] AS ActivityStatusID,
    ISNULL(A.TaskAssignedEmployeeID, '') AS EmployeeID,
    ISNULL(E.FullName, '') AS Employee, P
    .PartyID, P
    .FullName AS Party,
    (ISNULL(ADDR.StreetAddress, '') + ' ' + ISNULL(ADDR.City, '') + ' ' + ISNULL(ADDR.StateID, '') + ' ' + ISNULL(ADDR.ZipCode, '')) AS PartyAddress,
    CASE ISNULL(A.ParentActivityID, '')
    WHEN '' THEN ISNULL(AT.[Description], '')
    ELSE ''
    END AS ActivityDocumentType, A
    .TaskScheduledFromDT AS StartDT, A
    .TaskScheduledToDT AS EndDT, dbo
    .CalcTimeFormat(CONVERT(DECIMAL(10, 2), ISNULL(DATEDIFF(MINUTE, A.MWStartDT, A.MWEndDT), 0))) + ' Hrs.' AS Duration,
    CASE ISNULL(A.ParentActivityID, '')
    WHEN '' THEN (SELECT CONVERT(NVARCHAR(10), COUNT(MA2.MasterAgreementID))
    FROM MasterAgreements MA2
    WHERE MA2.SourceActivityID = A.ActivityID
    AND MA2.EmployeeID = A.TaskAssignedEmployeeID)
    ELSE ''
    END AS DocumentResult,
    CASE ISNULL(LT.[Description], '')
    WHEN '' THEN (SELECT ISNULL(LT.[Description], '')
    FROM LeadTypes LT
    LEFT OUTER JOIN Activities A1 ON A1.SLCLeadTypeID = LT.LeadTypeID
    WHERE A1.ActivityID = A.ParentActivityID)
    ELSE ISNULL(LT.[Description], '')
    END AS LeadType,
    CASE WHEN A.ParentActivityID IS NULL THEN A.ActivityID ELSE ParentActivityID END AS P2,
    (SELECT COUNT(ARH.ActivityID) FROM ActivityReschedulesHistory ARH WHERE ARH.ActivityID = A.ActivityID) AS ReschedulesHistory,
    ISNULL(C.CustomerID, '') AS CustomerID, A
    .ServiceLocationPartyID,
    (
    SELECT COUNT(ActivityID)
    FROM Activities A2
    WHERE A2.ActivityID = A.ActivityID AND A2.ActivityStatusID != 'CPL' --Open
    ) AS OpenActivities,
    (
    SELECT COUNT(ActivityID)
    FROM Activities A2
    WHERE A2.ActivityID = A.ActivityID
    AND A.ActivityStatusID <> 'CPL'
    AND A.parentactivityID is null
    ) AS PendingActivities,
    (
    SELECT COUNT(distinct(A2.ParentActivityID))
    FROM Activities A2
    INNER JOIN FormInstances FI2 ON FI2.ActivityID = A2.ActivityID
    INNER JOIN FormInstanceProposals FIP2 ON FIP2.FormInstanceID = FI2.FormInstanceID
    WHERE A2.ParentActivityID = A.ActivityID
    AND A.ActivityStatusID = 'CPL'
    AND A.parentactivityID is null
    AND FIP2.Sold = 'N'
    ) AS ProposedActivities,
    (
    SELECT SUM(FIP2.InitialAmount)
    FROM Activities A2
    INNER JOIN FormInstances FI2 ON FI2.ActivityID = A2.ActivityID
    INNER JOIN FormInstanceProposals FIP2 ON FIP2.FormInstanceID = FI2.FormInstanceID
    WHERE A2.ParentActivityID = A.ActivityID
    AND A.ActivityStatusID = 'CPL'
    AND A.parentactivityID is null
    AND FIP2.Sold = 'N'
    )As ProposedAmount,
    (
    SELECT COUNT(Distinct(A2.ParentActivityID))
    FROM Activities A2
    INNER JOIN FormInstances FI2 ON FI2.ActivityID = A2.ActivityID
    INNER JOIN FormInstanceProposals FIP2 ON FIP2.FormInstanceID = FI2.FormInstanceID
    WHERE A2.ParentActivityID = A.ActivityID
    AND A.ActivityStatusID = 'CPL'
    and FIP2.Sold='Y'
    ) AS SoldActivities,
    (
    SELECT SUM(FIP2.InitialAmount)
    FROM Activities A2
    INNER JOIN FormInstances FI2 ON FI2.ActivityID = A2.ActivityID
    INNER JOIN FormInstanceProposals FIP2 ON FIP2.FormInstanceID = FI2.FormInstanceID
    WHERE A2.ParentActivityID = A.ActivityID
    AND A.ActivityStatusID = 'CPL'
    AND A.parentactivityID is null
    and FIP2.Sold = 'Y'
    )AS SoldAmount,
    (
    select COUNT(Distinct(A2.ParentActivityID))
    From Activities A2
    INNER JOIN FormInstances FI2 ON FI2.ActivityID = A2.ActivityID
    INNER JOIN FormInstanceProposals FIP2 ON FIP2.FormInstanceID = FI2.FormInstanceID
    where (A2.ParentActivityID = A.ActivityID)
    AND
    ( A
    .ActivityStatusID = 'CPL'
    AND A.parentactivityID is null
    )
    AND (FIP2.Sold = 'N' or FIP2.Sold='Y')
    ) AS PerformedActivities,
    (
    SELECT COUNT(ActivityID)
    FROM Activities A2
    WHERE A2.ActivityID = A.ActivityID AND A2.ActivityStatusID = 'CPL' --Completed
    ) AS CompletedActivities,
    (SELECT TOP 1 ISNULL(MA.MasterAgreementID, '') FROM MasterAgreements MA WHERE MA.SourceActivityID = A.ActivityID AND MA.Sold = 'Y')
    AS MasterAgreementSold, --Item 37448, 'Phase 2 Issues List' A
    .SLCPlanDT,A
    .ActivityTypeID,FIPSold
    = CASE FIP.Sold
    WHEN 'Y' THEN 'Sold'
    WHEN 'N' THEN 'Not Sold'
    ELSE 'Not Sold'
    END,A
    .ActivityStatusID as AActivityStatusIDInto
    #MyTempTable FROM
    Activities A INNER
    JOIN BusinessUnits BU ON BU.BusinessUnitID = A.TaskBusinessUnitID INNER
    JOIN ActivityTypes AT ON AT.ActivityTypeID = A.ActivityTypeID INNER
    JOIN ActivityStatuses ACS ON ACS.ActivityStatusID = A.ActivityStatusID INNER
    JOIN Parties P ON P.PartyID = A.ServiceLocationPartyID LEFT
    OUTER JOIN FormInstances FI on FI.ActivityID = A.ActivityIDLEFT
    OUTER JOIN FormInstanceProposals FIP on FI.FormInstanceID = FIP.FormInstanceIDLEFT
    OUTER JOIN LeadTypes LT ON LT.LeadTypeID = A.SLCLeadTypeID LEFT
    OUTER JOIN PartyAddresses PA ON PA.PartyID = P.PartyID AND PA.SequenceIdentifier = A.ServiceLocationSequenceIdentifier LEFT
    OUTER JOIN Addresses ADDR ON ADDR.AddressID = PA.AddressID LEFT
    OUTER JOIN Frequencies F ON F.FrequencyID = A.FrequencyID LEFT
    OUTER JOIN Employees E ON E.EmployeeID = A.TaskAssignedEmployeeID LEFT
    OUTER JOIN Customers C ON C.PartyID = P.PartyID WHERE
    (A.ActivityID IN (SELECT ActivityID FROM @ParentActivities)) -- AND (A.TaskAssignedEmployeeID = @EmployeeID OR @EmployeeID IS NULL)ORDER
    BY P2, A.ParentActivityID, A.ActivityID -- Selecting into another temp table #MyTempTable2SELECT
    IsNull(EmployeeID,'') As EmployeeID,
    ISNull(Employee,'')AS Employee,
    ISNUll(Sum(PendingActivities),0) As Pending,
    ISNULL(Sum(ProposedActivities),0) As Proposed,
    ISNULL(SUM(ProposedAmount),0) As ProposedAmount,
    ISNULL(Sum(SoldActivities),0) AS Sold,
    ISNULL(SUM(SoldAmount),0)As SoldAmount,
    ISNULL(Sum(PerformedActivities),0) AS Performed,
    --ISNULL(SUM(PendingActivities + ProposedActivities + SoldActivities + PerformedActivities),0) As TotalLeads,
    SUM(ISNULL(PendingActivities,0) + ISnull(ProposedActivities,0) + ISNULL(SoldActivities,0) + IsNULL(PerformedActivities,0)) As TotalLeads,
    --ISNULL(SUM((SoldActivities/NullIF(PendingActivities + ProposedActivities + SoldActivities + PerformedActivities,0))*100),0) AS Closing,
    (
    select Count(slcleadtypeid) from Activities A
    --Inner Join @TempTable tmp on tmp.TaskAssignedEmployeeID = A.EmployeeID
    Inner Join @ParentActivities PA on PA.ActivityID = A.ActivityID
    where
    -- ActivityTypeID IN ('WOI', 'SLC', 'CBR')ActivityStatusID
    = 'cpl'
    and ServiceLineID = 'TC'
    and SLCLeadTypeID = 'INB'
    )AS NumINBTC,
    (
    select Count(slcleadtypeid) from Activities A
    --Inner Join @TempTable tmp on tmp.TaskAssignedEmployeeID = A.EmployeeID
    Inner Join @ParentActivities PA on PA.ActivityID = A.ActivityID
    where
    -- ActivityTypeID IN ('WOI', 'SLC', 'CBR')ActivityStatusID
    = 'cpl'
    and ServiceLineID = 'TC'
    and SLCLeadTypeID = 'CTV'
    )AS NumCTVTC,
    (
    select Count(slcleadtypeid) from Activities A
    Inner Join @ParentActivities PA on PA.ActivityID = A.ActivityID
    where
    --ActivityTypeID IN ('WOI', 'SLC', 'CBR') andActivityStatusID
    = 'cpl'
    and ServiceLineID = 'PC'
    and SLCLeadTypeID = 'INB'
    )AS NumINBPC,
    (
    select Count(slcleadtypeid) from Activities A
    Inner Join @ParentActivities PA on PA.ActivityID = A.ActivityID
    where
    --ActivityTypeID IN ('WOI', 'SLC', 'CBR') andActivityStatusID
    = 'cpl'
    and ServiceLineID = 'PC'
    and SLCLeadTypeID = 'CTV'
    )AS NumCTVPC,
    (
    select Count(slcleadtypeid) from Activities A
    Inner Join @ParentActivities PA on PA.ActivityID = A.ActivityID
    where
    --ActivityTypeID IN ('WOI', 'SLC', 'CBR') andActivityStatusID
    = 'cpl'
    and SLCLeadTypeID = 'CTV'
    )AS TotalCTV,
    (
    select Count(slcleadtypeid) from Activities A
    Inner Join @ParentActivities PA on PA.ActivityID = A.ActivityID
    where
    --ActivityTypeID IN ('WOI', 'SLC', 'CBR') andActivityStatusID
    = 'cpl'
    and SLCLeadTypeID = 'INB'
    )AS TotalINB,
    (
    select Count(A.ActivityID)
    from Activities A
    inner join @ParentActivities PA on PA.ActivityID = A.ActivityID
    where MWEndDT
    >= TaskScheduledFromDT and MWEndDT <= TaskScheduledToDT
    )AS cntOnTime,
    (
    select Count(distinct DATEDIFF(d,0,E.occuranceDT)) As NumDaysWorked
    from EventEntries E
    inner join @TempTable tmp on tmp.TaskAssignedEmployeeID = EmployeeID
    inner join PDAUsers PU on PU.EmployeeID = tmp.TaskAssignedEmployeeID
    inner join Devices D on PU.PDAUserID= D.PDAUserID
    where EventTypeID=20 and E.DeviceID = D.DeviceID
    and OccuranceDT between @StartDate AND @EndDate
    )AS NumDaysWorkedInto
    #MyTempTable2from
    #MyTempTable Group
    ByEmployeeID
    , EmployeeORDER
    BY EmployeeSelectEmployeeID
    ,Employee
    ,Pending
    ,Proposed
    ,Sold
    ,Performed
    ,TotalLeads
    ,NumDaysWorked
    ,NumINBTC
    ,NumCTVTC
    ,NumINBPC
    ,NumCTVPC
    ,cntOnTime
    ,ProposedAmount
    ,SoldAmount
    ,TotalCTV
    ,TotalINB
    ,--ISNull(Convert(Decimal(10,2),MTT.Closing),0) AS Closing,
    Convert(Decimal(10,2),SUM(ISNULL(Sold,0)/(ISNULL(Pending,0)+ISNULL(Proposed,0)+ISNULL(Sold,0)+ISNULL(Performed,0)))*100) AS Closing,
    --ISNULL(SUM((Sold/NullIF(Pending + Proposed + Sold + Performed,0))*100),0) AS Closing,
    SUM((ISNULL(Proposed,0) + ISNULL(Sold,0) + ISNULL(Performed,0))/ISNULL(NumDaysWorked,0)) AS AvgCallsPerDay,
    Sum(ISNULL(Sold,0)/ISNULL(NumDaysWorked,0)) AS AvgNumofSalesPerDay,
    Sum((ISNULL(Proposed,0) + ISNULL(Sold,0))/ISNULL(NumDaysWorked,0)) AS AvgNumofProposalsPerDay,
    SUm((ISNULL(Proposed,0) + ISNULL(Sold,0) + ISNULL(Performed,0))/NullIF(Sold,0)) AS AvgNumCallsForSale,
    SuM((ISNULL(Proposed,0)+ISNULL(Sold,0))/NULLIF(sold,0))AS AvgNumProposalsForSale,
    Convert(Decimal(10,2),SUM(((ISNULL(NumINBTC,0) + ISNULL(NumCTVTC,0))/Nullif(NumCTVTC,0)))) AS TCLeadRates,
    Convert(Decimal(10,2),SUM(((ISNULL(NumINBPC,0) + ISNULL(NumCTVPC,0))/Nullif(NumCTVPC,0)))) As PCLeadsRate,
    Convert(Decimal(10,2),Sum((ISNULL(Pending,0) + ISNULL(Proposed,0) + ISNULL(Sold,0) + ISNULL(Performed,0))/(ISNULL(Pending,0) + ISNULL(Proposed,0) + ISNULL(Sold,0) + ISNULL(Performed,0))*100))AS [Schedule Rate],
    Convert(Decimal(10,2),Sum((ISNULL(Proposed,0) + ISNULL(Sold,0) + ISNULL(Performed,0))/(ISNULL(Pending,0) + ISNULL(Proposed,0) + ISNULL(Sold,0) + ISNULL(Performed,0))*100))AS [FullFillment Rate],
    Convert(Decimal(10,2),Sum((((ISNULL(Proposed,0) + ISNULL(Sold,0))/(ISNULL(Proposed,0) + ISNULL(Sold,0) + ISNULL(Performed,0)))*100))) AS [Proposal Rate],
    SUM((ISNULL(TotalINB,0) + ISNULL(TotalCTV,0))/ISNULL(TotalINB,0)) AS LeadConverstionRate,
    Convert(Decimal(10,2),Sum((ISNULL(cntOnTime,0)/ISNULL(TotalLeads,0))*100))AS [On Time],
    Convert(Money,Sum((ISNULL(ProposedAmount,0) + ISNULL(SoldAmount,0))/ISNULL(NumDaysWorked,0)),1)As AvgProposalsperDay,
    Convert(Money,Sum((ISNULL(SoldAmount,0))/ISNULL(NumDaysWorked,0)),1)As AvgSoldperDay,
    SUM((ISNULL(Proposed,0) + ISNULL(Sold,0))/ISNULL(Sold,0)) AS AvgofCallstoMakeSale,
    SUM((ISNULL(Proposed,0) + ISNULL(Sold,0) + ISNULL(Performed,0) )/ISNULL(Sold,0)) AS AvgofProposalstoMakeSale
    ----ISNull(Convert(Decimal(10,2),MTT.Closing),0) AS Closing,
    --ISNULL(Convert(Decimal(10,2),SUM(Sold/NullIF(Pending+Proposed+Sold+Performed,0))*100),0) AS Closing,
    ----ISNULL(SUM((Sold/NullIF(Pending + Proposed + Sold + Performed,0))*100),0) AS Closing,
    --ISNULL(SUM(Proposed + Sold + Performed)/NullIF(NumDaysWorked,0),0)AS AvgCallsPerDay,
    --ISNUll(Sum(Sold/NULLIF(NumDaysWorked,0)),0)AS AvgNumofSalesPerDay,
    --ISNULL(Sum(Proposed + Sold)/NullIF(NumDaysWorked,0),0) AS AvgNumofProposalsPerDay,
    --ISNUll(SUm(Proposed + Sold + Performed)/NullIF(Sold,0),0)AS AvgNumCallsForSale,
    --ISNULL(SuM(Proposed+Sold)/NULLIF(sold,0),0)AS AvgNumProposalsForSale,
    --ISNULL(SUM(Convert(Decimal(10,2),((NumINBTC + NumCTVTC)/Nullif(NumCTVTC,0)))),0) AS TCLeadRates,
    --ISNULL(SUM(Convert(Decimal(10,2),((NumINBPC + NumCTVPC)/Nullif(NumCTVPC,0)))),0) As PCLeadsRate,
    --ISNULL(Sum(Convert(Decimal(10,2),(Pending + Proposed + Sold + Performed)/NullIF((Pending + Proposed + Sold + Performed),0))*100),0)AS [Schedule Rate],
    --ISNULL(Sum(Convert(Decimal(10,2),(Proposed + Sold + Performed)/NULLIF((Pending + Proposed + Sold + Performed),0)*100)),0)AS [FullFillment Rate],
    --ISNULL(Sum(Convert(Decimal(10,2),(((Proposed + Sold)/Nullif((Proposed + Sold + Performed),0))*100))),0) AS [Proposal Rate],
    --ISNULL(SUM((TotalINB + TotalCTV)/NULLIF(TotalINB,0)),0) AS LeadConverstionRate,
    --ISNULL(Sum(Convert(Decimal(10,2),(cntOnTime/Nullif(TotalLeads,0))*100)),0)AS [On Time],
    --ISNULL(Convert(Money,Sum((ProposedAmount + SoldAmount)/Nullif(NumDaysWorked,0)),1),0)As AvgProposalsperDay,
    --ISNULL(Convert(Money,Sum((SoldAmount)/Nullif(NumDaysWorked,0)),1),0)As AvgSoldperDay,
    --ISNULL(SUM((Proposed + Sold)/NullIF(Sold,0)),0) AS AvgofCallstoMakeSale,
    --ISNULL(SUM((Proposed + Sold + Performed )/NullIF(Sold,0)),0) AS AvgofProposalstoMakeSaleFrom#MyTempTable2
    Group by EmployeeID, Employee, Pending
    ,Proposed, Sold, Proposed, Performed, TotalLeads, NumDaysWorked
    , NumINBTC, NumCTVTC, NumINBPC, NumCTVPC, cntOnTime
    ,ProposedAmount, SoldAmount, TotalCTV, TotalINB
    Order by Employee DESC
    select * from #MyTempTableselect
    * from #MyTempTable2drop
    table #MyTempTabledrop
    table #MyTempTable2
  2. kss113 New Member

    One more thing.
    I don't have any problems until I try to select from#MyTempTable
    into #MyTempTable2
    Thanks,
    KS
  3. kss113 New Member

    Here is another funny issue, I am using 2008 R2, and I get those squiggaly read lines under my temp tables and the items I am selecting from. Either invalid column or invalid object.
    But the query still runs fine sometimes show the null warning and sometimes a completed successfully. But always runs and calcuates the values.
    KS
  4. FrankKalis Moderator

    Welcome to the forum!
    This is no "real" error message, but rather an informational message. If you don't want it to appear, put a SET ANSI_WARNINGS OFF at the beginning of the batch and SET ANSI_WARNINGS ON at the end.
  5. jcelko New Member

    This warning message will not stop wxwcution. But there is more to than just this! Imagine a CURSOR with an aggregate that dropped a NULL in a grouoing. When is the warmnign sent?
    1) When the CURSOR is DECLARE-d
    2) When the CURSOR is OPEN-ed
    3) When the affeced grouped row is FETCH-ed
    The ANSI Standards say this is "implementation defined" and the "Big Three" do not do it the same way. There is a good ANSI X3H2 story about how this happened and I might tell it in a column in SQL Server column.

Share This Page