Case statement within a where clause - based on 2 variables

Question

Post reply

Case statement within a where clause - based on 2 variables

DerbyNeal

Hall of Fame

Points: 3854
More actions
March 2, 2015 at 8:08 am

#151403

Hi,
Using the following code (and adjusting the variables to test the different scenarios), I need to build on the existing WHERE clause, to incorporate a CASE statement.
USE [myDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[myTable](
[Customer] [nvarchar](20) NULL,
[Type] [tinyint] NULL,
[DueDate1] [datetime] NULL,
[DueDate2] [datetime] NULL
) ON [PRIMARY]
GO
INSERT dbo.myTable
VALUES('Customer 1', 1, 'Jan 01 2015 12:00:00:000AM', 'Feb 01 2015 12:00:00:000AM');
INSERT dbo.myTable
VALUES('Customer 2', 1, 'Feb 01 2015 12:00:00:000AM', 'Mar 09 2015 12:00:00:000AM');
INSERT dbo.myTable
VALUES('Customer 3', 1, 'Mar 01 2015 12:00:00:000AM', 'Mar 23 2015 12:00:00:000AM');
INSERT dbo.myTable
VALUES('Customer 4', 1, 'Jan 01 2015 12:00:00:000AM', 'Apr 12 2015 12:00:00:000AM');
INSERT dbo.myTable
VALUES('Customer 5', 2, 'Jan 01 2015 12:00:00:000AM', 'Apr 25 2015 12:00:00:000AM');
INSERT dbo.myTable
VALUES('Customer 6', 1, 'Jan 08 2015 12:00:00:000AM', 'Aug 17 2015 12:00:00:000AM');
INSERT dbo.myTable
VALUES('Customer 7', 2, 'Jan 03 2015 12:00:00:000AM', 'Apr 04 2015 12:00:00:000AM');
INSERT dbo.myTable
VALUES('Customer 8', 1, 'Jan 15 2015 12:00:00:000AM', 'Jun 05 2015 12:00:00:000AM');
DECLARE
@DueDate1DATETIME,
@DueDate2DATETIME,
@Condition1BIT,
@Condition2BIT,
@TypeINTEGER
--Adjust dates and conditions to test variations
SET @DueDate1 = 'Jan 01 2015 12:00:00:000AM';
SET @DueDate2 = 'Mar 31 2015 12:00:00:000AM';
SET @Condition1 = 1;
SET @Condition2 = 1;
SET @Type = 1;
SELECT Customer, [Type], DueDate1, DueDate2 FROM dbo.myTable mt
WHERE mt.[Type] = @Type
--AND to include CASE @Condition1 and @Condition2
The conditions are:
If @Condition1 = 1
mt.DueDate1 BETWEEN @DueDate1 AND @DueDate2
If @Condition2 = 1
mt.DueDate2 BETWEEN @DueDate1 AND @DueDate2
If @Condition1 = 1 AND @Condition2 = 1
mt.DueDate1 BETWEEN @DueDate1 AND @DueDate2
AND mt.DueDate2 BETWEEN @DueDate1 AND @DueDate2
Any ideas?
Thanks in advance,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 1

How about this:

SELECT Customer, [Type], DueDate1, DueDate2 FROM dbo.myTable mt

WHERE mt.[Type] = @Type

AND ((@Condition1=1 AND mt.DueDate1 BETWEEN @DueDate1 AND @DueDate2) OR (@Condition1<>1))

AND ((@Condition2=1 AND mt.DueDate2 BETWEEN @DueDate1 AND @DueDate2) OR (@Condition2<>1))

...or am I missing something?

Phil Parkin SSC Guru Points: 246979 More actions · Answer 2

Or this?

select Customer

,Type

,DueDate1

,DueDate2

from dbo.myTable mt

where mt.Type = @Type

and (case when (

@Condition1 = 1

and @Condition2 = 1

and mt.DueDate1 between @DueDate1 and @DueDate2

and mt.DueDate2 between @DueDate1 and @DueDate2

) then 1

when (

@Condition1 = 1

and mt.DueDate1 between @DueDate1 and @DueDate2

) then 1

when (

@Condition2 = 1

and mt.DueDate2 BETWEEN @DueDate1 AND @DueDate2

) then 1

else 0

end) = 1

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

I would simplify it.

SELECT Customer, [Type], DueDate1, DueDate2

FROM dbo.myTable mt

WHERE mt.[Type] = @Type

AND (mt.DueDate1 BETWEEN @DueDate1 AND @DueDate2 OR @Condition1 = 0)

AND (mt.DueDate2 BETWEEN @DueDate1 AND @DueDate2 OR @Condition2 = 0)

OPTION(RECOMPILE)

Note the RECOMPILE. This article explains the reason to use it, but you should test if it's the best option in your scenario.

sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

DerbyNeal Hall of Fame Points: 3854 More actions · Answer 4

Hi,

Thanks guys. I've done some initial testing and the issue appears to have been resolved.

Excellent prompt responses!