Solved

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

Posted on 2011-03-11
12
401 Views
Last Modified: 2012-05-11
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
Comment
Question by:bigmikey88
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 24

Expert Comment

by:jimyX
ID: 35112199
Select * from Table where (Year >= 2009 and month >= 3 and Year <= 2010 and month <= 3)
0
 
LVL 8

Assisted Solution

by:dba2dba
dba2dba earned 150 total points
ID: 35112216
select * from table
where
((year=2009) AND (month>=3 and month<=12))
and
((year=2010) AND (month>=1 and month<=3))
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 350 total points
ID: 35112261
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
 
LVL 24

Expert Comment

by:jimyX
ID: 35112289
Mine needs parentheses:
Select * from Table where (Year >= 2009 and month >= 3) and (Year <= 2010 and month <= 3)
0
 
LVL 8

Expert Comment

by:dba2dba
ID: 35112302
Yes gdemaria: is correct. The AND in the where clause needs to be OR.

THanks,
0
 

Expert Comment

by:etanox
ID: 35112303
select * from table where
(year=2009 and month>=3)
or
(year=2010 and month<=3)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 39

Expert Comment

by:gdemaria
ID: 35112331

> 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
 
LVL 39

Expert Comment

by:gdemaria
ID: 35112343
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
 
LVL 24

Expert Comment

by:jimyX
ID: 35112497
You are right.
0
 

Author Closing Comment

by:bigmikey88
ID: 35112559
<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
 

Author Comment

by:bigmikey88
ID: 35112596
Well, after further testing the code I wrote did not work well.

I'm going to take another approach.

Mike
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35112620
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

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now