SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Exclude rows


Exclude rows

Author
Message
lanky_doodle
lanky_doodle
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 121
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!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64660 Visits: 17979
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.

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)
lanky_doodle
lanky_doodle
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 121
Thanks Sean.

They're not real column names. I think you may have solved it anyway. If not, i'll provide some DDL
Alan.B
Alan.B
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13810 Visits: 8015
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.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't 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. " -- Itzek Ben-Gan 2001
Alan.B
Alan.B
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13810 Visits: 8015
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



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't 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. " -- Itzek Ben-Gan 2001
Alan.B
Alan.B
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13810 Visits: 8015
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

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't 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. " -- Itzek Ben-Gan 2001
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64660 Visits: 17979
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.

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)
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18375 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
lanky_doodle
lanky_doodle
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 121
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.
lanky_doodle
lanky_doodle
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 121
[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 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search