Easy SQL Select Case When Statement throwing an incorrect syntax error and I can't find it!

Question

Thank you to everyone who tries to help!

I have 2 case select statements and I get the error on this statement:

Center,
Case Center
When 'Concord Call Center' Then 'CCC'
When 'Livermore Call Center' Then 'LCC'
When 'Morgan Hill Call Center' Then 'MHCC'
When 'Natomas Call Center' Then 'NCC'
When 'Virtual Call Center' Then 'VCC'
Else Center
End,

The error I'm getting is: No column was specified for column 14 of 'ANDREWSTABLE'.

Please see below where it exists in the full statement:

Select segstart
    ,Interval
    ,dialed_num
    ,FiscalMonthYear
    ,SegStart_Date
    ,row_date
    ,Name
    ,Xferto
    ,TransferType
    ,Agent
    ,Sup
    ,Manager
    ,Center
    ,Xferfrom
    ,talktime
    ,ANDREWSTABLE.transferred
    ,ANDREWSTABLE.disposition
    ,split1
    ,hsplit.starttime
    ,CASE
    WHEN hsplit.callsoffered > 0 
    THEN (CAST(hsplit.acceptable as DECIMAL)/hsplit.callsoffered)*100
    ELSE '0'
    END AS 'Service Level'
    ,hsplit.callsoffered
    ,hsplit.acceptable
FROM
(
Select segstart,
CASE
--when hour is = 0 and min < 30 then 0
WHEN DATEPART(HOUR, segstart) = 0 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('0' as int)
--when hour is = 0 and min > 30 then 30
WHEN DATEPART(HOUR, segstart) = 0 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('30' as int)
--when hour is 1 and min < 30 then 100
WHEN DATEPART(HOUR, segstart) = 1 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('100' as int)
--when hour is 1 and min > 30 then 130
WHEN DATEPART(HOUR, segstart) = 1 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('130' as int)
--when hour is 2 and min < 30 then 200
WHEN DATEPART(HOUR, segstart) = 2 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('200' as int)
--when hour is 2 and min > 30 then 230
WHEN DATEPART(HOUR, segstart) = 2 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('230' as int)
--when hour is 3 and min < 30 then 300
WHEN DATEPART(HOUR, segstart) = 3 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('300' as int)
--when hour is 3 and min > 30 then 330
WHEN DATEPART(HOUR, segstart) = 3 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('330' as int)
--when hour is 4 and min < 30 then 400
WHEN DATEPART(HOUR, segstart) = 4 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('400' as int)
--when hour is 4 and min > 30 then 430
WHEN DATEPART(HOUR, segstart) = 4 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('430' as int)
--when hour is 5 and min < 30 then 500
WHEN DATEPART(HOUR, segstart) = 5 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('500' as int)
--when hour is 5 and min > 30 then 530
WHEN DATEPART(HOUR, segstart) = 5 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('530' as int)
--when hour is 6 and min < 30 then 600
WHEN DATEPART(HOUR, segstart) = 6 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('600' as int)
--when hour is 6 and min > 30 then 630
WHEN DATEPART(HOUR, segstart) = 6 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('630' as int)
--when hour is 7 and min < 30 then 700
WHEN DATEPART(HOUR, segstart) = 7 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('700' as int)
--when hour is 7 and min > 30 then 730
WHEN DATEPART(HOUR, segstart) = 7 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('730' as int)
--when hour is 8 and min < 30 then 800
WHEN DATEPART(HOUR, segstart) = 8 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('800' as int)
--when hour is 8 and min > 30 then 830
WHEN DATEPART(HOUR, segstart) = 8 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('830' as int)
--when hour is 9 and min < 30 then 900
WHEN DATEPART(HOUR, segstart) = 9 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('900' as int)
--when hour is 9 and min > 30 then 930
WHEN DATEPART(HOUR, segstart) = 9 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('930' as int)
--when hour is 10 and min < 30 then 1000
WHEN DATEPART(HOUR, segstart) = 10 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1000' as int)
--when hour is 10 and min > 30 then 1030
WHEN DATEPART(HOUR, segstart) = 10 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('1030' as int)
--when hour is 11 and min < 30 then 1100
WHEN DATEPART(HOUR, segstart) = 11 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1100' as int)
--when hour is 11 and min > 30 then 1130
WHEN DATEPART(HOUR, segstart) = 11 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('1130' as int)
--when hour is 12 and min < 30 then 1200
WHEN DATEPART(HOUR, segstart) = 12 AND DATEPART(MINUTE, segstart) < 30  THEN CAST('1200' as int)
--when hour is 12 and min < 30 then 1230
WHEN DATEPART(HOUR, segstart) = 12 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('1230' as int)
--when hour is 13 and min < 30 then 1300
WHEN DATEPART(HOUR, segstart) = 13 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1300' as int)
--when hour is 13 and min > 30 then 1330
WHEN DATEPART(HOUR, segstart) = 13 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('1330' as int)
--when hour is 14 and min < 30 then 1400
WHEN DATEPART(HOUR, segstart) = 14 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1400' as int)
--when hour is 14 and min > 30 then 1430
WHEN DATEPART(HOUR, segstart) = 14 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('1430' as int)
--when hour is 15 and min < 30 then 1500
WHEN DATEPART(HOUR, segstart) = 15 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1500' as int)
--when hour is 15 and min > 30 then 1530
WHEN DATEPART(HOUR, segstart) = 15 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('1530' as int)
--when hour is 16 and min < 30 then 1600
WHEN DATEPART(HOUR, segstart) = 16 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1600' as int)
--when hour is 16 and min > 30 then 1630
WHEN DATEPART(HOUR, segstart) = 16 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('1630' as int)
--when hour is 17 and min < 30 then 1700
WHEN DATEPART(HOUR, segstart) = 17 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1700' as int)
--when hour is 17 and min > 30 then 1730
WHEN DATEPART(HOUR, segstart) = 17 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('1730' as int)
--when hour is 18 and min < 30 then 1800
WHEN DATEPART(HOUR, segstart) = 18 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1800' as int)
--when hour is 18 and min > 30 then 1830
WHEN DATEPART(HOUR, segstart) = 18 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('1830' as int)
--when hour is 19 and min < 30 then 1900
WHEN DATEPART(HOUR, segstart) = 19 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1900' as int)
--when hour is 19 and min > 30 then 1930
WHEN DATEPART(HOUR, segstart) = 19 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('1930' as int)
--when hour is 20 and min < 30 then 2000
WHEN DATEPART(HOUR, segstart) = 20 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('2000' as int)
--when hour is 20 and min > 30 then 2030
WHEN DATEPART(HOUR, segstart) = 20 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('2030' as int)
--when hour is 21 and min < 30 then 2100
WHEN DATEPART(HOUR, segstart) = 21 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('2100' as int)
--when hour is 21 and min > 30 then 2130
WHEN DATEPART(HOUR, segstart) = 21 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('2130' as int)
--when hour is 22 and min < 30 then 2200
WHEN DATEPART(HOUR, segstart) = 22 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('2200' as int)
--when hour is 22 and min > 30 then 2230
WHEN DATEPART(HOUR, segstart) = 22 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('2230' as int)
--when hour is 23 and min < 30 then 2300
WHEN DATEPART(HOUR, segstart) = 23 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('2300' as int)
--when hour is 23 and min > 30 then 2330
WHEN DATEPART(HOUR, segstart) = 23 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('2330' as int)
ELSE Null End as Interval,
    FiscalMonthYear,
    SegStart_Date,
    dialed_num,
    callid,
    Name,
    t.Queue AS 'Xferto',
    TransferType,
    RepLName+', '+RepFName AS Agent,
    SupLName+', '+SupFName AS Sup,
    MgrLName+', '+MgrFName AS Manager,
    Center
Case Center
When 'Concord Call Center' Then 'CCC'
When 'Livermore Call Center' Then 'LCC'
When 'Morgan Hill Call Center' Then 'MHCC'
When 'Natomas Call Center' Then 'NCC'
When 'Virtual Call Center' Then 'VCC'
Else Center
End,
    q.Queue AS 'Xferfrom',
    e.anslogin,
    e.origlogin,
    t.Extension,
    transferred,
    disposition,
    talktime,
    split1
From CMS_ECH.dbo.CaliforniaECH e

INNER JOIN Cal_RemReporting.dbo.TransferVDNs t on e.dialed_num = t.Extension
INNER JOIN InfoQuest.dbo.IQ_Employee_Profiles_v3_AvayaId q on e.origlogin = q.AvayaID
INNER JOIN Cal_RemReporting.dbo.udFiscalMonthTable f on e.SegStart_Date = f.Tdate

Where SegStart_Date between getdate()-90 and getdate()-1
    And SegStart_Date between RepToSup_StartDate and RepToSup_EndDate
    And SegStart_Date between SupToMgr_StartDate and SupToMgr_EndDate
    And SegStart_Date between Avaya_StartDate and Avaya_EndDate
    And SegStart_Date between RepQueue_StartDate and RepQueue_EndDate
    AND e.disposition like '4' 
    OR e.transferred like '1'
) AS ANDREWSTABLE

Inner Join CMS_ECH.dbo.hsplit hsplit on hsplit.starttime = ANDREWSTABLE.Interval and hsplit.row_date = ANDREWSTABLE.SegStart_Date and ANDREWSTABLE.split1 = hsplit.split

Where did I go wrong? It says I have incorrect syntax and I have stared and stared and googled and googled and I can't come up with how to correct this! It must be something simple.

Thank you again for all your help!

Answer 1

This part is not well formed:

    ,Center
Case
When 'Concord Call Center' Then 'CCC'
When 'Livermore Call Center' Then 'LCC'
When 'Morgan Hill Call Center' Then 'MHCC'
When 'Natomas Call Center' Then 'NCC'
When 'Virtual Call Center' Then 'VCC'
Else Center
End
    ,q.Queue AS 'Xferfrom'

You either need a comma before Case, or something like Mycenter = instead of the first Center.

Answer 2

You just need an alias for the column name. Since it's a derived table, it requires it.

 Case Center
    When 'Concord Call Center' Then 'CCC'
    When 'Livermore Call Center' Then 'LCC'
    When 'Morgan Hill Call Center' Then 'MHCC'
    When 'Natomas Call Center' Then 'NCC'
    When 'Virtual Call Center' Then 'VCC'
    Else Center
 End as Center