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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Query Help Cum.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

egemen_ates
Yak Posting Veteran

Turkey
76 Posts

Posted - 10/08/2013 :  02:53:42  Show Profile  Reply with Quote
CODE AMOUNT
100 15
100.01 30
100.01.001 40
100.01.001.12 10
1 20
1.111 40
1.111.110 90

How can i write query for this output.thanks
OUTPUT
Code Amount
100 95
100.01 80
100.01.001 50
100.01.001.12 10
1 150
1.111 130
1.111.110 90

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/08/2013 :  05:31:22  Show Profile  Reply with Quote

SELECT CODE,
AMOUNT + COALESCE(tot,0) AS AMOUNT
FROM Table t 
OUTER APPLY(SELECT SUM(AMOUNT) AS tot
            FROM Table
            WHERE CODE LIKE t.CODE + '.%'
            AND LEN(CODE) > LEN(t.CODE)
            )t1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

egemen_ates
Yak Posting Veteran

Turkey
76 Posts

Posted - 10/08/2013 :  06:45:19  Show Profile  Reply with Quote
visakh16 Thank you but this code performance very poor.How can i write another way?
quote:
Originally posted by visakh16


SELECT CODE,
AMOUNT + COALESCE(tot,0) AS AMOUNT
FROM Table t 
OUTER APPLY(SELECT SUM(AMOUNT) AS tot
            FROM Table
            WHERE CODE LIKE t.CODE + '.%'
            AND LEN(CODE) > LEN(t.CODE)
            )t1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 10/08/2013 :  14:38:57  Show Profile  Reply with Quote
You could try this - (quirky update).
Basically, create a table with a clustered index on Code and an extra column to hold your desired amount. then populate it with your data and run the update below:

create table #temp (Code varchar(50), amount int, tot int primary key clustered (code))

;with yourTable (CODE, AMOUNT)
as
(
       select '100', 15 union all
       select '100.01', 30 union all
       select '100.01.001', 40 union all
       select '100.01.001.12', 10 union all
       select '1', 20 union all
       select '1.111', 40 union all
       select '1.111.110', 90
)

insert #temp
select Code, amount, 0
from yourTable
order by Code

declare @tot int
       ,@prevCode varchar(50)

update t set
       @tot = t.tot = case when charindex(@prevCode, t.Code) > 0 then @tot + t.amount else t.amount end
       ,@prevCode = t.Code
from   #temp t

select * from #temp

OUTPUT:
Code                                               amount      tot
-------------------------------------------------- ----------- -----------
1                                                  20          20
1.111                                              40          60
1.111.110                                          90          150
100                                                15          15
100.01                                             30          45
100.01.001                                         40          85
100.01.001.12                                      10          95


Be One with the Optimizer
TG
Go to Top of Page

egemen_ates
Yak Posting Veteran

Turkey
76 Posts

Posted - 10/11/2013 :  03:15:10  Show Profile  Reply with Quote
Thanks my friend but this is reverse :(

quote:
Originally posted by TG

You could try this - (quirky update).
Basically, create a table with a clustered index on Code and an extra column to hold your desired amount. then populate it with your data and run the update below:

create table #temp (Code varchar(50), amount int, tot int primary key clustered (code))

;with yourTable (CODE, AMOUNT)
as
(
       select '100', 15 union all
       select '100.01', 30 union all
       select '100.01.001', 40 union all
       select '100.01.001.12', 10 union all
       select '1', 20 union all
       select '1.111', 40 union all
       select '1.111.110', 90
)

insert #temp
select Code, amount, 0
from yourTable
order by Code

declare @tot int
       ,@prevCode varchar(50)

update t set
       @tot = t.tot = case when charindex(@prevCode, t.Code) > 0 then @tot + t.amount else t.amount end
       ,@prevCode = t.Code
from   #temp t

select * from #temp

OUTPUT:
Code                                               amount      tot
-------------------------------------------------- ----------- -----------
1                                                  20          20
1.111                                              40          60
1.111.110                                          90          150
100                                                15          15
100.01                                             30          45
100.01.001                                         40          85
100.01.001.12                                      10          95


Be One with the Optimizer
TG

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/11/2013 :  03:27:44  Show Profile  Reply with Quote
just reverse the logic and you'll get what you want!


create table #temp (Code varchar(50), amount int, tot int primary key clustered (code desc))

;with yourTable (CODE, AMOUNT)
as
(
       select '100', 15 union all
       select '100.01', 30 union all
       select '100.01.001', 40 union all
       select '100.01.001.12', 10 union all
       select '1', 20 union all
       select '1.111', 40 union all
       select '1.111.110', 90
)

insert #temp
select Code, amount, 0
from yourTable


declare @tot int
       ,@prevCode varchar(50)

update t set
       @tot = t.tot = case when patindex(t.Code + '.%',@prevCode)>0  then @tot + t.amount else t.amount end
       ,@prevCode = t.Code
from   #temp t
select * from #temp order by code

drop table #temp


output
----------------------------------
Code	       amount	tot
----------------------------------
1	       20	150
1.111	       40	130
1.111.110      90	90
100	       15	95
100.01	       30	80
100.01.001     40	50
100.01.001.12  10	10



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

egemen_ates
Yak Posting Veteran

Turkey
76 Posts

Posted - 10/11/2013 :  03:41:05  Show Profile  Reply with Quote
Thanks visakh16 this is okay. I'm confused
quote:
Originally posted by visakh16

just reverse the logic and you'll get what you want!


create table #temp (Code varchar(50), amount int, tot int primary key clustered (code desc))

;with yourTable (CODE, AMOUNT)
as
(
       select '100', 15 union all
       select '100.01', 30 union all
       select '100.01.001', 40 union all
       select '100.01.001.12', 10 union all
       select '1', 20 union all
       select '1.111', 40 union all
       select '1.111.110', 90
)

insert #temp
select Code, amount, 0
from yourTable


declare @tot int
       ,@prevCode varchar(50)

update t set
       @tot = t.tot = case when patindex(t.Code + '.%',@prevCode)>0  then @tot + t.amount else t.amount end
       ,@prevCode = t.Code
from   #temp t
select * from #temp order by code

drop table #temp


output
----------------------------------
Code	       amount	tot
----------------------------------
1	       20	150
1.111	       40	130
1.111.110      90	90
100	       15	95
100.01	       30	80
100.01.001     40	50
100.01.001.12  10	10



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/11/2013 :  08:25:09  Show Profile  Reply with Quote
I'm confused
why? whats the issue?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 10/11/2013 :  10:19:32  Show Profile  Reply with Quote
To "reverse the logic" Visakh16 simply changed the Clustered Primary Key of #temp to (code desc)
Then the nature of this type of update: "UPDATE SET <variable> = <column> = <expression>" is that the updates are applied in order of the clustered index.

so basically he just ran my code backwards.

Be One with the Optimizer
TG
Go to Top of Page

egemen_ates
Yak Posting Veteran

Turkey
76 Posts

Posted - 10/21/2013 :  03:47:56  Show Profile  Reply with Quote
this code not working for this table.
create table #temp (Code varchar(50), amount int, tot int primary key clustered (code desc))

;with yourTable (CODE, AMOUNT)
as
(
SELECT '102' ,0 UNION ALL
SELECT '102.100', 0 UNION ALL
SELECT '102.100.000001' ,8646633 UNION ALL
SELECT '102.100.000002' ,0 UNION ALL
SELECT '102.100.000003' ,10133 UNION ALL
SELECT '102.100.000004' ,0 UNION ALL
SELECT '102.100.000005' ,12539 UNION ALL
SELECT '102.100.000006' ,51484 UNION ALL
SELECT '102.100.000007' ,13423 UNION ALL
SELECT '102.100.000008' ,0 UNION ALL
SELECT '102.100.000009' ,22342 UNION ALL
SELECT '102.100.000010' ,45373 UNION ALL
SELECT '102.100.000011' ,94399 UNION ALL
SELECT '102.100.000012' ,73561 UNION ALL
SELECT '102.100.000013' ,43621 UNION ALL
SELECT '102.100.000015' ,119212 UNION ALL
SELECT '102.100.000016' ,0 UNION ALL
SELECT '102.100.000017' ,0 UNION ALL
SELECT '102.100.000018' ,350086 UNION ALL
SELECT '102.100.000019' ,0 UNION ALL
SELECT '102.100.000020' ,5520 UNION ALL
SELECT '102.100.000021' ,68757 UNION ALL
SELECT '102.100.000025' ,74909 UNION ALL
SELECT '102.100.000026' ,119216 UNION ALL
SELECT '102.100.000027' ,127511 UNION ALL
SELECT '102.100.000028' ,47300 UNION ALL
SELECT '102.100.000029' ,12707 UNION ALL
SELECT '102.100.000030' ,75 UNION ALL
SELECT '102.100.000031' ,0 UNION ALL
SELECT '102.100.000100' ,0
)

insert #temp
select Code, amount, 0
from yourTable


declare @tot int
,@prevCode varchar(50)

update t set
@tot = t.tot = case when patindex(t.Code + '.%',@prevCode) > 0 then @tot + t.amount else t.amount end
,@prevCode = t.Code
from #temp t
select * from #temp order by code
drop table #temp





quote:
Originally posted by TG

To "reverse the logic" Visakh16 simply changed the Clustered Primary Key of #temp to (code desc)
Then the nature of this type of update: "UPDATE SET <variable> = <column> = <expression>" is that the updates are applied in order of the clustered index.

so basically he just ran my code backwards.

Be One with the Optimizer
TG

Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 10/21/2013 :  10:54:57  Show Profile  Reply with Quote
That's why it's always good to post sample data that is representative of your actual data
You could go back to what Visakh posted.

Be One with the Optimizer
TG
Go to Top of Page

egemen_ates
Yak Posting Veteran

Turkey
76 Posts

Posted - 10/21/2013 :  12:10:39  Show Profile  Reply with Quote
This is true,
That's my fault.Im Sorry,thanks for everything

quote:
Originally posted by TG

That's why it's always good to post sample data that is representative of your actual data
You could go back to what Visakh posted.

Be One with the Optimizer
TG

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.22 seconds. Powered By: Snitz Forums 2000