Solved

Nested if/then statement in Where clause

Posted on 2009-05-08
5
471 Views
Last Modified: 2012-05-06
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      
0
Comment
Question by:mattkovo
  • 2
  • 2
5 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24337131
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
 

Author Closing Comment

by:mattkovo
ID: 31579497
Thanks!
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24337592
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24337743
>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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24340674
Anesesh,
That would be one of those, "Been there; done that; got the scars to prove it." situations. ;-)
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question