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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply