auto generated enquiry noumber

Question

0.00/5 (No votes)

See more:

Hello All,

I have a view named as vwEnquiry in which i have two coloumn
1- strBranchCode varchar(10)and
2- datEnquiry datetime

i want to generate a auto generated code
which has formate like strBranchCode+year+month+1,
strBranchCode+year+month+2,
strBranchCode+year+month+3 etc

where the year and the month will be extract from datEnquiry

can any body help me out to write this code in MSSQL

Thanks and regard
Subiya

Posted 27-Jul-11 2:06am

ahsan.subiya

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gordon Beeming · Answer 1 · 2011-07-27T09:26:00

Solution 2

Google WITH ROWNUMS, this might be what you'll be looking for for the auto numbering

To get the year you can use DATEPART(year,datEnquiry) and DATEPART(month,datEnquiry) and you'd probably need to cast these if you going to be concatenating them as follows CAST(DATEPART(year,datEnquiry) as VARCHAR)

Posted 27-Jul-11 9:26am

Gordon Beeming

Updated 27-Jul-11 9:35am

v3

Christian Graus · Answer 2 · 2011-07-27T02:12:00

Solution 1

Your best bet is to use an index for the 1/2/3 etc and build the rest from there, you can do a select that concatenates values in to one.

Posted 27-Jul-11 2:12am

Christian Graus

Comments

Gordon Beeming 27-Jul-11 15:28pm

I think you've misused the term index in your solution. An index in SQL will not give any form of auto numbering to any query

Christian Graus 27-Jul-11 19:01pm

Yes, you're right. I meant an identity field. Blame the jetlag...