January 31, 2012 at 6:54 pm
Can someone help me to get this query to work?
DECLARE @option bit
,@begindate datetime
,@enddate datetime
/@paymentdate datetime
Set @option = 1
,@begindate = ‘2/1/2012’
,@enddate = ‘2/20/2012’
,@paymentdate = '2/8/2012'
Select * from inventory i
where i.TransactionType = '810'
and (CASE @option
WHEN 1 THEN i.PaymentDueDate between @begindate and @enddate
ELSE i.CreateDate between @begindate and @enddate
END)
January 31, 2012 at 8:07 pm
The CASE statement is intended to return a single value. For you condition, you are trying to checking whether either paymentduedate or createdate is between begindate and endate (depending on the value of @option).
So, write a CASE statement that returns a single date (either paymentduedate or createdate) and then do the between comparison based on the result of the case statement. The following should do the job for you....
DECLARE @option bit
,@begindate datetime
,@enddate datetime
/@paymentdate datetime
Set @option = 1
,@begindate = ‘2/1/2012’
,@enddate = ‘2/20/2012’
,@paymentdate = '2/8/2012'
Select * from inventory i
where i.TransactionType = '810'
and (CASE @option
WHEN 1 THEN i.PaymentDueDate ELSE i.CreateDate END between @begindate and @enddate
)
January 31, 2012 at 9:05 pm
It works, Thank you so much.
January 31, 2012 at 11:51 pm
You may find this form makes better use of any indexes you might have:
IF @Option = 1
BEGIN
SELECT *
FROM dbo.Inventory AS inv
WHERE
inv.TransactionType = '810'
AND PaymentDueDate >= @BeginDate
AND PaymentDueDate <= @EndDate
ELSE
BEGIN
SELECT *
FROM dbo.Inventory AS inv
WHERE
inv.TransactionType = '810'
AND CreateDate >= @BeginDate
AND CreateDate <= @EndDate
END;
February 1, 2012 at 5:06 am
I agree with SQL Kiwi that his solution has the potential to make much better use of indexes etc and hence may well perform better.
My solution was intended to answer the question about how to use the CASE statement.
As usual, whenever you take advice from forums such as this, please take the time to read and understand all of the answers. The first one (mine in this case) may not be the ideal solution to the problem and other people definitely add value to the discussion
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply