• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

How to write WHERE clause for a date range when using separate columns for year and month

How do I write the WHERE clause to include a date range or period range when I have a column for the year and a column for the month

I have a SQL table with 3 columns - Year, Month, Amount

Typical rows are:
Year   Month   Amount
2009,    1,       $12
2009,    3,       $15
2009,    12,     $30
2010,    2,       $10
2010,   12,      $67

I would like to query for     2009 / 3 thru  2010 / 3

So the 2,3,4 row would be included and the first and last row not included in query

I'm using SQL 2008 and  ColdFusion9



0
bigmikey88
Asked:
bigmikey88
  • 4
  • 3
  • 2
  • +2
2 Solutions
 
jimyXCommented:
Select * from Table where (Year >= 2009 and month >= 3 and Year <= 2010 and month <= 3)
0
 
dba2dbaCommented:
select * from table
where
((year=2009) AND (month>=3 and month<=12))
and
((year=2010) AND (month>=1 and month<=3))
0
 
gdemariaCommented:
i think the proposed solutions need an OR...

> Select * from Table where (Year >= 2009 and month >= 3 and Year <= 2010 and month <= 3)

Seem this will only return March in 2009 or 2010

I think this one needs and OR, otherwise nothing will match as the year can't be 2009 AND 2010...
select * from table
where ((year=2009) AND (month>=3 and month<=12))
and    ((year=2010) AND (month>=1 and month<=3))


select * from table
where (
  (year=2009 AND (month>=3 and month<=12) )
OR 
  (year=2010 AND (month>=1 and month<=3) ) 
)

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
jimyXCommented:
Mine needs parentheses:
Select * from Table where (Year >= 2009 and month >= 3) and (Year <= 2010 and month <= 3)
0
 
dba2dbaCommented:
Yes gdemaria: is correct. The AND in the where clause needs to be OR.

THanks,
0
 
etanoxCommented:
select * from table where
(year=2009 and month>=3)
or
(year=2010 and month<=3)
0
 
gdemariaCommented:

> Mine needs parentheses:

Parentheses don't matter when you have all "and" s..


> Select * from Table where (Year >= 2009 and month >= 3) and (Year <= 2010 and month <= 3)

Try testing Month= 7 in year 2009 it will not match because of month <= 3 at the end which is ANDed

0
 
gdemariaCommented:
etanox is right, you don't need to specify <= 12 or >=1 as there are no months greater than 12 or less than 1.


0
 
jimyXCommented:
You are right.
0
 
bigmikey88Author Commented:
<cfquery name="GetEarnings" datasource="mydb" username="#User#" password="#Pass#" dbtype="ODBC">
SELECT  Period_Year, Period_Month, Amount AS Earnings
FROM   mytable
Where
(
(Period_Year = #StartYear# AND (Period_Month >= #StartMonth# AND Period_Month <= 12)) OR

((Period_Year < #EndYear# AND Period_Year <> #StartYear#) AND (Period_Month >= 1 AND Period_Month <= 12)) OR

 (Period_Year = #EndYear# AND (Period_Month >= 1 AND Period_Month <= #EndMonth#))
 )
Order By Period_Year, Period_Month
</cfquery>

The ideas from gdemaria and dba2dba pointd me in the right direction and gdemaria's comments about needing the OR were correct.

Since the years and months are dynamic, and can span several years, I had to have another OR clause.  It seems to be working.  But I need to test it more.

Thx all
0
 
bigmikey88Author Commented:
Well, after further testing the code I wrote did not work well.

I'm going to take another approach.

Mike
0
 
gdemariaCommented:
this is a modified version of what you wrote..
SELECT  Period_Year, Period_Month, Amount AS Earnings
FROM   mytable
Where 
(
   (Period_Year = #StartYear# AND Period_Month >= #StartMonth#)
OR
   (Period_Year > #StartYear# AND Period_Year < #EndYear#)
OR
   (Period_Year = #EndYear# AND Period_Month <= #EndMonth#)
 )
Order By Period_Year, Period_Month

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now