Post reply

Excluding rows from table

  • June 30, 2010 at 4:29 pm

    #223469

    Hi

    Here is my sample data.

    CREATE TABLE #WaterDays (

    MeterCode VARCHAR (25) NULL

    , MeterUsage NUMERIC(12,2) NULL

    , ReadingYear INT

    , Readingtype VARCHAR (25) NULL

    )

    INSERT INTO #WaterDays(MeterCode ,MeterUsage,ReadingYear , Readingtype)

    SELECT '100',20.00,2008,'Actual'

    UNION ALL

    SELECT '101',40.00,2008,'Actual'

    UNION ALL

    SELECT '103',30.00,2008,'Actual'

    UNION ALL

    SELECT '104',20.00,2008,'Actual'

    UNION ALL

    SELECT '100',25.00,2009,'Actual'

    UNION ALL

    SELECT '101',23.00,2009,'Actual'

    UNION ALL

    SELECT '102',20.00,2009,'Actual'

    UNION ALL

    SELECT '103',20.00,2009,'Actual'

    UNION ALL

    SELECT '104',20.00,2009,'Final'

    UNION ALL

    SELECT '100',20.00,2010,'Actual'

    UNION ALL

    SELECT '101',20.00,2010,'Actual'

    UNION ALL

    SELECT '102',20.00,2010,'Actual'

    SELECT * FROM #WaterDays

    I would like to exclude Metercodes which are having reading type as Final.

    For Example If i select any year it doesn't matter ,i would like to see below Meter Codes.

    100 ,102,103 .

    I already tried in this way.

    SELECT * FROM #WaterDays WHERE Readingtype <> 'Final'

    But in this case it is excluding that row only not the meter code .

    Let me know if its not clear.

    Thanks

    Anitha

  • June 30, 2010 at 5:12 pm

    #1187395

    What is the reasoning behind NOT showing 101?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 30, 2010 at 5:21 pm

    #1187397

    Is this what you're trying to do?

    SELECT wd.MeterCode, wd.MeterUsage, wd.ReadingYear, wd.Readingtype

    FROM #WaterDays wd

    WHERE NOT EXISTS

    (

    SELECT sq.MeterCode

    FROM #WaterDays sq

    WHERE sq.MeterCode = wd.MeterCode

    AND sq.Readingtype = 'Final'

    )

    └> bt


    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • June 30, 2010 at 5:30 pm

    #1187402

    Hi Jeff

    The Reason is If reading type is final that meter will be historic one.

    Thats why i dont want to see 101.

  • June 30, 2010 at 5:37 pm

    #1187405

    No bteraberry .

    I am getting still 101 ,i dont want see 101 as it is historic (Final Status).

    The query which you supplied excluding only row ,not the metercode.

    I am expecting out put like this.

    100 2008

    102 2008

    103 2008

    104 2008

    100 2009

    102 2009 etc.

    Thanks

  • June 30, 2010 at 5:38 pm

    #1187406

    anitha.cherukuri (6/30/2010)

    The Reason is If reading type is final that meter will be historic one.

    Thats why i dont want to see 101.

    I cannot see a "Final" row for 101 - I think that's the reason why Jeff asked the question 🙂

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • June 30, 2010 at 6:05 pm

    #1187410

    Sorry ,i thought 101 as Historic one.

    Your query is perfect

    Thanks a lot..

    Anitha

  • July 1, 2010 at 6:47 am

    #1187630

    anitha.cherukuri (6/30/2010)

    Hi Jeff

    The Reason is If reading type is final that meter will be historic one.

    Thats why i dont want to see 101.

    Your test data has a "Final" for 104 but not 101.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 1, 2010 at 6:49 am

    #1187632

    Ah... dang it... gotta read down before posting. I see the 101 thing has been resolved.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply