Exclude rows

  • Hey,

    Say I have a table with the following rows;

    Case | Hours | Type

    xxx | 3 | 1

    xxx | 3 | 2

    xxx | 4 | 1

    xxx | 1 | 1

    and I do a sum of the hours, I get 11. However, a Type of 1 is a billed item and 2 is a credit item. If something has been credited, the billed item row should be excluded, resulting in a total of 5.

    I'm shtoock!

  • It would be a lot easier is you would provide ddl and sample data in a consumable format. Also, you should try to avoid reserved words for object/column names (all three of your column names are reserved words).

    Assuming that hours is always a positive value you can just sum the values like this.

    create table #SomeTable

    (

    CaseNum char(3),

    MyHours int,

    MyType int

    )

    insert #SomeTable

    select 'xxx', 3, 1 union all

    select 'xxx', 3, 2 union all

    select 'xxx', 4, 1 union all

    select 'xxx', 1, 1

    select SUM(Case when MyType = 2 then -1 * MyHours else MyHours end) as TotalHours

    from #SomeTable

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thanks Sean.

    They're not real column names. I think you may have solved it anyway. If not, i'll provide some DDL

  • This is hard without DDL but assuming you cannot have this:

    Case | Hours | Type

    xxx | 3 | 1

    xxx | 3 | 1

    Or this:

    Case | Hours | Type

    xxx | 3 | 2

    xxx | 3 | 2

    e.g. two non-distinct types for any case/hours combination, then this will work:

    Note that I used the ddl that Scott was nice enough to put together but added a constraint.

    IF object_id('tempdb..#SomeTable') IS NOT NULL DROP TABLE #SomeTable;

    CREATE TABLE #SomeTable

    (CaseNum char(3),

    MyHours int not null,

    MyType int not null,

    constraint case_hrs unique(CaseNum,MyHours,MyType));

    INSERT INTO #SomeTable

    SELECT 'xxx',3,1 UNION ALL

    SELECT 'xxx',3,2 UNION ALL

    SELECT 'xxx',4,1 UNION ALL

    SELECT 'xxx',1,1;

    WITH billed_credited AS

    (

    SELECT CaseNum, MyHours, COUNT(MyHours) AS bc

    FROM #SomeTable

    GROUP by CaseNum, MyHours

    )

    SELECT SUM(MyHours) AS ttl_hrs

    FROM billed_credited

    WHERE bc=1;

    Edit: noticed an error with my code :ermm:, will have updated code momentarily.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Using the sample code above, I believe this will do the trick:

    WITH billed_credited AS

    (

    SELECT CaseNum, MyHours, COUNT(MyHours) AS bc

    FROM #SomeTable

    GROUP by CaseNum, MyHours

    )

    SELECT SUM(x.Myhours) TotalHours

    FROM #sometable st

    CROSS APPLY billed_credited x

    WHERE x.CaseNum=st.CaseNum AND x.MyHours=st.MyHours

    AND (bc=1 and MyType=1);

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Sean Lange (9/12/2013)


    select SUM(Case when MyType = 2 then -1 * MyHours else MyHours end) as TotalHours

    from #SomeTable

    If I understand the original OP correctly (" If something has been credited, the billed item row should be excluded") this would not provide the right answer with this sample data:

    INSERT INTO #SomeTable

    SELECT 'xxx',3,1 UNION ALL

    SELECT 'yyy',3,2 UNION ALL

    SELECT 'xxx',4,1 UNION ALL

    SELECT 'xxx',1,1;

    I believe the right answer would be 8, your query would return a 5.

    ... but who knows without any ddl :Whistling:

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Alan.B (9/12/2013)


    Sean Lange (9/12/2013)


    select SUM(Case when MyType = 2 then -1 * MyHours else MyHours end) as TotalHours

    from #SomeTable

    If I understand the original OP correctly (" If something has been credited, the billed item row should be excluded") this would not provide the right answer with this sample data:

    INSERT INTO #SomeTable

    SELECT 'xxx',3,1 UNION ALL

    SELECT 'yyy',3,2 UNION ALL

    SELECT 'xxx',4,1 UNION ALL

    SELECT 'xxx',1,1;

    I believe the right answer would be 8, your query would return a 5.

    ... but who knows without any ddl :Whistling:

    The OP stated that the query should return 5.

    and I do a sum of the hours, I get 11. However, a Type of 1 is a billed item and 2 is a credit item. If something has been credited, the billed item row should be excluded, resulting in a total of 5.

    To be fair, the way I wrote that makes the most sense because you could have a credit that is not for the full amount. Imagine if there was 3 hours billed but only 2 credited.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • What happens if the credit amount is larger than the total for all other rows?

    Or is some sort of matching required to exclude only the row with the matching amount (e.g., nearest debit that is >= the credit).


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • It's a 3 party app that only allows you to credit the bill amount.

    So say you bill 4X £100 separately, when you raise a credit you have to cherry pick which bill amount you want to credit. So you can't bill 4X £100 and credit 1X £200 or 1X £500 for example. If you wanted to credit the whole bill, you'd have to credit the 4X £100 individually.

  • Alan.B (9/12/2013)If I understand the original OP correctly (" If something has been credited, the billed item row should be excluded") this would not provide the right answer with this sample data:

    I believe the right answer would be 8, your query would return a 5.

    ... but who knows without any ddl :Whistling:

    The billed item row and the credit row should both be excluded. So say there was only 2 rows, both for the same case, one row had 2 hours billed and the other row 2 hours credited, the result should be 0.

    In otherwords, if something has been credited, the results should show as if it was never billed in the first place. We're trying to work out sales money, so if someone bills £200 and then credits it all back, they've really not billed (gained) anything.

  • lanky_doodle (9/13/2013)


    Alan.B (9/12/2013)If I understand the original OP correctly (" If something has been credited, the billed item row should be excluded") this would not provide the right answer with this sample data:

    I believe the right answer would be 8, your query would return a 5.

    ... but who knows without any ddl :Whistling:

    The billed item row and the credit row should both be excluded. So say there was only 2 rows, both for the same case, one row had 2 hours billed and the other row 2 hours credited, the result should be 0.

    In otherwords, if something has been credited, the results should show as if it was never billed in the first place. We're trying to work out sales money, so if someone bills £200 and then credits it all back, they've really not billed (gained) anything.

    Then my solution should work just fine?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 11 posts - 1 through 10 (of 10 total)

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