Solved

Nested if/then statement in Where clause

Posted on 2009-05-08
5
469 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
Comment Utility
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
Comment Utility
Thanks!
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
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
Comment Utility
>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
Comment Utility
Anesesh,
That would be one of those, "Been there; done that; got the scars to prove it." situations. ;-)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now