|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL2000 can a generate an incremental row #?I just got a request for combining a date, with an incremental to generate a
job #. I hate the idea but then when do we think this up anyway? Here is what I'm playing with as of now from one of my developers: SELECT LEFT(CONVERT(VARCHAR(10),GETDATE(),10),2) + SUBSTRING(CONVERT(VARCHAR(10),GETDATE(),10),4,2) + RIGHT(CONVERT(VARCHAR(10),GETDATE(),10),2), I'd rather do a convert to string on the date but what do I know ;> SELECT CONVERT(CHAR(8), getdate(), 110) But how could you add to that char as 001, 002, 003 ? TIA __Stephen Does it work?
SELECT '0'+LEFT(CONVERT(VARCHAR(10),GETDATE(),10),2) + SUBSTRING(CONVERT(VARCHAR(10),GETDATE(),10),4,2) + RIGHT(CONVERT(VARCHAR(10),GETDATE(),10),2) Show quote "__Stephen" <sruss***@transactiongraphics.com> wrote in message news:eS7yx6gEGHA.140@TK2MSFTNGP12.phx.gbl... >I just got a request for combining a date, with an incremental to generate >a job #. I hate the idea but then when do we think this up anyway? > > Here is what I'm playing with as of now from one of my developers: > > SELECT LEFT(CONVERT(VARCHAR(10),GETDATE(),10),2) + > SUBSTRING(CONVERT(VARCHAR(10),GETDATE(),10),4,2) + > RIGHT(CONVERT(VARCHAR(10),GETDATE(),10),2), > > I'd rather do a convert to string on the date but what do I know ;> > > SELECT CONVERT(CHAR(8), getdate(), 110) > > But how could you add to that char as 001, 002, 003 ? > > TIA > > __Stephen > > > That would, but I have to do it for each row I pull from sales orders table.
Management realized that if I see the date is old then I'll know that I have a problem. PHB jumped in and demanded additional digits to the date to fix the issue, I tried to tell them that we already have warning reports on jobs lost in the cracks, but nooooooo! We have to devise a whole new set of crap based on a damn date. So any ideas on pulling data from a table, and row by row adding 1 to a value? I'll voodoo the heck out of it if we can do it in a select. I hate using a cursor for this. I'll be doing up to 10,000 rows give or take in a day so you can see why I think this is a bad idea! __Stephen Show quote "Uri Dimant" <u***@iscar.co.il> wrote in message news:O4yo6AhEGHA.3200@tk2msftngp13.phx.gbl... > Does it work? > > SELECT '0'+LEFT(CONVERT(VARCHAR(10),GETDATE(),10),2) + > SUBSTRING(CONVERT(VARCHAR(10),GETDATE(),10),4,2) + > RIGHT(CONVERT(VARCHAR(10),GETDATE(),10),2) > > > > > "__Stephen" <sruss***@transactiongraphics.com> wrote in message > news:eS7yx6gEGHA.140@TK2MSFTNGP12.phx.gbl... >>I just got a request for combining a date, with an incremental to generate >>a job #. I hate the idea but then when do we think this up anyway? >> >> Here is what I'm playing with as of now from one of my developers: >> >> SELECT LEFT(CONVERT(VARCHAR(10),GETDATE(),10),2) + >> SUBSTRING(CONVERT(VARCHAR(10),GETDATE(),10),4,2) + >> RIGHT(CONVERT(VARCHAR(10),GETDATE(),10),2), >> >> I'd rather do a convert to string on the date but what do I know ;> >> >> SELECT CONVERT(CHAR(8), getdate(), 110) >> >> But how could you add to that char as 001, 002, 003 ? >> >> TIA >> >> __Stephen >> >> >> > > Take a look at INDENTITY property
Show quote "__Stephen" <sruss***@transactiongraphics.com> wrote in message news:O$2y8EhEGHA.2648@TK2MSFTNGP11.phx.gbl... > That would, but I have to do it for each row I pull from sales orders > table. Management realized that if I see the date is old then I'll know > that I have a problem. PHB jumped in and demanded additional digits to > the date to fix the issue, I tried to tell them that we already have > warning reports on jobs lost in the cracks, but nooooooo! We have to > devise a whole new set of crap based on a damn date. > > So any ideas on pulling data from a table, and row by row adding 1 to a > value? I'll voodoo the heck out of it if we can do it in a select. I > hate using a cursor for this. > > I'll be doing up to 10,000 rows give or take in a day so you can see why I > think this is a bad idea! > > > __Stephen > > "Uri Dimant" <u***@iscar.co.il> wrote in message > news:O4yo6AhEGHA.3200@tk2msftngp13.phx.gbl... >> Does it work? >> >> SELECT '0'+LEFT(CONVERT(VARCHAR(10),GETDATE(),10),2) + >> SUBSTRING(CONVERT(VARCHAR(10),GETDATE(),10),4,2) + >> RIGHT(CONVERT(VARCHAR(10),GETDATE(),10),2) >> >> >> >> >> "__Stephen" <sruss***@transactiongraphics.com> wrote in message >> news:eS7yx6gEGHA.140@TK2MSFTNGP12.phx.gbl... >>>I just got a request for combining a date, with an incremental to >>>generate a job #. I hate the idea but then when do we think this up >>>anyway? >>> >>> Here is what I'm playing with as of now from one of my developers: >>> >>> SELECT LEFT(CONVERT(VARCHAR(10),GETDATE(),10),2) + >>> SUBSTRING(CONVERT(VARCHAR(10),GETDATE(),10),4,2) + >>> RIGHT(CONVERT(VARCHAR(10),GETDATE(),10),2), >>> >>> I'd rather do a convert to string on the date but what do I know ;> >>> >>> SELECT CONVERT(CHAR(8), getdate(), 110) >>> >>> But how could you add to that char as 001, 002, 003 ? >>> >>> TIA >>> >>> __Stephen >>> >>> >>> >> >> > > "Uri Dimant" <u***@iscar.co.il> wrote in message Great concept but now what I was after. I need to combine today's date news:eMWCtHhEGHA.524@TK2MSFTNGP09.phx.gbl... > Take a look at INDENTITY property 01052006 with 000001 and it will increase up through the morning to 110569. Tomorrow I change the date and restart the 000001 __Stephen you could do it after the fact.
add a datetime column with default of getdate(), and an identity column (to break ties on multirow inserts) then in an insert trigger, update the jobnumber, e.g. -- column assumptions for example: -- change to fit environment -- jobnumber char(14) -- exists already -- jobdatestamp datetime default getdate() -- add -- jobidentity int identity(1,1) -- add create trigger ins_yourtable_jobnumber on yourtable for insert as begin update yourtable set jobnumber = convert(varchar, yourtable.jobdatestamp, 112) + right('000000' + convert(varchar, (select count(*) from yourtable t where jobdatestamp>=dateadd(day,datediff(day,0,yourtable.jobdatestamp),0) and jobdatestamp<=yourtable.jobdatestamp and jobidentity<=yourtable.jobidentity )), 6) where jobidentity in (select jobidentity from inserted) end __Stephen wrote: Show quote > "Uri Dimant" <u***@iscar.co.il> wrote in message > news:eMWCtHhEGHA.524@TK2MSFTNGP09.phx.gbl... > >>Take a look at INDENTITY property > > > Great concept but now what I was after. I need to combine today's date > 01052006 with 000001 and it will increase up through the morning to 110569. > Tomorrow I change the date and restart the 000001 > > __Stephen > >  |
|||||||||||||||||||||||
Other interesting topics