SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Sum total problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Black_Trouble
Starting Member

Turkey
21 Posts

Posted - 09/09/2013 :  10:34:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 09/09/2013 :  11:41:49  Show Profile  Reply with Quote
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
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

358 Posts

Posted - 09/09/2013 :  11:51:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 09/09/2013 :  12:23:56  Show Profile  Reply with Quote
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

Turkey
21 Posts

Posted - 09/10/2013 :  03:17:28  Show Profile  Reply with Quote
Thank You Very Muck. Thanks :))))
Go to Top of Page

erikhaselhofer
Starting Member

30 Posts

Posted - 09/11/2013 :  01:25:43  Show Profile  Reply with Quote
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 - 09/11/2013 :  01:39:16  Show Profile  Reply with Quote
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

India
16 Posts

Posted - 09/11/2013 :  07:52:34  Show Profile  Reply with Quote
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

Turkey
21 Posts

Posted - 09/11/2013 :  08:52:46  Show Profile  Reply with Quote
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

Turkey
21 Posts

Posted - 09/11/2013 :  11:14:05  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

358 Posts

Posted - 09/11/2013 :  12:34:08  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000