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
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
Select * from Table where (Year >= 2009 and month >= 3 and Year <= 2010 and month <= 3)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Mine needs parentheses:
Select * from Table where (Year >= 2009 and month >= 3) and (Year <= 2010 and month <= 3)
Select * from Table where (Year >= 2009 and month >= 3) and (Year <= 2010 and month <= 3)
Yes gdemaria: is correct. The AND in the where clause needs to be OR.
THanks,
THanks,
select * from table where
(year=2009 and month>=3)
or
(year=2010 and month<=3)
(year=2009 and month>=3)
or
(year=2010 and month<=3)
> 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
etanox is right, you don't need to specify <= 12 or >=1 as there are no months greater than 12 or less than 1.
You are right.
ASKER
<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
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
ASKER
Well, after further testing the code I wrote did not work well.
I'm going to take another approach.
Mike
I'm going to take another approach.
Mike
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