-1

I'm developing a c# application that consists of Document Incoming System for my police station.

In this system, variable document's contents are been saved to an SQL database. I must give them a "Document Number".

I'm achieving all of these, but i want that every years last day such as 31.12.2014, the numbers that have been given to a document like "2145" will turn to "1" at the the first day of year 01.01.2015.

So, the records must be 2014/2145. and the last days of years turns to 2015/1.

How can I achieve this?

crthompson
  • 15,653
  • 6
  • 58
  • 80
paradoxIST
  • 31
  • 7
  • You need a bit more information for this to be clear. If I understand, you just want to update all the document numbers at the end of the year to reflect the new format? Do you want to save the old number? – crthompson May 07 '14 at 15:52
  • Actually, you want to do this on the first day of the new year, not the last day of the old one. – Dan Bracuk May 07 '14 at 16:12
  • If your database engine, which you did not specify, has sequences, use one for your document numbers. Then set up a scheduled job to re-set the sequence at 00:00 every January 1st. – Dan Bracuk May 07 '14 at 16:14
  • of course im saving the old numbers. im thinking to add new column and substring the dates 'Year' part. such as substring the last four number of 11.07.2014 and then in the new columns writing 2014/1478 – paradoxIST May 07 '14 at 16:16

1 Answers1

0

You count the existing documents for the same year, then add one.

So if you want to store a document that belongs to 2013, you first count how many existing documents you have in 2013, then add one.

I can't write the sql for you, because you haven't described the data structure, but it should be simple enough using SQL COUNT, and DATEPART to retrieve only the year from a date field.

PaulG
  • 13,871
  • 9
  • 56
  • 78