Simulating AutoNumber

[NetworkGuy101]

I am trying to simulate an autonumber for a defined query. I would like to choose all records where EventID is Null and eventdate = #Entereddate# ordered by eventtime

Once I get those results I want to loop through them placing a 1 in the first record and then incrementing by one in the following records. Here is my coe so far It is pretty scary looking I know. All help is definitly appreciated.

Joe

<cfset entereddate = &quot;1/28/2004&quot;>

<cfquery name=&quot;AllEvents&quot; datasource=&quot;test&quot;>
SELECT * FROM Events WHERE eventid is Null AND eventdate = '#entereddate#' orderby eventtime desc
</cfquery>
<cfloop query=&quot;eventq&quot;>
<cfquery name=&quot;addeventid&quot; datasource=&quot;test&quot;>
SELECT * FROM Events WHERE eventid is Null AND eventdate = '#entereddate#' order by eventtime desc
</cfquery>
<cfset totals = #addeventid.RecordCount#>
<cfoutput>
<cfset add1 = #totaly# - 1>
</cfoutput>
<cfquery name=&quot;eventid&quot; datasource=&quot;test&quot;>
UPDATE Events SET eventid= (
#add1#

) Where events.eventdate = #entereddate#
</cfquery>

</cfloop>

 

[ecobb]

Why are you executing the exact same query twice? You already have all of the results from the first query, so what's the point in looking all of them up again?

<cfquery name=&quot;AllEvents&quot; datasource=&quot;test&quot;>
SELECT * FROM Events WHERE eventid is Null AND eventdate = '#entereddate#' orderby eventtime desc
</cfquery>

<cfset CurrentRecord = 1>

<cfloop query=&quot;eventq&quot;>
<cfquery name=&quot;eventid&quot; datasource=&quot;test&quot;>
UPDATE Events SET eventid= (#CurrentRecord#)
Where events.eventdate = #entereddate#
</cfquery>
<cfset CurrentRecord = (CurrentRecord + 1)>
</cfloop>


Hope This Helps!

Ecobb

&quot;Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.&quot; - Homer Simpson

 

[r937]

if you want to &quot;simulate&quot; then just use #queryname.CurrentRow# in your CFOUTPUT

but to update the table again? may i ask why? i mean, what happens if another event is added with a time somewhere in the middle? are you going to pull them all out and renumber them?

rudy

http://r937.com/

SQL Consulting

 

[ecobb]

I agree with r937, instead of trying to simulate an autonumber, why not just use one? If you need this number to match back to another table, just use autonumber in that table, reference the second table to that number, and not worry about what order the numbers are in.

Hope This Helps!

Ecobb

&quot;Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.&quot; - Homer Simpson

 

[NetworkGuy101]

Well what happens is we have an event closeout date. The events all happen on different days at different times. After that date is when an event number is to be assigned. My goal is to assign the event number according to date. So all event numbers run 1 - 100 or so for that day. I could easily use an autonumber but that is not what is wanted here. No events will be renumbered after the closeout date. If an event does need does need to be added there is a select few who do that and will statically assign

 

[TruthInSatire]

what is it used for? display, update reference...?

http://thereptilian.com/

 

[NetworkGuy101]

Ok I got what ECOBB gave to work but it is placing the number 80 in the eventid field. Excuse the change in Datasources one set is for work and one is what I do at home. Which seems to be happening maybe because there are 80 Records. Not Sure though.

<cfset form.eventDate = &quot;#Form.Months#/#Form.Days#/#Form.Years#&quot;>

<cfparam name=&quot;FORM.EventDate&quot; default=&quot;1&quot;>
<cfquery name=&quot;Allevents&quot; datasource=&quot;IGNEW&quot;>
SELECT * FROM MSELcopy WHERE format(EventDate, 'M/DD/YYYY') = '#form.eventdate#'
</cfquery>
<cfset CurrentRecord = 1>
<cfloop query=&quot;Allevents&quot;>
<cfquery name=&quot;eventid&quot; datasource=&quot;IGNEW&quot;>
UPDATE MSELcopy SET eventid= (#CurrentRecord#)
Where format(EventDate, 'M/DD/YYYY') = '#form.eventdate#'
</cfquery>
<cfset CurrentRecord = (CurrentRecord + 1)>
</cfloop>

<cflocation url=&quot;form1.cfm&quot;>

 

[NetworkGuy101]

(Bombboy)I use this for a few things. For output to a Excel Doc, a live event Command and control, searching for an event. When events take place they will need to be referenced by the Event ID and not the event name. So what I am tasked with is creating an event ID for each event upon the close out date. Doing this dynamically on that date and separating it by event date is ideal for me. It actually will have many uses. It seems like as soon as I create something neat to play with they always ask for more.

 

[ecobb]

Do any of your events have the same date? This query is going to update EVERY record whith the same date EVERY TIME it's run.

<cfquery name=&quot;eventid&quot; datasource=&quot;IGNEW&quot;>
UPDATE MSELcopy SET eventid= (#CurrentRecord#)
Where format(EventDate, 'M/DD/YYYY') = '#form.eventdate#'
</cfquery>

So, as it loops through, every record with the date '1/21/2004' will have the eventid set to 1, then 2, then 3, then 4...

You have to have some way to uniquely identify each record in your update query in order to update that record.


Hope This Helps!

Ecobb

&quot;Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.&quot; - Homer Simpson

 

[NetworkGuy101]

Ok I see it now. But How is every event with the same eventdate getting 80 as the ID. I think that is the amount of records that are returned with that date so is that the amount of times the loop will run? Would it worked if I said only where eventid is Null and Eventdate is equal to form.evendate. That way once it assigns the first record a number it will not be picked up in the query loop again. I think I get it but not to sure.

 

[ecobb]

Correct, it is returning 80 becuase that's the number of records you're looping through (returned from the first query). Each time it loops through, it sets the value eventid to whatever &quot;CurrentRecord&quot; is, which is the number of records it's looped through so far. So all eventid's will be set to 1, then 2, then 3, then 4, etc...

Would it worked if I said only where eventid is Null and Eventdate is equal to form.evendate
That wouldn't work because your query is going to update every record at the same time. So, assuming all eventid's are null by default, all eventid's would be set to the first value in the loop and nothing else would get updated (beacuse none of them are null now.)

You have to have some type of unique identifier/primary key on the table, right? I mean, there has to be some way to distinguish between the records. Since you're using SELECT *, there must be something in the results that you can use the distinguish each record. Whatever it is, it needs to be added to your update query so you can specify which particular record you want to update.



Hope This Helps!

Ecobb

&quot;Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.&quot; - Homer Simpson

 

[NetworkGuy101]

Is there a way to out put the current record in a query? Like if it was the second record found in that query to ouput that number?

 

[NetworkGuy101]

Or Current record number rather

 

[NetworkGuy101]

Ok Well I figured it out. and I think I have figured it out without having to create a second table but havent tried it yet. I used some of what ECobb Said. I created a second table and then just queried the table that had the data I wanted and then inserted it into my second table with a loop.

Heres the code. Thanks ALL!

<cfquery name=&quot;MSELinjects&quot; datasource=&quot;IGNEW&quot;>
SELECT * FROM MSELtest WHERE
Complete = 'No' AND Remove = 'No' order by eventdate, eventtime
</cfquery>


<cfquery name=&quot;MSELinsert&quot; datasource=&quot;IGNEW&quot;>
SELECT * FROM MSELtest1
</cfquery>
<cfset currentrecord = (0)>
<cfloop query=&quot;MSELinjects&quot;>
<cfquery name=&quot;autonumber&quot; datasource=&quot;IGNEW&quot;>
SELECT EventID FROM MSELtest1
</cfquery>
<cfset CurrentRecord = (CurrentRecord + 1)>
<cfquery datasource=&quot;IGNEW&quot;>
INSERT INTO MSELtest1 (Eventtype, Eventnumber, EventDate, Eventtime, Locations, UnitEvaluated, Event, EstDuration, OPR,
OCR, EETMember, EETUnit, Methodofinput, Remarks, complete) VALUES (
<cfif isdefined(&quot;MSELinjects.EventDate&quot AND #LSDateformat(mselinjects.eventdate, 'M/DD/YYYY')# lte '1/27/2004'>
'P1-'
<cfelse>
'P2-'
</cfif>
,
<cfif IsDefined(&quot;currentrecord&quot AND #currentrecord# NEQ &quot;&quot;>
'#currentrecord#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined(&quot;MSELinjects.EventDate&quot AND #MSELinjects.EventDate# NEQ &quot;&quot;>
'#MSELinjects.EventDate#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined(&quot;MSELinjects.EventTime&quot AND #MSELinjects.EventTime# NEQ &quot;&quot;>
'#MSELinjects.EventTime#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined(&quot;MSELinjects.Locations&quot AND #MSELinjects.Locations# NEQ &quot;&quot;>
'#MSELinjects.Locations#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined(&quot;MSELinjects.UnitEvaluated&quot AND #MSELinjects.UnitEvaluated# NEQ &quot;&quot;>
'#MSELinjects.UnitEvaluated#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined(&quot;MSELinjects.Event&quot AND #MSELinjects.Event# NEQ &quot;&quot;>
'#MSELinjects.Event#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined(&quot;MSELinjects.EstDuration&quot AND #MSELinjects.EstDuration# NEQ &quot;&quot;>
'#MSELinjects.EstDuration#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined(&quot;MSELinjects.OPR&quot AND #MSELinjects.OPR# NEQ &quot;&quot;>
'#MSELinjects.OPR#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined(&quot;MSELinjects.OCR&quot AND #MSELinjects.OCR# NEQ &quot;&quot;>
'#MSELinjects.OCR#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined(&quot;MSELinjects.EETMember&quot AND #MSELinjects.EETMember# NEQ &quot;&quot;>
'#MSELinjects.EETMember#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined(&quot;MSELinjects.EETUnit&quot AND #MSELinjects.EETUnit# NEQ &quot;&quot;>
'#MSELinjects.EETUnit#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined(&quot;MSELinjects.methodofinput&quot AND #MSELinjects.methodofinput# NEQ &quot;&quot;>
'#MSELinjects.methodofinput#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined(&quot;MSELinjects.Remarks&quot AND #MSELinjects.Remarks# NEQ &quot;&quot;>
'#MSELinjects.Remarks#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined(&quot;MSELinjects.complete&quot AND #MSELinjects.complete# NEQ &quot;&quot;>
'#MSELinjects.complete#'
<cfelse>
NULL
</cfif>
)
</cfquery>
</cfloop>