CODEWITHSUNDEEP
sqlsql-servert-sql
Shankar
Shankar

Reputation: 157

Using select query for between where clause

I have to find out a date falls between two other dates which are selected from a different table in Microsoft SQL Server

I.e. I want to do something like

     Select A.* from  ( select member.key, 
           case when effective_date between (select month_start and month_end 
           from sales_month 
           where month=2 and year=2013) bucket_1
           then 1 else 0 from member ) as A
          where a.bucket_1 != 0

I have to duplicate case statement for different months. Any ideas / help?

Thanks

Shankar.

Upvotes: 0

Views: 4968

Answers (4)

Aaron Bertrand
Aaron Bertrand

Reputation: 280272

Not sure why you need to get the month and year from a table. You're looking for all members whose effective date falls in February of 2013, right? What is sales_month doing for you here?

DECLARE @m INT, @y INT;
SELECT @m = 2, @y = 2013;


DECLARE @d DATETIME;
SET @d = DATEADD(MONTH, @m-1, DATEADD(YEAR, @y-1900, 0));

SELECT m.*            -- don't use SELECT *!
 FROM dbo.member AS m -- always use schema prefix!
  WHERE effective_date >= @d 
  AND effective_date < DATEADD(MONTH, 1, @d); -- don't use BETWEEN!

If it needs to come from a table (e.g. your sales month is not the same as a calendar month for some reason), then use a join, but I'm not sure what your BETWEEN logic really means. I'll leave it in in case it's logical, but in most cases it's not.

SELECT m.*
 FROM dbo.member AS m
 INNER JOIN dbo.sales_month AS s
 ON m.effective_date BETWEEN s.month_start AND s.month_end
 WHERE s.[month] = 2 AND s.[year] = 2013; -- don't use reserved words as column names!

Relevant links:

Upvotes: 0

Registered User
Registered User

Reputation: 8395

This could be done with a JOIN:

SELECT m.* 
FROM member m
JOIN sales_month sm
    ON sm.month = 2
    AND sm.year = 2013
    AND m.effective_date BETWEEN sm.month_start AND sm.month_end;

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269593

If there are no duplicates that you expect, you can use a join:

select m.*
from member m join
     sales_month sm
     on m.effective_date between sm.month_start and sm.month_end and
        sm.month = 2 and sm.year = 2013

Otherwise, try a correlated subquery with exists:

select m.*
from member m
where exists (select 1 from sales_month where month=2 and year=2013 and m.effective_date between month_start and month_end)

Upvotes: 1

judda
judda

Reputation: 3972

Use variables to hold the information.

DECLARE @StartDate DATETIME,
        @EndDate DATETIME
SELECT @StartDate = month_start
       , @EndDate = month_end
FROM sales_month
WHERE [month] = 2 AND [year] = 2013

SELECT *
from member
where effective_date BETWEEN @StartDate AND @EndDate

Upvotes: 4

Related Questions