StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Laurence has it right.</p> <p>Indents are my (and everybody's) friend when SQL code starts looking like this. If a derived set is indented, it's easier to see where its definition starts and ends:</p> <pre><code>SELECT some columns FROM (lots of hideous code) SetA WHERE Things AND More Things </code></pre> <p>I've snipped out most of the hideousness from the code-snippet you're unlucky enough to be dealing with:</p> <pre><code>CREATE TABLE #ACSResults (AreaID VARCHAR(4), Location VARCHAR(50),MonthName VARCHAR(6), RepResolve FLOAT, ERP FLOAT) INSERT INTO #ACSResults SELECT -- ColListStart 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 -- ColListEnd FROM (/*Eurgh! Hideous legacy code that makes my eyes bleed just glancing at it*/) 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, & 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 --------------------------------------------------------------------------------REPEAT TABLE------------------------------------------------------------------------------------------------------ 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 -- ColList Start eh.AreaID, vl.VirtualLocationDescription, Month (acs.statdate) AS MonthName, Cast(Sum(acs.Repeats2Hr)as float) /nullif(Sum(acs.Calls2Hr), 0) as 'HourRepeatPercent' -- COlListEnd From /* More hideous legacy code */ </code></pre> <p>The "Columns in INSERT and SELECT must match" error you're getting will disappear if you just make sure that the columns in the SELECT match those in the table referenced by the INSERT (my comments -ColListStart and -ColListEnd define this area). So you'd add your extra column to each CREATE TABLE, and then add it (perhaps, initially, just as the literal</p> <pre><code>'AnAreaDirector' AS AreaDirector </code></pre> <p>) to the SELECT statement, in the right place in the order of course. You'll nned a GROUP BY AreaDirector at the end of each SELECT (i.e. after everything else) as well.</p> <p>Then the real fun starts, which is digging into the stuff within the () (which I've snipped out), and figuring out whether you can grab the AD column from the tables referenced within them, or whether you need to join to yet another table.</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