Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Exclude rows Expand / Collapse
Author
Message
Posted Thursday, September 12, 2013 8:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 5:53 AM
Points: 51, Visits: 101
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!
Post #1494190
Posted Thursday, September 12, 2013 8:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 PM
Points: 13,015, Visits: 11,816
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1494197
Posted Thursday, September 12, 2013 8:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 5:53 AM
Points: 51, Visits: 101
Thanks Sean.

They're not real column names. I think you may have solved it anyway. If not, i'll provide some DDL
Post #1494219
Posted Thursday, September 12, 2013 8:38 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 549, Visits: 2,550
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 , will have updated code momentarily.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1494220
Posted Thursday, September 12, 2013 9:34 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 549, Visits: 2,550
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);



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1494249
Posted Thursday, September 12, 2013 9:57 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 549, Visits: 2,550
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


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1494263
Posted Thursday, September 12, 2013 10:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 PM
Points: 13,015, Visits: 11,816
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


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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1494273
Posted Thursday, September 12, 2013 6:29 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:01 PM
Points: 3,605, Visits: 5,198
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1494409
Posted Friday, September 13, 2013 2:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 5:53 AM
Points: 51, Visits: 101
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.
Post #1494501
Posted Friday, September 13, 2013 2:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 5:53 AM
Points: 51, Visits: 101
[b]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


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.
Post #1494502
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse