Replace data in View Query

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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

  • '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

  • 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

  • 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

  • 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

  • Sorry Phil. I don't follow. This is way beyond my ability

  • 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

  • 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

  • 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

  • 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