Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2009
    Posts
    91

    View Entry Replace

    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

  2. #2
    Join Date
    Nov 2004
    Posts
    1,380
    Try:
    Code:
    SELECT CASE WHEN Entries <= 2400 THEN Entries ELSE 2400 END as Entries
    FROM ...
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Oct 2009
    Posts
    91
    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

  4. #4
    Join Date
    Nov 2004
    Posts
    1,380
    Code:
    SELECT TOP (100) PERCENT 
      EmpID, 
      CASE WHEN SUM(TimeDuration) <= 2400 
        THEN SUM(TimeDuration) 
        ELSE 2400 
      END AS TimeDuration
    ...
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Oct 2009
    Posts
    91
    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

  6. #6
    Join Date
    Oct 2009
    Posts
    91
    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

  7. #7
    Join Date
    Nov 2004
    Posts
    1,380
    Quote Originally Posted by sullyman View Post
    Hi Wim,

    Many thanks. Your example did work when i rearranged it. Thanks again
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Oct 2009
    Posts
    91
    Definately deserve a Keg of Beer Wim

  9. #9
    Join Date
    Nov 2004
    Posts
    1,380
    Quote Originally Posted by sullyman View Post
    Definately deserve a Keg of Beer Wim
    Thank you! Added one beer.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,943
    Heck, I'll throw in another beer just on general principles!



    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Nov 2004
    Posts
    1,380
    Quote Originally Posted by Pat Phelan View Post
    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 !
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •