11/25/2024 9:38:58 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss reports for SalesLogix including Crystal, SQL Reports, etc. View the code of conduct for posting guidelines.
|
|
|
|
Creating a group to show all duplicate records in DB based on certain criteria...
Posted: 28 Aug 08 3:10 PM
|
Hey everyone. I'm having a rough time figuring this out. I need to display all duplicate Account records where the account names match, the states match and exclude any DUPLICATE records that contain the account type 'CPS'.
I've created test groups many ways. One of which is to create the group and in the Account.Account field use the 'in' condition and put SQL in like the following:
SELECT a.account
FROM account a join address adr
ON
a.addressid = adr.addressid
WHERE
a.type not like '%CPS%'
GROUP BY
a.account, a.type, adr.state
HAVING
COUNT(*) > 1
However this does not work 100%. This does show duplicates, but doesn't narrow them down by state and exclude any dupes that contain 'cps' fopr the type.
Another method I've tried is creating a view and using it to narrow down my search though I'm getting some crazy looping when I do this that way.
Any clues? |
|
|
| |
|
Re: Creating a group to show all duplicate records in DB based on certain criteria...
Posted: 29 Aug 08 7:54 AM
|
Hi John, thank you for your reply.
Even after modifying the select statement to:
SELECT a.account
FROM account a join address adr
ON
a.addressid = adr.addressid and a.type not like '%CPS%'
GROUP BY
a.account, adr.state
HAVING
COUNT(*) > 1
Doesn't do exactly what I need it to do. The group needs to check if even one of the records that meet the dupe criteria has the word CPS in the type field and then to ignore all the "possible" dupes". Then after this it needs to only show the dupes that are in the same state.
Right now it just shows a list of all like company names in order if the company name shows more than once. It's as if the group by "state" and the "not like '%CPS%'" isn't even there at all. I'm missing something. |
|
|
|
Re: Creating a group to show all duplicate records in DB based on certain criteria...
Posted: 29 Aug 08 9:40 AM
|
I've gotten a little farther.
For my group I now have:
----------------------------------------
Account.Account does contain data
Account.Account in: SELECT account FROM account GROUP BY account HAVING COUNT(*) > 1
NOT Account.Account in: SELECT account FROM account GROUP BY account, type HAVING type like '%CPS%'
----------------------------------------
This seems to weed out my account types containing CPS. Now I'm trying to figure out how to get rid of any records that do not have more than one state per account. |
|
|
|
Re: Creating a group to show all duplicate records in DB based on certain criteria...
Posted: 29 Aug 08 10:35 AM
|
Even Further Yet however still not there...
For my group I now have:
----------------------------------------
Account.Account does contain data
Account.Account in:
SELECT a.account
FROM account a join address adr
on a.addressid = adr.addressid
GROUP BY a.account, adr.state
HAVING count(*) > 1
NOT Account.Account in: SELECT account FROM account GROUP BY account, type HAVING type like '%CPS%'
----------------------------------------
This weeds out types containging CPS and any company who is not a dupe based on the state as a criteria as well however still show rows where a single account is located i 1 state if that state holds mutliple accounts.
For example: this is what it shows now:
1 Account 1, FLORIDA
2 Account 1, FLORIDA
3 Account 1, TEXAS
4 Account 1, VIRGINIA
This is what it should show (and only show)
1 Account 1, FLORIDA
2 Account 1, FLORIDA
Any clues? |
|
|
|
Re: Creating a group to show all duplicate records in DB based on certain criteria...
Posted: 29 Aug 08 12:43 PM
|
I figured out how to acheive what I need done.
Since I needed to find dupes based on account and state mostly I created a calculated field called AccountState and chose this as my field to drop into the Query Builder in SLX using a similar select statement show as below (some changes have been made to use more criteria)
Account.AccountStateStatus in:
SELECT (a.account + adr.state + a.status) as ACCOUNT_STATE_STATUS 'i know the as is not needed though I used it anyhow 
FROM account a join address adr
ON a.addressid = adr.addressid
GROUP BY a.account, adr.state, a.status
HAVING count(*) > 1
NOT Account.Account in:
SELECT account FROM account GROUP BY account, type HAVING type like '%CPS%'
Just an FYI incase anyone else would like to know how to catch dupes 
-RJ |
|
|
| |
|
Re: Creating a group to show all duplicate records in DB based on certain criteria...
Posted: 02 Sep 08 11:07 AM
|
| Thanks Phil. You are correct. Luckily this is all we need at the time. Also a quick question. I through some way managed to install 7.2 then uninstall and put 7.1 back on my machine and realized that groups I am creating using joins only work on my machine. I figured out that I am still using the 7.2 provider. I installed the 7.2 provider on my boss's computer and he now can see my groups. Would this cause any problems using a 7.2 provider with 7.1? I can't imagine it would being all of our users besides 3 or 4 are remotes. |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|