Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187

    Unanswered: Insert into Temp table

    Hi,
    I am trying to insert into temp table multiple times and then pull everything out. How would I do that? I get records back, but everything is 0. Why? Here is my stored procedure.

    CREATE PROCEDURE sp_SummaryReport
    (
    @startdate datetime,
    @enddate datetime
    )
    AS
    BEGIN
    SET NOCOUNT ON

    CREATE TABLE #CalcTemp (DataID bigint IDENTITY(1,1) NOT FOR REPLICATION, ReportType varchar(2), Volume int, NetEffect decimal(10,1), GrossEffect decimal(10,1), WeekEndDate datetime)


    DECLARE @OnTime decimal(10,1)
    DECLARE @UnControlled decimal(10,1)
    DECLARE @Volume int
    DECLARE @GrossEffect decimal(10,1)
    DECLARE @NetEffect decimal(10,1)
    DECLARE @WeekEndDate datetime
    --ARS AA
    SET @OnTime = (SELECT COUNT(DataID) FROM tblARSData WHERE (tblARSData.WeekEndDate BETWEEN @startdate AND @enddate) AND OnTimeFlag = 1 AND ARSScanType = 'D' AND ARSType='AA')
    SET @UnControlled = (SELECT COUNT(DataID) FROM tblARSData WHERE (tblARSData.WeekEndDate BETWEEN @startdate AND @enddate) AND ControlFlag = 'U' AND ARSScanType = 'D' AND ARSType='AA')
    SET @Volume = (SELECT COUNT(DataID) FROM tblARSData WHERE (tblARSData.WeekEndDate BETWEEN @startdate AND @enddate) AND ARSScanType = 'D' AND ARSType='AA')
    SET @GrossEffect = ((@OnTime/@Volume) * 100)
    SET @NetEffect = (((@OnTime + @UnControlled)/@Volume) * 100)
    SET @WeekEndDate = (SELECT DISTINCT WeekEndDate FROM tblARSData)

    INSERT INTO #CalcTemp(ReportType, Volume, NetEffect, GrossEffect, WeekEndDate)
    VALUES ('AA',
    @Volume,
    @NetEffect,
    @GrossEffect,
    @WeekEndDate)
    --ARS AN
    SET @OnTime = (SELECT COUNT(DataID) FROM tblARSData WHERE (tblARSData.WeekEndDate BETWEEN @startdate AND @enddate) AND OnTimeFlag = 1 AND ARSScanType = 'D' AND ARSType='AN')
    SET @UnControlled = (SELECT COUNT(DataID) FROM tblARSData WHERE (tblARSData.WeekEndDate BETWEEN @startdate AND @enddate) AND ControlFlag = 'U' AND ARSScanType = 'D' AND ARSType='AN')
    SET @Volume = (SELECT COUNT(DataID) FROM tblARSData WHERE (tblARSData.WeekEndDate BETWEEN @startdate AND @enddate) AND ARSScanType = 'D' AND ARSType='AN')
    SET @GrossEffect = ((@OnTime/@Volume) * 100)
    SET @NetEffect = (((@OnTime + @UnControlled)/@Volume) * 100)
    SET @WeekEndDate = (SELECT DISTINCT WeekEndDate FROM tblARSData)

    INSERT INTO #CalcTemp(ReportType, Volume, NetEffect, GrossEffect, WeekEndDate)
    VALUES ('AN',
    @Volume,
    @NetEffect,
    @GrossEffect,
    @WeekEndDate)
    --ARS AC
    SET @OnTime = (SELECT COUNT(DataID) FROM tblARSData WHERE (tblARSData.WeekEndDate BETWEEN @startdate AND @enddate) AND OnTimeFlag = 1 AND ARSScanType = 'D' AND ARSType='AC')
    SET @UnControlled = (SELECT COUNT(DataID) FROM tblARSData WHERE (tblARSData.WeekEndDate BETWEEN @startdate AND @enddate) AND ControlFlag = 'U' AND ARSScanType = 'D' AND ARSType='AC')
    SET @Volume = (SELECT COUNT(DataID) FROM tblARSData WHERE (tblARSData.WeekEndDate BETWEEN @startdate AND @enddate) AND ARSScanType = 'D' AND ARSType='AC')
    SET @GrossEffect = ((@OnTime/@Volume) * 100)
    SET @NetEffect = (((@OnTime + @UnControlled)/@Volume) * 100)
    SET @WeekEndDate = (SELECT DISTINCT WeekEndDate FROM tblARSData)

    INSERT INTO #CalcTemp(ReportType, Volume, NetEffect, GrossEffect, WeekEndDate)
    VALUES ('AC',
    @Volume,
    @NetEffect,
    @GrossEffect,
    @WeekEndDate)
    --General
    SET @OnTime = (SELECT COUNT(DataID) FROM tblShipData WHERE (tblShipData.WeekEndDate BETWEEN @startdate AND @enddate) AND OnTimeFlag = 1 AND ReportType = 'GN' AND ScanType='D')
    SET @UnControlled = (SELECT COUNT(DataID) FROM tblShipData WHERE (tblShipData.WeekEndDate BETWEEN @startdate AND @enddate) AND ControlFlag = 'U' AND ReportType = 'GN' AND ScanType='D')
    SET @Volume = (SELECT COUNT(DataID) FROM tblShipData WHERE (tblShipData.WeekEndDate BETWEEN @startdate AND @enddate) AND ReportType = 'GN' AND ScanType='D')
    SET @GrossEffect = ((@OnTime/@Volume) * 100)
    SET @NetEffect = (((@OnTime + @UnControlled)/@Volume) * 100)
    SET @WeekEndDate = (SELECT DISTINCT WeekEndDate FROM tblShipData)

    INSERT INTO #CalcTemp(ReportType, Volume, NetEffect, GrossEffect, WeekEndDate)
    VALUES ('GN',
    @Volume,
    @NetEffect,
    @GrossEffect,
    @WeekEndDate)
    --Odessey
    SET @OnTime = (SELECT COUNT(DataID) FROM tblShipData WHERE (tblShipData.WeekEndDate BETWEEN @startdate AND @enddate) AND OnTimeFlag = 1 AND ReportType = 'OD' AND ScanType='D')
    SET @UnControlled = (SELECT COUNT(DataID) FROM tblShipData WHERE (tblShipData.WeekEndDate BETWEEN @startdate AND @enddate) AND ControlFlag = 'U' AND ReportType = 'OD' AND ScanType='D')
    SET @Volume = (SELECT COUNT(DataID) FROM tblShipData WHERE (tblShipData.WeekEndDate BETWEEN @startdate AND @enddate) AND ReportType = 'OD' AND ScanType='D')
    SET @GrossEffect = ((@OnTime/@Volume) * 100)
    SET @NetEffect = (((@OnTime + @UnControlled)/@Volume) * 100)
    SET @WeekEndDate = (SELECT DISTINCT WeekEndDate FROM tblShipData)

    INSERT INTO #CalcTemp(ReportType, Volume, NetEffect, GrossEffect, WeekEndDate)
    VALUES ('OD',
    @Volume,
    @NetEffect,
    @GrossEffect,
    @WeekEndDate)
    --General
    SET @OnTime = (SELECT COUNT(DataID) FROM tblShipData WHERE (tblShipData.WeekEndDate BETWEEN @startdate AND @enddate) AND OnTimeFlag = 1 AND ReportType = 'HU' AND ScanType='D')
    SET @UnControlled = (SELECT COUNT(DataID) FROM tblShipData WHERE (tblShipData.WeekEndDate BETWEEN @startdate AND @enddate) AND ControlFlag = 'U' AND ReportType = 'HU' AND ScanType='D')
    SET @Volume = (SELECT COUNT(DataID) FROM tblShipData WHERE (tblShipData.WeekEndDate BETWEEN @startdate AND @enddate) AND ReportType = 'HU' AND ScanType='D')
    SET @GrossEffect = ((@OnTime/@Volume) * 100)
    SET @NetEffect = (((@OnTime + @UnControlled)/@Volume) * 100)
    SET @WeekEndDate = (SELECT DISTINCT WeekEndDate FROM tblShipData)

    INSERT INTO #CalcTemp(ReportType, Volume, NetEffect, GrossEffect, WeekEndDate)
    VALUES ('HU',
    @Volume,
    @NetEffect,
    @GrossEffect,
    @WeekEndDate)


    SELECT tblListReportType.ReportType AS 'Report Type',
    tblListReportType.ReportID AS ReportID,
    SUM(#CalcTemp.Volume) AS Volume,
    CAST(SUM(#CalcTemp.NetEffect)/COUNT(#CalcTemp.DataID) as decimal(10,1)) AS 'Net % Effective',
    CAST(SUM(#CalcTemp.GrossEffect)/COUNT(#CalcTemp.DataID) as decimal(10,1)) AS 'Gross % Effective'
    FROM #CalcTemp
    INNER JOIN tblListReportType ON LTRIM(RTRIM(LOWER(#CalcTemp.ReportType))) = LTRIM(RTRIM(LOWER(tblListReportType.ReportAbv)))
    GROUP BY tblListReportType.ReportType,
    tblListReportType.ReportID
    END
    GO

  2. #2
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Insert into Temp table

    When you do it step by step in the QA do you really insert results
    in your temporary table ?

    Replace your @StartDate and @EndDate by some values and run


    DECLARE @OnTime decimal(10,1)
    DECLARE @UnControlled decimal(10,1)
    DECLARE @Volume int
    DECLARE @GrossEffect decimal(10,1)
    DECLARE @NetEffect decimal(10,1)
    DECLARE @WeekEndDate datetime
    DECLARE @EndDate as DateTime
    DECLARE @StartDate as DateTime


    SET @EndDate='2010-01-01'
    SET @StartDate='1950-01-01'

    SET @OnTime = (SELECT COUNT(DataID) FROM tblARSData WHERE (tblARSData.WeekEndDate BETWEEN @startdate AND @enddate) AND OnTimeFlag = 1 AND ARSScanType = 'D' AND ARSType='AA')
    SET @UnControlled = (SELECT COUNT(DataID) FROM tblARSData WHERE (tblARSData.WeekEndDate BETWEEN @startdate AND @enddate) AND ControlFlag = 'U' AND ARSScanType = 'D' AND ARSType='AA')
    SET @Volume = (SELECT COUNT(DataID) FROM tblARSData WHERE (tblARSData.WeekEndDate BETWEEN @startdate AND @enddate) AND ARSScanType = 'D' AND ARSType='AA')
    SET @GrossEffect = ((@OnTime/@Volume) * 100)
    SET @NetEffect = (((@OnTime + @UnControlled)/@Volume) * 100)
    SET @WeekEndDate = (SELECT DISTINCT WeekEndDate FROM tblARSData)

    INSERT INTO #CalcTemp(ReportType, Volume, NetEffect, GrossEffect, WeekEndDate)
    VALUES ('AA',
    @Volume,
    @NetEffect,
    @GrossEffect,
    @WeekEndDate)


    Select * from #CalcTemp

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •