Problem with CTE and filter in Query

Question

Problem with CTE and filter in Query

krypto69

SSChampion

Points: 13591
More actions
July 30, 2013 at 2:22 pm

#395226

Hi,
I have this query that if I filter by 'AND [ORG LEVEL 2] like '%ZSW' it gives me the monthly amount broken out by month.
If I remove that filter it looks it is totaling everything in the monthly amount column.
I need it to filter out by month, like it does when I use the [ORG LEVEL 2] filter, but without using the filter.
Here's the query
;WITH cte AS
(
select * from (SELECT
distinct pehPErcontrol
,case left(substring(pehPErcontrol,5,len(pehPErcontrol)),2)
when '01' then 'January'
when '02' then 'February'
when '03' then 'March'
when '04' then 'April'
when '05' then 'May'
when '06' then 'June'
when '07' then 'July'
when '08' then 'August'
when '09' then 'September'
when '10' then 'October'
when '11' then 'November'
when '12' then 'December'
end as [UltiMonth],
rtrim(eepNameLast) +
', ' + rtrim(eepNameFirst) +
' ' + coalesce(substring(eepNameMiddle,1,1) + '.', '') as Name,
eepNameLast AS [Last Name],
IsNull(eepNameSuffix,'') AS [Suffix],
eepNameFirst AS [First Name],
IsNull(eepNameMiddle,'') AS [Middle Name],
pehCurAmt AS [Current Amount],
pehCurHrs AS [Current Hours],
pehCoID AS [Company ID],
pehEEID AS [EE ID],
pehEmpNo AS [Emp No],
pehLocation AS [Location],
pehJobCode AS [Job Code],
pehOrgLvl1 AS [Org Level 1],
pehOrgLvl2 AS [Org Level 2],
pehOrgLvl3 AS [Org Level 3],
pehOrgLvl4 AS [Org Level 4],
pehPayGroup AS [Pay Group],
pehProject AS [Project],
pehShfShiftAmt AS [Shift Amount],
pehearncode AS [Earn Code],
pehIsVoided AS [IS Voided],
pehIsVoidingRecord AS [Voiding Record],
pehIsOvertime AS [Is Overtime]
FROM EmpPers JOIN pearhist ph ON ph.pehEEID = eepEEID
join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013]
ON [orglevel] = pehOrgLvl2) t
right outer join
WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] ob
on t.[UltiMonth] = ob.[month] and orglevel = [org level 2]
where pehPerControl > '201301011'
AND [EARN CODE] = '0002'
AND [IS Voided] <> 'Y'
AND [Voiding Record] <> 'Y'
AND [Is Overtime] = 'Y'
AND [org level 2] like '%ZSW'
)
SELECT *
,SUM([Current Amount])
OVER (PARTITION BY [UltiMonth]) AS [Monthly Amount]
FROM cte

Viewing 3 posts - 1 through 2 (of 2 total)

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

We need more information to see what's happening. Please read the article linked in my signature to find out what we need.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Phil Parkin SSC Guru Points: 246695 More actions · Answer 2

Having a CTE which is effectively defined as

select * from (subquery)

is somewhat bizarre too.

You've been here long enough to know how to post questions - not only does your post omit DDL, sample data and desired results, it doesn't ask for anything either.

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.