June 14, 2011 at 8:02 am
Hi folks,
I have a strange request which i don't know is possible. Have looked up replace function and update field but that is not exactly what i wish.
I have a view with the following data
View 1
Entries
2000
4000
3000
I need to pull out data that is less than or equal to 2400 but entries over 2400 should be equal to 2400 without effecting the uderlying data.
e.g.
I need to query data from the above with the result of the following without effecting the underlying data entries.
2000
2400
2400
Any help appreciated
Sully
June 14, 2011 at 8:06 am
Please post table definitions, sample data and expected results as per http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2011 at 8:10 am
SELECT
CASE WHEN f1 > 2400 THEN 2400
ELSE f1
END
FROM t1
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 14, 2011 at 8:43 am
Many thanks but i can't seem to work with my own query. Below is the original query which results in
EmpID | TimeDuration
zz1 | 2700
zz2 | 2500
zz3 | 2000
SELECT TOP (100) PERCENT EmpID, SUM(TimeDuration) AS TimeDuration
FROM (SELECT EmpID, TimeDuration
FROM dbo.D_Modules
UNION ALL
SELECT EmpID, TimeDuration
FROM dbo.D_Activities) AS dtTimes
GROUP BY EmpID
ORDER BY EmpID
I have tried the following but no luck
SELECT TOP (100) PERCENT EmpID, SUM(CASE WHEN TimeDuration > 2400 THEN 2400 ELSE TimeDuration END) AS TimeDuration
FROM (SELECT EmpID, TimeDuration
FROM dbo.D_Modules
UNION ALL
SELECT EmployeeID, TimeDuration
FROM dbo.D_Activities) AS dtTimes
GROUP BY EmpID
ORDER BY EmpID
June 14, 2011 at 8:51 am
'No luck' doesn't help diagnostics much.
You never mentioned that you were using a UNION - you need to do the union first as a subquery and then build your case/sum over the UNIONed data set.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 14, 2011 at 8:58 am
Sorry Phil. I was trying to apply your example to mine but i'm still having trouble trying to build what you said. Can you give me an example so i can apply it to my query
June 14, 2011 at 9:05 am
Do you mean build the case on top of the query? I tried the following but resulted in error
SELECT TOP (100) PERCENT EmpID, CASE WHEN TimeDuration > 2400 THEN 2400 ELSE TimeDuration END AS TimeDuration
FROM (SELECT EmpID, SUM(TimeDuration) AS TimeDuration
FROM (SELECT EmpID, TimeDuration
FROM dbo.D_Modules
UNION ALL
SELECT EmpID, TimeDuration
FROM dbo.D_Activities) AS dtTimes) AS derivedtbl_1
GROUP BY EmpID
ORDER BY EmpID
June 14, 2011 at 9:08 am
Sorry - bit pushed for time hence brief replies.
I meant build your subquery, including the UNION, and then apply the CASE on that.
;with cte as
(select f1
from t1
union
select
f2 from t2)
select case when f1 > 2400 etc etc
from cte
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 14, 2011 at 9:22 am
Sorry Phil. I don't follow. This is way beyond my ability
June 14, 2011 at 9:40 am
Got the following query to run but it is not applying the case argument. Any ideas appreciated ?
WITH CTE AS (SELECT EmpID, TimeDuration
FROM dbo.D_Modules
UNION ALL
SELECT EmpID, TimeDuration
FROM dbo.D_Activities)
SELECT TOP (100) PERCENT EmpID, SUM(CASE WHEN TimeDuration > 2400 THEN 2400 ELSE TimeDuration END) AS TimeDuration
FROM CTE AS CTE_1
GROUP BY EmpID
June 14, 2011 at 10:09 am
bpmosullivan (6/14/2011)
Got the following query to run but it is not applying the case argument. Any ideas appreciated ?WITH CTE AS (
SELECT EmpID, TimeDuration
FROM dbo.D_Modules
UNION ALL
SELECT EmpID, TimeDuration
FROM dbo.D_Activities
)
SELECT TOP (100) PERCENT EmpID, SUM(CASE WHEN TimeDuration > 2400 THEN 2400 ELSE TimeDuration END) AS TimeDuration
FROM CTE AS CTE_1
GROUP BY EmpID
When combining CASE statements with aggregate functions like SUM, the scope is important. As it's written, the CASE statement is looking at each individual record. I think that you want to look at the total duration instead.
WITH CTE AS (
SELECT EmpID, TimeDuration
FROM dbo.D_Modules
UNION ALL
SELECT EmpID, TimeDuration
FROM dbo.D_Activities
)
SELECT EmpID, CASE WHEN Sum(TimeDuration) > 2400 THEN 2400 ELSE Sum(TimeDuration) END AS TimeDuration
FROM CTE AS CTE_1
GROUP BY EmpID
I also took out the TOP (100) PERCENT, since it doesn't gain you anything. The only reason to use TOP (100) PERCENT is if you are trying to force a particular order in a view or subquery, and the value of even that case is questionable.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 14, 2011 at 10:35 am
Looks like it should work OK - what happens?
Try a couple of minor refinements:
WITH CTE AS (SELECT EmpID, TimeDuration
FROM dbo.D_Modules
UNION ALL
SELECT EmpID, TimeDuration
FROM dbo.D_Activities)
SELECT CTE.EmpID, SUM(CASE WHEN CTE.TimeDuration > 2400 THEN 2400 ELSE CTE.TimeDuration END) AS TimeDuration
FROM CTE
GROUP BY CTE.EmpID
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 14, 2011 at 2:57 pm
Got the following to work Phil with help from another forum. Thought i'd post it here to help others
WITH CTE AS (SELECT EmpID, TimeDuration
FROM dbo.D_Modules
UNION ALL
SELECT EmpID, TimeDuration
FROM dbo.D_Activities)
SELECT TOP (100) PERCENT EmpID, CASE WHEN SUM(TimeDuration) <= 2400 THEN SUM(TimeDuration) ELSE 2400 END AS TimeDuration
FROM CTE AS CTE_1
GROUP BY EmpID
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply