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

Try:

Hi Wim,

Thanks for reply. I tried this but because i am using a union i am stuck. How do i encapsulate the Case statement over my query below ?


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

Thanks Wim.. But i need to add the sum of the unions.

I have been working with the following query but the results are still < than 2400. All records are showing but it's not applying the case for me. any idea ?


WITH CTE AS (SELECT EmployeeID, TimeDuration
FROM dbo.D_CPDModules
UNION ALL
SELECT EmployeeID, TimeDuration
FROM dbo.D_CPDActivities)
SELECT TOP (100) PERCENT EmployeeID, SUM(CASE WHEN TimeDuration > 2400 THEN '2400' ELSE TimeDuration END) AS TimeDuration
FROM CTE AS CTE_1
GROUP BY EmployeeID

Hi Wim,

Many thanks. Your example did work when i rearranged it. Thanks again

WITH CTE AS (SELECT EmployeeID, TimeDuration
FROM dbo.D_CPDModules
UNION ALL
SELECT EmployeeID, TimeDuration
FROM dbo.D_CPDActivities)
SELECT TOP (100) PERCENT EmployeeID, CASE WHEN SUM(TimeDuration) <= 2400 THEN SUM(TimeDuration) ELSE 2400 END AS TimeDuration
FROM CTE AS CTE_1
GROUP BY EmployeeID

You're welcome.

Feel free to donate to my "Earned beers", if you think I'm entitled.

Still looking for a catchy phrase to subtly remind people about a beer donation. Something in the line of "Don't forget the guide" or "Will work for food".
"Will do SQL homework for beer" just doesn't sound so exalted. And what if it was for their work?

Problems, problems, problems.

Definately deserve a Keg of Beer Wim

Thank you! Added one beer.

Heck, I'll throw in another beer just on general principles!



-PatP

WOOT!! You just increased my Earned Beers score by 33%!

I'm sending you an Earned Beer back !