Databases
StackOverflow2013
Postsdbo
POInclude manager hierarchy
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POInclude manager hierarchy
    primarykey
    data
    text
    <p>I've been given this code that currently ranks Call Centers based on given criteria. I've been tasked with changing this ranking from Center based to Associate Director (AKA AD, Manager, BottomUpLevel02). </p> <p>The problem is, I don't have a lot of experience working with actually creating tables. I typically manipulate views so I'm sure there are some syntax rules that I'm seriously lacking here. </p> <p>Here's the code:</p> <pre><code>SET NOCOUNT ON DECLARE @StartDate SMALLDATETIME, @EndDate SMALLDATETIME SET @StartDate = '2012/07/01' SET @EndDate = '2012/07/31' IF OBJECT_ID('tempdb..#ACSResults') IS NOT NULL DROP TABLE #ACSResults CREATE TABLE #ACSResults (AreaID VARCHAR(4), Location VARCHAR(50), MonthName VARCHAR(6), RepResolve FLOAT, ERP FLOAT) INSERT INTO #ACSResults SELECT a.area, a.location, a.monthname, CASE WHEN SUM(CASE WHEN a.RepResolve IN ('1','0') THEN 1 ELSE 0 END) = 0 THEN NULL ELSE CAST(SUM(CASE WHEN a.RepResolve = '1' THEN 1 ELSE 0 END)AS FLOAT) / CAST(SUM(CASE WHEN a.RepResolve IN ('1','0') THEN 1 ELSE 0 END) AS FLOAT) END AS REPRESOLVE, CASE WHEN SUM(CASE WHEN a.ERP IN ('0','1','2','3','4','5','6','7','8', '9', '10') THEN 1 ELSE 0 END) = 0 THEN NULL ELSE (CAST(SUM(CASE WHEN a.ERP IN ('8', '9', '10') THEN 1 ELSE 0 END) AS FLOAT) / CAST(SUM(CASE WHEN a.ERP in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10') THEN 1 ELSE 0 END) AS FLOAT))END AS ERP FROM (SELECT CASE WHEN vl.virtuallocationtypeid =13 THEN 'PP' WHEN eh.PeopleSoftDepartmentID IN ('2922', '9050', '9136', '9130','9134','9135', '9137', '9060') THEN 'FS' WHEN eh.PeopleSoftDepartmentID ='2390' THEN 'CMO' WHEN eh.virtuallocationtypeid = 3 OR eh.peoplesoftDepartmentid IN ('9165', '9166','9157','9167','&lt;&lt;OSVBSC&gt;&gt;') THEN 'BSC' WHEN (eh.virtuallocationid IN ('111', '113', '114', '115', '116', '118', '119', '120', '122', '123') AND acs.OfferDate &lt; '04/01/2011') OR (eh.virtuallocationid IN ('116','120','123') AND acs.OfferDate &gt;= '04/01/2011') THEN 'SPC2' WHEN (eh.virtuallocationid IN ('112', '121', '110') AND acs.OfferDate &gt;= '07/01/2011') THEN 'SPC2' WHEN (eh.virtuallocationid IN ('99') AND acs.OfferDate &lt; '07/01/2011') AND eh.PeopleSoftDepartmentID IN ('9030', '9080', '9355', '9040', '&lt;&lt;OSCCALTECH&gt;&gt;', '&lt;&lt;WDTSMO&gt;&gt;', '9195','&lt;&lt;OSVTS&gt;&gt;') THEN 'SPC2' WHEN eh.EmployeeTypeID = 'V' THEN 'SPC' WHEN vl.virtuallocationtypeid =14 THEN 'SPC2' WHEN (eh.virtuallocationID IS NULL AND eh.AreaId IS NULL) THEN eh.PeopleSoftAreaID WHEN eh.virtuallocationID IS NULL THEN eh.AreaID ELSE vl.AreaId END AS Area, CASE WHEN eh.VirtualLocationID = 102 THEN 'HQ Quality - GSC' WHEN eh.peoplesoftDepartmentid IN ('9165', '9166','9167') THEN 'GSC' WHEN eh.peoplesoftdepartmentid ='&lt;&lt;OSVAOL&gt;&gt;' AND eh.virtuallocationid =64 THEN 'ALORICA AOL' WHEN (eh.virtuallocationid IN ('99', '148') AND eh.PeopleSoftDepartmentID IN ('9030', '9080', '9355', '9040', '&lt;&lt;OSCCALTECH&gt;&gt;', '&lt;&lt;WDTSMO&gt;&gt;', '9195','&lt;&lt;OSVTS&gt;&gt;')) THEN 'Wichita Falls (WDS) - Tech' WHEN eh.virtuallocationID IS NULL OR vl.VirtualLocationTypeID = 4 THEN 'Others' ELSE vl.VirtualLocationDescription END AS Location, CASE WHEN eh.PeopleSoftDepartmentID IN ( '9180', '&lt;&lt;WEBILING&gt;&gt;','&lt;&lt;OSVSPN&gt;&gt;') THEN 'Bilingual' WHEN eh.PeopleSoftDepartmentID IN ( '9150', '9440', '9370', '9260', '9330', '9460','9155','9215','9157','9175','&lt;&lt;OSVBSC&gt;&gt;') THEN 'BSC' WHEN eh.PeopleSoftDepartmentID IN ( '9395', '9010', '9341', '9340', '9450', '9131', '9025', '10014901', '&lt;&lt;SO_WEVIS&gt;&gt;', '&lt;&lt;WE_MWVIS&gt;&gt;', '&lt;&lt;WEVIS&gt;&gt;', '&lt;&lt;NEVIS&gt;&gt;', '&lt;&lt;OSCCALTEL&gt;&gt;', '&lt;&lt;SOVIS&gt;&gt;', '&lt;&lt;NE_MWVIS&gt;&gt;', '&lt;&lt;MWVIS&gt;&gt;', '&lt;&lt;OSVCS&gt;&gt;') THEN 'Care' WHEN eh.PeopleSoftDepartmentID IN ('9030', '9080', '9355', '9040', '&lt;&lt;OSCCALTECH&gt;&gt;', '&lt;&lt;WDTSMO&gt;&gt;', '9195','&lt;&lt;OSVTS&gt;&gt;' ) THEN 'Tech' WHEN eh.PeopleSoftDepartmentID IN ('&lt;&lt;AOL_WEVIS&gt;&gt;', '&lt;&lt;OSVAOL&gt;&gt;') THEN 'AOL' WHEN eh.peoplesoftdepartmentid IN ('&lt;&lt;OSVCS_TCS&gt;&gt;', '&lt;&lt;OSVOB_TCS&gt;&gt;') THEN 'New Hire Transition' WHEN eh.PeopleSoftDepartmentID = '9390' THEN 'EPP' WHEN eh.PeopleSoftDepartmentID = '9120' THEN 'ERT' WHEN eh.PeopleSoftDepartmentID = '9190' THEN 'IRT' WHEN eh.PeopleSoftDepartmentID = '9085' THEN 'Bilingual Tech' WHEN eh.PeopleSoftDepartmentID IN ('9490', '&lt;&lt;LNP&gt;&gt;','&lt;&lt;OSVLNP&gt;&gt;') THEN 'LNP' WHEN eh.PeopleSoftDepartmentID = '9070' THEN 'Retention' WHEN eh.PeopleSoftDepartmentID = '9115' THEN 'GLOBAL' WHEN eh.PeopleSoftDepartmentID IN ('&lt;&lt;ONEBILL&gt;&gt;', '&lt;&lt;OSVOB&gt;&gt;') THEN 'Onebill' WHEN eh.peoplesoftdepartmentid = '&lt;&lt;PPCARE&gt;&gt;' THEN 'Prepaid Care' WHEN eh.peoplesoftdepartmentid = '&lt;&lt;PPTECH&gt;&gt;' THEN 'Prepaid Tech' ELSE 'Other' END AS DeptGroup, psd.PeopleSoftDepartmentDescription AS Dept, acs.IVRCallID, acs.OfferDate, acs.EmployeeID, acs.ACSSUserID, Cast(acs.DidRepResolve as Float) As RepResolve, acs.Question1 AS FCR, acs.Question2 AS ERP, acs.Question3 AS NPS, acs.ACSSCallID, month (acs.Offerdate) AS MonthName FROM dbCustomerSurvey.Detail.vwAfterCallSurvey acs LEFT JOIN dbEmployee.Summary.vwEmployeeHistory eh ON acs.EmployeeID = eh.EmployeeID AND acs.OfferDate BETWEEN eh.StartDate AND eh.EndDate LEFT JOIN dbEmployee.Config.vwName Rep ON eh.EmployeeID = Rep.EmployeeID LEFT JOIN dbEmployee.Config.vwName Sup ON eh.BottomUp01ID = Sup.EmployeeID LEFT JOIN dbEmployee.Config.vwName Mgr ON eh.BottomUp02ID = Mgr.EmployeeID LEFT JOIN dbEmployee.Config.vwName Dir ON eh.BottomUp03ID = Dir.EmployeeID LEFT JOIN dbEmployee.Config.vwVirtualLocation vl ON eh.VirtualLocationID = vl.VirtualLocationID LEFT JOIN dbEmployee.Config.vwDepartment d ON eh.DepartmentID = d.DepartmentID LEFT JOIN dbEmployee.Config.vwPeopleSoftDepartment psd ON eh.PeopleSoftDepartmentID = psd.PeopleSoftDepartmentID WHERE acs.CurrentStatus IN ('Completed', 'COMP') AND acs.surveytype IN ('ACS_FCR_Rep_Perform', 'ACS_FCR_Rep_Perform-prepaid', 'ACS_Rep_Perform_BSC2', 'ACS_Rep_Perform_Gov') AND acs.OfferDate BETWEEN @StartDate AND @EndDate) a WHERE a.Area IN ('WE', 'mw','ne','so') AND a.Location NOT IN ('West Area Staff - CS', 'Northeast Area Staff - CS', 'Midwest Area Staff - CS', 'Others', 'West Area Finance - CS', 'Midwest Area Marketing, Sales, &amp; Training', 'South Area Staff - CS', 'Midwest Area Finance - CS', 'Bellevue - CS') AND a.deptgroup in ('aol','bilingual','Bilingual Tech','care','global','lnp', 'onebill','other','retention','tech', 'new hire transition') GROUP BY a.area, a.location, a.monthname </code></pre> <p>-- Repeat table</p> <pre><code>IF OBJECT_ID('tempdb..#HRRep') IS NOT NULL DROP TABLE #HRRep CREATE TABLE #HRRep (AreaID VARCHAR(4), Location VARCHAR(50), MonthName VARCHAR(6), HourRepeatPercent FLOAT) INSERT INTO #HRRep (AreaID, Location, MonthName, HourRepeatPercent) SELECT eh.AreaID, vl.VirtualLocationDescription, MONTH (acs.statdate) AS MonthName, CAST(SUM(acs.Repeats2Hr)as FLOAT) / NULLIF(SUM(acs.Calls2Hr), 0) as 'HourRepeatPercent' FROM dbReportSummary.ReportSummary.vwRepeatCalls2Hr acs WITH (NOLOCK) LEFT JOIN dbEmployee.Summary.vwEmployeeHistory eh WITH (NoLOCK) ON acs.EmployeeID = eh.EmployeeID AND acs.StatDate BETWEEN eh.StartDate AND eh.EndDate LEFT JOIN dbEmployee.Config.vwVirtualLocation vl ON eh.VirtualLocationID = vl.VirtualLocationID LEFT JOIN dbEmployee.Config.vwDepartment d ON eh.DepartmentID = d.DepartmentID LEFT JOIN dbEmployee.Config.vwPeopleSoftDepartment psd ON eh.PeopleSoftDepartmentID = psd.PeopleSoftDepartmentID WHERE acs.StatDate BETWEEN @StartDate AND @EndDate AND eh.PeopleSoftDepartmentID IN ('9180', '&lt;&lt;WEBILING&gt;&gt;','&lt;&lt;OSVSPN&gt;&gt;', '9150', '9440', '9370', '9260', '9330', '9460','9155','9215','9157','9175','&lt;&lt;OSVBSC&gt;&gt;', '9395', '9010', '9341', '9340', '9450', '9131', '9025', '10014901', '&lt;&lt;SO_WEVIS&gt;&gt;', '&lt;&lt;WE_MWVIS&gt;&gt;', '&lt;&lt;WEVIS&gt;&gt;', '&lt;&lt;NEVIS&gt;&gt;', '&lt;&lt;OSCCALTEL&gt;&gt;', '&lt;&lt;SOVIS&gt;&gt;', '&lt;&lt;NE_MWVIS&gt;&gt;', '&lt;&lt;MWVIS&gt;&gt;', '&lt;&lt;OSVCS&gt;&gt;', '9030', '9080', '9355', '9040', '&lt;&lt;OSCCALTECH&gt;&gt;', '&lt;&lt;WDTSMO&gt;&gt;', '9195','&lt;&lt;OSVTS&gt;&gt;', '&lt;&lt;AOL_WEVIS&gt;&gt;', '&lt;&lt;OSVAOL&gt;&gt;', '&lt;&lt;OSVCS_TCS&gt;&gt;', '&lt;&lt;OSVOB_TCS&gt;&gt;' , '9390', '9120', '9190', '9085', '9490', '&lt;&lt;LNP&gt;&gt;', '&lt;&lt;OSVLNP&gt;&gt;', '9070', '9115', '&lt;&lt;ONEBILL&gt;&gt;', '&lt;&lt;OSVOB&gt;&gt;', '&lt;&lt;PPCARE&gt;&gt;', '&lt;&lt;PPTECH&gt;&gt;') AND eh.AreaID IN ('MW', 'NE', 'SO', 'WE') AND vl.VirtualLocationDescription NOT IN ('West Area Staff - CS', 'Northeast Area Staff - CS', 'Midwest Area Staff - CS', 'Others', 'West Area Finance - CS') AND eh.Titleid IN ('1','2','3','509') GROUPBY eh.AreaID, vl.VirtualLocationDescription, MONTH (acs.statdate) </code></pre> <p>-- (?) --</p> <pre><code>SELECT a.Location, a.RepResolve, a.RRRank, a.ERP, a.ERPRank,a.HourRepeatPercent, a.RepeatRank, (a.RRRank + a.ERPRank+a.RepeatRank) as 'Total Rank Points', RANK() OVER(ORDER BY (a.RRRank + a.ERPRank+a.RepeatRank) ASC, a.RepResolve DESC) AS 'Overall Rank' FROM (SELECT a.Location, a.RepResolve, RANK() OVER(ORDER BY a.RepResolve DESC) AS RRRank, a.ERP, RANK() OVER(ORDER BY a.ERP DESC) AS ERPRank, b.HourRepeatPercent, RANK() OVER(ORDER BY b.HourRepeatPercent ASC) AS RepeatRank FROM #ACSResults AS A LEFT JOIN #HRRep AS B ON a.areaid = b.areaid AND a.monthname = b.monthname AND a.location = b.location) a GROUP BY a.Location, a.RepResolve, a.RRRank, a.ERP, a.ERPRank,a.HourRepeatPercent, a.RepeatRank, (a.RRRank + a.ERPRank + a.RepeatRank) </code></pre> <p>I keep getting errors about the select clause not matching the insert clause when I try adding AD to the mix. Can anybody please point out the areas that need to be modified in order to include AD? </p> <p>EDIT: I was able to successfully add an AD column! The new problem: The results are a bit off. Before when the results were ranked based on location, each distinct location was assigned a rank based on a total of points. It was clean and the numbers added up. Now there are duplicate ADs and the ranking is backwards (least amount of total ranking points is listed as 1st place). </p> <p>I simply added the AD column to the INSERT statement and the select statements. Can anybody tell me, in addition to adding the AD Column throughout the appropriate locations of the query, should I be modifying any of the operations or functions of the query to reflect a true ranking for the given date range? Please feel free to ask for a clarification on any part of this. Thanks in advance for any assistance you can provide. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USa_horse_with_no_name
    1. USciw916
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POInclude manager hierarchy
      1. USciw916
      1. VTFavorite
    2. VO
      singulars
      1. POInclude manager hierarchy
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    3. VO
      singulars
      1. POInclude manager hierarchy
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThere's an old saying in computer programming, that you should write everything as if the next person to see it is a homicidal axe wielding maniac with your address. I think you should buy an axe.
      singulars
      1. POInclude manager hierarchy
      1. USLaurence
    2. COhaha I concur...alas this is what I am left to work with at the moment. Bleh
      singulars
      1. POInclude manager hierarchy
      1. USciw916
    3. COIt's worth mentioning that most RDBMSs that support the `CREATE TABLE FROM SELECT` bit will take column names and definitions from the columns selected, so you don't have to explicitly list those. Obviously, things like primary keys (and usually constraints) can't be transferred, but it does make table definition simpler.
      singulars
      1. POInclude manager hierarchy
      1. USClockwork-Muse
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload