Hi all,
I m designing a hospital management project, and I need to provide unique id to every patient. The requirement from our client is that the format of this unique id is: ddmmyyxxx. For example, 080710001, 080710002 etc
Here is what I did:
concat dd mm and yy strings. now concat auto increment field which is initialized with 000. so the first record will be ddmmyy000. and it will continue.

My problem is, after every day, the initial value of auto increment field(last three digits) shud be reset to 000. i.e., for 09/07/2010, the first record shud start with 090710000 and not some 090710xyz.
How this can be done? Shud I need to alter table every day to reset the auto increment value?




Thanks in advance.

shud shud shud tsk tsk tsk

wtf are you gonna do, shud your hospital ever have more than 999 patients in a day?

someone in your company shud speak to your client about this nonsensical requirement

Where is this hospital located (specifically what governmental oversite does the hospital need to meet)? They are almost certainly setting themselves up for a regulatory nightmare, and we need to understand which rules they need to comply with in order to advise you appropriately.

-PatP

@r937
That requirement was posed for better readability of the ids.
There is one more constraint: assume we start with id 080710001. after some days, id will be ddmmyy999. The next id becomes ddmmyy1000. which doesnt fit under the specified format. Hence the better idea is to reset the last 3 digits every day.

Analogy: your room number 401 in a hotel doesnot imply that hotel has 400 more rooms.

that's a very poor requirement in any case, and you did not meet it

u can reply only if u have some basic knowledge about the topic.

i guess that lets me out

If you post such an obscure requirement you have to expect that people are going to question you about it.

I'm not running away from answering to queries. But in the meantime, I want replies in favor of the topic and not some personal comments or disgusting remarks. If it is so obscure, then tell me your solution to this question. Will you answer in the same tone to your manager?

no, because he's paying me a ~lot~ more than you are

with you i can be honest