Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with CTE and filter in Query Expand / Collapse
Author
Message
Posted Tuesday, July 30, 2013 2:22 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, October 23, 2014 7:31 AM
Points: 706, Visits: 1,752
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




Post #1479157
Posted Tuesday, July 30, 2013 3:31 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:57 PM
Points: 3,908, Visits: 8,860
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1479191
Posted Tuesday, July 30, 2013 11:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:18 AM
Points: 5,245, Visits: 12,161
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.



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

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1479284
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse