Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Sum total problem

Author  Topic 

Black_Trouble
Starting Member

21 Posts

Posted - 2013-09-09 : 10:34:20
Hi,

I tried to explain in the following table. point the important thing. rollup previous one is required. I hope you have understood. English is no more




SELECT hesapkodu [Hesap Kodu], hesapadi [Hesap Adi],sum(borc) as BORÇ from muhasebe group by hesapkodu

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-09 : 11:41:49
[code]SELECT hesapkodu [Hesap Kodu], hesapadi [Hesap Adi],
CASE WHEN borc IS NULL THEN
sum(borc) OVER (PARTITION BY LEFT(KOD,3)) ELSE borc END as BORÇ
from muhasebe[/code]
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-09-09 : 11:51:11
My suggestion:

with muhasebe (hesapkodu,hesapadi,borc,alacak)
as ( select '100' ,'KASA' , 0,null
union all select '100.01' ,'KASA' , 0,null
union all select '100.01.001','MERKEZ KASA' ,500,null
union all select '100.01.002','SUBE KASA' ,200,null
union all select '600' ,'SATISLAR' , 0,null
union all select '600.01' ,'SUBE SATISLARI' , 0,null
union all select '600.01.001','%8 LIK SATISLAR' ,400,null
union all select '600.01.002','%18 LIK SATISLAR',250,null
union all select '600.01.003','%1 LIK SATISLAR' , 40,null
union all select '600.02' ,'CIRO PRIMLERI' , 0,null
union all select '600.02.001','CIROLAR' ,150,null
union all select '600.02.002','2.CIROLAR' , 50,null
)
select a.*
from (select b.hesapkodu
,b.hesapadi
,sum(a.borc) as borc
from muhasebe as a
inner join muhasebe as b
on a.hesapkodu<>b.hesapkodu
and a.hesapkodu like b.hesapkodu+'%'
and len(a.hesapkodu)>len(b.hesapkodu)
group by b.hesapkodu
,b.hesapadi
union all
select a.hesapkodu
,a.hesapadi
,a.borc
from muhasebe as a
left outer join muhasebe as b
on b.hesapkodu<>a.hesapkodu
and b.hesapkodu like a.hesapkodu+'%'
where b.hesapkodu is null
) as a
order by a.hesapkodu
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-09 : 12:23:56
Strike my suggestion and go with bitsmed's. I was only looking at the first set of data.
Go to Top of Page

Black_Trouble
Starting Member

21 Posts

Posted - 2013-09-10 : 03:17:28
Thank You Very Muck. Thanks :))))
Go to Top of Page

erikhaselhofer
Starting Member

30 Posts

Posted - 2013-09-11 : 01:25:43
This is another approach to the problem.

What it does is first modify hesapkodu to always have 3 periods in it. The reason for this is that you can then use the PARSENAME function to parse the string. Then, lastly, you use the WITH ROLLUP to aggregate the totals for each part of the hierarchy, which is indicated by the NULL values.

WITH cte AS
(
SELECT
CASE LEN(hesapkodu) - LEN(REPLACE(hesapkodu,'.',''))
WHEN 0 THEN CONCAT(hesapkodu,'.0.0')
WHEN 1 THEN CONCAT(hesapkodu,'.0')
WHEN 2 THEN hesapkodu
END AS hesapkodu,
borc
FROM muhasebe
)
SELECT PARSENAME(hesapkodu,3),
PARSENAME(hesapkodu,2),
PARSENAME(hesapkodu,1),
SUM(borc) AS total
FROM cte
GROUP BY PARSENAME(hesapkodu,3),
PARSENAME(hesapkodu,2),
PARSENAME(hesapkodu,1) WITH ROLLUP
Go to Top of Page

erikhaselhofer
Starting Member

30 Posts

Posted - 2013-09-11 : 01:39:16
Forgot the results.

(No column name)	(No column name)	(No column name)	total
100 0 0 0
100 0 NULL 0
100 01 0 0
100 01 001 500
100 01 002 200
100 01 NULL 700
100 NULL NULL 700
600 0 0 0
600 0 NULL 0
600 01 0 0
600 01 001 400
600 01 002 250
600 01 003 40
600 01 NULL 690
600 02 0 0
600 02 001 150
600 02 002 50
600 02 NULL 200
600 NULL NULL 890
NULL NULL NULL 1590
Go to Top of Page

divya.ce
Starting Member

16 Posts

Posted - 2013-09-11 : 07:52:34
I have written one similar post on sqlservercentral. Please check on http://www.sqlservercentral.com/articles/Iterative/64699/ Let me know if its suitable.
Go to Top of Page

Black_Trouble
Starting Member

21 Posts

Posted - 2013-09-11 : 08:52:46
bitsmed thanks :)

his colleague delivered to the exact result code. a result which is exactly what I want I try to
Go to Top of Page

Black_Trouble
Starting Member

21 Posts

Posted - 2013-09-11 : 11:14:05
I had to change the table. I wonder how you can get the same result.The table data is as follows. Make your summer school homework help.


table muhasebe2

hesapkodu ba islem
100.01.001 1 100,00
100.01.001 2 150,00
600.01.001 1 500,00
600.01.002 2 600,00
100.01.001 1 300,00
100.02.001 1 50,00
600.02.001 2 100,00


SELECT [HESAP KODU],BORÇ,ALACAK
from (select [hesapkodu] as [HESAP KODU]
,[BORÇ]=sum(case when [ba]='1' then [islem] else 0 end)
,[ALACAK]=sum(case when [ba]='2' then [islem] else 0 end)
FROM [dbo].[muhasebe2]
group by hesapkodu) as a



table muhasebe





the result should be as follows.

hesapkodu hesapadi BORÇ ALACAK



Can you help me do so.
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-09-11 : 12:34:08
This ought to do it:

with muhasebe (hesapkodu,hesapadi)
as ( select '100' ,'KASA'
union all select '100.01' ,'KASA'
union all select '100.01.001','MERKEZ KASA'
union all select '100.02' ,'SUBE KASA'
union all select '100.02.001','SUBE KASA'
union all select '600' ,'SATISLAR'
union all select '600.01' ,'YURTICI SATISLAR'
union all select '600.01.001','%8 LIK SATISLAR'
union all select '600.01.002','%18 LIK SATISLAR'
union all select '600.02' ,'CIRO SATISLARI'
union all select '600.02.001','ILK CIRO'
)
,muhasebe2 (hesapkodu,ba,islem)
as (
select '100.01.001',1,100.00
union all select '100.01.001',2,150.00
union all select '600.01.001',1,500.00
union all select '600.01.002',2,600.00
union all select '100.01.001',1,300.00
union all select '100.02.001',1, 50.00
union all select '600.02.001',2,100.00
)
select a.*
from (select b.hesapkodu
,b.hesapadi
,sum(case when c.ba=1 then c.islem else 0 end) as borc
,sum(case when c.ba=2 then c.islem else 0 end) as alacak
from muhasebe as a
inner join muhasebe as b
on a.hesapkodu<>b.hesapkodu
and a.hesapkodu like b.hesapkodu+'%'
and len(a.hesapkodu)>len(b.hesapkodu)
left outer join muhasebe2 as c
on c.hesapkodu=a.hesapkodu
group by b.hesapkodu
,b.hesapadi
union all
select a.hesapkodu
,a.hesapadi
,sum(case when c.ba=1 then c.islem else 0 end) as borc
,sum(case when c.ba=2 then c.islem else 0 end) as alacak
from muhasebe as a
left outer join muhasebe as b
on b.hesapkodu<>a.hesapkodu
and b.hesapkodu like a.hesapkodu+'%'
left outer join muhasebe2 as c
on c.hesapkodu=a.hesapkodu
where b.hesapkodu is null
group by a.hesapkodu
,a.hesapadi
) as a
order by a.hesapkodu

Result:

100 KASA 450.00 150.00
100.01 KASA 400.00 150.00
100.01.001 MERKEZ KASA 400.00 150.00
100.02 SUBE KASA 50.00 0.00
100.02.001 SUBE KASA 50.00 0.00
600 SATISLAR 500.00 700.00
600.01 YURTICI SATISLAR 500.00 600.00
600.01.001 %8 LIK SATISLAR 500.00 0.00
600.01.002 %18 LIK SATISLAR 0.00 600.00
600.02 CIRO SATISLARI 0.00 100.00
600.02.001 ILK CIRO 0.00 100.00

I'm not getting the exact same result as in your example. On line "600.01.001" in field alacak, you show 600, I get 0 and on line "600.01.002" in field alacak, you show 0, I get 600.
Double checking you provided sample data, I belive you made a small typo.
Go to Top of Page
   

- Advertisement -