CASE WHEN @ShowPrice = 'M'
THEN CONVERT(DECIMAL(10, 2), ( cs.std_cost ) * ( 1
+ ( ISNULL(@Margin,
0) / 100.0 ) ))
ELSE CONVERT(DECIMAL(10, 2), cs.a_price)
END AS std_price
WHERE ( cs.DIVISION = 'KLL' )
AND @MaximumPrice = 0
OR @MaximumPrice <= cs.a_price
OR @MaximumPrice <= cs.std_cost
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT DISTINCT
LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) AS Scale_Style ,
ISNULL(zzxexfvr.stor_file, ' ') AS Img_File_Name ,
cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.EndLocation ,
SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) AS OTS ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
CONVERT(DECIMAL(10, 2), cs.std_cost) AS std_cost ,
cs.Size_Ind ,
CASE WHEN @ShowPrice = 'M'
THEN CONVERT(DECIMAL(10, 2), ( cs.std_cost ) * ( 1
+ ( ISNULL(@Margin,
0) / 100.0 ) ))
ELSE CONVERT(DECIMAL(10, 2), cs.a_price)
END AS std_price ,
cs.Main_Label ,
SUM(cs.AvailQOH) AS AvailQOH ,
SUM(cs.QOH) AS QOH ,
CASE WHEN ( EXISTS ( SELECT *
FROM zzxrangd AS Rg
WHERE ( Rg.style = cs.STYLE )
AND ( Rg.color_code = cs.COLOR_CODE )
AND ( Rg.lbl_code = cs.LBL_CODE ) ) )
THEN ( 'Y' )
ELSE ( 'N' )
END AS RangeComponent ,
cs.season ,
cs.Classification ,
dbo.KLLfn_Get_Max_ETAWhs_Date_Style_Color_Lbl_Scale(cs.STYLE,
cs.COLOR_CODE,
cs.LBL_CODE,
cs.group_code4) AS ETADate ,
cs.Roll_Lbl ,
cs.NeckLabel
FROM ( SELECT a_price ,
SUM(AvailQOH) AS AvailQOH ,
Classification ,
COLOR_CODE ,
DIVISION ,
group_code4 ,
LBL_CODE ,
Main_Label ,
NeckLabel ,
SUM(OTSOpen) AS OTSOpen ,
SUM(OTSPlan) AS OTSPlan ,
SUM(QOH) AS QOH ,
Scale_Name ,
season ,
SeaSyrScale ,
Size_Ind ,
std_cost ,
STYLE ,
EndLocation ,
MAX(Roll_Lbl) AS Roll_Lbl
FROM [KLL Cut and Sold OTS Sum - New Rollup]
WHERE ( EndLocation IN ( @Location ) )
GROUP BY a_price ,
Classification ,
COLOR_CODE ,
DIVISION ,
group_code4 ,
LBL_CODE ,
Main_Label ,
NeckLabel ,
Scale_Name ,
season ,
SeaSyrScale ,
Size_Ind ,
std_cost ,
STYLE ,
EndLocation
) AS cs
INNER JOIN zzxstylr ON cs.DIVISION = zzxstylr.division
AND cs.STYLE = zzxstylr.style
LEFT OUTER JOIN zzxexfvr ON zzxstylr.pkey = zzxexfvr.FKEY
AND zzxexfvr.CONTEXTID = 'ZZXSTYLR'
AND zzxexfvr.prime_image = 'Y'
WHERE ( cs.DIVISION = 'KLL' )
AND ( @MaxQty = 0 )
OR ( cs.DIVISION = 'KLL' )
AND ( @MinQty = 0 )
GROUP BY LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) ,
ISNULL(zzxexfvr.stor_file, ' ') ,
cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.EndLocation ,
cs.std_cost ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
CONVERT(DECIMAL(10, 2), cs.std_cost) ,
cs.Size_Ind ,
CONVERT(DECIMAL(10, 2), cs.a_price) ,
cs.Main_Label ,
cs.season ,
cs.Classification ,
dbo.KLLfn_Get_Max_ETAWhs_Date_Style_Color_Lbl_Scale(cs.STYLE,
cs.COLOR_CODE,
cs.LBL_CODE,
cs.group_code4) ,
cs.Roll_Lbl ,
cs.NeckLabel
HAVING ( cs.season IN ( @Season ) )
AND ( cs.group_code4 IN ( @Size_Scale ) )
AND ( cs.LBL_CODE IN ( @Label ) )
AND ( cs.Main_Label IN ( @Main_Label ) )
AND ( SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) > @MinQty )
AND ( cs.Classification IN ( @Class ) )
AND ( cs.NeckLabel IN ( @NeckLabels ) )
OR ( cs.season IN ( @Season ) )
AND ( cs.group_code4 IN ( @Size_Scale ) )
AND ( cs.LBL_CODE IN ( @Label ) )
AND ( cs.Main_Label IN ( @Main_Label ) )
AND ( SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) BETWEEN 1
AND @MaxQty )
AND ( cs.Classification IN ( @Class ) )
AND ( cs.NeckLabel IN ( @NeckLabels ) )
ORDER BY cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
cs.Size_Ind ,
cs.EndLocation;
WHERE cs.DIVISION = 'KLL'
AND (@MaxQty = 0 OR @MinQty = 0)
AND (
@MaximumPrice = 0
OR (@ShowPrice = 'M' AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), (cs.std_cost) * ( 1+ (ISNULL(@Margin,0) / 100.0 ))))
OR (@ShowPrice <> 'M' AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), cs.a_price))
)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT DISTINCT
LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) AS Scale_Style ,
ISNULL(zzxexfvr.stor_file, ' ') AS Img_File_Name ,
cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.EndLocation ,
SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) AS OTS ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
CONVERT(DECIMAL(10, 2), cs.std_cost) AS std_cost ,
cs.Size_Ind ,
CASE WHEN @ShowPrice = 'M'
THEN CONVERT(DECIMAL(10, 2), ( cs.std_cost ) * ( 1
+ ( ISNULL(@Margin,
0) / 100.0 ) ))
ELSE CONVERT(DECIMAL(10, 2), cs.a_price)
END AS std_price ,
cs.Main_Label ,
SUM(cs.AvailQOH) AS AvailQOH ,
SUM(cs.QOH) AS QOH ,
CASE WHEN ( EXISTS ( SELECT *
FROM zzxrangd AS Rg
WHERE ( Rg.style = cs.STYLE )
AND ( Rg.color_code = cs.COLOR_CODE )
AND ( Rg.lbl_code = cs.LBL_CODE ) ) )
THEN ( 'Y' )
ELSE ( 'N' )
END AS RangeComponent ,
cs.season ,
cs.Classification ,
dbo.KLLfn_Get_Max_ETAWhs_Date_Style_Color_Lbl_Scale(cs.STYLE,
cs.COLOR_CODE,
cs.LBL_CODE,
cs.group_code4) AS ETADate ,
cs.Roll_Lbl ,
cs.NeckLabel
FROM ( SELECT a_price ,
SUM(AvailQOH) AS AvailQOH ,
Classification ,
COLOR_CODE ,
DIVISION ,
group_code4 ,
LBL_CODE ,
Main_Label ,
NeckLabel ,
SUM(OTSOpen) AS OTSOpen ,
SUM(OTSPlan) AS OTSPlan ,
SUM(QOH) AS QOH ,
Scale_Name ,
season ,
SeaSyrScale ,
Size_Ind ,
std_cost ,
STYLE ,
EndLocation ,
MAX(Roll_Lbl) AS Roll_Lbl
FROM [KLL Cut and Sold OTS Sum - New Rollup]
WHERE ( EndLocation IN ( @Location ) )
GROUP BY a_price ,
Classification ,
COLOR_CODE ,
DIVISION ,
group_code4 ,
LBL_CODE ,
Main_Label ,
NeckLabel ,
Scale_Name ,
season ,
SeaSyrScale ,
Size_Ind ,
std_cost ,
STYLE ,
EndLocation
) AS cs
INNER JOIN zzxstylr ON cs.DIVISION = zzxstylr.division
AND cs.STYLE = zzxstylr.style
LEFT OUTER JOIN zzxexfvr ON zzxstylr.pkey = zzxexfvr.FKEY
AND zzxexfvr.CONTEXTID = 'ZZXSTYLR'
AND zzxexfvr.prime_image = 'Y'
WHERE ( cs.DIVISION = 'KLL' )
AND ( @MaxQty = 0 )
AND ( @MaximumPrice = 0
OR ( @ShowPrice = 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), ( cs.std_cost )
* ( 1 + ( ISNULL(@Margin, 0) / 100.0 ) ))
)
OR ( @ShowPrice <> 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), cs.a_price)
)
)
OR ( cs.DIVISION = 'KLL' )
AND ( @MinQty = 0 )
AND ( @MaximumPrice = 0
OR ( @ShowPrice = 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), ( cs.std_cost )
* ( 1 + ( ISNULL(@Margin, 0) / 100.0 ) ))
)
OR ( @ShowPrice <> 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), cs.a_price)
)
)
GROUP BY LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) ,
ISNULL(zzxexfvr.stor_file, ' ') ,
cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.EndLocation ,
cs.std_cost ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
CONVERT(DECIMAL(10, 2), cs.std_cost) ,
cs.Size_Ind ,
CONVERT(DECIMAL(10, 2), cs.a_price) ,
cs.Main_Label ,
cs.season ,
cs.Classification ,
dbo.KLLfn_Get_Max_ETAWhs_Date_Style_Color_Lbl_Scale(cs.STYLE,
cs.COLOR_CODE,
cs.LBL_CODE,
cs.group_code4) ,
cs.Roll_Lbl ,
cs.NeckLabel
HAVING ( cs.season IN ( @Season ) )
AND ( cs.group_code4 IN ( @Size_Scale ) )
AND ( cs.LBL_CODE IN ( @Label ) )
AND ( cs.Main_Label IN ( @Main_Label ) )
AND ( SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) > @MinQty )
AND ( cs.Classification IN ( @Class ) )
AND ( cs.NeckLabel IN ( @NeckLabels ) )
OR ( cs.season IN ( @Season ) )
AND ( cs.group_code4 IN ( @Size_Scale ) )
AND ( cs.LBL_CODE IN ( @Label ) )
AND ( cs.Main_Label IN ( @Main_Label ) )
AND ( SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) BETWEEN 1
AND @MaxQty )
AND ( cs.Classification IN ( @Class ) )
AND ( cs.NeckLabel IN ( @NeckLabels ) )
ORDER BY cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
cs.Size_Ind ,
cs.EndLocation;
I've applied code to my where clauseNo, you didn't. Compare your WHERE clause with mine. Can you see the BIG difference? That's why is not returning what you're expected.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT DISTINCT
LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) AS Scale_Style ,
ISNULL(zzxexfvr.stor_file, ' ') AS Img_File_Name ,
cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.EndLocation ,
SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) AS OTS ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
CONVERT(DECIMAL(10, 2), cs.std_cost) AS std_cost ,
cs.Size_Ind ,
CASE WHEN @ShowPrice = 'M'
THEN CONVERT(DECIMAL(10, 2), ( cs.std_cost ) * ( 1
+ ( ISNULL(@Margin,
0) / 100.0 ) ))
ELSE CONVERT(DECIMAL(10, 2), cs.a_price)
END AS std_price ,
cs.Main_Label ,
SUM(cs.AvailQOH) AS AvailQOH ,
SUM(cs.QOH) AS QOH ,
CASE WHEN ( EXISTS ( SELECT *
FROM zzxrangd AS Rg
WHERE ( Rg.style = cs.STYLE )
AND ( Rg.color_code = cs.COLOR_CODE )
AND ( Rg.lbl_code = cs.LBL_CODE ) ) )
THEN ( 'Y' )
ELSE ( 'N' )
END AS RangeComponent ,
cs.season ,
cs.Classification ,
dbo.KLLfn_Get_Max_ETAWhs_Date_Style_Color_Lbl_Scale(cs.STYLE,
cs.COLOR_CODE,
cs.LBL_CODE,
cs.group_code4) AS ETADate ,
cs.Roll_Lbl ,
cs.NeckLabel
FROM ( SELECT a_price ,
SUM(AvailQOH) AS AvailQOH ,
Classification ,
COLOR_CODE ,
DIVISION ,
group_code4 ,
LBL_CODE ,
Main_Label ,
NeckLabel ,
SUM(OTSOpen) AS OTSOpen ,
SUM(OTSPlan) AS OTSPlan ,
SUM(QOH) AS QOH ,
Scale_Name ,
season ,
SeaSyrScale ,
Size_Ind ,
std_cost ,
STYLE ,
EndLocation ,
MAX(Roll_Lbl) AS Roll_Lbl
FROM [KLL Cut and Sold OTS Sum - New Rollup]
WHERE ( EndLocation IN ( @Location ) )
GROUP BY a_price ,
Classification ,
COLOR_CODE ,
DIVISION ,
group_code4 ,
LBL_CODE ,
Main_Label ,
NeckLabel ,
Scale_Name ,
season ,
SeaSyrScale ,
Size_Ind ,
std_cost ,
STYLE ,
EndLocation
) AS cs
INNER JOIN zzxstylr ON cs.DIVISION = zzxstylr.division
AND cs.STYLE = zzxstylr.style
LEFT OUTER JOIN zzxexfvr ON zzxstylr.pkey = zzxexfvr.FKEY
AND zzxexfvr.CONTEXTID = 'ZZXSTYLR'
AND zzxexfvr.prime_image = 'Y'
WHERE cs.DIVISION = 'KLL'
AND ( @MaxQty = 0
OR @MinQty = 0
)
AND ( @MaximumPrice = 0
OR ( @ShowPrice = 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), ( cs.std_cost )
* ( 1 + ( ISNULL(@Margin, 0) / 100.0 ) ))
)
OR ( @ShowPrice <> 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), cs.a_price)
)
)
GROUP BY LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) ,
ISNULL(zzxexfvr.stor_file, ' ') ,
cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.EndLocation ,
cs.std_cost ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
CONVERT(DECIMAL(10, 2), cs.std_cost) ,
cs.Size_Ind ,
CONVERT(DECIMAL(10, 2), cs.a_price) ,
cs.Main_Label ,
cs.season ,
cs.Classification ,
dbo.KLLfn_Get_Max_ETAWhs_Date_Style_Color_Lbl_Scale(cs.STYLE,
cs.COLOR_CODE,
cs.LBL_CODE,
cs.group_code4) ,
cs.Roll_Lbl ,
cs.NeckLabel
HAVING ( cs.season IN ( @Season ) )
AND ( cs.group_code4 IN ( @Size_Scale ) )
AND ( cs.LBL_CODE IN ( @Label ) )
AND ( cs.Main_Label IN ( @Main_Label ) )
AND ( SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) > @MinQty )
AND ( cs.Classification IN ( @Class ) )
AND ( cs.NeckLabel IN ( @NeckLabels ) )
OR ( cs.season IN ( @Season ) )
AND ( cs.group_code4 IN ( @Size_Scale ) )
AND ( cs.LBL_CODE IN ( @Label ) )
AND ( cs.Main_Label IN ( @Main_Label ) )
AND ( SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) BETWEEN 1
AND @MaxQty )
AND ( cs.Classification IN ( @Class ) )
AND ( cs.NeckLabel IN ( @NeckLabels ) )
ORDER BY cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
cs.Size_Ind ,
cs.EndLocation;
WHERE ( cs.DIVISION = 'KLL' )
AND ( @MaxQty = 0 )
AND ( @MaximumPrice = 0
OR ( @ShowPrice = 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), ( cs.std_cost )
* ( 1 + ( ISNULL(@Margin, 0) / 100.0 ) ))
)
OR ( @ShowPrice <> 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), cs.a_price)
)
)
OR ( cs.DIVISION = 'KLL' )
AND ( @MinQty = 0 )
AND ( @MaximumPrice = 0
OR ( @ShowPrice = 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), ( cs.std_cost )
* ( 1 + ( ISNULL(@Margin, 0) / 100.0 ) ))
)
OR ( @ShowPrice <> 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), cs.a_price)
)
)
Can you see the difference?
DECLARE @Season CHAR(5);
DECLARE @ShowPrice CHAR(1);
DECLARE @Margin DECIMAL(10, 2);
DECLARE @MaximumPrice DECIMAL(10, 2);
DECLARE @MaxQty INT;
DECLARE @MinQty INT;
SET @Season = '20171';
SET @ShowPrice = 'M';
SET @Margin = '15';
SET @MaximumPrice = '6.00';
SET @MaxQty = '0';
SET @MinQty = '0';
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT DISTINCT
LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) AS Scale_Style ,
ISNULL(zzxexfvr.stor_file, ' ') AS Img_File_Name ,
cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.EndLocation ,
--SUM(CONVERT(INT, CASE @OTSOVR
-- WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
-- ELSE ( cs.OTSOpen )
-- END)) AS OTS ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
CONVERT(DECIMAL(10, 2), cs.std_cost) AS std_cost ,
cs.Size_Ind ,
CASE WHEN @ShowPrice = 'M'
THEN CONVERT(DECIMAL(10, 2), ( cs.std_cost ) * ( 1
+ ( ISNULL(@Margin,
0) / 100.0 ) ))
ELSE CONVERT(DECIMAL(10, 2), cs.a_price)
END AS std_price ,
cs.Main_Label ,
SUM(cs.AvailQOH) AS AvailQOH ,
SUM(cs.QOH) AS QOH ,
CASE WHEN ( EXISTS ( SELECT *
FROM zzxrangd AS Rg
WHERE ( Rg.style = cs.STYLE )
AND ( Rg.color_code = cs.COLOR_CODE )
AND ( Rg.lbl_code = cs.LBL_CODE ) ) )
THEN ( 'Y' )
ELSE ( 'N' )
END AS RangeComponent ,
cs.season ,
cs.Classification ,
dbo.KLLfn_Get_Max_ETAWhs_Date_Style_Color_Lbl_Scale(cs.STYLE,
cs.COLOR_CODE,
cs.LBL_CODE,
cs.group_code4) AS ETADate ,
cs.Roll_Lbl ,
cs.NeckLabel
FROM ( SELECT a_price ,
SUM(AvailQOH) AS AvailQOH ,
Classification ,
COLOR_CODE ,
DIVISION ,
group_code4 ,
LBL_CODE ,
Main_Label ,
NeckLabel ,
SUM(OTSOpen) AS OTSOpen ,
SUM(OTSPlan) AS OTSPlan ,
SUM(QOH) AS QOH ,
Scale_Name ,
season ,
SeaSyrScale ,
Size_Ind ,
std_cost ,
STYLE ,
EndLocation ,
MAX(Roll_Lbl) AS Roll_Lbl
FROM [KLL Cut and Sold OTS Sum - New Rollup]
--WHERE ( EndLocation IN ( @Location ) )
GROUP BY a_price ,
Classification ,
COLOR_CODE ,
DIVISION ,
group_code4 ,
LBL_CODE ,
Main_Label ,
NeckLabel ,
Scale_Name ,
season ,
SeaSyrScale ,
Size_Ind ,
std_cost ,
STYLE ,
EndLocation
) AS cs
INNER JOIN zzxstylr ON cs.DIVISION = zzxstylr.division
AND cs.STYLE = zzxstylr.style
LEFT OUTER JOIN zzxexfvr ON zzxstylr.pkey = zzxexfvr.FKEY
AND zzxexfvr.CONTEXTID = 'ZZXSTYLR'
AND zzxexfvr.prime_image = 'Y'
WHERE cs.DIVISION = 'KLL'
AND ( @MaxQty = 0
OR @MinQty = 0
)
AND ( @MaximumPrice = 0
OR ( @ShowPrice = 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), ( cs.std_cost )
* ( 1 + ( ISNULL(@Margin, 0) / 100.0 ) ))
)
OR ( @ShowPrice <> 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), cs.a_price)
)
)
GROUP BY LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) ,
ISNULL(zzxexfvr.stor_file, ' ') ,
cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.EndLocation ,
cs.std_cost ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
CONVERT(DECIMAL(10, 2), cs.std_cost) ,
cs.Size_Ind ,
CONVERT(DECIMAL(10, 2), cs.a_price) ,
cs.Main_Label ,
cs.season ,
cs.Classification ,
dbo.KLLfn_Get_Max_ETAWhs_Date_Style_Color_Lbl_Scale(cs.STYLE,
cs.COLOR_CODE,
cs.LBL_CODE,
cs.group_code4) ,
cs.Roll_Lbl ,
cs.NeckLabel
HAVING ( cs.season IN ( @Season ) )
-- AND ( cs.group_code4 IN ( @Size_Scale ) )
-- AND ( cs.LBL_CODE IN ( @Label ) )
-- AND ( cs.Main_Label IN ( @Main_Label ) )
-- AND ( SUM(CONVERT(INT, CASE @OTSOVR
-- WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
-- ELSE ( cs.OTSOpen )
-- END)) > @MinQty )
-- AND ( cs.Classification IN ( @Class ) )
-- AND ( cs.NeckLabel IN ( @NeckLabels ) )
-- OR ( cs.season IN ( @Season ) )
-- AND ( cs.group_code4 IN ( @Size_Scale ) )
-- AND ( cs.LBL_CODE IN ( @Label ) )
-- AND ( cs.Main_Label IN ( @Main_Label ) )
-- AND ( SUM(CONVERT(INT, CASE @OTSOVR
-- WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
-- ELSE ( cs.OTSOpen )
-- END)) BETWEEN 1
-- AND @MaxQty )
-- AND ( cs.Classification IN ( @Class ) )
-- AND ( cs.NeckLabel IN ( @NeckLabels ) )
ORDER BY cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
cs.Size_Ind ,
cs.EndLocation;
WHERE cs.season = @Season
AND ...
I didn't have much time to see your raw data properly but I didn't find any Season = '20171'.SET @Margin = 15;
SET @MaximumPrice = 6.00;
SET @MaxQty = 0;
SET @MinQty = 0;
I'm also using SQL2012 so in theory could I do something like this? Obviously my situation is a bit more involved.Yes, this can be done.
Or IIF() function if you’re using SQL Server 2012:
SELECT ProductID, ProductName,ProductDesc
FROM product
WHERE ProductID =IIF(@productID IS NULL, ProductID, @productID )
)
AND ( cs.std_price < = @MaximumPrice
OR ISNULL(@MaximumPrice, '0') = '0'
)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT DISTINCT
LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) AS Scale_Style ,
ISNULL(zzxexfvr.stor_file, ' ') AS Img_File_Name ,
cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.EndLocation ,
SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) AS OTS ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
SUM(CONVERT(DECIMAL(10, 2), cs.std_cost)) AS std_cost ,
cs.Size_Ind ,
SUM(CONVERT(DECIMAL(10, 2), cs.std_price)) AS std_price ,
cs.Main_Label ,
SUM(cs.AvailQOH) AS AvailQOH ,
SUM(cs.QOH) AS QOH ,
CASE WHEN ( EXISTS ( SELECT *
FROM zzxrangd AS Rg
WHERE ( Rg.style = cs.STYLE )
AND ( Rg.color_code = cs.COLOR_CODE )
AND ( Rg.lbl_code = cs.LBL_CODE ) ) )
THEN ( 'Y' )
ELSE ( 'N' )
END AS RangeComponent ,
cs.season ,
cs.Classification ,
dbo.KLLfn_Get_Max_ETAWhs_Date_Style_Color_Lbl_Scale(cs.STYLE,
cs.COLOR_CODE,
cs.LBL_CODE,
cs.group_code4) AS ETADate ,
cs.Roll_Lbl ,
cs.NeckLabel
FROM ( SELECT a_price ,
SUM(AvailQOH) AS AvailQOH ,
Classification ,
COLOR_CODE ,
DIVISION ,
group_code4 ,
LBL_CODE ,
Main_Label ,
NeckLabel ,
SUM(OTSOpen) AS OTSOpen ,
SUM(OTSPlan) AS OTSPlan ,
SUM(QOH) AS QOH ,
Scale_Name ,
season ,
SeaSyrScale ,
Size_Ind ,
std_cost ,
CASE WHEN @ShowPrice = 'M'
THEN ( std_cost ) * ( 1 + ( ISNULL(@Margin, 0)
/ 100.0 ) )
ELSE a_price
END AS std_price ,
STYLE ,
EndLocation ,
MAX(Roll_Lbl) AS Roll_Lbl
FROM [KLL Cut and Sold OTS Sum - New Rollup]
WHERE ( EndLocation IN ( @Location ) )
GROUP BY a_price ,
Classification ,
COLOR_CODE ,
DIVISION ,
group_code4 ,
LBL_CODE ,
Main_Label ,
NeckLabel ,
Scale_Name ,
season ,
SeaSyrScale ,
Size_Ind ,
std_cost ,
CASE WHEN @ShowPrice = 'M'
THEN ( std_cost ) * ( 1 + ( ISNULL(@Margin, 0)
/ 100.0 ) )
ELSE a_price
END ,
STYLE ,
EndLocation
) AS cs
INNER JOIN zzxstylr ON cs.DIVISION = zzxstylr.division
AND cs.STYLE = zzxstylr.style
LEFT OUTER JOIN zzxexfvr ON zzxstylr.pkey = zzxexfvr.FKEY
AND zzxexfvr.CONTEXTID = 'ZZXSTYLR'
AND zzxexfvr.prime_image = 'Y'
WHERE cs.DIVISION = 'KLL'
AND ( @MaxQty = 0
OR @MinQty = 0
)
AND ( cs.std_price < = @MaximumPrice
OR ISNULL(@MaximumPrice, '0') = '0'
)
GROUP BY LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) ,
ISNULL(zzxexfvr.stor_file, ' ') ,
cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.EndLocation ,
cs.std_cost ,
cs.std_price ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
CONVERT(DECIMAL(10, 2), cs.std_cost) ,
cs.Size_Ind ,
CONVERT(DECIMAL(10, 2), cs.a_price) ,
cs.Main_Label ,
cs.season ,
cs.Classification ,
dbo.KLLfn_Get_Max_ETAWhs_Date_Style_Color_Lbl_Scale(cs.STYLE,
cs.COLOR_CODE,
cs.LBL_CODE,
cs.group_code4) ,
cs.Roll_Lbl ,
cs.NeckLabel
HAVING ( cs.season IN ( @Season ) )
AND ( cs.group_code4 IN ( @Size_Scale ) )
AND ( cs.LBL_CODE IN ( @Label ) )
AND ( cs.Main_Label IN ( @Main_Label ) )
AND ( SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) > @MinQty )
AND ( cs.Classification IN ( @Class ) )
AND ( cs.NeckLabel IN ( @NeckLabels ) )
OR ( cs.season IN ( @Season ) )
AND ( cs.group_code4 IN ( @Size_Scale ) )
AND ( cs.LBL_CODE IN ( @Label ) )
AND ( cs.Main_Label IN ( @Main_Label ) )
AND ( SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) BETWEEN 1
AND @MaxQty )
AND ( cs.Classification IN ( @Class ) )
AND ( cs.NeckLabel IN ( @NeckLabels ) )
ORDER BY cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
cs.Size_Ind ,
cs.EndLocation;
where
(@maximumprice>0 and @maximumPrice<=
CASE WHEN @ShowPrice = 'M'
THEN CONVERT(DECIMAL(10, 2), ( cs.std_cost ) * ( 1
+ ( ISNULL(@Margin,
0) / 100.0 ) ))
ELSE CONVERT(DECIMAL(10, 2), cs.a_price)
or
@maximumprice=0
)