TheLazyPig
Programmer
Hi!
I wanted to delete only the old records but I ended up deleting both.
To get duplicate records I used having count(*) > 1 then Delete from region.
How can I remove only the records from selected duplicates...
Thank you!
I wanted to delete only the old records but I ended up deleting both.
To get duplicate records I used having count(*) > 1 then Delete from region.
Code:
SET DELETED ON
SET SAFETY OFF
LOCAL pcPopFile,pcCurrDir
*----------------------------------
pcPopFile = "C:\BACKUP_FILES\NETPAY"
pcCurrDir = "C:\BACKUP_FILES\Region"
pcScrPath = pcCurrDir+"\230308\region.dbf"
pcBckPath = pcCurrDir+"\230308\regionbk.dbf"
pcRegion = 'NCR' &®ION
pcAsOf = GOMONTH(DATE(2023,02,01),1)-.1 &&ASOF
pcBkDate = DATE(2023,02,28)
pnCtr = 0
*----------------------------------
IF ATC("REGION.DBF",pcScrPath) = 0
MESSAGEBOX("Invalid File Name!",48)
ELSE
USE (pcScrPath) IN 0 SHARED
SELECT region
COUNT TO lnRegCnt
WAIT WINDOW NOWAIT "Checking for duplicate records....."
**To Check Duplicate Records by Region
SELECT * ;
FROM region ;
WHERE RG+DIV+STA+EMPNO+FNAME+MI+LNAME IN ( ;
SELECT RG+DIV+STA+EMPNO+FNAME+MI+LNAME ;
FROM region ;
WHERE RG = pcRegion ;
GROUP BY 1 ;
HAVING COUNT(*)>1 ;
) ORDER BY EMPNO ;
INTO CURSOR curDup READWRITE
llWithDup = reccount("curDup") > 0
IF llWithDup
MESSAGEBOX("With Duplicate Record Found!")
**Duplicates that are less than asof date
IF asof < pcAsOf
**get count of duplicate
SELECT curDup
COUNT TO lnDupCnt
WAIT WINDOW NOWAIT "No. of Duplicate Records : " + TRANSFORM(lnDupCnt,"###,###,###.##")
**to insert duplicate record to regionbk for history
WAIT WINDOW NOWAIT "....Backup Records...."
=regBackUp()
MESSAGEBOX("Successfully Inserted Records!")
**delete duplicate records where asof less than pcAsOf
[highlight #FCE94F]=deleteDuplicate()[/highlight]
MESSAGEBOX("Successfully Deleted!")
ENDIF
ENDIF
**count of all deleted records from region
SELECT * FROM region WHERE DELETED()
COUNT TO lnDelCnt
WAIT WINDOW NOWAIT "No. of All Deleted Records : " + TRANSFORM(lnDelCnt,"###,###,###.##")
SELECT region
COUNT TO lnTotCnt
MESSAGEBOX("Total Count : " + TRANSFORM(lnTotCnt,"###,###,###.##"))
ENDIF
**To backup deleted records from region to regionbk
PROCEDURE regBackUp
CLOSE ALL
USE (pcBckPath) ALIAS RegionBk
SELECT 0
USE (pcScrPath) IN 0 SHARED ALIAS Region
SELECT * ;
FROM region ;
WHERE RG+DIV+STA+EMPNO IN ( ;
SELECT RG+DIV+STA+EMPNO ;
FROM region ;
WHERE RG = pcRegion ;
GROUP BY 1 ;
HAVING COUNT(*)>1 ;
) AND asof < pcBkDate ;
INTO CURSOR curDup
COUNT TO lnCurDup
WAIT WINDOW NOWAIT "No. of Records Backup : " + TRANSFORM(lnCurDup,"###,###,###.##")
**Insert Duplicate Records in RegionBK (old records)
SELECT Regionbk
APPEND FROM DBF('curDup')
WAIT WINDOW NOWAIT "Append RegionBK with Duplicate Records....."
**Replace blank deleted date to SYSDATE
SELECT RegionBK
REPLACE Deleted WITH DATE() FOR EMPTY(Deleted) IN RegionBK
WAIT WINDOW NOWAIT "Updated empty deleted with system date....."
ENDPROC
**To Delete Duplicate Records
PROCEDURE [highlight #FCE94F]deleteDuplicate[/highlight]
CLOSE ALL
USE (pcScrPath) IN 0 SHARED ALIAS Region
SELECT * ;
FROM region ;
WHERE RG+DIV+STA+EMPNO IN ( ;
SELECT RG+DIV+STA+EMPNO ;
FROM region ;
WHERE RG = pcRegion ;
GROUP BY 1 ;
HAVING COUNT(*)>1 ;
) AND asof < pcBkDate ;
INTO CURSOR toDelete
SELECT region
DELETE FROM region WHERE RG+DIV+STA+EMPNO IN (SELECT * ;
FROM region ;
WHERE RG+DIV+STA+EMPNO IN ( ;
SELECT RG+DIV+STA+EMPNO ;
FROM region ;
WHERE RG = pcRegion ;
GROUP BY 1 ;
HAVING COUNT(*)>1 ;
) AND asof < pcBkDate)
**get count of deleted
SELECT toDelete
COUNT TO lnDelCnt
WAIT WINDOW NOWAIT "No. of Deleted Records : " + TRANSFORM(lnDelCnt,"###,###,###.##")
ENDPROCHow can I remove only the records from selected duplicates...
Thank you!
