Sql case statements..
-
Hi Gurus..
I am trying to write a sql case statement that will filter as follows:
My lack of syntax knowledge on sql case is getting me no where.. any ideas..
CREATE PROCEDURE prcTestCase
(@BeginDate datetime,
@EndDate datetime,
@DateFilter int
)
SELECT a.filentumber,
a.clientrefnumber,
c.CompletionDate AS FirstCompletionDate,
d.CompletionDate AS SecondCompletionDate
FROM Files a
INNER JOIN FileSteps c
on c.filenumber= a.filenumber
INNER JOIN FileSteps d on d.filenumber = c.filenumber
( Case @DateFilter
when 0
then c.CompletionDate between @BeginDate AND @EndDate
And d.CompletionDate between @BeginDate AND @EndDate
when 1
then c.CompletionDate between @BeginDate AND @EndDate
when 2
then d.CompletionDate between @BeginDate AND @EndDate
end)
-
The case statement is used in the select clause not the join clause. i think what you are trying to do is accomplished by the following code.
CREATE PROCEDURE prcTestCase
(@BeginDate datetime,
@EndDate datetime,
@DateFilter int
)
SELECT a.filentumber,
a.clientrefnumber,
c.CompletionDate AS FirstCompletionDate,
d.CompletionDate AS SecondCompletionDate
FROM Files a
INNER JOIN FileSteps c
on c.filenumber= a.filenumber
INNER JOIN FileSteps d on d.filenumber = c.filenumber
where
(@DateFilter = 0 and c.CompletionDate between @BeginDate AND @EndDate And d.CompletionDate between @BeginDate AND @EndDate)
or (@DateFilter = 1 and c.CompletionDate between @BeginDate AND @EndDate)
or (@DateFilter = 2 and d.CompletionDate between @BeginDate AND @EndDate)
Chris
-
"The case statement is used in the select clause not the join clause"
I have to disagree with this statement. There is nothing syntax wise preventing you from using CASE in JOIN or WHERE clause.
You may have to find a good DB design or business logic reason to use it but it works and gives you quite few nice SQL tricks to have fun with.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
I'd argue that the design is flawed
The intention is to query data on 1 or both dates - so give the sproc the parameters it needs, or leave them null if that date is irrelvant to the search.
CREATE PROCEDURE prcTestCase
(@FirstBeginDate datetime = null,
@FirstEndDate datetime = null,
@SecondBeginDate datetime = null,
@SecondEndDate datetime = null
)
SELECT a.filentumber,
a.clientrefnumber,
c.CompletionDate AS FirstCompletionDate,
d.CompletionDate AS SecondCompletionDate
FROM Files a
INNER JOIN FileSteps c
ON c.filenumber= a.filenumber
INNER JOIN FileSteps d
ON d.filenumber = c.filenumber
WHERE (c.CompletionDate >= @FirstBeginDate OR @FirstBeginDate Is Null )
AND (c.CompletionDate <= @FirstEndDate OR @FirstEndDate Is Null )
AND (d.CompletionDate >= @SecondBeginDate OR @SecondBeginDate Is Null )
AND (d.CompletionDate <= @SecondEndDate OR @SecondEndDate Is Null )
-
Thanks Chris
for a solution.. For all other posts agreed sql case in where clause is neat trick that is where I was hoping for correction on syntax.. So instead of making recommendations it's best to show results..
-
Well, Chris gave you an example that worked so there was no need for fixing the syntax of your code since in this case the usage of CASE was not justified and probably not even doable.
I just stated you can use CASE in JOINs and WHERE clauses when applicable, CASE is not limited to SELECT.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply