Getting Error "All queries combined using a UNION..." Why??

Question

Getting Error "All queries combined using a UNION..." Why??

david.ostrander

SSCommitted

Points: 1828
More actions
December 14, 2012 at 10:21 am

#266247

Hello -
I have SQL View (which I would like to go on record I did not write it) that has three Union All's in it. After each Select I added the following line...
CASE WHEN SUBSTRING(P.gl_subacct, 1, 3) IN ( 'SPA', 'CSG' ) THEN SUBSTRING(P.gl_subacct, 4, 3)
ELSE class.descr END AS 'Vertical' ,
After each from I added a Inner Join...
INNER JOIN custclass AS class WITH ( NOLOCK ) ON class.classid = c.classid
And after each Group by I added...
class.descr,
When I go to run the script I keep getting the same error message.
Msg 205, Level 16, State 1, Procedure hgvTimeDataFetchWithRates_Rosetta, Line 4
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
I also have included a copy of that View
USE [Rosetta]
GO
/****** Object: View [dbo].[hgvTimeDataFetchWithRates_Rosetta] Script Date: 12/13/2012 22:02:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[hgvTimeDataFetchWithRates_Rosetta] AS
SELECT
C.Name AS 'Customer', C.CustId AS 'CustomerID',
pc.name as 'Parent Company',
C.ClassId AS 'Customer Subaccount',
P.project_desc AS 'Project', P.project AS 'ProjectId',
p.gl_subacct AS 'Project Sub Acct',
det.ld_desc as 'Detail Description',
Emp.emp_name AS 'Resource', Emp.em_id24 AS 'Employee_Level', Emp.employee AS 'ResourceID', Emp.gl_subacct AS 'Employee Subaccount',
pj.pjt_entity AS 'Task ID', pj.pjt_entity_desc AS 'Task', pj.contract_type AS 'Category',
CASE WHEN pj.contract_type = 'BILL' THEN 'Yes' ELSE 'No' END AS Billable,
hdr.pe_date AS 'TimeCard', P.status_pa,
CASE WHEN SUBSTRING(P.gl_subacct, 1, 3) IN ('SPA', 'CSG') THEN SUBSTRING(P.gl_subacct, 4, 3)ELSE class.descr END AS 'Vertical',
--Ps.Description AS 'Vertical', Ps2.Description AS '2nd Segement',
--CASE WHEN SUBSTRING(P.gl_subacct, 1, 3) = 'SPA' THEN ps2.Description WHEN SUBSTRING(P.gl_subacct, 1, 3) = 'CSG' THEN ps2.Description ELSE ps.Description END AS 'ProjectWorkgroup',
Emp1.emp_name AS 'Manager',
Emp2.emp_name AS 'Partner',
EMPs.Description AS 'Resource Division', EMPs2.Description AS 'Resource Department',
CASE WHEN SUBSTRING(Emp.gl_subacct, 1, 3) = 'SPA' THEN emps2.Description WHEN SUBSTRING(Emp.gl_subacct, 1, 3) = 'CSG' THEN emps2.Description ELSE emps.Description END AS 'ResourceWorkgroup',
det.docnbr AS 'Document Number', SUM(det.day1_hr1) AS Day1, SUM(det.day2_hr1) AS Day2, SUM(det.day3_hr1) AS Day3, SUM(det.day4_hr1) AS Day4, SUM(det.day5_hr1) AS Day5, SUM(det.day6_hr1) AS Day6, SUM(det.day7_hr1) AS Day7, SUM(det.total_hrs) AS TotalHours,
SUM(CASE WHEN pj.contract_type = 'BILL' THEN total_hrs ELSE 0 END) AS BillableHours,
CASE WHEN pj.contract_type = 'BILL' THEN (RR.Rate) * SUM(total_hrs) ELSE 0 END AS Revenue,
RR2.Rate * SUM(det.total_hrs) AS Cost,
CASE WHEN pj.contract_type = 'BILL' THEN (RR.Rate) ELSE 0 END AS BillRate,
RR2.Rate AS [Cost Rate],
CASE WHEN det.LD_ID17 = '1' THEN 'Review Required' WHEN det.LD_ID17 = 'A' THEN 'Approved' WHEN det.LD_ID17 = 'R' THEN 'Rejected' WHEN det.LD_ID17 = '' THEN 'No Review Required' END AS [Line Item Status],
CASE WHEN hdr.le_status = 'I' THEN 'In Process' WHEN hdr.le_status = 'A' THEN 'Approved' WHEN hdr.le_status = 'C' THEN 'Completed' WHEN hdr.le_status= 'R' THEN 'Rejected' WHEN hdr.le_status = 'P' THEN 'Posted' WHEN hdr.le_status = 'T' THEN 'Timesheet' WHEN hdr.le_status = 'X' THEN 'Corrected' ELSE hdr.le_status END AS [Time Card Status],
CASE WHEN hdr.le_type = 'R' THEN 'Regular' WHEN hdr.le_type = 'C' THEN 'Correction' ELSE hdr.le_type END AS [Time Card Type],
P.alloc_method_cd AS [Project Allocation Method], SUBSTRING(P.project, 4, 3) AS Brand, SUBSTRING(P.project, 7, 2) AS Year, SUBSTRING(P.project, 9, 2) AS Type,
CASE SUBSTRING(P.Project, 11, 1) WHEN 'N' THEN 'Non-revenue' WHEN 'R' THEN 'Revenue' ELSE SUBSTRING(P.Project, 11, 1) END AS [Revenue Type],
SUBSTRING(P.project, 12, 5) AS [Project Identifier],
empt.code_value_desc as [Employee Type],
loc.code_value_desc as Location,
lev.code_value_desc as Level,
emp.em_id15 as ADP,
fd.name as 'Primary Function',
hdr.fiscalno as 'Fiscal Month',
CASE p.cpnyid WHEN 'RMG' THEN 'Rosetta Marketing Group' WHEN 'RIL' THEN 'Rosetta Interactive Limited' ELSE p.cpnyid END as 'Company',
code.code_value_desc as [Contract Type],
bill.emp_name as [Finance Contact],
s.total_budget_amount as [Contract Amount]
FROM pjlabhdr AS hdr WITH(NOLOCK)
INNER JOIN PJLABDET AS det WITH(NOLOCK) ON det.docnbr = hdr.docnbr
INNER JOIN PJPENT AS pj WITH(NOLOCK) ON pj.pjt_entity = det.pjt_entity AND pj.project = det.project
INNER JOIN PJPROJ AS P WITH(NOLOCK) ON det.project = P.project
INNER JOIN Customer AS C WITH(NOLOCK) ON C.CustId = P.customer
INNER JOIN CustClass AS class WITH(NOLOCK) ON class.classid = c.classid
INNER JOIN PJEMPLOY AS Emp WITH(NOLOCK) ON Emp.employee = hdr.employee
LEFT OUTER JOIN
(SELECT labor_rate as rate, employee,
STARTDATE = effect_date,
ENDDATE = ISNULL(DATEADD(DAY, - 1,
(SELECT TOP 1 effect_date
FROM PJEmpPjt WITH(NOLOCK)
WHERE Employee = EP.Employee
AND effect_date > EP.effect_date
ORDER BY effect_Date asc)
), DATEADD(YEAR, 10, EP.effect_date))
FROM PJEmpPjt AS ep WITH(NOLOCK)) AS RR2 ON RR2.employee = Emp.employee AND pe_date BETWEEN rr2.STARTDATE AND rr2.ENDDATE
INNER JOIN SegDef AS Ps WITH(NOLOCK) ON Ps.ID = SUBSTRING(P.gl_subacct, 1, 3) AND Ps.SegNumber = 1
INNER JOIN SegDef AS Ps2 WITH(NOLOCK) ON Ps2.ID = SUBSTRING(P.gl_subacct, 4, 6) AND Ps2.SegNumber = 2
INNER JOIN SegDef AS EMPs WITH(NOLOCK) ON EMPs.ID = SUBSTRING(Emp.gl_subacct, 1, 3) AND EMPs.SegNumber = 1
INNER JOIN SegDef AS EMPs2 WITH(NOLOCK) ON EMPs2.ID = SUBSTRING(Emp.gl_subacct, 4, 6) AND EMPs2.SegNumber = 2
LEFT OUTER JOIN
(SELECT rate, rate_key_value1, rate_key_value2, rate_table_id,
STARTDATE = effect_date,
ENDDATE = ISNULL(DATEADD(DAY, -1,
(SELECT top 1 effect_date
FROM PJRate WITH(NOLOCK)
WHERE Rate_Key_Value1 = r.Rate_Key_Value1
AND Rate_Key_Value2 = r.Rate_Key_Value2
AND Rate_Table_id = r.Rate_Table_id
AND rate_level = 4 and rate_type_cd = 'BR'
AND effect_date > R.effect_date
ORDER BY effect_date ASC)
), DATEADD(YEAR, 10, R.effect_date))
FROM pjrate r WITH(NOLOCK)) AS RR ON RR.rate_key_value1 = pj.pjt_entity AND RR.rate_key_value2 = Emp.employee AND RR.rate_table_id = P.rate_table_id AND pe_date BETWEEN rr.STARTDATE AND rr.ENDDATE
LEFT OUTER JOIN PJEMPLOY AS Emp1 WITH(NOLOCK) ON Emp1.employee = P.manager1
LEFT OUTER JOIN PJEMPLOY AS Emp2 WITH(NOLOCK) ON Emp2.employee = P.manager2
LEFT OUTER JOIN Changepoint.dbo.Resources r WITH(NOLOCK) ON r.userdefinedresourceid = emp.employee and r.deleted = 0
LEFT OUTER JOIN Changepoint.dbo.FunctionDescription fd WITH(NOLOCK) ON fd.functionid = r.primaryFunctionId
LEFT OUTER JOIN
(select c2.userdefinedcustomerid, c1.name
from Changepoint..Customer c1 WITH(NOLOCK)
right join Changepoint..Customer c2 WITH(NOLOCK) ON c2.ParentCustomerId = c1.CustomerId
where c2.deleted = 0) as PC ON PC.userdefinedcustomerid = c.custid
LEFT OUTER JOIN pjCode code WITH(NOLOCK) ON code.code_value = p.contract_type AND code.code_type = 'CONT'
LEFT OUTER JOIN pjCode loc WITH(NOLOCK) ON loc.code_value = emp.em_id21 AND loc.code_type = 'ELOC'
LEFT OUTER JOIN pjCode lev WITH(NOLOCK) ON lev.code_value = emp.em_id24 AND lev.code_type = 'ELEV'
LEFT OUTER JOIN pjCode empt WITH(NOLOCK) ON empt.code_value = emp.emp_type_cd AND empt.code_type = 'EMPT'
LEFT OUTER JOIN pjbill b WITH(NOLOCK) ON b.project = p.project
LEFT OUTER JOIN pjEmploy bill WITH(NOLOCK) ON bill.employee = b.biller
LEFT OUTER JOIN pjptdsum s WITH(NOLOCK) ON s.project = p.project AND acct = 'Contract Value' AND s.pjt_entity IN ('DFLT')
--where pe_date between '11/1/2010' and '11/23/2010'
--> '1/3/2009' and p.project like 'dbi%'
GROUP BY C.Name, C.CustId, PC.Name, C.ClassId, class.descr, P.project_desc, P.project,
det.ld_desc,
Emp.emp_name, Emp.em_id24, Emp.employee, pj.pjt_entity, pj.pjt_entity_desc,
pj.contract_type, pj.contract_type, hdr.pe_date, P.status_pa, Ps.Description, Ps2.Description, P.gl_subacct, Emp1.emp_name, Emp2.emp_name,
EMPs.Description, EMPs2.Description, Emp.gl_subacct, det.docnbr, RR.rate, RR2.Rate, det.ld_id17, hdr.le_status, hdr.le_type,
P.alloc_method_cd,empt.code_value_desc,
loc.code_value_desc, lev.code_value_desc, emp.em_id15, fd.name, hdr.fiscalno, p.cpnyid, code.code_value_desc, bill.emp_name, s.total_budget_amount
HAVING (SUM(det.total_hrs) <> 0) OR (SUM(det.total_hrs) IS NULL)
UNION ALL
SELECT C.Name AS Customer, C.CustId AS CustomerID, pc.name as [Parent Company], C.ClassId AS [Customer Subaccount], P.project_desc AS Project, P.project AS ProjectId, p.gl_subacct AS 'Project Sub Acct',
hdr.tr_comment AS [Detail Description],
Emp.emp_name AS Resource, Emp.em_id24 AS Employee_Level, Emp.employee AS ResourceID, Emp.gl_subacct AS [Employee Subaccount],
pj.pjt_entity AS [Task ID], pj.pjt_entity_desc AS Task, pj.contract_type AS Category,
CASE WHEN pj.contract_type = 'BILL' THEN 'Yes' ELSE 'No' END AS Billable, hdr.trans_date AS TimeCard, P.status_pa, Ps.Description AS Vertical,
Ps2.Description AS [2nd Segement], Emp1.emp_name AS Manager, Emp2.emp_name AS Partner,
EMPs.Description AS [Resource Division], EMPs2.Description AS [Resource Department],CASE WHEN SUBSTRING(P.gl_subacct, 1, 3) IN ('SPA', 'CSG') THEN SUBSTRING(P.gl_subacct, 4, 3)ELSE class.descr END AS 'Vertical', CASE WHEN SUBSTRING(Emp.gl_subacct, 1, 3) = 'SPA' THEN emps2.Description WHEN SUBSTRING(Emp.gl_subacct, 1, 3) = 'CSG' THEN emps2.Description ELSE emps.Description END AS 'ResourceWorkgroup', 'CHRG' AS [Document Number], SUM(hdr.units) AS Day1,
0 AS Day2, 0 AS Day3, 0 AS Day4, 0 AS Day5, 0 AS Day6, 0 AS Day7, SUM(hdr.units) AS TotalHours,
SUM(CASE WHEN pj.contract_type = 'BILL' THEN units ELSE 0 END) AS BillableHours, CASE WHEN pj.contract_type = 'BILL' THEN (RR.Rate)
* SUM(units) ELSE 0 END AS Revenue, RR2.Rate * SUM(hdr.units) AS Cost, CASE WHEN pj.contract_type = 'BILL' THEN (RR.Rate)
ELSE 0 END AS BillRate, RR2.Rate AS [Cost Rate], 'Approved' AS [Line Item Status], 'Approved' AS [Time Card Status], 'Charge' AS [Time Card Type],
P.alloc_method_cd AS [Project Allocation Method], SUBSTRING(P.project, 4, 3) AS Brand, SUBSTRING(P.project, 7, 2) AS Year, SUBSTRING(P.project, 9,
2) AS Type, CASE SUBSTRING(P.Project, 11, 1) WHEN 'N' THEN 'Non-revenue' WHEN 'R' THEN 'Revenue' ELSE SUBSTRING(P.Project, 11, 1)
END AS [Revenue Type], SUBSTRING(P.project, 12, 5) AS [Project Identifier],
empt.code_value_desc as [Employee Type],
loc.code_value_desc as Location, lev.code_value_desc as Level, emp.em_id15 as ADP, fd.name as [Primary Function], hdr.fiscalno as 'Fiscal Month',
CASE p.cpnyid WHEN 'RMG' THEN 'Rosetta Marketing Group' WHEN 'RIL' THEN 'Rosetta Interactive Limited' ELSE p.cpnyid END as 'Company',
code.code_value_desc as [Contract Type],
bill.emp_name as [Finance Contact], s.total_budget_amount as [Contract Amount]
FROM dbo.PJTRAN AS hdr WITH(NOLOCK) INNER JOIN
-- dbo.PJLABDET AS det WITH(NOLOCK) ON det.ld_desc = hdr.tr_comment INNER JOIN
dbo.PJPENT AS pj WITH(NOLOCK) ON pj.pjt_entity = hdr.pjt_entity AND pj.project = hdr.project INNER JOIN
dbo.PJPROJ AS P WITH(NOLOCK) ON hdr.project = P.project INNER JOIN
dbo.Customer AS C WITH(NOLOCK) ON C.CustId = P.customer INNER JOIN
dbo.CustClass AS class WITH ( NOLOCK ) ON class.classid = c.classid INNER JOIN
dbo.PJEMPLOY AS Emp WITH(NOLOCK) ON Emp.employee = hdr.employee INNER JOIN
dbo.SegDef AS Ps WITH(NOLOCK) ON Ps.ID = SUBSTRING(P.gl_subacct, 1, 3) AND Ps.SegNumber = 1 INNER JOIN
dbo.SegDef AS Ps2 WITH(NOLOCK) ON Ps2.ID = SUBSTRING(P.gl_subacct, 4, 6) AND Ps2.SegNumber = 2 INNER JOIN
dbo.SegDef AS EMPs WITH(NOLOCK) ON EMPs.ID = SUBSTRING(Emp.gl_subacct, 1, 3) AND EMPs.SegNumber = 1 INNER JOIN
dbo.SegDef AS EMPs2 WITH(NOLOCK) ON EMPs2.ID = SUBSTRING(Emp.gl_subacct, 4, 6) AND EMPs2.SegNumber = 2
LEFT OUTER JOIN
(SELECT rate, rate_key_value1, rate_key_value2, rate_table_id, STARTDATE = effect_date,
ENDDATE =
ISNULL(DATEADD(DAY, -1, (SELECT top 1 effect_date
FROM PJRate WITH(NOLOCK)
WHERE Rate_Key_Value1 = r.Rate_Key_Value1 AND
Rate_Key_Value2 = r.Rate_Key_Value2
AND Rate_Table_id = r.Rate_Table_id AND
rate_level = 4 and rate_type_cd = 'BR'
AND effect_date >R.effect_date
order by effect_date asc)), DATEADD(YEAR, 10, R.effect_date))
FROM pjrate r WITH(NOLOCK))
AS RR
ON RR.rate_key_value1 = pj.pjt_entity
AND RR.rate_key_value2 = Emp.employee
AND RR.rate_table_id = P.rate_table_id
AND trans_date BETWEEN rr.STARTDATE AND rr.ENDDATE
LEFT OUTER JOIN
dbo.PJEMPLOY AS Emp1 WITH(NOLOCK) ON Emp1.employee = P.manager1 LEFT OUTER JOIN
dbo.PJEMPLOY AS Emp2 WITH(NOLOCK) ON Emp2.employee = P.manager2
LEFT OUTER JOIN
(SELECT labor_rate as rate, employee, STARTDATE = effect_date, ENDDATE = ISNULL(DATEADD(DAY, - 1,
(SELECT top 1 effect_date
FROM PJEmpPjt WITH(NOLOCK)
WHERE Employee = EP.Employee
AND effect_date > EP.effect_date
ORDER BY effect_Date asc)), DATEADD(YEAR, 10, EP.effect_date))
FROM dbo.PJEmpPjt AS ep WITH(NOLOCK))
AS RR2
ON RR2.employee = Emp.employee AND trans_date BETWEEN rr2.STARTDATE AND rr2.ENDDATE
LEFT JOIN Changepoint.dbo.Resources r WITH(NOLOCK) ON r.userdefinedresourceid = emp.employee and r.deleted= 0
LEFT JOIN Changepoint.dbo.FunctionDescription fd WITH(NOLOCK) ON fd.functionid = r.primaryFunctionId
LEFT JOIN
(select c2.userdefinedcustomerid, c1.name
from Changepoint..Customer c1 WITH(NOLOCK)
right join Changepoint..Customer c2 WITH(NOLOCK) ON c2.ParentCustomerId = c1.CustomerId
where c2.deleted = 0) as PC ON PC.userdefinedcustomerid = c.custid
LEFT OUTER JOIN pjCode code WITH(NOLOCK) ON code.code_value = p.contract_type AND code.code_type = 'CONT'
LEFT OUTER JOIN pjCode loc WITH(NOLOCK) ON loc.code_value = emp.em_id21 AND loc.code_type = 'ELOC'
LEFT OUTER JOIN pjCode lev WITH(NOLOCK) ON lev.code_value = emp.em_id24 AND lev.code_type = 'ELEV'
LEFT OUTER JOIN pjCode empt WITH(NOLOCK) ON empt.code_value = emp.emp_type_cd AND empt.code_type = 'EMPT'
LEFT OUTER JOIN pjbill b WITH(NOLOCK) ON b.project = p.project
LEFT OUTER JOIN pjEmploy bill WITH(NOLOCK) ON bill.employee = b.biller
LEFT OUTER JOIN pjptdsum s WITH(NOLOCK) ON s.project = p.project AND s.acct = 'Contract Value' AND s.pjt_entity IN ('DFLT')
WHERE (hdr.batch_type = 'CHRG') AND (hdr.acct IN ('LABOR EE', 'LABOR FL'))
--and p.project like 'rog%'
--and trans_date between '11/1/2010' and '11/23/2010'
--AND trans_date = '8/14/2010' --and p.project like 'dbi%'
GROUP BY C.Name, C.CustId, PC.Name, C.ClassId, P.project_desc, class.descr, P.project, Emp.emp_name, Emp.em_id24, Emp.employee, pj.pjt_entity, pj.pjt_entity_desc,
hdr.tr_comment,
pj.contract_type, pj.contract_type, hdr.trans_date, P.status_pa, Ps.Description, Ps2.Description, P.gl_subacct, Emp1.emp_name, Emp2.emp_name,
EMPs.Description, EMPs2.Description, Emp.gl_subacct, RR.rate, RR2.Rate, empt.code_value_desc,
loc.code_value_desc, lev.code_value_desc, emp.em_id15, fd.name, hdr.fiscalno, p.cpnyid,
p.alloc_method_cd, code.code_value_desc,
bill.emp_name, s.total_budget_amount
HAVING (SUM(hdr.units) <> 0) OR
(SUM(hdr.units) IS NULL)
UNION ALL
SELECT
C.Name AS 'Customer', C.CustId AS 'CustomerID',
pc.name as 'Parent Company',
C.ClassId AS 'Customer Subaccount',
P.project_desc AS 'Project', P.project AS 'ProjectId',
p.gl_subacct AS 'Project Sub Acct',
det.ld_desc as 'Detail Description',
Emp.emp_name AS 'Resource', Emp.em_id24 AS 'Employee_Level', Emp.employee AS 'ResourceID', Emp.gl_subacct AS 'Employee Subaccount',
pj.pjt_entity AS 'Task ID', pj.pjt_entity_desc AS 'Task', pj.contract_type AS 'Category',
CASE WHEN pj.contract_type = 'BILL' THEN 'Yes' ELSE 'No' END AS Billable,
hdr.pe_date AS 'TimeCard', P.status_pa,
CASE WHEN SUBSTRING(P.gl_subacct, 1, 3) IN ('SPA', 'CSG') THEN SUBSTRING(P.gl_subacct, 4, 3)ELSE class.descr END AS 'Vertical',
--Ps.Description AS 'Vertical', Ps2.Description AS '2nd Segement',
--CASE WHEN SUBSTRING(P.gl_subacct, 1, 3) = 'SPA' THEN ps2.Description WHEN SUBSTRING(P.gl_subacct, 1, 3) = 'CSG' THEN ps2.Description ELSE ps.Description END AS 'ProjectWorkgroup',
Emp1.emp_name AS 'Manager',
Emp2.emp_name AS 'Partner',
EMPs.Description AS 'Resource Division', EMPs2.Description AS 'Resource Department',
CASE WHEN SUBSTRING(Emp.gl_subacct, 1, 3) = 'SPA' THEN emps2.Description WHEN SUBSTRING(Emp.gl_subacct, 1, 3) = 'CSG' THEN emps2.Description ELSE emps.Description END AS 'ResourceWorkgroup',
det.docnbr AS 'Document Number', SUM(det.day1_hr1) AS Day1, SUM(det.day2_hr1) AS Day2, SUM(det.day3_hr1) AS Day3, SUM(det.day4_hr1) AS Day4, SUM(det.day5_hr1) AS Day5, SUM(det.day6_hr1) AS Day6, SUM(det.day7_hr1) AS Day7, SUM(det.total_hrs) AS TotalHours,
SUM(CASE WHEN pj.contract_type = 'BILL' THEN total_hrs ELSE 0 END) AS BillableHours,
CASE WHEN pj.contract_type = 'BILL' THEN
((CASE WHEN p.Rate_Table_id = 'R125' THEN 125
WHEN p.Rate_Table_id = 'R155' THEN 155
WHEN p.Rate_Table_id = 'R130' THEN 130
WHEN p.Rate_Table_id = 'R161' THEN 161.20
WHEN p.Rate_Table_id = 'R167' AND pe_date between '2/1/2011' and '1/31/2012' AND p.project not in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 161.20
WHEN p.Rate_Table_id = 'R167' AND pe_date >= '2/1/2012' AND p.project not in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 167.65
WHEN p.Rate_Table_id = 'R167' AND p.project in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 167.65
WHEN p.Rate_Table_id = 'R135' AND pe_date between '2/1/2011' and '1/31/2012' AND p.project not in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 130.00
WHEN p.Rate_Table_id = 'R135' AND pe_date >= '2/1/2012' AND p.project not in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 135.20
WHEN p.Rate_Table_id = 'R135' AND p.project in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 135.20
WHEN p.Rate_Table_id = 'ACT1' THEN 160.00
ELSE rr.rate
END)) * SUM(total_hrs)
ELSE 0
END AS Revenue,
(RR2.Rate) * SUM(det.total_hrs) AS Cost,
CASE WHEN pj.contract_type = 'BILL' THEN
(((CASE WHEN p.Rate_Table_id = 'R125' THEN 125
WHEN p.Rate_Table_id = 'R155' THEN 155
WHEN p.Rate_Table_id = 'R130' THEN 130
WHEN p.Rate_Table_id = 'R161' THEN 161.20
WHEN p.Rate_Table_id = 'R167' AND pe_date between '2/1/2011' and '1/31/2012' AND p.project not in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 161.20
WHEN p.Rate_Table_id = 'R167' AND pe_date >= '2/1/2012' AND p.project not in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 167.65
WHEN p.Rate_Table_id = 'R167' AND p.project in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 167.65
WHEN p.Rate_Table_id = 'R135' AND pe_date between '2/1/2011' and '1/31/2012' AND p.project not in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 130.00
WHEN p.Rate_Table_id = 'R135' AND pe_date >= '2/1/2012' AND p.project not in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 135.20
WHEN p.Rate_Table_id = 'R135' AND p.project in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 135.20
WHEN p.Rate_Table_id = 'ACT1' THEN 160.00
ELSE rr.rate
END)))
ELSE 0 END AS BillRate, (RR2.Rate) AS [Cost Rate]
,
CASE WHEN det.LD_ID17 = '1' THEN 'Review Required' WHEN det.LD_ID17 = 'A' THEN 'Approved' WHEN det.LD_ID17 = 'R' THEN 'Rejected' WHEN det.LD_ID17 = '' THEN 'No Review Required' END AS [Line Item Status],
CASE WHEN hdr.le_status = 'I' THEN 'In Process' WHEN hdr.le_status = 'A' THEN 'Approved' WHEN hdr.le_status = 'C' THEN 'Completed' WHEN hdr.le_status= 'R' THEN 'Rejected' WHEN hdr.le_status = 'P' THEN 'Posted' WHEN hdr.le_status = 'T' THEN 'Timesheet' WHEN hdr.le_status = 'X' THEN 'Corrected' ELSE hdr.le_status END AS [Time Card Status],
CASE WHEN hdr.le_type = 'R' THEN 'Regular' WHEN hdr.le_type = 'C' THEN 'Correction' ELSE hdr.le_type END AS [Time Card Type],
P.alloc_method_cd AS [Project Allocation Method], SUBSTRING(P.project, 4, 3) AS Brand, SUBSTRING(P.project, 7, 2) AS Year, SUBSTRING(P.project, 9, 2) AS Type,
CASE SUBSTRING(P.Project, 11, 1) WHEN 'N' THEN 'Non-revenue' WHEN 'R' THEN 'Revenue' ELSE SUBSTRING(P.Project, 11, 1) END AS [Revenue Type],
SUBSTRING(P.project, 12, 5) AS [Project Identifier],
empt.code_value_desc as [Employee Type],
loc.code_value_desc as Location,
lev.code_value_desc as Level,
emp.em_id15 as ADP,
fd.name as 'Primary Function',
hdr.fiscalno as 'Fiscal Month',
CASE p.cpnyid WHEN 'RMG' THEN 'Rosetta Marketing Group' WHEN 'RIL' THEN 'Rosetta Interactive Limited' ELSE p.cpnyid END as 'Company',
code.code_value_desc as [Contract Type],
bill.emp_name as [Finance Contact],
s.total_budget_amount as [Contract Amount]
FROM RIL..pjlabhdr AS hdr WITH(NOLOCK)
INNER JOIN RIL..PJLABDET AS det WITH(NOLOCK) ON det.docnbr = hdr.docnbr
INNER JOIN RIL..PJPENT AS pj WITH(NOLOCK) ON pj.pjt_entity = det.pjt_entity AND pj.project = det.project
INNER JOIN RIL..PJPROJ AS P WITH(NOLOCK) ON det.project = P.project
INNER JOIN RIL..Customer AS C WITH(NOLOCK) ON C.CustId = P.customer
INNER JOIN RIL..CustClass AS class WITH(NOLOCK) ON class.classid = c.classid
INNER JOIN RIL..PJEMPLOY AS Emp WITH(NOLOCK) ON Emp.employee = hdr.employee
LEFT OUTER JOIN
(SELECT labor_rate as rate, employee,
STARTDATE = effect_date,
ENDDATE = ISNULL(DATEADD(DAY, - 1,
(SELECT TOP 1 effect_date
FROM RIL..PJEmpPjt WITH(NOLOCK)
WHERE Employee = EP.Employee
AND effect_date > EP.effect_date
ORDER BY effect_Date asc)
), DATEADD(YEAR, 10, EP.effect_date))
FROM RIL..PJEmpPjt AS ep WITH(NOLOCK)) AS RR2 ON RR2.employee = Emp.employee AND pe_date BETWEEN rr2.STARTDATE AND rr2.ENDDATE
INNER JOIN RIL..SegDef AS Ps WITH(NOLOCK) ON Ps.ID = SUBSTRING(P.gl_subacct, 1, 3) AND Ps.SegNumber = 1
INNER JOIN RIL..SegDef AS Ps2 WITH(NOLOCK) ON Ps2.ID = SUBSTRING(P.gl_subacct, 4, 6) AND Ps2.SegNumber = 2
INNER JOIN RIL..SegDef AS EMPs WITH(NOLOCK) ON EMPs.ID = SUBSTRING(Emp.gl_subacct, 1, 3) AND EMPs.SegNumber = 1
INNER JOIN RIL..SegDef AS EMPs2 WITH(NOLOCK) ON EMPs2.ID = SUBSTRING(Emp.gl_subacct, 4, 6) AND EMPs2.SegNumber = 2
LEFT OUTER JOIN
(SELECT RATE, rate_key_value1, rate_key_value2, rate_table_id, STARTDATE = effect_date,
ENDDATE =
ISNULL(DATEADD(DAY, -1, (SELECT top 1 effect_date
FROM RIL..PJRate WITH(NOLOCK)
WHERE Rate_Key_Value1 = r.Rate_Key_Value1
-- Rate_Key_Value2 = r.Rate_Key_Value2
AND Rate_Table_id = r.Rate_Table_id AND
rate_level = 4 and rate_type_cd = 'BR'
AND effect_date >R.effect_date
order by effect_date asc)), DATEADD(YEAR, 10, R.effect_date))
FROM RIL..pjrate r WITH(NOLOCK)
WHERE rate_level = 4 and rate_type_cd = 'BR')
AS RR
ON RR.rate_key_value1 = pj.pjt_entity AND RR.rate_key_value2 = Emp.employee
--AND RR.rate_key_value2 = Emp.employee
AND RR.rate_table_id = P.rate_table_id
AND pe_date BETWEEN rr.STARTDATE AND rr.ENDDATE
LEFT OUTER JOIN RIL..PJEMPLOY AS Emp1 WITH(NOLOCK) ON Emp1.employee = P.manager1
LEFT OUTER JOIN RIL..PJEMPLOY AS Emp2 WITH(NOLOCK) ON Emp2.employee = P.manager2
LEFT OUTER JOIN Changepoint.dbo.Resources r WITH(NOLOCK) ON r.userdefinedresourceid = emp.employee and r.deleted = 0
LEFT OUTER JOIN Changepoint.dbo.FunctionDescription fd WITH(NOLOCK) ON fd.functionid = r.primaryFunctionId
LEFT OUTER JOIN
(select c2.userdefinedcustomerid, c1.name
from Changepoint..Customer c1 WITH(NOLOCK)
right join Changepoint..Customer c2 WITH(NOLOCK) ON c2.ParentCustomerId = c1.CustomerId
where c2.deleted = 0) as PC ON PC.userdefinedcustomerid = c.custid
LEFT OUTER JOIN RIL..pjCode code WITH(NOLOCK) ON code.code_value = p.contract_type AND code.code_type = 'CONT'
LEFT OUTER JOIN RIL..pjCode loc WITH(NOLOCK) ON loc.code_value = emp.em_id21 AND loc.code_type = 'ELOC'
LEFT OUTER JOIN RIL..pjCode lev WITH(NOLOCK) ON lev.code_value = emp.em_id24 AND lev.code_type = 'ELEV'
LEFT OUTER JOIN RIL..pjCode empt WITH(NOLOCK) ON empt.code_value = emp.emp_type_cd AND empt.code_type = 'EMPT'
LEFT OUTER JOIN RIL..pjbill b WITH(NOLOCK) ON b.project = p.project
LEFT OUTER JOIN RIL..pjEmploy bill WITH(NOLOCK) ON bill.employee = b.biller
LEFT OUTER JOIN RIL..pjptdsum s WITH(NOLOCK) ON s.project = p.project AND acct = 'Contract Value' AND s.pjt_entity IN ('DFLT')
--where pe_date between '11/1/2010' and '11/23/2010'--> '1/3/2009' and p.project like 'dbi%'
GROUP BY C.Name, C.CustId, PC.Name, C.ClassId, class.descr, P.project_desc, P.project,
det.ld_desc,
Emp.emp_name, Emp.em_id24, Emp.employee, pj.pjt_entity, pj.pjt_entity_desc,
pj.contract_type, pj.contract_type, hdr.pe_date, P.status_pa, Ps.Description, Ps2.Description, P.gl_subacct, Emp1.emp_name, Emp2.emp_name,
EMPs.Description, EMPs2.Description, Emp.gl_subacct, det.docnbr, RR.rate, RR2.Rate, det.ld_id17, hdr.le_status, hdr.le_type,
P.alloc_method_cd,empt.code_value_desc,
loc.code_value_desc, lev.code_value_desc, emp.em_id15, fd.name, hdr.fiscalno, p.cpnyid,
code.code_value_desc,
bill.emp_name, s.total_budget_amount, P.rate_table_id
HAVING (SUM(det.total_hrs) <> 0) OR (SUM(det.total_hrs) IS NULL)
UNION ALL
SELECT C.Name AS Customer, C.CustId AS CustomerID, pc.name as [Parent Company], C.ClassId AS [Customer Subaccount], P.project_desc AS Project, P.project AS ProjectId, p.gl_subacct AS 'Project Sub Acct',
hdr.tr_comment AS [Detail Description],
Emp.emp_name AS Resource, Emp.em_id24 AS Employee_Level, Emp.employee AS ResourceID, Emp.gl_subacct AS [Employee Subaccount],
pj.pjt_entity AS [Task ID], pj.pjt_entity_desc AS Task, pj.contract_type AS Category,
CASE WHEN pj.contract_type = 'BILL' THEN 'Yes' ELSE 'No' END AS Billable, hdr.trans_date AS TimeCard, P.status_pa, Ps.Description AS Vertical,
Ps2.Description AS [2nd Segement], Emp1.emp_name AS Manager, Emp2.emp_name AS Partner,
EMPs.Description AS [Resource Division], EMPs2.Description AS [Resource Department], CASE WHEN SUBSTRING(P.gl_subacct, 1, 3) IN ('SPA', 'CSG') THEN SUBSTRING(P.gl_subacct, 4, 3)ELSE class.descr END AS 'Vertical',
CASE WHEN SUBSTRING(Emp.gl_subacct, 1, 3) = 'SPA' THEN emps2.Description WHEN SUBSTRING(Emp.gl_subacct, 1, 3) = 'CSG' THEN emps2.Description ELSE emps.Description END AS 'ResourceWorkgroup',
'CHRG' AS [Document Number], SUM(hdr.units) AS Day1,
0 AS Day2, 0 AS Day3, 0 AS Day4, 0 AS Day5, 0 AS Day6, 0 AS Day7, SUM(hdr.units) AS TotalHours,
SUM(CASE WHEN pj.contract_type = 'BILL' THEN units ELSE 0 END) AS BillableHours,
CASE WHEN pj.contract_type = 'BILL' THEN
((CASE WHEN p.Rate_Table_id = 'R125' THEN 125
WHEN p.Rate_Table_id = 'R155' THEN 155
WHEN p.Rate_Table_id = 'R130' THEN 130
WHEN p.Rate_Table_id = 'R161' THEN 161.20
WHEN p.Rate_Table_id = 'R167' AND trans_date between '2/1/2011' and '1/31/2012' AND p.project not in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 161.20
WHEN p.Rate_Table_id = 'R167' AND trans_date >= '2/1/2012' AND p.project not in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 167.65
WHEN p.Rate_Table_id = 'R167' AND p.project in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 167.65
WHEN p.Rate_Table_id = 'R135' AND trans_date between '2/1/2011' and '1/31/2012' AND p.project not in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 130.00
WHEN p.Rate_Table_id = 'R135' AND trans_date >= '2/1/2012' AND p.project not in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 135.20
WHEN p.Rate_Table_id = 'R135' AND p.project in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 135.20
WHEN p.Rate_Table_id = 'ACT1' THEN 160.00
ELSE rr.rate
END)) * SUM(units)
ELSE 0
END AS Revenue,
(RR2.Rate) * SUM(hdr.units) AS Cost,
CASE WHEN pj.contract_type = 'BILL' THEN
(((CASE WHEN p.Rate_Table_id = 'R125' THEN 125
WHEN p.Rate_Table_id = 'R155' THEN 155
WHEN p.Rate_Table_id = 'R130' THEN 130
WHEN p.Rate_Table_id = 'R161' THEN 161.20
WHEN p.Rate_Table_id = 'R167' AND trans_date between '2/1/2011' and '1/31/2012' AND p.project not in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 161.20
WHEN p.Rate_Table_id = 'R167' AND trans_date >= '2/1/2012' AND p.project not in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 167.65
WHEN p.Rate_Table_id = 'R167' AND p.project in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 167.65
WHEN p.Rate_Table_id = 'R135' AND trans_date between '2/1/2011' and '1/31/2012' AND p.project not in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 130.00
WHEN p.Rate_Table_id = 'R135' AND trans_date >= '2/1/2012' AND p.project not in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 135.20
WHEN p.Rate_Table_id = 'R135' AND p.project in ('RGCPMO12SIRM2085', 'RGCROG12SIRCRDA2') THEN 135.20
WHEN p.Rate_Table_id = 'ACT1' THEN 160.00
ELSE rr.rate
END)))
ELSE 0 END AS BillRate,
(RR2.Rate) AS [Cost Rate], 'Approved' AS [Line Item Status], 'Approved' AS [Time Card Status], 'Charge' AS [Time Card Type],
P.alloc_method_cd AS [Project Allocation Method], SUBSTRING(P.project, 4, 3) AS Brand, SUBSTRING(P.project, 7, 2) AS Year, SUBSTRING(P.project, 9,
2) AS Type, CASE SUBSTRING(P.Project, 11, 1) WHEN 'N' THEN 'Non-revenue' WHEN 'R' THEN 'Revenue' ELSE SUBSTRING(P.Project, 11, 1)
END AS [Revenue Type], SUBSTRING(P.project, 12, 5) AS [Project Identifier],
empt.code_value_desc as [Employee Type],
loc.code_value_desc as Location, lev.code_value_desc as Level, emp.em_id15 as ADP, fd.name as [Primary Function], hdr.fiscalno as 'Fiscal Month',
CASE p.cpnyid WHEN 'RMG' THEN 'Rosetta Marketing Group' WHEN 'RIL' THEN 'Rosetta Interactive Limited' ELSE p.cpnyid END as 'Company',
code.code_value_desc as [Contract Type],
bill.emp_name as [Finance Contact], s.total_budget_amount as [Contract Amount]
FROM RIL.dbo.PJTRAN AS hdr WITH(NOLOCK) INNER JOIN
-- dbo.PJLABDET AS det WITH(NOLOCK) ON det.ld_desc = hdr.tr_comment INNER JOIN
RIL.dbo.PJPENT AS pj WITH(NOLOCK) ON pj.pjt_entity = hdr.pjt_entity AND pj.project = hdr.project INNER JOIN
RIL.dbo.PJPROJ AS P WITH(NOLOCK) ON hdr.project = P.project INNER JOIN
RIL.dbo.Customer AS C WITH(NOLOCK) ON C.CustId = P.customer INNER JOIN
RIL.dbo.CustClass AS class WITH ( NOLOCK ) ON class.classid = c.classid INNER JOIN
RIL.dbo.PJEMPLOY AS Emp WITH(NOLOCK) ON Emp.employee = hdr.employee INNER JOIN
RIL.dbo.SegDef AS Ps WITH(NOLOCK) ON Ps.ID = SUBSTRING(P.gl_subacct, 1, 3) AND Ps.SegNumber = 1 INNER JOIN
RIL.dbo.SegDef AS Ps2 WITH(NOLOCK) ON Ps2.ID = SUBSTRING(P.gl_subacct, 4, 6) AND Ps2.SegNumber = 2 INNER JOIN
RIL.dbo.SegDef AS EMPs WITH(NOLOCK) ON EMPs.ID = SUBSTRING(Emp.gl_subacct, 1, 3) AND EMPs.SegNumber = 1 INNER JOIN
RIL.dbo.SegDef AS EMPs2 WITH(NOLOCK) ON EMPs2.ID = SUBSTRING(Emp.gl_subacct, 4, 6) AND EMPs2.SegNumber = 2
LEFT OUTER JOIN
(SELECT RATE, rate_key_value1, rate_key_value2, rate_table_id, STARTDATE = effect_date,
ENDDATE =
ISNULL(DATEADD(DAY, -1, (SELECT top 1 effect_date
FROM RIL..PJRate WITH(NOLOCK)
WHERE Rate_Key_Value1 = r.Rate_Key_Value1
-- Rate_Key_Value2 = r.Rate_Key_Value2
AND Rate_Table_id = r.Rate_Table_id AND
rate_level = 4 and rate_type_cd = 'BR'
AND effect_date >R.effect_date
order by effect_date asc)), DATEADD(YEAR, 10, R.effect_date))
FROM RIL..pjrate r WITH(NOLOCK)
WHERE rate_level = 4 and rate_type_cd = 'BR')
AS RR
ON RR.rate_key_value1 = pj.pjt_entity AND RR.rate_key_value2 = Emp.employee
--AND RR.rate_key_value2 = Emp.employee
AND RR.rate_table_id = P.rate_table_id
AND trans_date BETWEEN rr.STARTDATE AND rr.ENDDATE
LEFT OUTER JOIN
RIL.dbo.PJEMPLOY AS Emp1 WITH(NOLOCK) ON Emp1.employee = P.manager1 LEFT OUTER JOIN
RIL.dbo.PJEMPLOY AS Emp2 WITH(NOLOCK) ON Emp2.employee = P.manager2
LEFT OUTER JOIN
(SELECT labor_rate as rate, employee, STARTDATE = effect_date, ENDDATE = ISNULL(DATEADD(DAY, - 1,
(SELECT top 1 effect_date
FROM RIL..PJEmpPjt WITH(NOLOCK)
WHERE Employee = EP.Employee
AND effect_date > EP.effect_date
ORDER BY effect_Date asc)), DATEADD(YEAR, 10, EP.effect_date))
FROM RIL.dbo.PJEmpPjt AS ep WITH(NOLOCK))
AS RR2
ON RR2.employee = Emp.employee AND trans_date BETWEEN rr2.STARTDATE AND rr2.ENDDATE
LEFT JOIN Changepoint.dbo.Resources r WITH(NOLOCK) ON r.userdefinedresourceid = emp.employee and r.deleted= 0
LEFT JOIN Changepoint.dbo.FunctionDescription fd WITH(NOLOCK) ON fd.functionid = r.primaryFunctionId
LEFT JOIN
(select c2.userdefinedcustomerid, c1.name
from Changepoint..Customer c1 WITH(NOLOCK)
right join Changepoint..Customer c2 WITH(NOLOCK) ON c2.ParentCustomerId = c1.CustomerId
where c2.deleted = 0) as PC ON PC.userdefinedcustomerid = c.custid
LEFT OUTER JOIN RIL..pjCode code WITH(NOLOCK) ON code.code_value = p.contract_type AND code.code_type = 'CONT'
LEFT OUTER JOIN RIL..pjCode loc WITH(NOLOCK) ON loc.code_value = emp.em_id21 AND loc.code_type = 'ELOC'
LEFT OUTER JOIN pjCode lev WITH(NOLOCK) ON lev.code_value = emp.em_id24 AND lev.code_type = 'ELEV'
LEFT OUTER JOIN RIL..pjCode empt WITH(NOLOCK) ON empt.code_value = emp.emp_type_cd AND empt.code_type = 'EMPT'
LEFT OUTER JOIN RIL..pjbill b WITH(NOLOCK) ON b.project = p.project
LEFT OUTER JOIN RIL..pjEmploy bill WITH(NOLOCK) ON bill.employee = b.biller
LEFT OUTER JOIN RIL..pjptdsum s WITH(NOLOCK) ON s.project = p.project AND s.acct = 'Contract Value' AND s.pjt_entity IN ('DFLT')
WHERE (hdr.batch_type = 'CHRG') AND (hdr.acct IN ('LABOR EE', 'LABOR FL'))
--and p.project like 'rog%'
--and trans_date between '11/1/2010' and '11/23/2010'
--AND trans_date = '8/14/2010' --and p.project like 'dbi%'
GROUP BY C.Name, C.CustId, PC.Name, C.ClassId, P.project_desc, class.descr, P.project, Emp.emp_name, Emp.em_id24, Emp.employee, pj.pjt_entity, pj.pjt_entity_desc,
hdr.tr_comment,
pj.contract_type, pj.contract_type, hdr.trans_date, P.status_pa, Ps.Description, Ps2.Description, P.gl_subacct, Emp1.emp_name, Emp2.emp_name,
EMPs.Description, EMPs2.Description, Emp.gl_subacct, RR.rate, RR2.Rate, empt.code_value_desc,
loc.code_value_desc, lev.code_value_desc, emp.em_id15, fd.name, hdr.fiscalno, p.cpnyid,
p.alloc_method_cd, code.code_value_desc,
bill.emp_name, s.total_budget_amount, P.rate_table_id
HAVING (SUM(hdr.units) <> 0) OR
(SUM(hdr.units) IS NULL)

I'm stuck on where the bottle neck is. If anyone can give me some insight please...
Thanks
David

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246686 More actions · Answer 1

Someone had a lot of fun writing that query.

I believe that error means that there are unequal numbers of columns in the different SELECTs - so things just don't match up.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Alexander Suprun SSCertifiable Points: 6233 More actions · Answer 2

What part of the error message you don't understand?

Alex Suprun

david.ostrander SSCommitted Points: 1828 More actions · Answer 3

Oh I understand the message. Its my eyes that are not seeing where the issue is.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 4

david.ostrander (12/14/2012)
Oh I understand the message. Its my eyes that are not seeing where the issue is.

Try running the queries one at a time and counting the columns ...

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

david.ostrander SSCommitted Points: 1828 More actions · Answer 5

I was able to find out what it was. There were two rows I removed that I didn't remove in the last Select.

Thank you both for the reply's

Happy Holidays!!