mattkovo
asked on
Nested if/then statement in Where clause
Instead of having 2 Select statements, how can I have one and use the if/then in the where section? Basically I want to do.... "if @SelectZeroPriceItems = '1' then Extenstion > 0. I am using this in a stored procedure.
As
if @SelectZeroPriceItems = '0'
BEGIN
SET NOCOUNT ON;
SELECT
Guest_Check_Name,
Report_GroupID,
Sum ((Extension) + (Discount_Amount)) as 'Sales',
Sum (Qty) as 'Qty'
FROM CHECK_ITEM_RECORD
where storeid = @SelectStore
and Flag_Voided is null
and check_file_date between @SelectStartDate and @SelectEndDate
and Extension > 0
Group by Guest_Check_Name, Report_GroupID
order by Sales Desc
End
if @SelectZeroPriceItems = '1'
BEGIN
SET NOCOUNT ON;
SELECT
Guest_Check_Name,
Report_GroupID,
Sum ((Extension) + (Discount_Amount)) as 'Sales',
Sum (Qty) as 'Qty'
FROM CHECK_ITEM_RECORD
where storeid = @SelectStore
and Flag_Voided is null
and check_file_date between @SelectStartDate and @SelectEndDate
Group by Guest_Check_Name, Report_GroupID
order by Sales Desc
End
As
if @SelectZeroPriceItems = '0'
BEGIN
SET NOCOUNT ON;
SELECT
Guest_Check_Name,
Report_GroupID,
Sum ((Extension) + (Discount_Amount)) as 'Sales',
Sum (Qty) as 'Qty'
FROM CHECK_ITEM_RECORD
where storeid = @SelectStore
and Flag_Voided is null
and check_file_date between @SelectStartDate and @SelectEndDate
and Extension > 0
Group by Guest_Check_Name, Report_GroupID
order by Sales Desc
End
if @SelectZeroPriceItems = '1'
BEGIN
SET NOCOUNT ON;
SELECT
Guest_Check_Name,
Report_GroupID,
Sum ((Extension) + (Discount_Amount)) as 'Sales',
Sum (Qty) as 'Qty'
FROM CHECK_ITEM_RECORD
where storeid = @SelectStore
and Flag_Voided is null
and check_file_date between @SelectStartDate and @SelectEndDate
Group by Guest_Check_Name, Report_GroupID
order by Sales Desc
End
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
aneeshattingal:,
I think that the initial IF statement needs to be dropped, otherwise the SQL will only execute if @SelectZeroPriceItems is a 0. ;-)
See below.
I think that the initial IF statement needs to be dropped, otherwise the SQL will only execute if @SelectZeroPriceItems is a 0. ;-)
See below.
SET NOCOUNT ON;
SELECT
Guest_Check_Name,
Report_GroupID,
Sum ((Extension) + (Discount_Amount)) as 'Sales',
Sum (Qty) as 'Qty'
FROM CHECK_ITEM_RECORD
where storeid = @SelectStore
and Flag_Voided is null
and check_file_date between @SelectStartDate and @SelectEndDate
and ( (@SelectZeroPriceItems = '0' AND Extension > 0) or @SelectZeroPriceItems = '1' )
Group by Guest_Check_Name, Report_GroupID
order by Sales Desc;
>I think that the initial IF statement needs to be dropped, otherwise the SQL will only execute if @SelectZeroPriceItems is a 0. ;-)
yup, i pasted the original query but forgot to remove that line at the end , nice catch :)
Aneesh
yup, i pasted the original query but forgot to remove that line at the end , nice catch :)
Aneesh
Anesesh,
That would be one of those, "Been there; done that; got the scars to prove it." situations. ;-)
That would be one of those, "Been there; done that; got the scars to prove it." situations. ;-)
ASKER