StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>To put some flesh on Clockwork Muse's ideas. If you're allowed to create tables, start with (replace the nvarchars with whatever your database actually has)</p> <pre><code>Create Table dbo.DepartmentGroupLookup ( PeopleSoftDepartmentID nvarchar(30) Primary Key Not Null, DeptGroup nvarchar(30) not null ) Insert Into dbo.DepartmentGroupLookup (PeopleSoftDepartmentID, DeptGroup) Values ('9180', 'Bilingual'), ('<<WEBILING>>', 'Bilingual'), ..., ('9150', 'BSC'), ... etc Create Function dbo.LookupLocation( @VirtualLocationID as int, @VirtualLocationDescription nvarchar(30), @VirtualLocationTypeID int, @PeopleSoftDepartmentID nvarchar(30) ) Returns nvarchar(30) As Begin Return Case When @VirtualLocationID = 102 Then 'HQ Quality - GSC' When @PeopleSoftDepartmentID In ('9165', '9166','9167') Then 'GSC' When @PeopleSoftDepartmentID ='<<OSVAOL>>' And @VirtualLocationID = 64 Then 'ALORICA AOL' When @VirtualLocationID In ('99', '148') And @PeopleSoftDepartmentID In ( '9030', '9080', '9355', '9040', '<<OSCCALTECH>>', '<<WDTSMO>>', '9195','<<OSVTS>>' ) Then 'Wichita Falls (WDS) - Tech' When @VirtualLocationID Is Null Or @VirtualLocationTypeID = 4 Then 'Others' Else @VirtualLocationDescription End End </code></pre> <p>Do a similar thing for the Area lookup, now your first inner query becomes</p> <pre><code>Select dbo.LookupArea(eh.virtuallocationID, v1.virtuallocationtypeid, vl.AreaId, eh.PeopleSoftAreaID, eh.PeopleSoftDepartmentID, acs.OfferDate, eh.EmployeeTypeID) As Area, -- There may be other dependencies dbo.LookupLocation(eh.VirtualLocationID, vl.VirtualLocationDescription, vl.VirtualLocationTypeID, eh.peoplesoftDepartmentid) As Location, IsNull(dgl.DeptGroup, 'Other') 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 Outer Join dbEmployee.Summary.vwEmployeeHistory eh On acs.EmployeeID = eh.EmployeeID And acs.OfferDate Between eh.StartDate And eh.EndDate Left Outer Join dbEmployee.Config.vwName Rep On eh.EmployeeID = Rep.EmployeeID Left Outer Join dbEmployee.Config.vwName Sup On eh.BottomUp01ID = Sup.EmployeeID Left Outer Join dbEmployee.Config.vwName Mgr On eh.BottomUp02ID = Mgr.EmployeeID Left Outer Join dbEmployee.Config.vwName Dir On eh.BottomUp03ID = Dir.EmployeeID Left Outer Join dbEmployee.Config.vwVirtualLocation vl On eh.VirtualLocationID = vl.VirtualLocationID Left Outer Join dbEmployee.Config.vwDepartment d On eh.DepartmentID = d.DepartmentID Left Outer Join dbEmployee.Config.vwPeopleSoftDepartment psd On eh.PeopleSoftDepartmentID = psd.PeopleSoftDepartmentID Left Outer Join dbo.DepartmentGroupLookup dgl On eh.PeopleSoftDepartmentID = dgl.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 </code></pre> <p>The goal is to keep simplifying (or at least moving stuff out of sight) in this way until it's possible to understand what's going on.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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