troubleshooting Question

Where and in how many places have I messed up this Query?

Avatar of csePixelated
csePixelatedFlag for United States of America asked on
Microsoft SQL ServerSQL
5 Comments1 Solution78 ViewsLast Modified:
I'm stuck, I have very little experience in SQL, and it has already been interesting trying to find little differences in how to write a query for MS SQL 2012 when I was barely familiar with MS SQL 2008. I have the flowing query (apologies on how choppy it is, as it has evolved while I wrestled with each new error)
UPDATE 
 [SUBSCRIBER].[dbo].[Subscriber Data]
 SET
  [AcctType] = UPPER(AcctType)


UPDATE S
SET S.[AcctType] = CASE
	WHEN S.[AcctLineCode] = 'XA' 
			THEN 'ANSWER'
	WHEN S.[AcctLineCode] IN ('XX', 'XD', 'XIT', 'XT', 'YE', 'ZR', 'ZZ', 'XS8', 'XS5', 'XR9', 'XR8', 'XR4', 'XR1', 'XF' , 'X5', 'X19' , 'X13' )  
			THEN 'INFO'
	WHEN S.[AcctLineCode] IN ('DA1', 'DC1', 'DC5', 'DR1', 'DR3', 'DR4', 'DR7', 'DR9', 'DS1', 'DS5', 'DS8', 'D11', 'DV7', 'DV9' )  
			THEN 'DISCONNECTED'
	WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE 'UL/C%'  
			THEN 'UL/COMMERCIAL (S3/D)' 
	WHEN hisb.[OC_Count] >= 10 
		AND his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE 'UL/C%'  
			THEN 'UL/COMMERCIAL (R3/D)'
	WHEN his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE 'UL/C%'  
			THEN 'UL/COMMERCIAL (1/D)'
    WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE '%COM%'  
			THEN 'COMMERCIAL (S3/D)' 
	WHEN hisb.[OC_Count] >= 10 
		AND his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE '%COM%'  
			THEN 'COMMERCIAL (R3/D)'
	WHEN his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE '%COM%'  
			THEN 'COMMERCIAL (1/D)'
	WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE 'RE%'  
			THEN 'RESIDENTIAL (S3/D)' 
	WHEN hisb.[OC_Count] >= 10 
		AND his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE 'RE%'  
			THEN 'RESIDENTIAL (R3/D)'
	WHEN his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE 'RE%'  
			THEN 'RESIDENTIAL (T/D)'
	WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE 'MED%'  
			THEN 'MEDICAL (S3/D)' 
	WHEN hisb.[OC_Count] >= 10 
		AND his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE 'MED%'  
			THEN 'MEDICAL (R3/D)'
	WHEN his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE 'MED%'  
			THEN 'MEDICAL (1/D)'
	WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE 'UL/C%'  
			THEN 'UL/COMMERCIAL (S3/W)' 
	WHEN hisb.[OC_Count] >= 10 
		AND his.[AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE 'UL/C%'  
			THEN 'UL/COMMERCIAL (R3/W)'
	WHEN [AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE 'UL/C%'  
			THEN 'UL/COMMERCIAL (1/W)'
    WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE '%COM%'  
			THEN 'COMMERCIAL (S3/W)' 
	WHEN hisb.[OC_Count] >= 10 
		AND his.[AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE '%COM%'  
			THEN 'COMMERCIAL (R3/W)'
	WHEN [AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE '%COM%'  
			THEN 'COMMERCIAL (1/W)'
	WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE 'RE%'  
			THEN 'RESIDENTIAL (S3/W)' 
	WHEN [OC_Count] >= 10 
		AND his.[AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE 'RE%'  
			THEN 'RESIDENTIAL (R3/W)'
	WHEN [AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE 'RE%'  
			THEN 'RESIDENTIAL (T/W)'
	WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE 'MED%'  
			THEN 'MEDICAL (S3/D)' 
	WHEN hisb.[OC_Count] >= 10 
		AND his.[AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE 'MED%'  
			THEN 'MEDICAL (R3/D)'
	WHEN [AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE 'MED%'  
			THEN 'MEDICAL (1/D)'
	WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 1 
		AND S.[AcctType] LIKE 'UL/C%'  
			THEN 'UL/COMMERCIAL (S3/M)' 
	WHEN hisb.[OC_Count] >= 10 
		AND his.[AlarmCode_Count] >= 1 
		AND S.[AcctType] LIKE 'UL/C%'  
			THEN 'UL/COMMERCIAL (R3/M)'
	WHEN [AlarmCode_Count] >= 1 
		AND S.[AcctType] LIKE 'UL/C%'  
			THEN 'UL/COMMERCIAL (1/M)'
    WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 1 
		AND S.[AcctType] LIKE '%COM%'  
			THEN 'COMMERCIAL (S3/M)' 
	WHEN hisb.[OC_Count] >= 10 
		AND his.[AlarmCode_Count] >= 1 
		AND S.[AcctType] LIKE '%COM%'  
			THEN 'COMMERCIAL (R3/M)'
	WHEN his.[AlarmCode_Count] >= 1 
		AND S.[AcctType] LIKE '%COM%'  
			THEN 'COMMERCIAL (1/M)'
	WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 1 
		AND S.[AcctType] LIKE 'RE%'  
			THEN 'RESIDENTIAL (S3/M)' 
	WHEN hisb.[OC_Count] >= 10 
		AND his.[AlarmCode_Count] >= 1 
		AND S.[AcctType] LIKE 'RE%'  
			THEN 'RESIDENTIAL (R3/M)'
	WHEN his.[AlarmCode_Count] >= 1 
		AND S.[AcctType] LIKE 'RE%'  
			THEN 'RESIDENTIAL (T/M)'
    WHEN S.[AcctType] LIKE 'UL/C%'  
			THEN 'UL/COMMERCIAL (1/-)'
    WHEN S.[AcctType] LIKE 'COM%'  
			THEN 'COMMERCIAL (1/-)'
    WHEN S.[AcctType] LIKE 'RE%'  
			THEN 'RESIDENTIAL (T/-)'
	WHEN S.[AcctType] LIKE 'MED%'  
			THEN 'MEDICAL (T/-)'
    ELSE '?' END
FROM [SUBSCRIBER].[dbo].[Subscriber Data] S
INNER JOIN (
    SELECT [AccountID], COUNT(*) AS [AlarmCode_Count]
    FROM [History0119].[dbo].[SignalHistory] his
    WHERE [AlarmCode] IN ('991', 'TSF', 'TST')
    GROUP BY AccountID
) AS his ON his.[AccountID] = S.[AccountID]
INNER JOIN (
    SELECT [AccountID], COUNT(*) AS [OC_Count]
    FROM [History0119].[dbo].[SignalHistory] his
	LEFT JOIN [History1218].[dbo].[SignalLog] Sl ON his.[signalID] = Sl.[signalID]
    WHERE his.[AlarmCode] IN ('ACL', 'AOP', 'CL-',
 'CLA', 'CLC', 'CLE', 'CLL', 'CLO', 'CLS', 'CLP',
 'COC', 'DEC', 'DIS', 'FCE', 'KCL', 'KOP', 'NYC',
 'NYO', 'OCU', 'OP-', 'OPA', 'OPD', 'OPE', 'OPL',
 'OPN', 'OPP', 'OPS', 'RCL', 'RCO', 'ROP', 'TS1',
 'TS2', 'XCT') 
		AND Sl.[fAction] NOT IN ('* Account is in TEST Mode
', '* Zone/Code is in TEST Mode
'                                   
            )
    GROUP BY AccountID
) AS hisb ON his.[AccountID] = S.[AccountID]

INNER JOIN (
    SELECT [AccountID], COUNT(*) AS [Ts_Count]
    FROM [SUBSCRIBER].[dbo].[Time Schedules] TS
    GROUP BY AccountID
) AS ts ON ts.[AccountID] = S.[AccountID]

Open in new window


I know it is long, I'm sure there is a way to make it smaller, however currently I am perplexed as to why my result is.

(18618 rows affected)

(0 rows affected)  

So my simple question is where and in how many places have I messed up as the column is now all upper case, but that is it.
TS1.png
SUB1.png
SUB2.png
THIS0119SH.png
THIS0119SL.png
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros