Table myTable
field names------Type----number of characters
mDate-------------D------8
Vs----------------C------200
Debit-------------N------12 with 2 decimals
output from curDups
minvs------mdate----------cntdups
conso------05/28/2013------3
henr-------08/28/2013------2
key--------09/16/2013------2
menu-------09/18/2013------2
>BTW, what do you mean by 'text' field? Are you using VFP 9 table? If yes, what is the structure of your table (list all columns with their types).
>
>>Vs is a text field. Could that have thrown off the code? Here is the print out with minVs added in:
>>
>>
>>mdate---------Vs-------mRec-----keepInfo---cntDups---minVs
>>05/28/2013---menu------2----------0----------3-------conso
>>05/28/2013---conso-----3----------1----------3-------conso
>>05/28/2013---direc-----4----------0----------3-------conso
>>08/28/2013---henry----18----------1----------2-------conso
>>08/28/2013---tran-----19----------0----------2-------conso
>>09/16/2013---texa-----25----------0----------2-------conso
>>09/16/2013---key------26----------1----------2-------conso
>>
>>
>>>For the first date 05/28/2013 it should be A3 marked as 1 as KeepInfo. If that's not correct, then I am not sure what is the bug here. Add curDups.MinVs into result in order to see.
>>>
>>>>Thank you!! I corrected a few spelling errors, in the second SQL statement. Hopefully I didn't break anything:
>>>>
>>>>
>>>>select min(mVend) as MinVs, MDate, COUNT(*) as cntDups ;
>>>>from ch5886 ;
>>>>GROUP BY MDate ;
>>>>HAVING COUNT(*) > 1 into cursor curDups NOFILTER
>>>>
>>>>SELECT T.*, IIF(T.mVend = curDups.MinVs, 1, 0) as KeepInfo, curDups.cntDups ;
>>>>from ch5886 T INNER JOIN curDups ON T.MDate = curDups.MDate into cursor curFinal nofilter
>>>>BROWSE width 10
>>>>
>>>>
>>>>Here is my result. I don't know how to consistently format a table, here; Anyways,the keepInfo is inconsistent, sometimes the first occurance is 0, sometimes it is 1, sometimes there is more than one 0, as shown below? I changed the value of the V's, to show that it really does not matter what they are. I just want to keep the info in the first Vs, each time there is more than on Vs per date.
>>>>
>>>>
>>>>mdate Vs mrec keepInfo cntdups
>>>>05/28/2013 V1 2 0-->0 3
>>>>05/28/2013 V5 3 1 3
>>>>05/28/2013 A3 4 0-->0 3
>>>>08/28/2013 Ba 18 1-->0 2
>>>>08/28/2013 B2 19 0 2
>>>>09/16/2013 Bc 25 0-->0 2
>>>>09/16/2013 R8 26 1 2
>>>>09/18/2013 R2 28 1-->1 2
>>>>09/18/2013 Z1 29 1 2
>>>>
>>>>
>>>>Thank you!
>>>>
>>>>>You can use an intermediate cursor, e.g.
>>>>>
>>>>>select min(Vs) as MinVs, MDate, COUNT(*) as cntDups ;
>>>>>from myTable ;
>>>>>GROUP BY MDate ;
>>>>>HAVING COUNT(*) > 1 into cursor curDups NOFILTER
>>>>>
>>>>>SELECT T.*, IIF(T.Vs = Dup.MinVs, 1, 0) as KeepInfo, Dup.cntDups ;
>>>>>from MyTable T INNER JOIN curDups Dup ON T.MDate = Dups.MDate into cursor curFinal nofilter
>>>>>
>>>>>
>>>>>
>>>>>In this case KeepInfo will tell you if you need to print or not. The resulting cursor will only contain records where you have duplicate dates. If you need all records from your table, use a LEFT JOIN instead of the INNER JOIN and change condition to Dup.mDate IS NULL or T.Vs = Dup.MinVs.
>>>>>
>>>>>
>>>>>>>>>>Hi, Is is possible to use an sql statement(s) to count and number duplicates? The original table looks something like this, showing an excerpt where there are three entries for one date:
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>Mdate Vs
>>>>>>>>>>05/28/2013 v1
>>>>>>>>>>05/28/2013 v1
>>>>>>>>>>05/28/2013 v1
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>The following is a start in that it will give me a table that shows how many duplicates exist for each date.
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>SELECT mdate,COUNT(mdate) FROM ch5 ;
>>>>>>>>>> INTO TABLE dups;
>>>>>>>>>> GROUP BY mdate ;
>>>>>>>>>> HAVING COUNT(mdate) > 1
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>Here is a part of the report that the above produces:
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>mdate cnt_mdate
>>>>>>>>>>05/28/2013 3
>>>>>>>>>>08/28/2013 2
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>And by using set relationship and a do while loop, I can count and label the duplicates. But I was just wondering if an SQL statement(s) could do it all? The final output that I need is::
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>Mdate cnt/date Vs
>>>>>>>>>>05/28/2013 1 v1
>>>>>>>>>>05/28/2013 2
>>>>>>>>>>05/28/2013 3
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>Thanks,
>>>>>>>>>>Steve
>>>>>>>>>
>>>>>>>>>To add sequential numbers to records, read the section "Including record numbers in results" in
http://www.tomorrowssolutionsllc.com/Conference%20Sessions/Solving%20Common%20Problems%20with%20VFP%27s%20SQL.pdf. Basically, you use one query to collect the results and another to add RECNO() to the field list.
>>>>>>>>>
>>>>>>>>>Tamar
>>>>>>>>
>>>>>>>>Thank you! The part you refer to is on p. 19. But I don't want to insert recno()'s per se. (I shouldn't have skipped at step.) I wondered if I could number the instances where there are multiple occurances for a given date, perhaps resulting in a numbering schema as shown in cnt/date or new_index; because, once I see something like this:
>>>>>>>>
>>>>>>>>
>>>>>>>>Mdate cnt/date new_index Vs
>>>>>>>>05/28/2013 1 2013052801 v1
>>>>>>>>05/28/2013 2 2013052802 v2
>>>>>>>>05/28/2013 3 2013052803 v3
>>>>>>>>
>>>>>>>>
>>>>>>>>I need to blank out all but the first of the multiple Vs's for a given date, for a final report that will look like this:
>>>>>>>>
>>>>>>>>
>>>>>>>>Mdate cnt/date Vs
>>>>>>>>05/28/2013 1 v1
>>>>>>>>05/28/2013 2
>>>>>>>>05/28/2013 3
>>>>>>>>
>>>>>>>
>>>>>>>Ah, yeah, what I missed was numbering within each group, once you've selected the records of interest. I see that you're using SQL Server, though, so in fact, this is easy using OVER and the ROW_NUMBER function. Check out this article on my site:
http://www.tomorrowssolutionsllc.com/Articles/Getting%20the%20Top%20N%20for%20each%20Group.pdf, in particular, the part that begins with "The SQL Server Solution."
>>>>>>>
>>>>>>>Tamar
>>>>>>
>>>>>>Again, my mistake, I should have indicated that am using solamente VFP 9.0 SP2. Do I need SQL server to do this? If so, I will shift. I am just looking for a simpler solution than coding everything with loops in VFP. Thanks again. Steve