March 30, 2016 at 5:26 pm
Hello all,
I need some help with a query that adds numbers into a table queue. Problem now is that it puts every number in even is there are more than one of the same number. Therefore someone can have their number inserted more than once. What I would like to do is take all the distinct numbers out of the temp table.
Here is the current query:
SELECT e.sub_id, e.country_code, e.sub_user_number, @messageBatchKey, message =
CASE
WHEN s.lang = 'english' THEN @messageValue
WHEN s.lang = 'spanish' THEN @messageValuesp
WHEN s.lang = 'creole' THEN @messageValue
ELSE @messageValue
END
FROM SUBSCRIPTION s INNER JOIN #distinctSubscriptionKeys d
ON s.sub_id = d.distinctSubscriptionKey
INNER JOIN PHONENUMBERS e
ON s.sub_id = e.sub_id
LEFT JOIN sirens t
ON s.sub_id = t.sub_id
WHERE e.comm_type = 'v'
AND s.active = '1'
AND t.sub_id IS NULL --Don't insert sirens
ORDER BY d.distinctSubscriptionKey desc
As you can see the subscription table is the primary table supported by the phonenumbers table that are linked by sub_id. What we need to do is grab the unique e.sub_user_number.
Any help would be greatly appreciated and if this is the wrong forum area, I apologize.
Thank you for any assistance!!!!!
March 30, 2016 at 6:12 pm
Scott,
Pretty good post for a rookie, but you're missing part of the question. We can't see your computer, so we can't see your database or the tables in it, so you need to provide us some of that...
1. CREATE TABLE scripts to create the tables relevant to the question.
2. INSERT scripts to populate the relevant tables with enough data to be able to understand/answer the question with working code.
If the data is confidential, feel free to muddle it or whatever. The structure is probably most important here.
Stealing a line from Sean Lange's signature line:
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
March 30, 2016 at 6:44 pm
...
INNER JOIN #distinctSubscriptionKeys d ON s.sub_id = d.distinctSubscriptionKey
INNER JOIN (
select sub_id, country_code, sub_user_number
from PHONENUMBERS
WHERE e.comm_type = 'v'
GROUP BY sub_id, country_code, sub_user_number) e ON s.sub_id = e.sub_id
LEFT JOIN sirens t
...
_____________
Code for TallyGenerator
March 31, 2016 at 6:24 am
Thank you for responding!!!
Sorry for not providing the needed information. I would have but it is very sensitive and by the time I would be authorized to provide the table structure and everything else that would be logical to help in assisting, a new version of MSSQL would be released 🙂
However I thought of another option....
What if I left everything the way it is and just added a delete statement like the following:
delete outgoing_voice
where sub_user_number IN (
SELECT TOP (100) PERCENT sub_user_number
FROM outgoing_voice
GROUP BY sub_user_number
HAVING (COUNT(*) > 1))
To give the full picture, this select statement is within an insert statement.... so the full statement is:
INSERT INTO outgoing_voice (sub_id, country_code, sub_user_number, message_log_id, message)
SELECT e.sub_id, e.country_code, e.sub_user_number, @messageBatchKey, message =
CASE
WHEN s.lang = 'english' THEN @messageValue
WHEN s.lang = 'spanish' THEN @messageValuesp
WHEN s.lang = 'creole' THEN @messageValue
ELSE @messageValue
END
FROM SUBSCRIPTION s INNER JOIN #distinctSubscriptionKeys d
ON s.sub_id = d.distinctSubscriptionKey
INNER JOIN PHONENUMBERS e
ON s.sub_id = e.sub_id
LEFT JOIN sirens t
ON s.sub_id = t.sub_id
WHERE e.comm_type = 'v'
AND s.active = '1'
AND t.sub_id IS NULL --Don't insert sirens
ORDER BY d.distinctSubscriptionKey desc
March 31, 2016 at 7:09 am
Whoops.... Actually that will obviously delete all numbers that have more than one. How can I adjust that delete query to delete all but one number???
Thanks!!!!
March 31, 2016 at 7:23 am
We're not asking for actual customer data. Just data that looks like it enough to write queries.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 31, 2016 at 7:31 am
Yeah I know.... but my higher ups would never understand that... it's a pain.
But I think I found a solution..... building off the delete approach:
DELETE FROM outgoing_voice
WHERE sub_id NOT IN
(
SELECT MIN(sub_id)
FROM outgoing_voice
GROUP BY sub_user_number
)
March 31, 2016 at 8:51 am
That might be the solution for you. Or it might be completely wrong. We cannot assess that without knowing more about your data and situation.
As Gail already writes, wo do not need -nay, we do not WANT to see your sensitive data. But we do need to see something. For my part, you can anonimyze everything. Change all table and column names. Use fake data that does not look at all like your real data. Just post a script that has CREATE TABLE statements, INSERT statements, and a query that is based off your real query and that exposes the issue you are now seeing. Add to that the results you WANT to have, and then we can probably help you. (After which you will have the task of mapping all the table and column names back to the original and testing the living daylights out of it).
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply