March 2, 2015 at 8:08 am
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,
March 2, 2015 at 8:30 am
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?
March 2, 2015 at 8:38 am
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
March 2, 2015 at 8:42 am
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.
March 2, 2015 at 8:47 am
Hi,
Thanks guys. I've done some initial testing and the issue appears to have been resolved.
Excellent prompt responses!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy