Mixing
I need a second look at my stored procedure
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have an issue and I do not know if it is with my stored procedure or with the SSRS-like report that looks against the output data in this stored procedure. When I run this stored procedure, I see the output I expect to see. I notice that it returns and integer. Is that cause for concern? Also, when I try to report against the output of this data and use any aggregates other than FIRST, I cannot get any of the aggregates to function.
What am I doing wrong or is the stored procedure correct?
USE [SPCPRD]
GO
/****** Object: StoredProcedure [dbo].[spReportPlantLineWeightEvaluation_Gainseeker] Script Date: 1/3/2019 8:26:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spReportPlantLineWeightEvaluation_Gainseeker]
(
@PlantCode char(30), -- '1002'
@MachineLineID char(30), -- 'ND'
@StartDate char(30), -- = '08/05/2018 00:00:00';
@EndDate char(30), -- = '09/05/2018 23:59:59';
@ProductCode char(30) -- = '20J16HD'
)
AS
BEGIN
-------------------------------------------------------------------------------------------
---- Create a CTE to hold the analysis data
-------------------------------------------------------------------------------------------
CREATE TABLE #AnalysisData
(
PLANT_CODE CHAR(30),
ITEM_CODE varchar(30),
SampleTime char(30),
MachineID char(30),
RowNum char(30),
RowPos char(30),
Cavity char(30),
TestedBy char(30),
[WEIGHT] float,
AverageLineWeight DECIMAL(10,2),
[THICKNESS SIDEWALL MIN] float,
[THICKNESS SIDEWALL MAX] float,
[OFF CENTER TOLERANCE] float,
[THICKNESS BOTTOM] float,
);
WITH FirstData AS
(
SELECT * FROM
(
SELECT OC_VDATA.UDL1 AS PLANT_CODE, dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12)) AS ITEM_CODE,
OC_VDATA.UDL5 AS TestName, OC_VDAT_AUX.UDL40 AS SampleTime, OC_VDAT_AUX.UDL8 AS MachineID, OC_VDAT_AUX.UDL13 AS RowNum,
OC_VDAT_AUX.UDL21 AS RowPos, OC_VDAT_AUX.UDL12 AS Cavity, OC_VDAT_AUX.UDL18 AS TestedBy, OC_VDATA.DATA1
FROM OC_VDAT_AUX INNER JOIN
OC_VDATA ON OC_VDAT_AUX.PARTNOAUX = OC_VDATA.PARTNO AND OC_VDAT_AUX.DATETIMEAUX = OC_VDATA.DATETIME
WHERE (OC_VDATA.UDL1 = @PlantCode) AND (OC_VDATA.UDL5 IN ('WEIGHT', 'THICKNESS BOTTOM', 'OFF CENTER TOLERANCE')) AND (OC_VDATA.UDL6 = @MachineLineID)
AND (OC_VDAT_AUX.UDL40 BETWEEN CONVERT(DATETIME, @StartDate, 102) AND CONVERT(DATETIME, @EndDate, 102))
AND (dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12)) = @ProductCode)
) AS BaseData1
PIVOT (Min(Data1) FOR TestName IN([WEIGHT],[THICKNESS BOTTOM],[OFF CENTER TOLERANCE])) as pvt
),
SecondData AS
(
SELECT OC_VDATA.UDL1 AS PLANT_CODE, dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12)) AS ITEM_CODE,
OC_VDATA.UDL5 AS TestName, OC_VDAT_AUX.UDL40 AS SampleTime, OC_VDAT_AUX.UDL8 AS MachineID, OC_VDAT_AUX.UDL13 AS RowNum,
OC_VDAT_AUX.UDL21 AS RowPos, OC_VDAT_AUX.UDL12 AS Cavity, OC_VDAT_AUX.UDL18 AS TestedBy,
OC_VDATA.DATA1 AS 'THICKNESS SIDEWALL MIN', OC_VDATA.DATA2 AS 'THICKNESS SIDEWALL MAX'
FROM OC_VDAT_AUX INNER JOIN
OC_VDATA ON OC_VDAT_AUX.PARTNOAUX = OC_VDATA.PARTNO AND OC_VDAT_AUX.DATETIMEAUX = OC_VDATA.DATETIME
WHERE (OC_VDATA.UDL1 = @PlantCode) AND (OC_VDATA.UDL5 = 'THICKNESS SIDEWALL') AND
(OC_VDATA.UDL6 = @MachineLineID) AND (OC_VDAT_AUX.UDL40 BETWEEN CONVERT(DATETIME, @StartDate, 102) AND CONVERT(DATETIME, @EndDate, 102))
AND (dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12)) = @ProductCode)
),
LineWeight AS
(
SELECT OC_VDATA.UDL1 AS PLANT_CODE, dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12)) AS ITEM_CODE, Cast(Avg(OC_VDATA.DATA1) AS decimal(10,2)) as AverageLineWeight
FROM OC_VDAT_AUX INNER JOIN
OC_VDATA ON OC_VDAT_AUX.PARTNOAUX = OC_VDATA.PARTNO AND OC_VDAT_AUX.DATETIMEAUX = OC_VDATA.DATETIME
WHERE (OC_VDATA.UDL1 = @PlantCode) AND (OC_VDATA.UDL5 = 'WEIGHT') AND (OC_VDATA.UDL6 = @MachineLineID) AND (OC_VDAT_AUX.UDL40 BETWEEN
CONVERT(DATETIME, @StartDate, 102) AND CONVERT(DATETIME, @EndDate, 102)) AND (dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12))
= @ProductCode)
GROUP BY OC_VDATA.UDL1, dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12))
)
INSERT INTO #AnalysisData
SELECT f.PLANT_CODE, f.ITEM_CODE, f.SampleTime, f.MachineID, f.RowNum, f.RowPos, f.Cavity, f.TestedBy, f.WEIGHT, lw.AverageLineWeight,
s.[THICKNESS SIDEWALL MIN], s.[THICKNESS SIDEWALL MAX], f.[OFF CENTER TOLERANCE], f.[THICKNESS BOTTOM]
FROM FirstData AS f INNER JOIN
SecondData AS s ON f.PLANT_CODE = s.PLANT_CODE INNER JOIN LineWeight AS lw ON f.PLANT_CODE = lw.PLANT_CODE
AND f.ITEM_CODE = s.ITEM_CODE
AND f.SampleTime = s.SampleTime
AND f.MachineID = s.MachineID
AND f.RowNum = s.RowNum
AND f.RowPos = s.RowPos
AND f.Cavity = s.Cavity
AND f.TestedBy = s.TestedBy
-----------------------------------------------------------------------------------------
-- Create a CTE to hold the spec data
-----------------------------------------------------------------------------------------
CREATE TABLE #SpecDataFinal
(
PLANT_CODE CHAR(30),
ITEM_CODE varchar(30),
[Weight - LowerSpec] float,
[Weight - Target] float,
[Weight - UpperSpec] float,
[Thickness Sidewall - LowerSpec] float,
[Thickness Sidewall - Target] float,
[Thickness Sidewall - UpperSpec] float,
[Thickness Bottom - LowerSpec] float,
[Thickness Bottom - Target] float,
[Thickness Bottom - UpperSpec] float,
[Off Center Tolerance - LowerSpec] float,
[Off Center Tolerance - Target] float,
[Off Center Tolerance - UpperSpec] float
);
WITH SpecDataMod AS
(
SELECT PLANT_CODE, ITEM_CODE, PROPERTY + ' - ' + SpecType AS PropertyType, SpecValue FROM
(
SELECT PLANT_CODE, PARTNO, ITEM_CODE, PROPERTY, v.*
FROM vwSpecsByPlantCode
CROSS APPLY (values ('LowerSpec', INDLS), ('Target', TARGETX), ('UpperSpec', INDUS)) v(SpecType, SpecValue)
) AS SpecData
)
INSERT INTO #SpecDataFinal
SELECT * FROM(SELECT PLANT_CODE, ITEM_CODE, PropertyType, SpecValue FROM SpecDataMod) AS SpecBaseData
PIVOT (Min(SpecValue) FOR PropertyType IN([Weight - LowerSpec],[Weight - Target],[Weight - UpperSpec],[Thickness Sidewall - LowerSpec]
,[Thickness Sidewall - Target],[Thickness Sidewall - UpperSpec],[Thickness Bottom - LowerSpec],[Thickness Bottom - Target]
,[Thickness Bottom - UpperSpec],[Off Center Tolerance - LowerSpec],[Off Center Tolerance - Target],[Off Center Tolerance - UpperSpec])) as pvt
-----------------------------------------------------------------------------------------
-- Finally, merge the data together
-----------------------------------------------------------------------------------------
SELECT RTRIM(ad.PLANT_CODE) AS PLANT_CODE, RTRIM(ad.ITEM_CODE) AS ITEM_CODE, RTRIM(ad.SampleTime) AS SampleTime, RTRIM(ad.MachineID) as MachineID,
RTRIM(ad.RowNum) as RowNum, RTRIM(ad.RowPos) as RowPos, RTRIM(ad.Cavity) as Cavity, RTRIM(ad.TestedBy) AS TestedBy, RTRIM(ad.WEIGHT) as WEIGHT,
RTRIM(ad.AverageLineWeight) AS AverageLineWeight, RTRIM(ad.[THICKNESS SIDEWALL MIN]) AS [THICKNESS SIDEWALL MIN],
rtrim(ad.[THICKNESS SIDEWALL MAX]) as [THICKNESS SIDEWALL MAX], rtrim(ad.[OFF CENTER TOLERANCE]) as [OFF CENTER TOLERANCE],
rtrim(ad.[THICKNESS BOTTOM]) as [THICKNESS BOTTOM], rtrim(sd.[Weight - LowerSpec]) as [Weight - LowerSpec], rtrim(sd.[Weight - Target]) as [Weight - Target],
rtrim(sd.[Weight - UpperSpec]) as [Weight - UpperSpec], rtrim(sd.[Thickness Sidewall - LowerSpec]) as [Thickness Sidewall - LowerSpec],
rtrim(sd.[Thickness Sidewall - Target]) AS [Thickness Sidewall - Target] , rtrim(sd.[Thickness Sidewall - UpperSpec]) as [Thickness Sidewall - UpperSpec],
rtrim(sd.[Thickness Bottom - LowerSpec]) as [Thickness Bottom - LowerSpec], rtrim(sd.[Thickness Bottom - Target]) as [Thickness Bottom - Target],
rtrim(sd.[Thickness Bottom - UpperSpec]) AS [Thickness Bottom - UpperSpec], rtrim(sd.[Off Center Tolerance - LowerSpec]) as [Off Center Tolerance - LowerSpec],
rtrim(sd.[Off Center Tolerance - Target]) as [Off Center Tolerance - Target], rtrim(sd.[Off Center Tolerance - UpperSpec]) as [Off Center Tolerance - UpperSpec]
FROM #AnalysisData as ad INNER JOIN #SpecDataFinal AS sd ON ad.ITEM_CODE = sd.ITEM_CODE AND ad.PLANT_CODE = sd.PLANT_CODE
ORDER BY RowPos
--SELECT *
--FROM #AnalysisData as ad INNER JOIN #SpecDataFinal AS sd ON ad.ITEM_CODE = sd.ITEM_CODE AND ad.PLANT_CODE = sd.PLANT_CODE
--ORDER BY ad.SampleTime, ad.RowNum, ad.RowPos
DROP TABLE #SpecDataFinal
DROP TABLE #AnalysisData
END
I can see my results as I expect to see them. When I put the dataset into SSRS and I want to use any of the aggregates, I can only see the FIRST aggregate when I run my report. If I use any of the others, like MIN, MAX, AVG, etc, and run my report, I don't see any of the other aggregates show in my report. It's just a blank spot in the report. Long story short, I was wondering if my stored procedure had anything to do with any of the other aggregates not being able to run.
sql
sql-server stored-procedures edited Jan 4 at 2:01
Yvette Colomb♦
20.4k1571113
asked Jan 3 at 13:35
EricALionsFanEricALionsFan
11
add a comment |
I have an issue and I do not know if it is with my stored procedure or with the SSRS-like report that looks against the output data in this stored procedure. When I run this stored procedure, I see the output I expect to see. I notice that it returns and integer. Is that cause for concern? Also, when I try to report against the output of this data and use any aggregates other than FIRST, I cannot get any of the aggregates to function.
What am I doing wrong or is the stored procedure correct?
USE [SPCPRD]
GO
/****** Object: StoredProcedure [dbo].[spReportPlantLineWeightEvaluation_Gainseeker] Script Date: 1/3/2019 8:26:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spReportPlantLineWeightEvaluation_Gainseeker]
(
@PlantCode char(30), -- '1002'
@MachineLineID char(30), -- 'ND'
@StartDate char(30), -- = '08/05/2018 00:00:00';
@EndDate char(30), -- = '09/05/2018 23:59:59';
@ProductCode char(30) -- = '20J16HD'
)
AS
BEGIN
-------------------------------------------------------------------------------------------
---- Create a CTE to hold the analysis data
-------------------------------------------------------------------------------------------
CREATE TABLE #AnalysisData
(
PLANT_CODE CHAR(30),
ITEM_CODE varchar(30),
SampleTime char(30),
MachineID char(30),
RowNum char(30),
RowPos char(30),
Cavity char(30),
TestedBy char(30),
[WEIGHT] float,
AverageLineWeight DECIMAL(10,2),
[THICKNESS SIDEWALL MIN] float,
[THICKNESS SIDEWALL MAX] float,
[OFF CENTER TOLERANCE] float,
[THICKNESS BOTTOM] float,
);
WITH FirstData AS
(
SELECT * FROM
(
SELECT OC_VDATA.UDL1 AS PLANT_CODE, dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12)) AS ITEM_CODE,
OC_VDATA.UDL5 AS TestName, OC_VDAT_AUX.UDL40 AS SampleTime, OC_VDAT_AUX.UDL8 AS MachineID, OC_VDAT_AUX.UDL13 AS RowNum,
OC_VDAT_AUX.UDL21 AS RowPos, OC_VDAT_AUX.UDL12 AS Cavity, OC_VDAT_AUX.UDL18 AS TestedBy, OC_VDATA.DATA1
FROM OC_VDAT_AUX INNER JOIN
OC_VDATA ON OC_VDAT_AUX.PARTNOAUX = OC_VDATA.PARTNO AND OC_VDAT_AUX.DATETIMEAUX = OC_VDATA.DATETIME
WHERE (OC_VDATA.UDL1 = @PlantCode) AND (OC_VDATA.UDL5 IN ('WEIGHT', 'THICKNESS BOTTOM', 'OFF CENTER TOLERANCE')) AND (OC_VDATA.UDL6 = @MachineLineID)
AND (OC_VDAT_AUX.UDL40 BETWEEN CONVERT(DATETIME, @StartDate, 102) AND CONVERT(DATETIME, @EndDate, 102))
AND (dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12)) = @ProductCode)
) AS BaseData1
PIVOT (Min(Data1) FOR TestName IN([WEIGHT],[THICKNESS BOTTOM],[OFF CENTER TOLERANCE])) as pvt
),
SecondData AS
(
SELECT OC_VDATA.UDL1 AS PLANT_CODE, dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12)) AS ITEM_CODE,
OC_VDATA.UDL5 AS TestName, OC_VDAT_AUX.UDL40 AS SampleTime, OC_VDAT_AUX.UDL8 AS MachineID, OC_VDAT_AUX.UDL13 AS RowNum,
OC_VDAT_AUX.UDL21 AS RowPos, OC_VDAT_AUX.UDL12 AS Cavity, OC_VDAT_AUX.UDL18 AS TestedBy,
OC_VDATA.DATA1 AS 'THICKNESS SIDEWALL MIN', OC_VDATA.DATA2 AS 'THICKNESS SIDEWALL MAX'
FROM OC_VDAT_AUX INNER JOIN
OC_VDATA ON OC_VDAT_AUX.PARTNOAUX = OC_VDATA.PARTNO AND OC_VDAT_AUX.DATETIMEAUX = OC_VDATA.DATETIME
WHERE (OC_VDATA.UDL1 = @PlantCode) AND (OC_VDATA.UDL5 = 'THICKNESS SIDEWALL') AND
(OC_VDATA.UDL6 = @MachineLineID) AND (OC_VDAT_AUX.UDL40 BETWEEN CONVERT(DATETIME, @StartDate, 102) AND CONVERT(DATETIME, @EndDate, 102))
AND (dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12)) = @ProductCode)
),
LineWeight AS
(
SELECT OC_VDATA.UDL1 AS PLANT_CODE, dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12)) AS ITEM_CODE, Cast(Avg(OC_VDATA.DATA1) AS decimal(10,2)) as AverageLineWeight
FROM OC_VDAT_AUX INNER JOIN
OC_VDATA ON OC_VDAT_AUX.PARTNOAUX = OC_VDATA.PARTNO AND OC_VDAT_AUX.DATETIMEAUX = OC_VDATA.DATETIME
WHERE (OC_VDATA.UDL1 = @PlantCode) AND (OC_VDATA.UDL5 = 'WEIGHT') AND (OC_VDATA.UDL6 = @MachineLineID) AND (OC_VDAT_AUX.UDL40 BETWEEN
CONVERT(DATETIME, @StartDate, 102) AND CONVERT(DATETIME, @EndDate, 102)) AND (dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12))
= @ProductCode)
GROUP BY OC_VDATA.UDL1, dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12))
)
INSERT INTO #AnalysisData
SELECT f.PLANT_CODE, f.ITEM_CODE, f.SampleTime, f.MachineID, f.RowNum, f.RowPos, f.Cavity, f.TestedBy, f.WEIGHT, lw.AverageLineWeight,
s.[THICKNESS SIDEWALL MIN], s.[THICKNESS SIDEWALL MAX], f.[OFF CENTER TOLERANCE], f.[THICKNESS BOTTOM]
FROM FirstData AS f INNER JOIN
SecondData AS s ON f.PLANT_CODE = s.PLANT_CODE INNER JOIN LineWeight AS lw ON f.PLANT_CODE = lw.PLANT_CODE
AND f.ITEM_CODE = s.ITEM_CODE
AND f.SampleTime = s.SampleTime
AND f.MachineID = s.MachineID
AND f.RowNum = s.RowNum
AND f.RowPos = s.RowPos
AND f.Cavity = s.Cavity
AND f.TestedBy = s.TestedBy
-----------------------------------------------------------------------------------------
-- Create a CTE to hold the spec data
-----------------------------------------------------------------------------------------
CREATE TABLE #SpecDataFinal
(
PLANT_CODE CHAR(30),
ITEM_CODE varchar(30),
[Weight - LowerSpec] float,
[Weight - Target] float,
[Weight - UpperSpec] float,
[Thickness Sidewall - LowerSpec] float,
[Thickness Sidewall - Target] float,
[Thickness Sidewall - UpperSpec] float,
[Thickness Bottom - LowerSpec] float,
[Thickness Bottom - Target] float,
[Thickness Bottom - UpperSpec] float,
[Off Center Tolerance - LowerSpec] float,
[Off Center Tolerance - Target] float,
[Off Center Tolerance - UpperSpec] float
);
WITH SpecDataMod AS
(
SELECT PLANT_CODE, ITEM_CODE, PROPERTY + ' - ' + SpecType AS PropertyType, SpecValue FROM
(
SELECT PLANT_CODE, PARTNO, ITEM_CODE, PROPERTY, v.*
FROM vwSpecsByPlantCode
CROSS APPLY (values ('LowerSpec', INDLS), ('Target', TARGETX), ('UpperSpec', INDUS)) v(SpecType, SpecValue)
) AS SpecData
)
INSERT INTO #SpecDataFinal
SELECT * FROM(SELECT PLANT_CODE, ITEM_CODE, PropertyType, SpecValue FROM SpecDataMod) AS SpecBaseData
PIVOT (Min(SpecValue) FOR PropertyType IN([Weight - LowerSpec],[Weight - Target],[Weight - UpperSpec],[Thickness Sidewall - LowerSpec]
,[Thickness Sidewall - Target],[Thickness Sidewall - UpperSpec],[Thickness Bottom - LowerSpec],[Thickness Bottom - Target]
,[Thickness Bottom - UpperSpec],[Off Center Tolerance - LowerSpec],[Off Center Tolerance - Target],[Off Center Tolerance - UpperSpec])) as pvt
-----------------------------------------------------------------------------------------
-- Finally, merge the data together
-----------------------------------------------------------------------------------------
SELECT RTRIM(ad.PLANT_CODE) AS PLANT_CODE, RTRIM(ad.ITEM_CODE) AS ITEM_CODE, RTRIM(ad.SampleTime) AS SampleTime, RTRIM(ad.MachineID) as MachineID,
RTRIM(ad.RowNum) as RowNum, RTRIM(ad.RowPos) as RowPos, RTRIM(ad.Cavity) as Cavity, RTRIM(ad.TestedBy) AS TestedBy, RTRIM(ad.WEIGHT) as WEIGHT,
RTRIM(ad.AverageLineWeight) AS AverageLineWeight, RTRIM(ad.[THICKNESS SIDEWALL MIN]) AS [THICKNESS SIDEWALL MIN],
rtrim(ad.[THICKNESS SIDEWALL MAX]) as [THICKNESS SIDEWALL MAX], rtrim(ad.[OFF CENTER TOLERANCE]) as [OFF CENTER TOLERANCE],
rtrim(ad.[THICKNESS BOTTOM]) as [THICKNESS BOTTOM], rtrim(sd.[Weight - LowerSpec]) as [Weight - LowerSpec], rtrim(sd.[Weight - Target]) as [Weight - Target],
rtrim(sd.[Weight - UpperSpec]) as [Weight - UpperSpec], rtrim(sd.[Thickness Sidewall - LowerSpec]) as [Thickness Sidewall - LowerSpec],
rtrim(sd.[Thickness Sidewall - Target]) AS [Thickness Sidewall - Target] , rtrim(sd.[Thickness Sidewall - UpperSpec]) as [Thickness Sidewall - UpperSpec],
rtrim(sd.[Thickness Bottom - LowerSpec]) as [Thickness Bottom - LowerSpec], rtrim(sd.[Thickness Bottom - Target]) as [Thickness Bottom - Target],
rtrim(sd.[Thickness Bottom - UpperSpec]) AS [Thickness Bottom - UpperSpec], rtrim(sd.[Off Center Tolerance - LowerSpec]) as [Off Center Tolerance - LowerSpec],
rtrim(sd.[Off Center Tolerance - Target]) as [Off Center Tolerance - Target], rtrim(sd.[Off Center Tolerance - UpperSpec]) as [Off Center Tolerance - UpperSpec]
FROM #AnalysisData as ad INNER JOIN #SpecDataFinal AS sd ON ad.ITEM_CODE = sd.ITEM_CODE AND ad.PLANT_CODE = sd.PLANT_CODE
ORDER BY RowPos
--SELECT *
--FROM #AnalysisData as ad INNER JOIN #SpecDataFinal AS sd ON ad.ITEM_CODE = sd.ITEM_CODE AND ad.PLANT_CODE = sd.PLANT_CODE
--ORDER BY ad.SampleTime, ad.RowNum, ad.RowPos
DROP TABLE #SpecDataFinal
DROP TABLE #AnalysisData
END
I can see my results as I expect to see them. When I put the dataset into SSRS and I want to use any of the aggregates, I can only see the FIRST aggregate when I run my report. If I use any of the others, like MIN, MAX, AVG, etc, and run my report, I don't see any of the other aggregates show in my report. It's just a blank spot in the report. Long story short, I was wondering if my stored procedure had anything to do with any of the other aggregates not being able to run.
sql
sql-server stored-procedures edited Jan 4 at 2:01
Yvette Colomb♦
20.4k1571113
asked Jan 3 at 13:35
EricALionsFanEricALionsFan
11
When have you add the code in the dataset in SSRS, Does it returns more data than the first row?
– Geovanny Hernandez
Jan 3 at 13:55
You said When I run this stored procedure, I see the output I expect to see. So what's the question? I notice that it returns and integer. Do you mean when you call it from an application? If so, this means success.
– scsimon
Jan 3 at 13:55
Geovanny - Yes, I can see my results as I expect to see them. When I put the dataset into SSRS and I want to use any of the aggregates, I can only see the FIRST aggregate when I run my report. If I use any of the others, like MIN, MAX, AVG, etc, and run my report, I don't see any of the other aggregates show in my report. It's just a blank spot in the report. Long story short, I was wondering if my stored procedure had anything to do with any of the other aggregates not being able to run.
– EricALionsFan
Jan 3 at 14:11
add a comment |
I have an issue and I do not know if it is with my stored procedure or with the SSRS-like report that looks against the output data in this stored procedure. When I run this stored procedure, I see the output I expect to see. I notice that it returns and integer. Is that cause for concern? Also, when I try to report against the output of this data and use any aggregates other than FIRST, I cannot get any of the aggregates to function.
What am I doing wrong or is the stored procedure correct?
USE [SPCPRD]
GO
/****** Object: StoredProcedure [dbo].[spReportPlantLineWeightEvaluation_Gainseeker] Script Date: 1/3/2019 8:26:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spReportPlantLineWeightEvaluation_Gainseeker]
(
@PlantCode char(30), -- '1002'
@MachineLineID char(30), -- 'ND'
@StartDate char(30), -- = '08/05/2018 00:00:00';
@EndDate char(30), -- = '09/05/2018 23:59:59';
@ProductCode char(30) -- = '20J16HD'
)
AS
BEGIN
-------------------------------------------------------------------------------------------
---- Create a CTE to hold the analysis data
-------------------------------------------------------------------------------------------
CREATE TABLE #AnalysisData
(
PLANT_CODE CHAR(30),
ITEM_CODE varchar(30),
SampleTime char(30),
MachineID char(30),
RowNum char(30),
RowPos char(30),
Cavity char(30),
TestedBy char(30),
[WEIGHT] float,
AverageLineWeight DECIMAL(10,2),
[THICKNESS SIDEWALL MIN] float,
[THICKNESS SIDEWALL MAX] float,
[OFF CENTER TOLERANCE] float,
[THICKNESS BOTTOM] float,
);
WITH FirstData AS
(
SELECT * FROM
(
SELECT OC_VDATA.UDL1 AS PLANT_CODE, dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12)) AS ITEM_CODE,
OC_VDATA.UDL5 AS TestName, OC_VDAT_AUX.UDL40 AS SampleTime, OC_VDAT_AUX.UDL8 AS MachineID, OC_VDAT_AUX.UDL13 AS RowNum,
OC_VDAT_AUX.UDL21 AS RowPos, OC_VDAT_AUX.UDL12 AS Cavity, OC_VDAT_AUX.UDL18 AS TestedBy, OC_VDATA.DATA1
FROM OC_VDAT_AUX INNER JOIN
OC_VDATA ON OC_VDAT_AUX.PARTNOAUX = OC_VDATA.PARTNO AND OC_VDAT_AUX.DATETIMEAUX = OC_VDATA.DATETIME
WHERE (OC_VDATA.UDL1 = @PlantCode) AND (OC_VDATA.UDL5 IN ('WEIGHT', 'THICKNESS BOTTOM', 'OFF CENTER TOLERANCE')) AND (OC_VDATA.UDL6 = @MachineLineID)
AND (OC_VDAT_AUX.UDL40 BETWEEN CONVERT(DATETIME, @StartDate, 102) AND CONVERT(DATETIME, @EndDate, 102))
AND (dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12)) = @ProductCode)
) AS BaseData1
PIVOT (Min(Data1) FOR TestName IN([WEIGHT],[THICKNESS BOTTOM],[OFF CENTER TOLERANCE])) as pvt
),
SecondData AS
(
SELECT OC_VDATA.UDL1 AS PLANT_CODE, dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12)) AS ITEM_CODE,
OC_VDATA.UDL5 AS TestName, OC_VDAT_AUX.UDL40 AS SampleTime, OC_VDAT_AUX.UDL8 AS MachineID, OC_VDAT_AUX.UDL13 AS RowNum,
OC_VDAT_AUX.UDL21 AS RowPos, OC_VDAT_AUX.UDL12 AS Cavity, OC_VDAT_AUX.UDL18 AS TestedBy,
OC_VDATA.DATA1 AS 'THICKNESS SIDEWALL MIN', OC_VDATA.DATA2 AS 'THICKNESS SIDEWALL MAX'
FROM OC_VDAT_AUX INNER JOIN
OC_VDATA ON OC_VDAT_AUX.PARTNOAUX = OC_VDATA.PARTNO AND OC_VDAT_AUX.DATETIMEAUX = OC_VDATA.DATETIME
WHERE (OC_VDATA.UDL1 = @PlantCode) AND (OC_VDATA.UDL5 = 'THICKNESS SIDEWALL') AND
(OC_VDATA.UDL6 = @MachineLineID) AND (OC_VDAT_AUX.UDL40 BETWEEN CONVERT(DATETIME, @StartDate, 102) AND CONVERT(DATETIME, @EndDate, 102))
AND (dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12)) = @ProductCode)
),
LineWeight AS
(
SELECT OC_VDATA.UDL1 AS PLANT_CODE, dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12)) AS ITEM_CODE, Cast(Avg(OC_VDATA.DATA1) AS decimal(10,2)) as AverageLineWeight
FROM OC_VDAT_AUX INNER JOIN
OC_VDATA ON OC_VDAT_AUX.PARTNOAUX = OC_VDATA.PARTNO AND OC_VDAT_AUX.DATETIMEAUX = OC_VDATA.DATETIME
WHERE (OC_VDATA.UDL1 = @PlantCode) AND (OC_VDATA.UDL5 = 'WEIGHT') AND (OC_VDATA.UDL6 = @MachineLineID) AND (OC_VDAT_AUX.UDL40 BETWEEN
CONVERT(DATETIME, @StartDate, 102) AND CONVERT(DATETIME, @EndDate, 102)) AND (dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12))
= @ProductCode)
GROUP BY OC_VDATA.UDL1, dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12))
)
INSERT INTO #AnalysisData
SELECT f.PLANT_CODE, f.ITEM_CODE, f.SampleTime, f.MachineID, f.RowNum, f.RowPos, f.Cavity, f.TestedBy, f.WEIGHT, lw.AverageLineWeight,
s.[THICKNESS SIDEWALL MIN], s.[THICKNESS SIDEWALL MAX], f.[OFF CENTER TOLERANCE], f.[THICKNESS BOTTOM]
FROM FirstData AS f INNER JOIN
SecondData AS s ON f.PLANT_CODE = s.PLANT_CODE INNER JOIN LineWeight AS lw ON f.PLANT_CODE = lw.PLANT_CODE
AND f.ITEM_CODE = s.ITEM_CODE
AND f.SampleTime = s.SampleTime
AND f.MachineID = s.MachineID
AND f.RowNum = s.RowNum
AND f.RowPos = s.RowPos
AND f.Cavity = s.Cavity
AND f.TestedBy = s.TestedBy
-----------------------------------------------------------------------------------------
-- Create a CTE to hold the spec data
-----------------------------------------------------------------------------------------
CREATE TABLE #SpecDataFinal
(
PLANT_CODE CHAR(30),
ITEM_CODE varchar(30),
[Weight - LowerSpec] float,
[Weight - Target] float,
[Weight - UpperSpec] float,
[Thickness Sidewall - LowerSpec] float,
[Thickness Sidewall - Target] float,
[Thickness Sidewall - UpperSpec] float,
[Thickness Bottom - LowerSpec] float,
[Thickness Bottom - Target] float,
[Thickness Bottom - UpperSpec] float,
[Off Center Tolerance - LowerSpec] float,
[Off Center Tolerance - Target] float,
[Off Center Tolerance - UpperSpec] float
);
WITH SpecDataMod AS
(
SELECT PLANT_CODE, ITEM_CODE, PROPERTY + ' - ' + SpecType AS PropertyType, SpecValue FROM
(
SELECT PLANT_CODE, PARTNO, ITEM_CODE, PROPERTY, v.*
FROM vwSpecsByPlantCode
CROSS APPLY (values ('LowerSpec', INDLS), ('Target', TARGETX), ('UpperSpec', INDUS)) v(SpecType, SpecValue)
) AS SpecData
)
INSERT INTO #SpecDataFinal
SELECT * FROM(SELECT PLANT_CODE, ITEM_CODE, PropertyType, SpecValue FROM SpecDataMod) AS SpecBaseData
PIVOT (Min(SpecValue) FOR PropertyType IN([Weight - LowerSpec],[Weight - Target],[Weight - UpperSpec],[Thickness Sidewall - LowerSpec]
,[Thickness Sidewall - Target],[Thickness Sidewall - UpperSpec],[Thickness Bottom - LowerSpec],[Thickness Bottom - Target]
,[Thickness Bottom - UpperSpec],[Off Center Tolerance - LowerSpec],[Off Center Tolerance - Target],[Off Center Tolerance - UpperSpec])) as pvt
-----------------------------------------------------------------------------------------
-- Finally, merge the data together
-----------------------------------------------------------------------------------------
SELECT RTRIM(ad.PLANT_CODE) AS PLANT_CODE, RTRIM(ad.ITEM_CODE) AS ITEM_CODE, RTRIM(ad.SampleTime) AS SampleTime, RTRIM(ad.MachineID) as MachineID,
RTRIM(ad.RowNum) as RowNum, RTRIM(ad.RowPos) as RowPos, RTRIM(ad.Cavity) as Cavity, RTRIM(ad.TestedBy) AS TestedBy, RTRIM(ad.WEIGHT) as WEIGHT,
RTRIM(ad.AverageLineWeight) AS AverageLineWeight, RTRIM(ad.[THICKNESS SIDEWALL MIN]) AS [THICKNESS SIDEWALL MIN],
rtrim(ad.[THICKNESS SIDEWALL MAX]) as [THICKNESS SIDEWALL MAX], rtrim(ad.[OFF CENTER TOLERANCE]) as [OFF CENTER TOLERANCE],
rtrim(ad.[THICKNESS BOTTOM]) as [THICKNESS BOTTOM], rtrim(sd.[Weight - LowerSpec]) as [Weight - LowerSpec], rtrim(sd.[Weight - Target]) as [Weight - Target],
rtrim(sd.[Weight - UpperSpec]) as [Weight - UpperSpec], rtrim(sd.[Thickness Sidewall - LowerSpec]) as [Thickness Sidewall - LowerSpec],
rtrim(sd.[Thickness Sidewall - Target]) AS [Thickness Sidewall - Target] , rtrim(sd.[Thickness Sidewall - UpperSpec]) as [Thickness Sidewall - UpperSpec],
rtrim(sd.[Thickness Bottom - LowerSpec]) as [Thickness Bottom - LowerSpec], rtrim(sd.[Thickness Bottom - Target]) as [Thickness Bottom - Target],
rtrim(sd.[Thickness Bottom - UpperSpec]) AS [Thickness Bottom - UpperSpec], rtrim(sd.[Off Center Tolerance - LowerSpec]) as [Off Center Tolerance - LowerSpec],
rtrim(sd.[Off Center Tolerance - Target]) as [Off Center Tolerance - Target], rtrim(sd.[Off Center Tolerance - UpperSpec]) as [Off Center Tolerance - UpperSpec]
FROM #AnalysisData as ad INNER JOIN #SpecDataFinal AS sd ON ad.ITEM_CODE = sd.ITEM_CODE AND ad.PLANT_CODE = sd.PLANT_CODE
ORDER BY RowPos
--SELECT *
--FROM #AnalysisData as ad INNER JOIN #SpecDataFinal AS sd ON ad.ITEM_CODE = sd.ITEM_CODE AND ad.PLANT_CODE = sd.PLANT_CODE
--ORDER BY ad.SampleTime, ad.RowNum, ad.RowPos
DROP TABLE #SpecDataFinal
DROP TABLE #AnalysisData
END
I can see my results as I expect to see them. When I put the dataset into SSRS and I want to use any of the aggregates, I can only see the FIRST aggregate when I run my report. If I use any of the others, like MIN, MAX, AVG, etc, and run my report, I don't see any of the other aggregates show in my report. It's just a blank spot in the report. Long story short, I was wondering if my stored procedure had anything to do with any of the other aggregates not being able to run.
sql
sql-server stored-procedures edited Jan 4 at 2:01
Yvette Colomb♦
20.4k1571113
asked Jan 3 at 13:35
EricALionsFanEricALionsFan
11
I have an issue and I do not know if it is with my stored procedure or with the SSRS-like report that looks against the output data in this stored procedure. When I run this stored procedure, I see the output I expect to see. I notice that it returns and integer. Is that cause for concern? Also, when I try to report against the output of this data and use any aggregates other than FIRST, I cannot get any of the aggregates to function.
What am I doing wrong or is the stored procedure correct?
USE [SPCPRD]
GO
/****** Object: StoredProcedure [dbo].[spReportPlantLineWeightEvaluation_Gainseeker] Script Date: 1/3/2019 8:26:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spReportPlantLineWeightEvaluation_Gainseeker]
(
@PlantCode char(30), -- '1002'
@MachineLineID char(30), -- 'ND'
@StartDate char(30), -- = '08/05/2018 00:00:00';
@EndDate char(30), -- = '09/05/2018 23:59:59';
@ProductCode char(30) -- = '20J16HD'
)
AS
BEGIN
-------------------------------------------------------------------------------------------
---- Create a CTE to hold the analysis data
-------------------------------------------------------------------------------------------
CREATE TABLE #AnalysisData
(
PLANT_CODE CHAR(30),
ITEM_CODE varchar(30),
SampleTime char(30),
MachineID char(30),
RowNum char(30),
RowPos char(30),
Cavity char(30),
TestedBy char(30),
[WEIGHT] float,
AverageLineWeight DECIMAL(10,2),
[THICKNESS SIDEWALL MIN] float,
[THICKNESS SIDEWALL MAX] float,
[OFF CENTER TOLERANCE] float,
[THICKNESS BOTTOM] float,
);
WITH FirstData AS
(
SELECT * FROM
(
SELECT OC_VDATA.UDL1 AS PLANT_CODE, dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12)) AS ITEM_CODE,
OC_VDATA.UDL5 AS TestName, OC_VDAT_AUX.UDL40 AS SampleTime, OC_VDAT_AUX.UDL8 AS MachineID, OC_VDAT_AUX.UDL13 AS RowNum,
OC_VDAT_AUX.UDL21 AS RowPos, OC_VDAT_AUX.UDL12 AS Cavity, OC_VDAT_AUX.UDL18 AS TestedBy, OC_VDATA.DATA1
FROM OC_VDAT_AUX INNER JOIN
OC_VDATA ON OC_VDAT_AUX.PARTNOAUX = OC_VDATA.PARTNO AND OC_VDAT_AUX.DATETIMEAUX = OC_VDATA.DATETIME
WHERE (OC_VDATA.UDL1 = @PlantCode) AND (OC_VDATA.UDL5 IN ('WEIGHT', 'THICKNESS BOTTOM', 'OFF CENTER TOLERANCE')) AND (OC_VDATA.UDL6 = @MachineLineID)
AND (OC_VDAT_AUX.UDL40 BETWEEN CONVERT(DATETIME, @StartDate, 102) AND CONVERT(DATETIME, @EndDate, 102))
AND (dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12)) = @ProductCode)
) AS BaseData1
PIVOT (Min(Data1) FOR TestName IN([WEIGHT],[THICKNESS BOTTOM],[OFF CENTER TOLERANCE])) as pvt
),
SecondData AS
(
SELECT OC_VDATA.UDL1 AS PLANT_CODE, dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12)) AS ITEM_CODE,
OC_VDATA.UDL5 AS TestName, OC_VDAT_AUX.UDL40 AS SampleTime, OC_VDAT_AUX.UDL8 AS MachineID, OC_VDAT_AUX.UDL13 AS RowNum,
OC_VDAT_AUX.UDL21 AS RowPos, OC_VDAT_AUX.UDL12 AS Cavity, OC_VDAT_AUX.UDL18 AS TestedBy,
OC_VDATA.DATA1 AS 'THICKNESS SIDEWALL MIN', OC_VDATA.DATA2 AS 'THICKNESS SIDEWALL MAX'
FROM OC_VDAT_AUX INNER JOIN
OC_VDATA ON OC_VDAT_AUX.PARTNOAUX = OC_VDATA.PARTNO AND OC_VDAT_AUX.DATETIMEAUX = OC_VDATA.DATETIME
WHERE (OC_VDATA.UDL1 = @PlantCode) AND (OC_VDATA.UDL5 = 'THICKNESS SIDEWALL') AND
(OC_VDATA.UDL6 = @MachineLineID) AND (OC_VDAT_AUX.UDL40 BETWEEN CONVERT(DATETIME, @StartDate, 102) AND CONVERT(DATETIME, @EndDate, 102))
AND (dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12)) = @ProductCode)
),
LineWeight AS
(
SELECT OC_VDATA.UDL1 AS PLANT_CODE, dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12)) AS ITEM_CODE, Cast(Avg(OC_VDATA.DATA1) AS decimal(10,2)) as AverageLineWeight
FROM OC_VDAT_AUX INNER JOIN
OC_VDATA ON OC_VDAT_AUX.PARTNOAUX = OC_VDATA.PARTNO AND OC_VDAT_AUX.DATETIMEAUX = OC_VDATA.DATETIME
WHERE (OC_VDATA.UDL1 = @PlantCode) AND (OC_VDATA.UDL5 = 'WEIGHT') AND (OC_VDATA.UDL6 = @MachineLineID) AND (OC_VDAT_AUX.UDL40 BETWEEN
CONVERT(DATETIME, @StartDate, 102) AND CONVERT(DATETIME, @EndDate, 102)) AND (dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12))
= @ProductCode)
GROUP BY OC_VDATA.UDL1, dbo.fnItemCodeFromPartNo(LEFT(OC_VDAT_AUX.PARTNOAUX, 12))
)
INSERT INTO #AnalysisData
SELECT f.PLANT_CODE, f.ITEM_CODE, f.SampleTime, f.MachineID, f.RowNum, f.RowPos, f.Cavity, f.TestedBy, f.WEIGHT, lw.AverageLineWeight,
s.[THICKNESS SIDEWALL MIN], s.[THICKNESS SIDEWALL MAX], f.[OFF CENTER TOLERANCE], f.[THICKNESS BOTTOM]
FROM FirstData AS f INNER JOIN
SecondData AS s ON f.PLANT_CODE = s.PLANT_CODE INNER JOIN LineWeight AS lw ON f.PLANT_CODE = lw.PLANT_CODE
AND f.ITEM_CODE = s.ITEM_CODE
AND f.SampleTime = s.SampleTime
AND f.MachineID = s.MachineID
AND f.RowNum = s.RowNum
AND f.RowPos = s.RowPos
AND f.Cavity = s.Cavity
AND f.TestedBy = s.TestedBy
-----------------------------------------------------------------------------------------
-- Create a CTE to hold the spec data
-----------------------------------------------------------------------------------------
CREATE TABLE #SpecDataFinal
(
PLANT_CODE CHAR(30),
ITEM_CODE varchar(30),
[Weight - LowerSpec] float,
[Weight - Target] float,
[Weight - UpperSpec] float,
[Thickness Sidewall - LowerSpec] float,
[Thickness Sidewall - Target] float,
[Thickness Sidewall - UpperSpec] float,
[Thickness Bottom - LowerSpec] float,
[Thickness Bottom - Target] float,
[Thickness Bottom - UpperSpec] float,
[Off Center Tolerance - LowerSpec] float,
[Off Center Tolerance - Target] float,
[Off Center Tolerance - UpperSpec] float
);
WITH SpecDataMod AS
(
SELECT PLANT_CODE, ITEM_CODE, PROPERTY + ' - ' + SpecType AS PropertyType, SpecValue FROM
(
SELECT PLANT_CODE, PARTNO, ITEM_CODE, PROPERTY, v.*
FROM vwSpecsByPlantCode
CROSS APPLY (values ('LowerSpec', INDLS), ('Target', TARGETX), ('UpperSpec', INDUS)) v(SpecType, SpecValue)
) AS SpecData
)
INSERT INTO #SpecDataFinal
SELECT * FROM(SELECT PLANT_CODE, ITEM_CODE, PropertyType, SpecValue FROM SpecDataMod) AS SpecBaseData
PIVOT (Min(SpecValue) FOR PropertyType IN([Weight - LowerSpec],[Weight - Target],[Weight - UpperSpec],[Thickness Sidewall - LowerSpec]
,[Thickness Sidewall - Target],[Thickness Sidewall - UpperSpec],[Thickness Bottom - LowerSpec],[Thickness Bottom - Target]
,[Thickness Bottom - UpperSpec],[Off Center Tolerance - LowerSpec],[Off Center Tolerance - Target],[Off Center Tolerance - UpperSpec])) as pvt
-----------------------------------------------------------------------------------------
-- Finally, merge the data together
-----------------------------------------------------------------------------------------
SELECT RTRIM(ad.PLANT_CODE) AS PLANT_CODE, RTRIM(ad.ITEM_CODE) AS ITEM_CODE, RTRIM(ad.SampleTime) AS SampleTime, RTRIM(ad.MachineID) as MachineID,
RTRIM(ad.RowNum) as RowNum, RTRIM(ad.RowPos) as RowPos, RTRIM(ad.Cavity) as Cavity, RTRIM(ad.TestedBy) AS TestedBy, RTRIM(ad.WEIGHT) as WEIGHT,
RTRIM(ad.AverageLineWeight) AS AverageLineWeight, RTRIM(ad.[THICKNESS SIDEWALL MIN]) AS [THICKNESS SIDEWALL MIN],
rtrim(ad.[THICKNESS SIDEWALL MAX]) as [THICKNESS SIDEWALL MAX], rtrim(ad.[OFF CENTER TOLERANCE]) as [OFF CENTER TOLERANCE],
rtrim(ad.[THICKNESS BOTTOM]) as [THICKNESS BOTTOM], rtrim(sd.[Weight - LowerSpec]) as [Weight - LowerSpec], rtrim(sd.[Weight - Target]) as [Weight - Target],
rtrim(sd.[Weight - UpperSpec]) as [Weight - UpperSpec], rtrim(sd.[Thickness Sidewall - LowerSpec]) as [Thickness Sidewall - LowerSpec],
rtrim(sd.[Thickness Sidewall - Target]) AS [Thickness Sidewall - Target] , rtrim(sd.[Thickness Sidewall - UpperSpec]) as [Thickness Sidewall - UpperSpec],
rtrim(sd.[Thickness Bottom - LowerSpec]) as [Thickness Bottom - LowerSpec], rtrim(sd.[Thickness Bottom - Target]) as [Thickness Bottom - Target],
rtrim(sd.[Thickness Bottom - UpperSpec]) AS [Thickness Bottom - UpperSpec], rtrim(sd.[Off Center Tolerance - LowerSpec]) as [Off Center Tolerance - LowerSpec],
rtrim(sd.[Off Center Tolerance - Target]) as [Off Center Tolerance - Target], rtrim(sd.[Off Center Tolerance - UpperSpec]) as [Off Center Tolerance - UpperSpec]
FROM #AnalysisData as ad INNER JOIN #SpecDataFinal AS sd ON ad.ITEM_CODE = sd.ITEM_CODE AND ad.PLANT_CODE = sd.PLANT_CODE
ORDER BY RowPos
--SELECT *
--FROM #AnalysisData as ad INNER JOIN #SpecDataFinal AS sd ON ad.ITEM_CODE = sd.ITEM_CODE AND ad.PLANT_CODE = sd.PLANT_CODE
--ORDER BY ad.SampleTime, ad.RowNum, ad.RowPos
DROP TABLE #SpecDataFinal
DROP TABLE #AnalysisData
END
I can see my results as I expect to see them. When I put the dataset into SSRS and I want to use any of the aggregates, I can only see the FIRST aggregate when I run my report. If I use any of the others, like MIN, MAX, AVG, etc, and run my report, I don't see any of the other aggregates show in my report. It's just a blank spot in the report. Long story short, I was wondering if my stored procedure had anything to do with any of the other aggregates not being able to run.
sql
sql-server stored-procedures sql
sql-server stored-procedures edited Jan 4 at 2:01
Yvette Colomb♦
20.4k1571113
asked Jan 3 at 13:35
EricALionsFanEricALionsFan
11
edited Jan 4 at 2:01
Yvette Colomb♦
20.4k1571113
asked Jan 3 at 13:35
EricALionsFanEricALionsFan
11
edited Jan 4 at 2:01
Yvette Colomb♦
20.4k1571113
edited Jan 4 at 2:01
Yvette Colomb♦
20.4k1571113
edited Jan 4 at 2:01
Yvette Colomb♦
20.4k1571113
20.4k1571113
asked Jan 3 at 13:35
EricALionsFanEricALionsFan
11
asked Jan 3 at 13:35
EricALionsFanEricALionsFan
11
asked Jan 3 at 13:35
EricALionsFanEricALionsFan
11
11
When have you add the code in the dataset in SSRS, Does it returns more data than the first row?
– Geovanny Hernandez
Jan 3 at 13:55
You said When I run this stored procedure, I see the output I expect to see. So what's the question? I notice that it returns and integer. Do you mean when you call it from an application? If so, this means success.
– scsimon
Jan 3 at 13:55
Geovanny - Yes, I can see my results as I expect to see them. When I put the dataset into SSRS and I want to use any of the aggregates, I can only see the FIRST aggregate when I run my report. If I use any of the others, like MIN, MAX, AVG, etc, and run my report, I don't see any of the other aggregates show in my report. It's just a blank spot in the report. Long story short, I was wondering if my stored procedure had anything to do with any of the other aggregates not being able to run.
– EricALionsFan
Jan 3 at 14:11
add a comment |
When have you add the code in the dataset in SSRS, Does it returns more data than the first row?
– Geovanny Hernandez
Jan 3 at 13:55
You said When I run this stored procedure, I see the output I expect to see. So what's the question? I notice that it returns and integer. Do you mean when you call it from an application? If so, this means success.
– scsimon
Jan 3 at 13:55
Geovanny - Yes, I can see my results as I expect to see them. When I put the dataset into SSRS and I want to use any of the aggregates, I can only see the FIRST aggregate when I run my report. If I use any of the others, like MIN, MAX, AVG, etc, and run my report, I don't see any of the other aggregates show in my report. It's just a blank spot in the report. Long story short, I was wondering if my stored procedure had anything to do with any of the other aggregates not being able to run.
– EricALionsFan
Jan 3 at 14:11
When have you add the code in the dataset in SSRS, Does it returns more data than the first row?
– Geovanny Hernandez
Jan 3 at 13:55
When have you add the code in the dataset in SSRS, Does it returns more data than the first row?
– Geovanny Hernandez
Jan 3 at 13:55
You said When I run this stored procedure, I see the output I expect to see. So what's the question? I notice that it returns and integer. Do you mean when you call it from an application? If so, this means success.
– scsimon
Jan 3 at 13:55
You said When I run this stored procedure, I see the output I expect to see. So what's the question? I notice that it returns and integer. Do you mean when you call it from an application? If so, this means success.
– scsimon
Jan 3 at 13:55
Geovanny - Yes, I can see my results as I expect to see them. When I put the dataset into SSRS and I want to use any of the aggregates, I can only see the FIRST aggregate when I run my report. If I use any of the others, like MIN, MAX, AVG, etc, and run my report, I don't see any of the other aggregates show in my report. It's just a blank spot in the report. Long story short, I was wondering if my stored procedure had anything to do with any of the other aggregates not being able to run.
– EricALionsFan
Jan 3 at 14:11
Geovanny - Yes, I can see my results as I expect to see them. When I put the dataset into SSRS and I want to use any of the aggregates, I can only see the FIRST aggregate when I run my report. If I use any of the others, like MIN, MAX, AVG, etc, and run my report, I don't see any of the other aggregates show in my report. It's just a blank spot in the report. Long story short, I was wondering if my stored procedure had anything to do with any of the other aggregates not being able to run.
– EricALionsFan
Jan 3 at 14:11
add a comment |
3 Answers
3
active
oldest
votes
When I run this stored procedure, I see the output I expect to see.
This means the stored procedure is working fine. If you aren't seeing the results in the SSRS, then the problem is in the SSRS.
answered Jan 3 at 14:44
Tab AllemanTab Alleman
27.6k62443
add a comment |
Too long for a comment, so here it goes.
First, you need some serious code reviewing. This is not the place for that. A good set of reviewers will offer useful comments about improving your code, improving your skills, making your code more efficient, etc.
Simple things like STOP USING RTRIM with float datatypes (e.g., rtrim(sd.[Off Center Tolerance - Target] ). Why do you do that? That column is defined as float. You trim PlantCode everywhere. Why? You defined it as char(30) everywhere in your code - what is the actual datatype in your schema? If it has variable length, then use varchar (especially in your parameter).
Why do you pass datetime values as strings for parameters? And you make another typical mistake - 23:59:59 is not the largest possible time component for datetime or datetime2. Don't use an inclusive upper boundary - use an exclusive upper boundary to avoid this sort of thing. BTW it might be that your forced implicit conversion of your float columns to string is the source of your problem.
Next - add "set nocount on" as the first statement to every stored procedure and trigger you write. This avoids sending the "x rows affected" message to the application executing this logic.
Try adding some useful comments for whoever must support this code. And make certain they are both accurate and useful. You have "create a cte to hold ..." in multiple places. First, no one generally cares how you generate the required information. Next, you are using CTEs to populate a temp table that you create. "Create" typically applies to objects that get created and it is unusual to use that term with a CTE. In fact, you use many CTEs rather than the one (singular) implied in your comment.
There are other things that a good code review should find - some best practices included.
answered Jan 3 at 14:58
SMorSMor
1,522259
add a comment |
Try cast or convert for your operations. It might work
answered Jan 4 at 2:15
PiyushPiyush
12
This is not the sort of thing that goes in an answer. In future please put it as a comment or have a more comprehensive answer
– RAB
Jan 4 at 2:22
1
Well i was not aware of protocol but will do
– Piyush
Jan 4 at 2:26
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54023357%2fi-need-a-second-look-at-my-stored-procedure%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
When I run this stored procedure, I see the output I expect to see.
This means the stored procedure is working fine. If you aren't seeing the results in the SSRS, then the problem is in the SSRS.
answered Jan 3 at 14:44
Tab AllemanTab Alleman
27.6k62443
add a comment |
When I run this stored procedure, I see the output I expect to see.
This means the stored procedure is working fine. If you aren't seeing the results in the SSRS, then the problem is in the SSRS.
answered Jan 3 at 14:44
Tab AllemanTab Alleman
27.6k62443
add a comment |
When I run this stored procedure, I see the output I expect to see.
This means the stored procedure is working fine. If you aren't seeing the results in the SSRS, then the problem is in the SSRS.
answered Jan 3 at 14:44
Tab AllemanTab Alleman
27.6k62443
When I run this stored procedure, I see the output I expect to see.
This means the stored procedure is working fine. If you aren't seeing the results in the SSRS, then the problem is in the SSRS.
answered Jan 3 at 14:44
Tab AllemanTab Alleman
27.6k62443
answered Jan 3 at 14:44
Tab AllemanTab Alleman
27.6k62443
answered Jan 3 at 14:44
Tab AllemanTab Alleman
27.6k62443
answered Jan 3 at 14:44
Tab AllemanTab Alleman
27.6k62443
27.6k62443
add a comment |
add a comment |
Too long for a comment, so here it goes.
First, you need some serious code reviewing. This is not the place for that. A good set of reviewers will offer useful comments about improving your code, improving your skills, making your code more efficient, etc.
Simple things like STOP USING RTRIM with float datatypes (e.g., rtrim(sd.[Off Center Tolerance - Target] ). Why do you do that? That column is defined as float. You trim PlantCode everywhere. Why? You defined it as char(30) everywhere in your code - what is the actual datatype in your schema? If it has variable length, then use varchar (especially in your parameter).
Why do you pass datetime values as strings for parameters? And you make another typical mistake - 23:59:59 is not the largest possible time component for datetime or datetime2. Don't use an inclusive upper boundary - use an exclusive upper boundary to avoid this sort of thing. BTW it might be that your forced implicit conversion of your float columns to string is the source of your problem.
Next - add "set nocount on" as the first statement to every stored procedure and trigger you write. This avoids sending the "x rows affected" message to the application executing this logic.
Try adding some useful comments for whoever must support this code. And make certain they are both accurate and useful. You have "create a cte to hold ..." in multiple places. First, no one generally cares how you generate the required information. Next, you are using CTEs to populate a temp table that you create. "Create" typically applies to objects that get created and it is unusual to use that term with a CTE. In fact, you use many CTEs rather than the one (singular) implied in your comment.
There are other things that a good code review should find - some best practices included.
answered Jan 3 at 14:58
SMorSMor
1,522259
add a comment |
Too long for a comment, so here it goes.
First, you need some serious code reviewing. This is not the place for that. A good set of reviewers will offer useful comments about improving your code, improving your skills, making your code more efficient, etc.
Simple things like STOP USING RTRIM with float datatypes (e.g., rtrim(sd.[Off Center Tolerance - Target] ). Why do you do that? That column is defined as float. You trim PlantCode everywhere. Why? You defined it as char(30) everywhere in your code - what is the actual datatype in your schema? If it has variable length, then use varchar (especially in your parameter).
Why do you pass datetime values as strings for parameters? And you make another typical mistake - 23:59:59 is not the largest possible time component for datetime or datetime2. Don't use an inclusive upper boundary - use an exclusive upper boundary to avoid this sort of thing. BTW it might be that your forced implicit conversion of your float columns to string is the source of your problem.
Next - add "set nocount on" as the first statement to every stored procedure and trigger you write. This avoids sending the "x rows affected" message to the application executing this logic.
Try adding some useful comments for whoever must support this code. And make certain they are both accurate and useful. You have "create a cte to hold ..." in multiple places. First, no one generally cares how you generate the required information. Next, you are using CTEs to populate a temp table that you create. "Create" typically applies to objects that get created and it is unusual to use that term with a CTE. In fact, you use many CTEs rather than the one (singular) implied in your comment.
There are other things that a good code review should find - some best practices included.
answered Jan 3 at 14:58
SMorSMor
1,522259
add a comment |
Too long for a comment, so here it goes.
First, you need some serious code reviewing. This is not the place for that. A good set of reviewers will offer useful comments about improving your code, improving your skills, making your code more efficient, etc.
Simple things like STOP USING RTRIM with float datatypes (e.g., rtrim(sd.[Off Center Tolerance - Target] ). Why do you do that? That column is defined as float. You trim PlantCode everywhere. Why? You defined it as char(30) everywhere in your code - what is the actual datatype in your schema? If it has variable length, then use varchar (especially in your parameter).
Why do you pass datetime values as strings for parameters? And you make another typical mistake - 23:59:59 is not the largest possible time component for datetime or datetime2. Don't use an inclusive upper boundary - use an exclusive upper boundary to avoid this sort of thing. BTW it might be that your forced implicit conversion of your float columns to string is the source of your problem.
Next - add "set nocount on" as the first statement to every stored procedure and trigger you write. This avoids sending the "x rows affected" message to the application executing this logic.
Try adding some useful comments for whoever must support this code. And make certain they are both accurate and useful. You have "create a cte to hold ..." in multiple places. First, no one generally cares how you generate the required information. Next, you are using CTEs to populate a temp table that you create. "Create" typically applies to objects that get created and it is unusual to use that term with a CTE. In fact, you use many CTEs rather than the one (singular) implied in your comment.
There are other things that a good code review should find - some best practices included.
answered Jan 3 at 14:58
SMorSMor
1,522259
Too long for a comment, so here it goes.
First, you need some serious code reviewing. This is not the place for that. A good set of reviewers will offer useful comments about improving your code, improving your skills, making your code more efficient, etc.
Simple things like STOP USING RTRIM with float datatypes (e.g., rtrim(sd.[Off Center Tolerance - Target] ). Why do you do that? That column is defined as float. You trim PlantCode everywhere. Why? You defined it as char(30) everywhere in your code - what is the actual datatype in your schema? If it has variable length, then use varchar (especially in your parameter).
Why do you pass datetime values as strings for parameters? And you make another typical mistake - 23:59:59 is not the largest possible time component for datetime or datetime2. Don't use an inclusive upper boundary - use an exclusive upper boundary to avoid this sort of thing. BTW it might be that your forced implicit conversion of your float columns to string is the source of your problem.
Next - add "set nocount on" as the first statement to every stored procedure and trigger you write. This avoids sending the "x rows affected" message to the application executing this logic.
Try adding some useful comments for whoever must support this code. And make certain they are both accurate and useful. You have "create a cte to hold ..." in multiple places. First, no one generally cares how you generate the required information. Next, you are using CTEs to populate a temp table that you create. "Create" typically applies to objects that get created and it is unusual to use that term with a CTE. In fact, you use many CTEs rather than the one (singular) implied in your comment.
There are other things that a good code review should find - some best practices included.
answered Jan 3 at 14:58
SMorSMor
1,522259
answered Jan 3 at 14:58
SMorSMor
1,522259
answered Jan 3 at 14:58
SMorSMor
1,522259
answered Jan 3 at 14:58
SMorSMor
1,522259
1,522259
add a comment |
add a comment |
Try cast or convert for your operations. It might work
answered Jan 4 at 2:15
PiyushPiyush
12
This is not the sort of thing that goes in an answer. In future please put it as a comment or have a more comprehensive answer
– RAB
Jan 4 at 2:22
1
Well i was not aware of protocol but will do
– Piyush
Jan 4 at 2:26
add a comment |
Try cast or convert for your operations. It might work
answered Jan 4 at 2:15
PiyushPiyush
12
This is not the sort of thing that goes in an answer. In future please put it as a comment or have a more comprehensive answer
– RAB
Jan 4 at 2:22
1
Well i was not aware of protocol but will do
– Piyush
Jan 4 at 2:26
add a comment |
Try cast or convert for your operations. It might work
answered Jan 4 at 2:15
PiyushPiyush
12
Try cast or convert for your operations. It might work
answered Jan 4 at 2:15
PiyushPiyush
12
answered Jan 4 at 2:15
PiyushPiyush
12
answered Jan 4 at 2:15
PiyushPiyush
12
answered Jan 4 at 2:15
PiyushPiyush
12
12
This is not the sort of thing that goes in an answer. In future please put it as a comment or have a more comprehensive answer
– RAB
Jan 4 at 2:22
1
Well i was not aware of protocol but will do
– Piyush
Jan 4 at 2:26
add a comment |
This is not the sort of thing that goes in an answer. In future please put it as a comment or have a more comprehensive answer
– RAB
Jan 4 at 2:22
1
Well i was not aware of protocol but will do
– Piyush
Jan 4 at 2:26
This is not the sort of thing that goes in an answer. In future please put it as a comment or have a more comprehensive answer
– RAB
Jan 4 at 2:22
This is not the sort of thing that goes in an answer. In future please put it as a comment or have a more comprehensive answer
– RAB
Jan 4 at 2:22
1
1
Well i was not aware of protocol but will do
– Piyush
Jan 4 at 2:26
Well i was not aware of protocol but will do
– Piyush
Jan 4 at 2:26
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54023357%2fi-need-a-second-look-at-my-stored-procedure%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
When have you add the code in the dataset in SSRS, Does it returns more data than the first row?
– Geovanny Hernandez
Jan 3 at 13:55
You said When I run this stored procedure, I see the output I expect to see. So what's the question? I notice that it returns and integer. Do you mean when you call it from an application? If so, this means success.
– scsimon
Jan 3 at 13:55
Geovanny - Yes, I can see my results as I expect to see them. When I put the dataset into SSRS and I want to use any of the aggregates, I can only see the FIRST aggregate when I run my report. If I use any of the others, like MIN, MAX, AVG, etc, and run my report, I don't see any of the other aggregates show in my report. It's just a blank spot in the report. Long story short, I was wondering if my stored procedure had anything to do with any of the other aggregates not being able to run.
– EricALionsFan
Jan 3 at 14:11