how to get new RequestId every (Financial Year)? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

how to get new RequestId every (Financial Year)?

Author

Topic

ch.rajesh
Starting Member

7 Posts

Posted - 2009-04-13 : 07:31:08

Hi all
I posted this already

still did not get any solution.

If any one knows kindly send the solution.

requirement is

I want a new RequestId for every Financila Year

ReqId ---- CreatedDate----- "MyFormat"
1 ----- 1/1/2009 ---- 1
2 ----- 2/2/2009 ---- 2
3 ----- 2/3/2009 ---- 3
4 ----- 2/4/2009 ---- 1
5 ----- 2/5/2009 ---- 2
6 ----- 2/03/2010 --- 3
7 ----- 2/04/2010 --- 1
8 ----- 2/05/2010 ----2

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-13 : 07:38:13

declare @t table(id int , dateval datetime)
insert into @t select
1 ,'1/1/2009' union all select
2 ,'2/2/2009' union all select
3 ,'2/3/2009' union all select
5 ,'2/5/2009' union all select
7 ,'2/04/2010' union all select
8 ,'2/05/2010'

select row_number() over(partition by year(dateval) order by dateval ) as rid, * from @t

ch.rajesh
Starting Member

7 Posts

Posted - 2009-04-13 : 08:08:09

Hi bklr,

it works fine for year
but I want for financial year

so from April 1st--31st March one set

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-13 : 08:18:15

consider date as mm/dd/yyy format
try this one
declare @t table(id int , dateval datetime)
insert into @t select
1 ,'1/1/2009' union all select
2 ,'2/2/2009' union all select
3 ,'2/3/2009' union all select
4,'4/4/2009' union all select
5 ,'2/5/2009' union all select
6,'2/3/2010' union all select
7 ,'5/04/2010' union all select
8 ,'6/05/2010'

select row_number() over(partition by case when month(dateval) < 4 then year(dateval) else year(dateval)+1 end order by dateval ) as rid, * from @t

ch.rajesh
Starting Member

7 Posts

Posted - 2009-04-14 : 00:40:12

Thnks bklr

that's solved my problem.

Thnk u again

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-14 : 07:17:54

quote:
Originally posted by ch.rajesh

Thnks bklr

that's solved my problem.

Thnk u again

welcome

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-14 : 07:35:39

[code]SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATEDIFF(MONTH, '19000401', dateval) / 12 ORDER BY dateval) AS rid
FROM @Sample [/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources