SQL 2000 Adding a column identifier

[dhutton]

Hello everyone,

My query below works fine - but I'm having problems with identifying each of the 4 reports that are run in here. I need to have a column that has the name of the report run which are as follows: (This runs ALL at once)

1) Cable TV

2) Cable Modem

3) ADSL

4) Cingular

Each report is clearly listed below - but I just want to add something into my WHERE statement that would identify each report.



--============= Report for Cable TV, Cable Modem, ADSL and Cingular

SELECT COUNT(DISTINCT so.OrderType) AS Total, so.Order# AS OrderNumber, so.soCreated AS Order_Date, so.OrderType AS OType, sv7.InOut, IssuedByEmp#,



CASE IssuedbyEmp#
WHEN '201' THEN 'David Crosby'
WHEN '401' THEN 'Wanda Roberts'
WHEN '402' THEN 'Shirley Tisdale'
WHEN '406' THEN 'Betty Jo Broom'
WHEN '407' THEN 'Patricia Murphy'
WHEN '411' THEN 'Juli Christopher'
WHEN '412' THEN 'Tiawanna Dinkins'
WHEN '420' THEN 'Debbie Hoyle'
WHEN '424' THEN 'Mary Alice Brown'
WHEN '430' THEN 'Nicole Ramsey'
WHEN '432' THEN 'Gail Granger'
WHEN '438' THEN 'Tonya Wilks'
WHEN '463' THEN 'Karen Silvia'
WHEN '464' THEN 'Robin Gurganious'
WHEN '532' THEN 'Lori Miller'
WHEN '555' THEN 'Pam McBrayer'
WHEN '551' THEN 'Mary Ann Wilks'
WHEN '470' THEN 'Minnie Neal'
WHEN '471' THEN 'Cres Caldwell'
WHEN '477' THEN 'Amanda Peake'
WHEN '473' THEN 'Alice Holmes'
WHEN '450' THEN 'Lisa Kelly'
WHEN '474' THEN 'Gloria Langly'
WHEN '425' THEN 'Gene Underwood'
WHEN '469' THEN 'Mary Black'
WHEN '482' THEN 'Debra Singleton'
WHEN '480' THEN 'Jenifer Nelson'
WHEN '481' THEN 'Michael Davis'
WHEN '484' THEN 'Angie Devenport'
WHEN '422' THEN 'Richard Johnson'
WHEN '540' THEN 'Name Unknown'

END AS EmpName,



CASE so.OrderType
-- ========================================= Order Type (1)
WHEN 'NI' THEN 'New Install'
WHEN 'DS' THEN 'Disconnect'
WHEN 'RE' THEN 'Reconnect'
WHEN 'MI' THEN 'Misc Install'
WHEN 'CE' THEN 'Change Equipment'
WHEN 'BR' THEN 'Billing Record'
WHEN 'MR' THEN 'Misc Remove'
WHEN 'TR' THEN 'Transfer Service'
WHEN 'NC' THEN 'Number Change'
WHEN 'IN' THEN 'Internet'
WHEN 'NO' THEN 'Number Out'
WHEN 'DI' THEN 'Cing Activation'
WHEN 'DY' THEN 'Cing Reinstall'
WHEN 'CI' THEN 'Cing Port In'
WHEN 'DT' THEN 'Disconnect'

END AS Order_Type,


[Company] =
CASE
-- ============================================ Exchange (2)
WHEN so.PhoneNum LIKE '377%' THEN 'Chester'
WHEN so.PhoneNum LIKE '385%' THEN 'Chester'
WHEN so.PhoneNum LIKE '581%' THEN 'Chester'
WHEN so.PhoneNum LIKE '482%' THEN 'Great Falls'
WHEN so.PhoneNum LIKE '789%' THEN 'Chester'
WHEN so.PhoneNum LIKE '635%' THEN 'Fairfield'
WHEN so.PhoneNum LIKE '633%' THEN 'Fairfield'
WHEN so.PhoneNum LIKE '712%' THEN 'Fairfield'
WHEN so.PhoneNum LIKE '815%' THEN 'Fairfield'
WHEN so.PhoneNum LIKE '337%' THEN 'Ridgeway'
WHEN so.PhoneNum LIKE '338%' THEN 'Ridgeway'
WHEN so.PhoneNum LIKE '545%' THEN 'Lockhart'
WHEN so.PhoneNum LIKE '272%' THEN 'Camden'
WHEN so.PhoneNum LIKE '408%' THEN 'Camden'
WHEN so.PhoneNum LIKE '424%' THEN 'Camden'
WHEN so.PhoneNum LIKE '425%' THEN 'Camden'
WHEN so.PhoneNum LIKE '432%' THEN 'Camden'
WHEN so.PhoneNum LIKE '438%' THEN 'Camden'
WHEN so.PhoneNum LIKE '713%' THEN 'Camden'
--================================================== ================== Area code and Exchange (3)
WHEN so.PhoneNum LIKE '803377%' THEN 'Chester'
WHEN so.PhoneNum LIKE '803385%' THEN 'Chester'
WHEN so.PhoneNum LIKE '803581%' THEN 'Chester'
WHEN so.PhoneNum LIKE '803482%' THEN 'Chester'
WHEN so.PhoneNum LIKE '803789%' THEN 'Chester'
WHEN so.PhoneNum LIKE '803635%' THEN 'Fairfield'
WHEN so.PhoneNum LIKE '803633%' THEN 'Fairfield'
WHEN so.PhoneNum LIKE '803712%' THEN 'Fairfield'
WHEN so.PhoneNum LIKE '803815%' THEN 'Fairfield'
WHEN so.PhoneNum LIKE '803337%' THEN 'Ridgeway'
WHEN so.PhoneNum LIKE '803338%' THEN 'Ridgeway'
WHEN so.PhoneNum LIKE '803545%' THEN 'Lockhart'
WHEN so.PhoneNum LIKE '803272%' THEN 'Camden'
WHEN so.PhoneNum LIKE '803408%' THEN 'Camden'
WHEN so.PhoneNum LIKE '803424%' THEN 'Camden'
WHEN so.PhoneNum LIKE '803425%' THEN 'Camden'
WHEN so.PhoneNum LIKE '803432%' THEN 'Camden'
WHEN so.PhoneNum LIKE '803438%' THEN 'Camden'
WHEN so.PhoneNum LIKE '803713%' THEN 'Camden'
WHEN so.PhoneNum LIKE '803482%' THEN 'Great Falls'
--================================================== ================================Cable TV PhoneNumber (4)
WHEN so.PhoneNum LIKE '999%' THEN 'Chester'
WHEN so.PhoneNum LIKE '998%' THEN 'Camden'
WHEN so.PhoneNum LIKE '997%' THEN 'Great Falls'
WHEN so.PhoneNum LIKE '8030%' THEN 'Winnsboro'
WHEN so.PhoneNum LIKE '996%' THEN 'Chester'
---================================================== ==================== Bill Cycle (5)
WHEN so.BillCycle = '1' THEN 'Chester'
WHEN so.BillCycle = '7' THEN 'Chester'
WHEN so.BillCycle = '20' THEN 'Chester'
WHEN so.BillCycle = '21' THEN 'Chester'
WHEN so.BillCycle = '51' THEN 'Winsboro'
WHEN so.BillCycle = '81' THEN 'Camden'
WHEN so.BillCycle = '82' THEN 'Camden'

END,


CASE
WHEN sv7.InOut = 'I' THEN 'IN'
WHEN sv7.InOut = 'O' THEN 'OUT'
ELSE ' '

END AS In_Out




FROM ctcmaster.dbo.ServiceOrders AS so INNER JOIN
ctcsec.dbo.SV02 AS sv2 ON
so.order# = sv2.SV2SO# INNER JOIN
ctcsec.dbo.SV07 AS sv7 ON
so.order# = sv7.Order# INNER JOIN
ctcsec.dbo.BLSEIT AS bl
ON sv7.Item = bl.SEIITM LEFT JOIN
ctcsec.dbo.SV11 AS sv11 ON
so.Order# = sv11.S11SO#
--================================================== ================================================== ===================

--***************************
--- Cable TV WHERE ====> * <><><><><>----------->
--***************************


--*** Cable In

WHERE so.OrderType IN ('NI', 'RE') AND so.Status <> 'D' AND sv7.Status <> 'D' AND bl.SEITRF = ' ' AND bl.SEISTP = ' ' AND sv7.ITEM IN ('BNDMX', 'BNDST', 'BNDSH', 'BNDHB', 'BNDSP', 'BNDSN','MAXCH', 'STRCH', 'SHOWT', 'HBOCH', 'SPCHS', 'SNCHS', 'AA00', 'AA000', 'AA001', 'AA01', 'AA10', 'PRIME', 'AB00', 'AB01', 'AB10', 'FULL', 'FLCHS', 'TOTAL', 'TLCHS', 'HDALL' ) AND sv7.InOut = 'I'
AND so.soCreated BETWEEN '20070901' AND '20070930'
OR

---*** BVP In
so.OrderType IN ('NI', 'RE') AND so.Status <> 'D' AND sv7.Status <> 'D' AND bl.SEITRF = ' ' AND bl.SEISTP = ' ' AND sv7.ITEM LIKE 'BVP%' AND sv7.InOut = 'I' AND so.soCreated BETWEEN '20070901' AND '20070930'
AND so.soCreated BETWEEN '20070901' AND '20070930'
OR

---*** Cable Disconnect
so.OrderType = 'DS' AND so.Status <> 'D' AND sv7.Status <> 'D' AND bl.SEITRF = ' ' AND bl.SEISTP = ' ' AND sv7.ITEM IN ('BNDMX', 'BNDST', 'BNDSH', 'BNDHB', 'BNDSP', 'BNDSN','MAXCH', 'STRCH', 'SHOWT', 'HBOCH', 'SPCHS', 'SNCHS', 'AA00', 'AA000', 'AA001', 'AA01', 'AA10', 'PRIME', 'AB00', 'AB01', 'AB10', 'FULL', 'FLCHS', 'TOTAL', 'TLCHS', 'HDALL' ) AND sv7.InOut = 'O' AND so.soCreated BETWEEN '20070901' AND '20070930'
AND so.soCreated BETWEEN '20070901' AND '20070930'
OR


---*** BVP Disconnect
so.OrderType = 'DS' AND so.Status <> 'D' AND sv7.Status <> 'D' AND bl.SEITRF = ' ' AND bl.SEISTP = ' ' AND sv7.ITEM LIKE 'BVP%' AND sv7.InOut = 'O' AND so.soCreated BETWEEN '20070901' AND '20070930'
AND so.soCreated BETWEEN '20070901' AND '20070930'
OR

--================================================== ================================================== ===================

--***************************
--- Cable Modem WHERE ====> * <><><><><>----------->
--***************************

---*** CableModem In
so.OrderType IN ('CE', 'IN', 'NI', 'CE', 'IN', 'RE', 'MI', 'BR', 'NC') AND sv7.Status <> 'D' AND so.Status <> 'D' AND sv7.InOut = 'I' AND bl.SEITRF = ' ' AND bl.SEISTP = ' ' AND sv7.Item = bl.SEIITM AND bl.SEICLS = 'IT' AND bl.SEICAT = 'MD'
AND so.soCreated BETWEEN '20070901' AND '20070930'
OR

---*** CableModem Disconnect
so.OrderType = 'DS' AND sv7.Status <> 'D' AND so.Status <> 'D' AND sv7.InOut = 'O' AND bl.SEITRF = ' ' AND bl.SEISTP = ' ' AND sv7.Item = bl.SEIITM AND bl.SEICLS = 'IT' AND bl.SEICAT = 'MD'
AND so.soCreated BETWEEN '20070901' AND '20070930'
OR

---*** CableModem Out's
so.OrderType IN ('MI', 'MR', 'BR', 'CE', 'NC', 'NO') AND sv7.Status <> 'D' AND so.Status <> 'D' AND sv7.InOut = 'O' AND bl.SEITRF = ' ' AND bl.SEISTP = ' ' AND sv7.Item = bl.SEIITM AND bl.SEICLS = 'IT' AND bl.SEICAT = 'MD'
AND so.soCreated BETWEEN '20070901' AND '20070930'
OR

--================================================== ================================================== ===================

--*************************
---==== ADSL WHERE =====> * <><><><><>------------>
--*************************


---*** ADSL In
(sv7.Item IN ('ADSL')) AND (sv7.Status <> 'D') AND (so.Status <> 'D') AND (sv7.InOut = 'I') AND (so.OrderType IN ('NI', 'RE', 'MI', 'BR', 'CE')) AND (bl.SEITRF = ' ') AND
(bl.SEISTP = ' ') AND so.soCreated BETWEEN '20070901' AND '20070930'

OR

---*** ADSL Disconnect
(sv7.Item IN ('ADSL')) AND (sv7.Status <> 'D') AND (so.Status <> 'D') AND (sv7.InOut = 'O') AND (so.OrderType = 'DS') AND (bl.SEITRF = ' ') AND
(bl.SEISTP = ' ') AND so.soCreated BETWEEN '20070901' AND '20070930'
OR

---*** ADSL Out
(sv7.Item IN ('ADSL')) AND (sv7.Status <> 'D') AND (so.Status <> 'D') AND (sv7.InOut = 'O') AND (so.OrderType IN ('MR', 'CE', 'BR', 'MI')) AND
(bl.SEITRF = ' ') AND (bl.SEISTP = ' ') AND so.soCreated BETWEEN '20070901' AND '20070930'

OR

--================================================== ================================================== ===================

--****************
-- Cingular ===> * <><><><><>----------------->
--****************

---*** Cingular In
so.OrderType IN ('DI', 'DY', 'CI') AND sv7.Status <> 'D' AND so.Status <> 'D' AND bl.SEITRF = ' ' AND bl.SEISTP = ' ' AND sv7.InOut = 'I'
AND so.soCreated BETWEEN '20070901' AND '20070930'
OR

---*** Cingular Disconnects
so.OrderType IN ('DI', 'DY', 'CI', 'DT') AND sv7.Status <> 'D' AND so.Status <> 'D' AND bl.SEITRF = ' ' AND bl.SEISTP = ' ' AND sv7.InOut = 'O'
AND so.soCreated BETWEEN '20070901' AND '20070930'

--================================================== ================================================== ===================



GROUP BY IssuedbyEmp#, so.Order#, so.soCreated, so.OrderType, sv7.InOut, so.PhoneNum, so.BillCycle

ORDER BY sv7.InOut, so.soCreated, EmpName

[iburyak]

Can you do separate reports or union first by each report which hardcode column marking report name and then group by?

Your group by mixes up all reports together.

[ck9663]

you might also want to use a table with emp# and name and just join it rather than have this long CASE WHEN.... code...you might have problem maintaining your code. this means whenever there are new employee, you have to fix your code

if you have a table, you just add new row on the emp table...

--CK