SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
david.ostrander
david.ostrander
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 692
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
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18499 Visits: 20440
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Alexander Suprun
Alexander Suprun
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

Group: General Forum Members
Points: 513 Visits: 1516
What part of the error message you don't understand?


Alex Suprun
david.ostrander
david.ostrander
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 692
Oh I understand the message. Its my eyes that are not seeing where the issue is.
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18499 Visits: 20440
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 ...


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
david.ostrander
david.ostrander
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 692
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!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search