How to Ignore the records based on their addition
-
I've table like this:
Id Type Amt
-----------------
101 Cash 100
101 Credit -100
102 Cash 200
101 Debit 100
102 Credit -50
102 Debit 100
101 Expense 200
------------------
I want the query to retrive records where for the same ID=101, if the Sum of the Amt=0
where Type in ('Debit', 'Credit'), IGNORE those 2 rows else retrive all rest of the rows.
Please Help
-
-
Thanks Dave, I tried but didnot work. Even I tried using Row_Number clause but no luck.
-
The result should be like:
Id Type Amt
-----------------
101 Cash 100
-- 101 Credit -100
101 Expense 200
-- 101 Debit 100
101 Expense 200
102 Cash 200
102 Credit -50
102 Debit 100
--------------
Could you provide me How the query would be..?
-
shishir999_ril (4/18/2011)
Thanks Dave, I tried but didnot work. Even I tried using Row_Number clause but no luck.What did you try?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
My Query:
---------------
Select tid, transtype, amt
from GLTbl a
Left outer join DetTblt b on a.transtype=b.transtype
where tid=12345
And transtype not in (Select transtype from gltbl where transtype in('debit','credit')
Group by transtype
Having SUM(Amt)=0)
Order by tid,transtype
-
Can you provide readily consumable test data, please?
e.g., from your first post: -
DECLARE @table AS TABLE(id INT, [type] VARCHAR(10), amt INT)
INSERT INTO @table
SELECT 101, 'Cash', 100
UNION ALL SELECT 101, 'Credit', -100
UNION ALL SELECT 102, 'Cash', 200
UNION ALL SELECT 101, 'Debit', 100
UNION ALL SELECT 102, 'Credit', -50
UNION ALL SELECT 102, 'Debit', 100
UNION ALL SELECT 101, 'Expense', 200
What are you expecting to return from that? Assuming I understand your requirements, you would get 0 rows returned since the sum of all of the records by ID where the TYPE is not Debit or Credit is never 0.
SELECT id, SUM(amt) AS sum_amt FROM @tableWHERE type NOT IN ('Debit','Credit')
GROUP BY id
/*Returns
id sum_amt
----------- -----------
101 300
102 200
*/
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Hi skc,
Thanks, I've copied the desired output of my records above in the post. As per the query, It will give me all the records for a particular
Tid except where Transtype are ('Debit', 'Credit') and the addition of those two values=0. If the sum of the (Debit+Credit)<>0, then those values also will be shown.
..:-)
-
The Output will be:
Id Type Amt
-----------------
101 Cash 100
101 Expense 200
-- 101 Credit -100 (skip)
-- 101 Debit 100 (skip) IGNORE these 2 entries under id=101, as Debit+Credit=0
101 Expense 150
102 Cash 200
102 Credit -50 **
102 Debit 100 ** Donot IGNORE these 2 entries under id=102, as Debit+Credit<>0
--------------
Please feel free to ask if required more clarification
-
Maybe this?
WITH CTE AS (SELECT Id,Type,Amt,
SUM(CASE WHEN Type IN ('Debit', 'Credit') THEN Amt ELSE 0 END) OVER(PARTITION BY Id) AS sm
FROM GLTbl)
SELECT Id,Type,Amt
FROM CTE
WHERE sm>0 OR Type NOT IN ('Debit', 'Credit');
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks Mark, I will test my code using this way, and will confirm you little later.
-
Thanks Mark, it worked for me...:w00t:
-
Hi Mark,
The Query what you provided worked fine. but now when I'm trying to add a new condition in the same query, it didnot work.
The Condition are Under same ID:
1) When Debit+Credit=0, then skip those entries else print the rest.
2) When Cash+Expense=0 then skip those entries else print the rest.
Expected Result:
Id Type Amt
-----------------
101 Cash 100
101 Expense 200
-- 101 Credit -100 (skip)
-- 101 Debit 100 (skip) IGNORE these 2 entries under id=101, as Debit+Credit=0
101 Expense 150
102 Credit -50 **
102 Debit 100 ** Donot IGNORE these 2 entries under id=102, as Debit+Credit<>0
Id Type Amt
-----------------
101 Cash 100
101 Expense -200
-- 101 Credit -100 (skip)
-- 101 Debit 100 (skip) IGNORE these 2 entries under id=101, as Debit+Credit=0
101 Expense -150
102 Credit -50 **
102 Debit 100 ** Donot IGNORE these 2 entries under id=102, as Debit+Credit<>0
102 Petty expense 100
-- 102 Cash 200 ##
-- 102 Expense -200 ## (skip) IGNORE these 2 entries under id=102, as Cash+Expense=0
102 Petty cash 100
103 Cash 200 ##
103 Expense -100 ## Donot IGNORE these 2 entries under id=102, as Cash+Expense<>0
--------------
Please help.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply