June 12, 2013 at 4:43 pm
I am trying to find out how to do the same thing I do in a join in a where clause.
I have the following that works fine:
DECLARE @Client TABLE
(
ClientId int,
ClientName varchar(100),
ValidDate smalldatetime
)
DECLARE @ValidDates TABLE
(
ValidDateId int,
StartDate smalldatetime,
EndDate smalldatetime
)
INSERT @Client VALUES (1, 'Tom Jones', '02/15/2013')
INSERT @Client VALUES (1, 'Larry Smith', '4/05/2013')
INSERT @Client VALUES (1, 'Jerry Lowell', '07/25/2013')
INSERT @ValidDates VALUES (1,'01/15/2013','03/30/2013')
INSERT @ValidDates VALUES (2,'5/15/2013','08/30/2013')
SELECT *
FROM @Client cl
JOIN @ValidDates vd
ON cl.ValidDate BETWEEN vd.StartDate AND vd.EndDate
But I need to do this in a where clause, possible using a subquery but how do I use the "BETWEEN" there?
Thanks,
Tom
June 12, 2013 at 4:48 pm
Something like this?
SELECT *
FROM @Client cl
WHERE EXISTS( SELECT 1
FROM @ValidDates vd
WHERE cl.ValidDate BETWEEN vd.StartDate AND vd.EndDate)
June 13, 2013 at 2:56 am
tshad (6/12/2013)
But I need to do this in a where clause, possible using a subquery but how do I use the "BETWEEN" there?
Why? Any particular reason?
SELECT *
FROM @Client cl
JOIN @ValidDates vd ON 1 = 1
WHERE cl.ValidDate BETWEEN vd.StartDate AND vd.EndDate
Would the above code suffice?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 13, 2013 at 10:56 am
That was it.
Not sure why I didn't realize this.
Thanks,
Tom
June 13, 2013 at 11:09 am
Hi Kingston,
Actually, there was a reason for it having to be in the where clause.
I was actually using a table variable for filtering based on a parameter that had a comma delimited list of values passed in @Quarter. If the parameter past was null, then all records were returned. If a list was passed (@Quarter not null) then only records with dates between those passed which are in the @QtrTable (which is table of dates that were passed in @Quarter)
The actual code in the Where clause was:
( @Quarter IS NULL OR
EXISTS ( SELECT 1
FROM @QtrTable qt
WHERE c.CreatedDate BETWEEN qt.BeginDate
AND qt.EndDate
)
)
Thanks,
Tom
June 13, 2013 at 11:37 am
tshad (6/13/2013)
That was it.Not sure why I didn't realize this.
Thanks,
Tom
I'm glad I could help. 😉
June 18, 2013 at 5:56 am
SELECT *
FROM @Client cl
JOIN @ValidDates vd
ON 2=2
where cl.ValidDate BETWEEN vd.StartDate AND vd.EndDate
Anything we give same value for on condition......
June 18, 2013 at 8:57 am
Arul prakash (6/18/2013)
SELECT *FROM @Client cl
JOIN @ValidDates vd
ON 2=2
where cl.ValidDate BETWEEN vd.StartDate AND vd.EndDate
Anything we give same value for on condition......
You missed the point, the OP didn't want a JOIN, he needed a semi join to avoid returning additional columns. See my first post on this thread.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply