I have a table where there can exist rows with duplicate acct_numbers, sales_locations, and sales_dates, BUT have DIFFERENT customer_names. I want to be able to create a way to identify each of these rows as a “1”, meaning they are separate transactions but have these three attributes matching, so I can count the occurrence of customers who subscribed on the same account, at the same location, on the same day.
I’m not too advanced with SQL…any suggestions on how to do this? Thanks.
Something like this will get you what you describe.
SyntaxEditor Code Snippet
Sel cname,acct_num,str_loc,sl_dt,1 from custs where (acct_num,str_loc,sl_dt) in (sel acct_num,str_loc,sl_dt from(sel acct_num,str_loc,sl_dt,count(*) cnt from custs group by 1,2,3 having cnt > 1)c(acct_num,str_loc,sl_dt,cnt) ) order by 2,3,4
Rglass, this appears to work! Thanks! However, I forgot one other validation that needs to occur.
Each row also has a field called TREATMENT (with a Y/N value). In addition to the three matches already mentioned, I only want to apply the "counter" (i.e. 1) when the combination of TREATMENT for these rows are either "Y" and "Y" or "Y" and "N"...I don't want to count the rows when the two columns both have a TREATMENT value of "N".
This is assuming that there are only two rows that have the 3 matches mentioned above (which I believe is the majority of the file)...there are some "acct_num, str_loc,sl_dt" combinations that could have up to 4 rows. If at least one of the rows has a "Y" value then I want all 4 rows to be counted/flagged.
Does this make sense? Thanks again for your time and help!