asked on

sql for selecting values with in given month/year range

Hi,

I have a table with the below details (sample data)

ID            otherID Year Month hosp_cnt non_hosp_cnt
8570050      9004800      2013      01            0            2
8570050      9004800      2013      02            8            0
8570050      9004800      2013      03            0            0
8570050      9004800      2013      04            0            55
8570050      9004800      2013      05            0            0
8570050      9004800      2013      06            0            0
8570050      9004800      2013      07            6            0
8570050      7995330      2012      01            0            0
8570050      7995330      2012      02            0            0
8570050      7995330      2012      03            0            7
8570050      7995330      2012      04            0            0
8570050      7995330      2012      05            4            0
8570050      7995330      2012      06            0            15
8570050      7995330      2012      07            0            0
8570050      7995330      2012      08            24            0
8570050      7995330      2012      09            0            0
8570050      7995330      2012      10            0            0
8570050      7995330      2012      11            0            0
8570050      7995330      2012      12            14            0

I am generating a report for the above table with input parameters...
I have the below values (parameters entered by user) to retrieve the data
ID = 8570050
Start Month: 10
Start Year : 2012
End Month: 02
End year: 2013

Now I want to a sql for get values from table with months between 10/2012 and 02/2013
i.e., for months 10/2012, 11/2012, 12/2012, 01/2013, 02/2013
If it is the date I can simply write sql as below
date >= start_date and date <= end_date
but months ? how can I compare

With the above data I need to retrieve the value within that month range, ie., i want
8570050      7995330      2012      10            0            0
8570050      7995330      2012      11            0            0
8570050      7995330      2012      12            14            0
8570050      9004800      2013      01            0            2
8570050      9004800      2013      02            8            0

so how can I compare it, is there any sql function to do that.
Any help is greatly appreciated

Thanks,
Singi

Kyle Abrahams, PMP

I would have 4 parameters

@yearStart int
@yearEnd int
@monthStart int
@monthEnd int

select * from <tbl>
where
(
(year = @yearStart and month >= @monthstart ) or -- beginnign year & start month or better
(year = @yearEnd and month <= @monthEnd ) or -- end year and end month or less
(year > @yearstart and year < @yearend) -- any other year between start and end
) -- AND . .. other criteria here.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chris Luttrell

This is another way to accomplish the dual Year and Month filters:
It avoids calculations and should still be able to use an index on ID and/or Year

SELECT *
FROM dbo.SampleTable
WHERE ID = @ID 
AND Year > = @Start_Year AND Year <= @End_year
AND Month >= CASE WHEN Year = @Start_Year THEN @Start_Month ELSE 0 END -- Only limit >= Start month in Start year
AND Month <= CASE WHEN Year = @End_year THEN @End_Month ELSE 13 END -- Only limit <= End month in End year

Open in new window

Scott Pletcher

Any value resulting from a CASE statement won't be used in a search key either.

shragi

ASKER

@ScottPletcher - i liked this solution, this works for my query.

just one more extension, how do I approach it if i don't have end year or end month.

For example:
if startyear = 2011 and startmonth = 11 (and no endyear or endmonth)
but i want the result of next 3 months.. how can i do that...
one solution i can think of is adding case statements like
case when month = 11 or 12 then get 2011/11, 2011/12 and 2012/01.
but i guess there should be an easy way to do that.
Any help ?

@ged325 - your solution is almost correct but it fails when i input 2012 as my start and end year.

shragi

ASKER

actually nevermind...i got it

SELECT ...
FROM dbo.atable
WHERE
year * 100 + month BETWEEN (@start_year * 100 + @start_month) AND
((@start_year * 100 + @start_month) + 90)

thank you all guys... you saved lot of time for me...

Scott Pletcher

>> if startyear = 2011 and startmonth = 11 (and no endyear or endmonth)
but i want the result of next 3 months.. <<

You can compute the end year and month, then use the query as before:

SET @end_year = YEAR(DATEADD(MONTH, 3, @start_year)
SET @end_month = MONTH(DATEADD(MONTH, 3, @start_year)