September 12, 2013 at 8:00 am
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!
September 12, 2013 at 8:11 am
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/
September 12, 2013 at 8:37 am
Thanks Sean.
They're not real column names. I think you may have solved it anyway. If not, i'll provide some DDL
September 12, 2013 at 8:38 am
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.
-- Itzik Ben-Gan 2001
September 12, 2013 at 9:34 am
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);
-- Itzik Ben-Gan 2001
September 12, 2013 at 9:57 am
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:
-- Itzik Ben-Gan 2001
September 12, 2013 at 10:10 am
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/
September 12, 2013 at 6:29 pm
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 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
September 13, 2013 at 2:37 am
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.
September 13, 2013 at 2:42 am
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.
September 13, 2013 at 7:54 am
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