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      
mattkovoAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
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 (  (@SelectZeroPriceItems = '0' AND Extension > 0)  or @SelectZeroPriceItems = '1' )
     
  Group by Guest_Check_Name, Report_GroupID
  order by Sales Desc      
     
     
End
0
 
mattkovoAuthor Commented:
Thanks!
0
 
8080_DiverCommented:
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.

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;

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>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
0
 
8080_DiverCommented:
Anesesh,
That would be one of those, "Been there; done that; got the scars to prove it." situations. ;-)
0
All Courses

From novice to tech pro — start learning today.