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?
 
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
 
_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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.