NextVal

25 pts.
Tags:
How can I find the next value in a date field? I want to update a field called VERSION, based on the date an item was approved OR based on its APPROVDATE. The record would look like this: ITEM APPOVDATE VERSION 783 3/25/2008 1.01 783 4/26/2008 1.02 783 4/26/2008 1.02 783 5/17/2008 1.03 783 5/17/2008 1.03 783 5/17/2008 1.03 There are multiple APPROVDATEs because several conditions caused the ITEM to be updated, but the ITEM updates were approved on the same date.

Answer Wiki

Thanks. We'll let you know when a new response is added.

As part of your stored procedure you will need to put logic in there to query for the current value, and if needed incriment the value by what ever amount you need to. In this case.01.

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • JennyMack
    Hi Scanguy, Can you provide a little more information -- what program(s) are you using? Thanks, Jenny Community Manager
    4,280 pointsBadges:
    report
  • Scanguy
    My local data base is in MS Access. My web based data base is MySQL using ColdFusion to interact between the two. My question is to create a query for my local data base in MS Access. I have a WorkingInventory table that contains all the past information about edits and updates we have made to drawings and Land Record plats and maps. When they were edited, what action caused the edit and when the plat/drawing was approved. Now I need to go back and add a version to the data base based on the APPROVDATE. This would link various old drawing to the actions that created the edit or change. The table looks like this now ITEM ACTIONNO APPROVDATE 782 SCANNED 3/30/2005 782 UTU-83420 4/22/2008 782 UTU-83746 4/22/2008 782 UTU-84367 5/17/2008 782 UTU-28910 5/17/2008 782 UTU-87663 5/17/2008 782 UTU-37342 7/25/2008 Now I need to add a field VERSION based on the APPROVDATE for each ITEM. The VERSION will be in the format 1.00 for the original scanned, the first edit would be VERSION 1.01 and the second 1.02 and so on for all ITEM and APPROVDATE. The new table would look like this: ITEM ACTIONNO APPROVDATE VERSION 782 SCANNED 3/30/2005 1.00 782 UTU-83420 4/22/2008 1.01 782 UTU-83746 4/22/2008 1.01 782 UTU-84367 5/17/2008 1.02 782 UTU-28910 5/17/2008 1.02 782 UTU-87663 5/17/2008 1.02 782 UTU-37342 7/25/2008 1.03 I can create the version counter, but I don't know how to find the NEXT DATE in the field APPROVDATE. and increament through the update of the field VERSION.
    25 pointsBadges:
    report
  • carlosdl
    More information is definitely needed here. What you need to do can be done with a loop running through the records ordered by approvdate, checking at each row to increment the version number if the current row's approvdate is greater than the one from the previous record. Regards,
    69,835 pointsBadges:
    report
  • Scanguy
    I have been working on this LOOP, but my problem comes because I have to fields that are incrementing. The ITEM increases after its MaxApproDate has been reached and each ITEM has numerious ApprovDates that increment from oldest ApprovDate to new ApprovDate for that ITEM to set the VERSION, which is also incrementing from 1.00 to what ever. This is where I'm in over my head. I have the Max(ITEM) as MaxItem LOOP while CurItem LESS THAN MaxItem
    25 pointsBadges:
    report
  • carlosdl
    I don't know the syntax you should use to write this, so I'm going to explain the logic of my idea. I see two ways to achieve what is needed: 1) Using two loops, one for the ITEMS, and one (inner) for the records of the current ITEM of the outer loop. Something like this:
    loop to run through all possible items (based on an item catalog, or a select distinct ITEM from the WorkingInventory table)
    	version = 1.00
    	previous_date = <item_creation_date>
    	loop to run thorugh all records of the current item (ordered by approvdate)
    		if <current_record>.approvdate > previous_Date then
    			version = version + 0.01
    		end if;
    		update the current row with the calculated version.
    		previous_date = <current_record>.approvdate
    	end loop
    end loop
    2) Using one loop. Something like this:
    previous_item = 0
    loop to to run through the WorkingInventory table (ordered by ITEM,APPROVDATE
    	if <current_record>.ITEM != previous_item then
    		version = 1.00
    		previous_date = <item_creation_date>
    	else
    		if <current_record>.approvdate > previous_Date then
    			version = version + 0.01
    		end if;
    		update the current row with the calculated version.
    		previous_date = <current_record>.approvdate
    		
    	end if;
    	previous_item = <current_row>.ITEM
    end loop
    That's my idea, which of course need to be reviewed and improved. Hope this helps.
    69,835 pointsBadges:
    report
  • carlosdl
    Hmm, It seems that the "code" tool didn't work. Sorry about that. I hope you can understand the previous suggestion.
    69,835 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following