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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Query Help Cum.

Author  Topic 

egemen_ates
Yak Posting Veteran

76 Posts
Posted - 2013-10-08 : 02:53:42
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

52326 Posts
Posted - 2013-10-08 : 05:31:22
[code]
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
[/code]

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

egemen_ates
Yak Posting Veteran

76 Posts
Posted - 2013-10-08 : 06:45:19
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
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2013-10-08 : 14:38:57
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

76 Posts
Posted - 2013-10-11 : 03:15:10
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

52326 Posts
Posted - 2013-10-11 : 03:27:44
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

76 Posts
Posted - 2013-10-11 : 03:41:05
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

52326 Posts
Posted - 2013-10-11 : 08:25:09
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
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2013-10-11 : 10:19:32
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

76 Posts
Posted - 2013-10-21 : 03:47:56
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
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2013-10-21 : 10:54:57
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

76 Posts
Posted - 2013-10-21 : 12:10:39
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
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site