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.
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 moreSELECT 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] |
|
|
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 |
|
|
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. |
|
|
Black_Trouble
Starting Member
21 Posts |
Posted - 2013-09-10 : 03:17:28
|
Thank You Very Muck. Thanks :)))) |
|
|
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(SELECTCASE LEN(hesapkodu) - LEN(REPLACE(hesapkodu,'.','')) WHEN 0 THEN CONCAT(hesapkodu,'.0.0') WHEN 1 THEN CONCAT(hesapkodu,'.0') WHEN 2 THEN hesapkoduEND AS hesapkodu,borcFROM muhasebe)SELECT PARSENAME(hesapkodu,3),PARSENAME(hesapkodu,2),PARSENAME(hesapkodu,1),SUM(borc) AS totalFROM cteGROUP BY PARSENAME(hesapkodu,3),PARSENAME(hesapkodu,2),PARSENAME(hesapkodu,1) WITH ROLLUP |
|
|
erikhaselhofer
Starting Member
30 Posts |
Posted - 2013-09-11 : 01:39:16
|
Forgot the results.(No column name) (No column name) (No column name) total100 0 0 0100 0 NULL 0100 01 0 0100 01 001 500100 01 002 200100 01 NULL 700100 NULL NULL 700600 0 0 0600 0 NULL 0600 01 0 0600 01 001 400600 01 002 250600 01 003 40600 01 NULL 690600 02 0 0600 02 001 150600 02 002 50600 02 NULL 200600 NULL NULL 890NULL NULL NULL 1590 |
|
|
divya.ce
Starting Member
16 Posts |
|
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 |
|
|
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 muhasebe2hesapkodu ba islem100.01.001 1 100,00100.01.001 2 150,00600.01.001 1 500,00600.01.002 2 600,00100.01.001 1 300,00100.02.001 1 50,00600.02.001 2 100,00SELECT [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 muhasebethe result should be as follows.hesapkodu hesapadi BORÇ ALACAKCan you help me do so. |
|
|
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.00100.01 KASA 400.00 150.00100.01.001 MERKEZ KASA 400.00 150.00100.02 SUBE KASA 50.00 0.00100.02.001 SUBE KASA 50.00 0.00600 SATISLAR 500.00 700.00600.01 YURTICI SATISLAR 500.00 600.00600.01.001 %8 LIK SATISLAR 500.00 0.00600.01.002 %18 LIK SATISLAR 0.00 600.00600.02 CIRO SATISLARI 0.00 100.00600.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. |
|
|
|
|
|
|
|