May 3, 2011 at 11:23 pm
Hi,
I'm using a query as:
------------------
WITH ExData AS (
SELECT TId,Ttype,TAmt,
SUM(CASE WHEN Ttype IN ('Debit', 'Credit') THEN Amt ELSE 0 END) OVER(PARTITION BY TId) AS sum1
----- Added Later condition -----
, SUM(CASE WHEN Ttype IN ('Cash', 'Expense') THEN Amt ELSE 0 END) OVER(PARTITION BY TId) AS sum2
FROM TdescTbl)
SELECT TId,Ttype,TAmt
FROM ExData
WHERE
(sum1<>0 and Sum2=0 OR Ttype NOT IN ('Debit', 'Credit'))
And (sum1=0 and Sum2<>0 OR Ttype NOT IN ('Cash', 'Expense'))
----------------
The Condition is Under same TID:
1) When Sum of Ttype as Debit+Credit=0, then skip those entries else print the rest.
---- Now I added another condition as:
2) When Sum of Ttype as Cash+Expense=0 then skip those entries also, else print the rest.
Expected Result:
TId Ttype TAmt
-----------------
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
--------------
*******************
When I'm trying to add a new condition in the same query, it is not working correctly.
Could someone help to identify the mistake in my query.
Thanks
May 4, 2011 at 7:10 am
It would be alot easier for us to help you if you provided some table layouts, sample data and expected results. From what I can see, you're going to need a having clause, but I'd rather wait until I see the above for a definitive answer
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 16, 2011 at 3:13 am
Hi Mike,
Please see below:
Select Tid (int), Ttype (varchar(50)), Tamt (float) from GeTbl;
Result (Current)
------------------
TId Ttype TAmt
-----------------
101 Cash -100
101 Expense -200
101 Credit -100
101 Debit -100
101 Expense -150
102 Credit -50
102 Debit -100
102 Petty expense 100
102 Cash 200
102 Expense -200
102 Petty cash 100
103 Cash 200
103 Expense -100
Result (Expected)
------------------
TId Ttype TAmt
-----------------
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
***************************************
Here Conditions are:
1) Ttype in('Debit', 'Credit') and the SUM(Tamt)=0, then IGNORE those lines else show the records
2) Ttype in('Cash', 'Expense') and the SUM(Tamt)=0, then IGNORE those lines else show the records.
When I applied 1st condition, it worked fine, but by adding the 2nd condition, it didnot give correct output.
May 16, 2011 at 7:24 am
Are Cash and Expense always going to have opposite signs? This is coded assuming they will, but the Debits and Credits have the same signs. What about the Petty Cash and Petty Expenses? Are they to be treated teh same as well? Is there a way to identify which groupings should be compared to show or not show? Here's a stab at it, but it will only work for Cash, Expense, Debit and Credit. There should be a way to link the like records together to make it soft.
create table #GeTbl (Tid int, Ttype varchar(50), Tamt float) ;
insert into #GeTbl values
('101','Cash','-100'),
('101','Expense','-200'),
('101','Credit','-100'),
('101','Debit','-100'),
('101','Expense','-150'),
('102','Credit','-50'),
('102','Debit','-100'),
('102','Petty expense','100'),
('102','Cash','200'),
('102','Expense','-200'),
('102','Petty cash','100'),
('103','Cash','200'),
('103','Expense','-100'),
('104','Cash','200'),
('104','Expense','-200')
select * from (
select * from #GeTbl
where Ttype in ('Cash','Expense')
and Tid in (select Tid from
(select Tid, SUM(Tamt) DrCrTotal
from #GeTbl
where Ttype in ('Cash','Expense')
group by Tid
having SUM(Tamt) <> 0) v)
union all
select * from #GeTbl
where Ttype in ('Debit','Credit')
and Tid in (select Tid from
(select Tid, SUM(case when TType = 'Credit' then Tamt * -1 else Tamt end) DrCrTotal
from #GeTbl
where Ttype in ('Debit','Credit')
group by Tid
having SUM(case when TType = 'Credit' then Tamt * -1 else Tamt end) <> 0) v)
union all
select * from #GeTbl
where Ttype not in ('Debit','Credit', 'Cash','Expense')
) x
order by TID
If you had a table that defined the relationships, then you could potentially do it with one query
Drop table #GeTbl
Drop table #GeType
Create table #GeType (TType varchar(50) primary key, GroupType tinyint)
insert into #GeType values
('Cash',1),
('Expense',1),
('Credit',2),
('Debit',2),
('Petty Cash',3),
('Petty Expense',3)
create table #GeTbl (Tid int, Ttype varchar(50), Tamt float) ;
insert into #GeTbl values
('101','Cash','-100'),
('101','Expense','-200'),
('101','Credit','-100'),
('101','Debit','-100'),
('101','Expense','-150'),
('102','Credit','-50'),
('102','Debit','-100'),
('102','Petty expense','100'),
('102','Cash','200'),
('102','Expense','-200'),
('102','Petty cash','100'),
('103','Cash','200'),
('103','Expense','-100'),
('104','Cash','200'),
('104','Expense','-200'),
('105','Petty expense','-100'),
('105','Cash','200'),
('105','Petty cash','100')
select g.Tid, g.Ttype, g.Tamt
from #GeTbl g
inner join #GeType gt
on g.Ttype = gt.TType
inner join (
select a.Tid, b.GroupType, Sum(a.Tamt) DrCrTotal
from #GeTbl a
inner join #GeType b
on a.Ttype = b.TType
group by a.Tid, b.GroupType
having SUM(a.Tamt) <> 0) v
on g.Tid = v.Tid
and gt.GroupType = v.GroupType
order by g.Tid
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 16, 2011 at 10:35 pm
Thanks Mike for the query. I appologise for wrongly mentioned info. Here is the correct way:
TId Ttype TAmt
-----------------
101 Cash 100
101 Expense -200
101 Credit -100
101 Debit 100
101 Expense -150
102 Credit -50
102 Debit 100
102 Cash 200
102 Expense -200
*********************
Debit and Cash will be always +100
Credit and Expense will be always -100
---------------------
I will try with the 1st script.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply