Creating a sustaining counter as in work orders - unique

[Joell]

Hi Guys,

Im still new to this SQL stuff and have a question about creating a counter that does not reset if I drop the temp table. Is this possible? I need to add new work order number (counter) to a daily orders table/view/procedure and I can make it work for one day but then when I drop the temp table, it resets the counter back. How can I keep the Max(orderno) going forward to the next day?

I am a little versed in stored procedures too. We are using SQL 2000 at the moment in the office.

Any ideas for this simple minded gal?

Thanks!
Jo

[ck9663]

Hi Guys,

Im still new to this SQL stuff and have a question about creating a counter that does not reset if I drop the temp table. Is this possible? I need to add new work order number (counter) to a daily orders table/view/procedure and I can make it work for one day but then when I drop the temp table, it resets the counter back. How can I keep the Max(orderno) going forward to the next day?

I am a little versed in stored procedures too. We are using SQL 2000 at the moment in the office.

Any ideas for this simple minded gal?

Thanks!
Jo

try the IDENTITY column. it may not be sequential, but it's unique and it will not reset

[Joell]

try the IDENTITY column. it may not be sequential, but it's unique and it will not reset

Thank you so much for responding! I did try the Identity column but I dont know how to make it not reset when pulling the stored procedure tomorrow. Should I send you my code? Maybe I shouldnt create table? How can you put an identity column in a SELECT stmt?

[ck9663]

Thank you so much for responding! I did try the Identity column but I dont know how to make it not reset when pulling the stored procedure tomorrow. Should I send you my code? Maybe I shouldnt create table? How can you put an identity column in a SELECT stmt?

try sending your code... just those that will be needed

[Joell]

Hello Again,

Here is my cutdown code. There is a lot more to the Select stmt but I cut it down just to show you around it. I would like to put the end result into a procedure so that I can push it to crystal reports and my end users can then export to Excel. Maybe even schedule the proc to run as a DTS package?



create procedure @businessunit varchar(30), @datepulled datetime

as

create table #UPSDaily(
Location_no varchar(20) not null,
OrderNo int Identity(100000,1) not null
)

Insert into #UPSDaily

Select c_id_alpha Location_No,
-- Dont I need a placeholder here somehow for orderno from the creation of the table above?
from cust inner join rxrf on cust.c_id = rxrf.c_id
where rxrf.next_date = getdate()

-- then I need to keep the last value of the OrderNo for the next day's pull of data.

declare @intCounter int
select @intCounter = coalesce(max(orderno), 1) from #UPSDaily
declare @when datetime
set @when = getDate()

update #UPSDaily
set @intCounter = OrderNO = @intCounter + 1

drop table #UPSDaily -- but this removes my orderno value for the beginning of the next day.
--I need the next sequential number to start off the next day's pull of data.


Thanks so much for your help again! - JOELL

[ck9663]

Hello Again,

Here is my cutdown code. There is a lot more to the Select stmt but I cut it down just to show you around it. I would like to put the end result into a procedure so that I can push it to crystal reports and my end users can then export to Excel. Maybe even schedule the proc to run as a DTS package?



create procedure @businessunit varchar(30), @datepulled datetime

as

create table #UPSDaily(
Location_no varchar(20) not null,
OrderNo int Identity(100000,1) not null
)

Insert into #UPSDaily

Select c_id_alpha Location_No,
-- Dont I need a placeholder here somehow for orderno from the creation of the table above?
from cust inner join rxrf on cust.c_id = rxrf.c_id
where rxrf.next_date = getdate()

-- then I need to keep the last value of the OrderNo for the next day's pull of data.

declare @intCounter int
select @intCounter = coalesce(max(orderno), 1) from #UPSDaily
declare @when datetime
set @when = getDate()

update #UPSDaily
set @intCounter = OrderNO = @intCounter + 1

drop table #UPSDaily -- but this removes my orderno value for the beginning of the next day.
--I need the next sequential number to start off the next day's pull of data.


Thanks so much for your help again! - JOELL

by the looks of this, you're just getting the last OrderNo? coz you're dropping the table anyway...

[Joell]

by the looks of this, you're just getting the last OrderNo? coz you're dropping the table anyway...

Is there a better way to structure (maybe some kind of while loop) to run my select while grabbing the next incremental value? If so, how do I code that? Its just not working as is. I need a sequential number to restart over each day that the query runs. If I use a table, then doesnt that make the database larger when it is not necessary? All I am trying to do is create a seq number for the work orders each day and not duplicate any number. The seq number needs to be in a column of the select statement that I am running. Does that make better sense than before?

HELP!

[Purple]

Hi Jo,

do you have a field in the table with todays date in it ?

If you do, select the max date and compare with today, if the date part of today is bigger, reset your counter..

If not then post the table structure for the table and the temp table..

Regards Purple

[Joell]

Hi Jo,

do you have a field in the table with todays date in it ?

If you do, select the max date and compare with today, if the date part of today is bigger, reset your counter..

If not then post the table structure for the table and the temp table..

Regards Purple

I think I had a typo in my question. What I need to have is a counter that does NOT reset each day. So for my last order on 8/27 is 833230, then the first order on 8/28 should be 833231. Does that make better sense? sorry for all of my confusion.

I do not know how to write the code for it.

Help please.

Jo

[Purple]

Hi Jo,

am I missing something, cant you just use an auto increment field ?

Purple

[Joell]

Hi Jo,

am I missing something, cant you just use an auto increment field ?

Purple

That sounds logical but I dont know how to do that. I have tried the Identity counter but then if you use a temp table, it resets the next time you run the query. I dont want it to reset. I need unique values every time I run the query and to never reset. I also do not want to add tables to my database. I just need to pull existing data and add an sequential number that will not reset each day I run the query.

Isnt there another way? Maybe could you tell me about the auto increment?

[Purple]

Jo,

Why are you using a temp table ?

Purple

[Joell]

Jo,

Why are you using a temp table ?

Purple

I thought creating a temp table would be better than to have a table created for 11 separate business divisions of work orders that need to be pulled every day and imported into another system. Wouldnt it make the database very large in a small amount of time?

how else could I pull data from one system, attach a sequential number and then import it into another system? system = database

[Purple]

Hi Jo,

It is often difficult to analyse the problem when somewhat distant from the basic requirements. From what you have described, I think I would look again at the database structure.

Maybe add an int column with a foreign key to a business unit table and have all of the 11 business units work orders in one table,

I have no idea of what you perceive large is, MSSQL will be fine with multiple million rows in a table if it is appropriately indexed and running on a server with enough grunt.

If you use an autoincrement field for the work order number you will automatically get unique incremental work order numbers.

Does this help or have I missed the point ?

Regards Purple

[Joell]

Hi Jo,

It is often difficult to analyse the problem when somewhat distant from the basic requirements. From what you have described, I think I would look again at the database structure.

Maybe add an int column with a foreign key to a business unit table and have all of the 11 business units work orders in one table,

I have no idea of what you perceive large is, MSSQL will be fine with multiple million rows in a table if it is appropriately indexed and running on a server with enough grunt.

If you use an autoincrement field for the work order number you will automatically get unique incremental work order numbers.

Does this help or have I missed the point ?

Regards Purple

You are awesome and I appreciate your help. I am not very versed in SQL lingo and so defining the problem is a bit of a challenge for me.

I am using proprietary software and trying to pull from it into another package without incrementing the values in the proprietary software.

If I create a new table and add a int column, then on day 2, how do I get the counter to NOT reset? My logic is not such that I can remove the previous day's work orders yet so I will be building and building this table adding new work orders every day. Maybe a better question would be if I create a new table on day 1, how do I add to it on day 2, day 3, keeping the counter int going?

Jo

[ck9663]

You are awesome and I appreciate your help. I am not very versed in SQL lingo and so defining the problem is a bit of a challenge for me.

I am using proprietary software and trying to pull from it into another package without incrementing the values in the proprietary software.

If I create a new table and add a int column, then on day 2, how do I get the counter to NOT reset? My logic is not such that I can remove the previous day's work orders yet so I will be building and building this table adding new work orders every day. Maybe a better question would be if I create a new table on day 1, how do I add to it on day 2, day 3, keeping the counter int going?

Jo

Jo...

In order to keep this value, you have to store it somewhere. What do you with the daily tables? Do you have a one master table that contain it all? if you do, you can take the max(counter)+1 on that master table as the starting counter on your daily table. for this, you might need a trigger to handle the counter on your daily table. make sure that your counter is the PK on both table to ensure uniqueness.

[Purple]

Hi Jo,

I suggest you have a master workorder table which holds all of the work orders created for all of the business areas and this a permanent table not a temp table..

When you create the table workOrder add a field as the primary key and set it as an auto increment field, this will be the work order id.

Now when you insert rows into the table the work order id field is automatically incremented by one for every new row. (dont try to set a value for this field on the insert, if you want the numbers to start from a specific value, ie other than 1 specify a seed value)

Also create a field to represent the business unit as an int and use a join to the business unit table where you may have columns like

buId buName buContact etc...

I would also reiterate my suggestion to take some time out of the coding work to reconsider the database structure - Mary (one of the site administrators) has written this article which you may find helpful..

Regards Purple