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
shragiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
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.
0
Scott PletcherSenior DBACommented:
SELECT ...
FROM dbo.atable
WHERE
    year >= @start_year AND year <= @end_year AND
    year * 100 + month BETWEEN @start_year * 100 + @start_month AND
        @end_year * 100 + @end_month


Technically the comparisons against year by itself are redundant, but they can provide index search keys (the conditions are "sargable"), while the computations cannot; thus, for performance reasons, it's a good idea to do both.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chris LuttrellSenior Database ArchitectCommented:
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

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott PletcherSenior DBACommented:
Any value resulting from a CASE statement won't be used in a search key either.
0
shragiAuthor Commented:
@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.
0
shragiAuthor Commented:
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...
0
Scott PletcherSenior DBACommented:
>> 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)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.