asked on
Where and in how many places have I messed up this Query?
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)
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
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]
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
Microsoft SQL ServerSQL
Last Comment
csePixelated