Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Umesh Kumar Sql Server Date between Query

Author  Topic 

umesh.kumar2
Starting Member

1 Post

Posted - 2014-05-29 : 11:56:22
Hi I have a Table as below

Month Year Rate
1 2013 100
2 2013 101
8 2014 105

The rate is the value for the Month(1)January and year 2013.

I wana have a query which need to get rate value between 3rd month 2013 and 5the month 2014.

The Output has to be

3 2013 101
4 2013 101
5 2013 101

to

4 2014 105
5 2014 105

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-29 : 14:44:53
what are the data types of [month] and [year]?

EDIT:
and what sql server version are you using?

EDIT2:
assuming Integers and 2012:

select [year],[month],[rate]
from yourTable
where datefromparts([year], [month], 01) between '2013-03-01' and '2014-05-01'



Be One with the Optimizer
TG
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-05-29 : 16:27:03
If you have gaps in your table, you can use this:
declare @year_from int=2013;
declare @month_from int=3;
declare @year_to int=2014;
declare @month_to int=5;

with cte (y,m)
as (select @year_from
,@month_from
union all
select y+case when m>=12 then 1 else 0 end
,case when m>=12 then 1 else m+1 end
from cte
where y*100+m<@year_to*100+@month_to
)
select a.[month]
,a.[year]
,a.rate
from (select a.m as [month]
,a.y as [year]
,b.rate
,row_number() over (partition by a.y,a.m order by b.[year] desc,b.[month] desc) as rn
from cte as a
left outer join yourtable as b
on b.[year]*100+b.[month]<=a.y*100+a.m
) as a
where a.rn=1
order by a.[year]
,a.[month]
Also, in order for the rate being 105 on month 3 and 4 in 2014, record 3 in your table probably would be 2013 (not 2014).
Go to Top of Page
   

- Advertisement -