August 22, 2013 at 12:25 pm
Hi,
I have this query that is close to what I need, but not close enough..
--Query Changed to include new codes
;WITH cte AS
(
select * from (SELECT
distinct pehPErcontrol ,
DATENAME(MONTH, pehpaydate) 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],
pehpaydate AS [Pay Date]
FROM EmpPers JOIN pearhist ph ON ph.pehEEID = eepEEID
right outer 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 [Pay Date] >= '2013-01-01'
AND [EARN CODE] IN ('0002', '0022','0025','0066','0104','A102','N002','N104', '002D', '0071','0096','0104','0121', '0123', '0151', '0200', '0201', '0202', '0205', 'A102', 'CREW', 'N002', 'N104', 'TXWP' )
AND [IS Voided] <> 'Y'
AND [Voiding Record] <> 'Y'
AND [Is Overtime] = 'Y'
AND [org level 2] like '%ZPR'
AND [ULTIMONTH] = 'JANUARY'
--ORDER BY pehpaydate
--AND [NAME] = 'Moskowitz, Joshua'
)
SELECT *
,SUM([Current Amount])
OVER (PARTITION BY [Emp No],[UltiMonth]) AS [Monthly Amount]
FROM cte
This returns the [Monthly Amount] for every employee, listed for every record. When I am already getting the sum for the month. The sum for the month is correct, it just lists it for every record. So, when I total in my dashboard program it's incorrect.
I think I need to add an additional column(s) so that I can add the totals correctly
So monthly amount looks like:
JANUARY EMPLOYEE #1 $190.03
JANUARY EMPLOYEE #1 $190.03
JANUARY EMPLOYEE #2 $240.28
JANUARY EMPLOYEE #2 $240.28
JANUARY EMPLOYEE #3 $164.69
JANUARY EMPLOYEE #3 $164.69
Where I really need it to look like:
JANUARY EMPLOYEE #1 $190.03
JANUARY EMPLOYEE #2 $240.28
JANUARY EMPLOYEE #3 $164.69
Been working on this for two days now and not making much progress...
August 22, 2013 at 12:46 pm
you should first define your CTE as a monthly aggregation (sum(Amount) group by month EMPlyee#)
the your final query is just:
SELECT {emplyee #, Name, etc.}, total=SUM(Amount)
group by {emplyee #, Name, etc.}
FROM cte
Or maybe I just don't understand what you are trying to do.
The probability of survival is inversely proportional to the angle of arrival.
August 22, 2013 at 3:57 pm
Your query is very difficult to read, because you don't prefix every column, and therefore it is difficult to tell where they are coming from.
It also suffers from having two right-joins - right-joins give me a headache. While
A LEFT JOIN B
and
B RIGHT JOIN A
means the same thing, I much prefer the former, and I find it a lot easier to understand.
The query would be more readable if you made that big derived table a CTE of its own. The query could also benefit from better indentation, although it may be that lack of identation is something which happened when you composed the post.
If you make these editorial changes, it will be somewhat easier for us to understand the query - and maybe for you as well.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 22, 2013 at 5:49 pm
Hi
Simplifying you query down, I would expect that you are seeing the following behaviour?
with simpleExample AS (
-- A simplified example of what I expect you are seeing from inside the CTE?
SELECT *
FROM (VALUES
('Emp1','January',100.00,1.0),
('Emp1','January',200.00,2.0),
('Emp2','January',50.00,1.0),
('Emp2','January',75.00,1.5),
('Emp3','January',100.00,2.0),
('Emp3','January',100.00,2.0),
('Emp3','January',50.00,1.0),
('Emp4','January',70.00,1.0)) AS S (empName, payMonth, currAmount, currHours)
)
SELECT *,
SUM(currAmount) OVER (PARTITION BY empName, payMonth) AS [Monthly Amount]
FROM simpleExample;
empName payMonth currAmount currHours Monthly Amount
------- -------- --------------------------------------- --------------------------------------- ---------------------------------------
Emp1 January 200.00 2.0 300.00
Emp1 January 100.00 1.0 300.00
Emp2 January 75.00 1.5 125.00
Emp2 January 50.00 1.0 125.00
Emp3 January 100.00 2.0 250.00
Emp3 January 50.00 1.0 250.00
Emp3 January 100.00 2.0 250.00
Emp4 January 70.00 1.0 70.00
To get the result the way you specified you could change you query to something like the following
with simpleExample AS (
-- A simplified example of what I expect you are seeing from inside the CTE?
SELECT *
FROM (VALUES
('Emp1','January',100.00,1.0),
('Emp1','January',200.00,2.0),
('Emp2','January',50.00,1.0),
('Emp2','January',75.00,1.5),
('Emp3','January',100.00,2.0),
('Emp3','January',100.00,2.0),
('Emp3','January',50.00,1.0),
('Emp4','January',70.00,1.0)) AS S (empName, payMonth, currAmount, currHours)
)
SELECT empName, payMonth, SUM(currAmount) AS [Monthly Amount]--, SUM(currHours) -- If you don't want to lose hours
FROM simpleExample
GROUP BY empName, payMonth;
Is this sort of what you are wanting?
August 22, 2013 at 5:53 pm
I more or less agree with sturner that the final query should probably be something like this:
SELECT [Emp No], [UltiMonth], SUM([Current Amount])
FROM cte
GROUP BY [Emp No],[UltiMonth];
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 23, 2013 at 9:39 am
works good dwain.c
thanks
August 23, 2013 at 9:40 am
mickyT (8/22/2013)
HiSimplifying you query down, I would expect that you are seeing the following behaviour?
with simpleExample AS (-- A simplified example of what I expect you are seeing from inside the CTE?
SELECT *
FROM (VALUES
('Emp1','January',100.00,1.0),
('Emp1','January',200.00,2.0),
('Emp2','January',50.00,1.0),
('Emp2','January',75.00,1.5),
('Emp3','January',100.00,2.0),
('Emp3','January',100.00,2.0),
('Emp3','January',50.00,1.0),
('Emp4','January',70.00,1.0)) AS S (empName, payMonth, currAmount, currHours)
)
SELECT *,
SUM(currAmount) OVER (PARTITION BY empName, payMonth) AS [Monthly Amount]
FROM simpleExample;
empName payMonth currAmount currHours Monthly Amount------- -------- --------------------------------------- --------------------------------------- ---------------------------------------
Emp1 January 200.00 2.0 300.00
Emp1 January 100.00 1.0 300.00
Emp2 January 75.00 1.5 125.00
Emp2 January 50.00 1.0 125.00
Emp3 January 100.00 2.0 250.00
Emp3 January 50.00 1.0 250.00
Emp3 January 100.00 2.0 250.00
Emp4 January 70.00 1.0 70.00
To get the result the way you specified you could change you query to something like the following
with simpleExample AS (-- A simplified example of what I expect you are seeing from inside the CTE?
SELECT *
FROM (VALUES
('Emp1','January',100.00,1.0),
('Emp1','January',200.00,2.0),
('Emp2','January',50.00,1.0),
('Emp2','January',75.00,1.5),
('Emp3','January',100.00,2.0),
('Emp3','January',100.00,2.0),
('Emp3','January',50.00,1.0),
('Emp4','January',70.00,1.0)) AS S (empName, payMonth, currAmount, currHours)
)
SELECT empName, payMonth, SUM(currAmount) AS [Monthly Amount]--, SUM(currHours) -- If you don't want to lose hours
FROM simpleExample
GROUP BY empName, payMonth;
Is this sort of what you are wanting?
Yes, mickyT - awesome thanks for your help!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply