how to get new RequestId every 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 Year?

Author

Topic

ch.rajesh
Starting Member

7 Posts

Posted - 2009-04-13 : 01:31:50

Hi All
I have one query ie, I have a table say "MstRequest". where I have Primary key for Column "RequestId" and it has identity on it.Here I have "CreatedDate" column also.

So i want to get my data based on year ie every year "RequstId" should start from "1". Let that column be a new one.

ex:
ReqId ---- CreatedDate----- "MyFormat"
1 ----- 1/1/09 ---- 1
2 ----- 2/2/09 ---- 2
3 ----- 2/3/09 ---- 3
4 ----- 2/2/10 ---- 1
5 ----- 2/2/10 ---- 2
6 ----- 2/3/10 ---- 3
7 ----- 2/2/11 ---- 1
8 ----- 2/4/11 ---- 2

In this way so kindly send me the solution if any one knows.
very urgent.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-13 : 02:41:59

quote:
Originally posted by ch.rajesh

Hi All
I have one query ie, I have a table say "MstRequest". where I have Primary key for Column "RequestId" and it has identity on it.Here I have "CreatedDate" column also.

So i want to get my data based on year ie every year "RequstId" should start from "1". Let that column be a new one.

ex:
ReqId ---- CreatedDate----- "MyFormat"
1 ----- 1/1/09 ---- 1
2 ----- 2/2/09 ---- 2
3 ----- 2/3/09 ---- 3
4 ----- 2/2/10 ---- 1
5 ----- 2/2/10 ---- 2
6 ----- 2/3/10 ---- 3
7 ----- 2/2/11 ---- 1
8 ----- 2/4/11 ---- 2

In this way so kindly send me the solution if any one knows.
very urgent.

Refer point 2
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-04-13 : 02:43:12

Hi try this once,

select *,row_number() over ( partition by right(createddate,2) order by reqid ) as Myformat from @temp

ch.rajesh
Starting Member

7 Posts

Posted - 2009-04-13 : 03:41:22

quote:
Originally posted by Nageswar9

Hi try this once,

select *,row_number() over ( partition by right(createddate,2) order by reqid ) as Myformat from @temp

Thank you for the reply

seems it is not working.

see the following result it shows
check the third row for year '09' it gives 3

3 2008-03-19 11:27:53.000 1
6 2008-03-19 11:27:53.000 2
14 2009-04-02 11:36:01.000 3
23 2011-04-09 01:30:04.000 4
24 2011-04-09 04:58:43.000 5
25 2011-04-09 05:00:22.000 6
26 2012-04-09 05:11:19.000 7

ch.rajesh
Starting Member

7 Posts

Posted - 2009-04-13 : 03:48:20

Hi Nageswar

Thanks for the reply,

Its working.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-04-13 : 06:13:48

quote:
Originally posted by ch.rajesh

Hi Nageswar

Thanks for the reply,

Its working.

Welcome

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-04-13 : 06:15:03

Hi try this one also

select *,( select count(1) from @temp where t.reqid >= reqid and right(createddate,2)= right(t.createddate,2)) AS MyFormat from @temp t

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-13 : 06:20:12

quote:
Originally posted by Nageswar9

Hi try this once,

select *,row_number() over ( partition by right(createddate,2) order by reqid ) as Myformat from @temp

If createddate is of datetime datatype, use year(createddate) instead of right(createddate,2)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-13 : 06:21:26

quote:
Originally posted by Nageswar9

Hi try this one also

select *,( select count(1) from @temp where t.reqid >= reqid and right(createddate,2)= right(t.createddate,2)) AS MyFormat from @temp t

This will become ineffecient if there are large number of data in the table.

Madhivanan

Failing to plan is Planning to fail

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-04-13 : 06:26:53

Ok , Madhivanan

ch.rajesh
Starting Member

7 Posts

Posted - 2009-04-13 : 06:27:49

Hi All again

If I want the same according to Financial year

How should I get it?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-13 : 08:49:39

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123708

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources