Re: Witango-Talk: Incremental Count of Subgroup Instances

[Charles Brown]

Thank you Troy, and Alan too, but I do need each visit numbered (don't ask).  Maintaining the sequential numbering during data entry is ok, I just got foggy trying to figure out how to slam the numbers into an existing database.  It is getting harder and harder to get other people to do my work for me.  Anyhow, I finally came up with the following.  Not rocket science, but seems to work.  I get a three column resultset from the original table (already sorted by client id and date); client id, visit date, and a blank visit count. I use an "<@IF>"to control for changing client id, and add the incremental count to the resultset array, starting the count over each time the id changes.   I then use the correctly numbered array to update the "Visit#" field in the original table. <@ROWS> <@IF EXPR="(@@resultset[<@CURROW>,1] != <@VAR 'custcode'>)">     <@ASSIGN NAME="custcode" VALUE="@@resultset[<@CURROW>,1]" SCOPE="local">     <@ASSIGN NAME="temp_count" VALUE="0" SCOPE="local"> </@IF>         <@ASSIGN NAME="temp_count" VALUE="<@CALC EXPR=<@VAR temp_count>+1>" SCOPE="local"> <@ASSIGN "resultset[<@CURROW>,3]" VALUE="<@VAR temp_count>" SCOPE="Local"> </@ROWS> <@ASSIGN "local$l_results" VALUE="@@resultset" SCOPE="Local"> Troy Sosamon wrote: Charles, Do you really need to know when the x visit was, or could you get by with the date of last one and total number of visits. Then you could make a view or a select something like this: select username, (max(visit_date)), (count(*)) from visitTable group by username If you just want one use add a where clause before the group by "where username = @@myuserbar" Troy Sosamon -----Original Message----- From: Charles Brown [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 02, 2003 6:17 PM To: [EMAIL PROTECTED] Subject: Witango-Talk: Incremental Count of Subgroup Instances This has got to be easier than I am making it. I have a table of client id numbers and their visit dates. I need to sort by the client id and date and assign the number of their visit - from 1 to n. - in a visit# field on the corresponding row in the table. So, for example, visit #4 for Fred was on 12/21/2003. Please speak real slow when responding to this request for assistance. I am not real quick on the uptake today. Oh yeah, the table has over 90,000 records in it, so I can't just give up and do it by hand. ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf