Using between in where clause from subquery

Question

Post reply

Using between in where clause from subquery

tshad

SSCertifiable

Points: 5905
More actions
June 12, 2013 at 4:43 pm

#278562

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

Viewing 8 posts - 1 through 8 (of 8 total)

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

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

Something like this?

SELECT *

FROM @Client cl

WHERE EXISTS( SELECT 1

FROM @ValidDates vd

WHERE cl.ValidDate BETWEEN vd.StartDate AND vd.EndDate)

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

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?

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

tshad SSCertifiable Points: 5905 More actions · Answer 3

That was it.

Not sure why I didn't realize this.

Thanks,

Tom

tshad SSCertifiable Points: 5905 More actions · Answer 4

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

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

tshad (6/13/2013)
That was it.
Not sure why I didn't realize this.
Thanks,
Tom

I'm glad I could help.

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

Arul prakash Old Hand Points: 318 More actions · Answer 6

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......

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

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.

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

Using between in where clause from subquery

Cookies on SQLServerCentral