SQL count instances and dependant on result update field on another table.

So I need to count instances of "in ( 'TST', '991', 'TSF', .....)" in the his.[AlarmCode] per [AccountID].
if the count is >= 1 and < 3 and (s.[UserField1] = '') then i want it to change s.[UserField1] to 'M'
if the count is >= 3 and < 16 and (s.[UserField1] = '') then i want it to change s.[UserField1] to 'W'
if the count is >= 16 and < 60 and (s.[UserField1] = '') then i want it to change s.[UserField1] to 'D'
if the count is >= 60 and (s.[UserField1] = '') then i want it to change s.[UserField1] to 'D'
I can't seem to get the syntax correct, i have fragments that i know i have correct, even if i need to change them.
[SUBSCRIBER].dbo.[Subscriber Data] S

Open in new window

LEFT JOIN [History0318].dbo.[SignalHistory]his on s.[AccountID] = his.[AccountID]

Open in new window

I know its a mess, i've been tinkering with it for a while now..
count (his.[AlarmCode] = in ( 'TST', '991', 'TSF', .....)) >= 1 and < 3 and (s.[UserField1] = '') then update s.[UserField1] = 'M'

Open in new window

any help in putting together this query would be appreciated, as i am most definitely lost.
LVL 1
csePixelatedAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
EDIT: Not quite following the relationships. Can you post your current query so we can see how the table records relate to each other?

Also, are you ONLY updating that fields or is this part of a larger query?
0
Scott PletcherSenior DBACommented:
UPDATE S
SET [UserField1] = CASE
    WHEN [AlarmCode_Count] >= 60 THEN 'D'
    WHEN [AlarmCode_Count] >= 16 THEN 'D'
    WHEN [AlarmCode_Count] >=  3 THEN 'W'
    WHEN [AlarmCode_Count] >=  1 THEN 'M'
    ELSE '?' END
FROM [SUBSCRIBER].dbo.[Subscriber Data] S
INNER JOIN (
    SELECT [AccountID], COUNT(*) AS [AlarmCode_Count]
    FROM [History0318].dbo.[SignalHistory]
    WHERE [AlarmCode] IN ('991', 'TSF', 'TST')
    GROUP BY AccountID
) AS his ON his.[AccountID] = S.[AccountID]
WHERE [S.UserField1] = ''
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
csePixelatedAuthor Commented:
Scott p, would that not just set all of them to m, or is there a priority in the order?
0
Scott PletcherSenior DBACommented:
Good point, I was just gonna add a comment about that!

One thing to know about a CASE statement is that it always processes only the first match.  So, you're right, 61 would indeed be > 1, so it would have matched the last condition, but only if it gets to it.  The earlier match short-circuits the process.
1
Scott PletcherSenior DBACommented:
For example (adjusted to be even clearer what is matching):

SELECT
    AlarmCode_Count,
    [UserField1] = CASE
    WHEN [AlarmCode_Count] >= 60 THEN 'D2'
    WHEN [AlarmCode_Count] >= 16 THEN 'D1'
    WHEN [AlarmCode_Count] >=  3 THEN 'W'
    WHEN [AlarmCode_Count] >=  1 THEN 'M'
    ELSE '?' END
FROM (
    VALUES(23),(45),(1),(61),(4)
) AS test_data(AlarmCode_Count)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.