Scenario :
Solution :
1. Suggest to Update latest Version, if version not fixed
2. Due to incorrect ways import of Credit Limit, caused duplicate Credit limit in Back End Table.
To Check Duplicate Info:
Run below Script :
Select C.id, D.CompanyCode, C.CreditLimit from CreditControl C
JOIN Debtors D
ON D.id = C.GLAccountId
Where (C.Glaccountid) in (
Select Glaccountid
from CreditControl
Group by GLAccountId
Having Count(*) >1
)
Another Script to check from related Table
Run below Script :
Select * from CreditControlDetail where CreditControlId in
(select id from CreditControl Where (Glaccountid) in (
Select Glaccountid
from CreditControl
Group by GLAccountId
Having Count(*) >1
))order by CreditControlId asc
To Remove Duplicate Info:
Run Below Script :
-- temp table to hold duplicated records
declare @tableThatContainsDuplicatedRecords table (id uniqueidentifier, code varchar(100), rowNo int)
-- insert the duplicated items
insert into @tableThatContainsDuplicatedRecords
Select C.id, D.CompanyCode, ROW_NUMBER() OVER (PARTITION BY D.CompanyCode ORDER BY C.id ASC) AS rowNo
from CreditControl C
JOIN Debtors D
ON D.id = C.GLAccountId
Where (C.Glaccountid) in (
Select Glaccountid
from CreditControl
Group by GLAccountId
Having Count(*) >1
)
-- display the duplicated records
select * from @tableThatContainsDuplicatedRecords
-- table to hold records to be deleted
declare @tableThatContainsRecordsToBeDeleted table (id uniqueidentifier)
insert into @tableThatContainsRecordsToBeDeleted
select
id
from @tableThatContainsDuplicatedRecords where rowNo <> 1
-- display the record's ids to be deleted
select * from @tableThatContainsRecordsToBeDeleted
-- delete duplicated items
delete from CreditControlDetail
where CreditControlId in (select id from @tableThatContainsRecordsToBeDeleted)
delete from CreditControl
where Id in (select id from @tableThatContainsRecordsToBeDeleted)
-- display table after deletion
Select C.id, D.CompanyCode, C.CreditLimit from CreditControl C
JOIN Debtors D
ON D.id = C.GLAccountId
Where (C.Glaccountid) in (
Select Glaccountid
from CreditControl
Group by GLAccountId
Having Count(*) >1
)
** Learn more about Azure Cloud
** Learn more about QNE Hybrid Cloud Software