Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2005
    Posts
    19
    Provided Answers: 1

    Question Unanswered: Results are returning on different row levels. HELP!

    How do I get my data to show starting at the first row instead of skipping down?

    Refer to the attachment.

    Code:
    CREATE PROCEDURE [dbo].[uspReportData] 
    	-- Add the parameters for the stored procedure here
    	@Metric1 as varchar(50) = NULL, @Metric2 as varchar(50) = NULL, @Metric3 as varchar(50) = NULL, @Metric4 as varchar(50) = NULL,
    	@Metric5 as varchar(50) = NULL, @Metric6 as varchar(50) = NULL, @Metric7 as varchar(50) = NULL, @Metric8 as varchar(50) = NULL,
    	@Plant1 as varchar(50) = NULL, @Plant2 as varchar(50) = NULL, @Plant3 as varchar(50) = NULL, @Plant4 as varchar(50) = NULL,
    	@Plant5 as varchar(50) = NULL, @Plant6 as varchar(50) = NULL, @Plant7 as varchar(50) = NULL, @Plant8 as varchar(50) = NULL,
    	@WorkCenter1 as varchar(50) = NULL, @WorkCenter2 as varchar(50) = NULL, @WorkCenter3 as varchar(50) = NULL, @WorkCenter4 as varchar(50) = NULL,
    	@WorkCenter5 as varchar(50) = NULL, @WorkCenter6 as varchar(50) = NULL, @WorkCenter7 as varchar(50) = NULL, @WorkCenter8 as varchar(50) = NULL
    	
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	
    	SET FMTONLY OFF;
    
    	DECLARE @ReportData TABLE( 
    	RecColumn1 real,
    	RecColumn2 real,
    	RecColumn3 real,
    	RecColumn4 real,
    	RecColumn5 real,
    	RecColumn6 real,
    	RecColumn7 real,
    	RecColumn8 real,
    	RecColumn9 real,
    	RecColumn10 real,
    	RecColumn11 real,
    	RecColumn12 real,
    	RecColumn13 real,
    	RecColumn14 real,
    	RecColumn15 real,
    	RecColumn16 real,
    	RecColumn17 real,
    	RecColumn18 real,
    	RecColumn19 real,
    	RecColumn20 real
    	 )
    
    	
    	Insert Into @ReportData (RecColumn1)
    	SELECT Value
    	FROM [dbo].[factMetricValues] fmv
    	WHERE PlantFK IN (SELECT PlantPK FROM [dbo].[dimPlant] WHERE Name = @Plant1)
    		AND WorkCenterIDFK IN (SELECT WorkCenterIDPK FROM dimWorkCenters WHERE Name = @WorkCenter1)
    		AND MetricIDFK IN (SELECT MetricIDPK FROM dimMetric WHERE Name = @Metric1)
    
    	Insert Into @ReportData (RecColumn2)
    	SELECT Value
    	FROM [dbo].[factMetricValues] fmv
    	WHERE PlantFK IN (SELECT PlantPK FROM [dbo].[dimPlant] WHERE Name = @Plant2)
    		AND WorkCenterIDFK IN (SELECT WorkCenterIDPK FROM dimWorkCenters WHERE Name = @WorkCenter2)
    		AND MetricIDFK IN (SELECT MetricIDPK FROM dimMetric WHERE Name = @Metric2)
    
    
    	Insert Into @ReportData (RecColumn3)
    	SELECT Value
    	FROM [dbo].[factMetricValues] fmv
    	WHERE PlantFK IN (SELECT PlantPK FROM [dbo].[dimPlant] WHERE Name = @Plant3)
    		AND WorkCenterIDFK IN (SELECT WorkCenterIDPK FROM dimWorkCenters WHERE Name = @WorkCenter3)
    		AND MetricIDFK IN (SELECT MetricIDPK FROM dimMetric WHERE Name = @Metric3)
    
    
    	Insert Into @ReportData (RecColumn4)
    	SELECT Value
    	FROM [dbo].[factMetricValues] fmv
    	WHERE PlantFK IN (SELECT PlantPK FROM [dbo].[dimPlant] WHERE Name = @Plant4)
    		AND WorkCenterIDFK IN (SELECT WorkCenterIDPK FROM dimWorkCenters WHERE Name = @WorkCenter4)
    		AND MetricIDFK IN (SELECT MetricIDPK FROM dimMetric WHERE Name = @Metric4)
    	
    
    	Insert Into @ReportData (RecColumn5)
    	SELECT Value
    	FROM [dbo].[factMetricValues] fmv
    	WHERE PlantFK IN (SELECT PlantPK FROM [dbo].[dimPlant] WHERE Name = @Plant5)
    		AND WorkCenterIDFK IN (SELECT WorkCenterIDPK FROM dimWorkCenters WHERE Name = @WorkCenter5)
    		AND MetricIDFK IN (SELECT MetricIDPK FROM dimMetric WHERE Name = @Metric5)
    
    	
    	Insert Into @ReportData (RecColumn6)
    	SELECT Value
    	FROM [dbo].[factMetricValues] fmv
    	WHERE PlantFK IN (SELECT PlantPK FROM [dbo].[dimPlant] WHERE Name = @Plant6)
    		AND WorkCenterIDFK IN (SELECT WorkCenterIDPK FROM dimWorkCenters WHERE Name = @WorkCenter6)
    		AND MetricIDFK IN (SELECT MetricIDPK FROM dimMetric WHERE Name = @Metric6)
    
    
    	Insert Into @ReportData (RecColumn7)
    	SELECT Value
    	FROM [dbo].[factMetricValues] fmv
    	WHERE PlantFK IN (SELECT PlantPK FROM [dbo].[dimPlant] WHERE Name = @Plant7)
    		AND WorkCenterIDFK IN (SELECT WorkCenterIDPK FROM dimWorkCenters WHERE Name = @WorkCenter7)
    		AND MetricIDFK IN (SELECT MetricIDPK FROM dimMetric WHERE Name = @Metric7)
    
    
    	Insert Into @ReportData (RecColumn8)
    	SELECT Value
    	FROM [dbo].[factMetricValues] fmv
    	WHERE PlantFK IN (SELECT PlantPK FROM [dbo].[dimPlant] WHERE Name = @Plant8)
    		AND WorkCenterIDFK IN (SELECT WorkCenterIDPK FROM dimWorkCenters WHERE Name = @WorkCenter8)
    		AND MetricIDFK IN (SELECT MetricIDPK FROM dimMetric WHERE Name = @Metric8)
    
    	
    	
    	
    	SET FMTONLY OFF;
    
    	SELECT RecColumn1, RecColumn2 , RecColumn3,  RecColumn4 , RecColumn5 , RecColumn6 , RecColumn7 , RecColumn8,
    			RecColumn9 , RecColumn10, RecColumn11, RecColumn12, RecColumn13, RecColumn14, RecColumn15, RecColumn16,
    			RecColumn17,RecColumn18, RecColumn19, RecColumn20
    	FROM @ReportData
    
    END
    Attached Thumbnails Attached Thumbnails Capture.PNG  

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It's because that's exactly what the data looks like in your table variable.

    What decides which value goes on each row?
    In your screenshot does the first column 95.6 go alongside the 88, 92.2, 82, etc and why?
    George
    Home | Blog

Posting Permissions

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