Row wise decrement. - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Row wise decrement.

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-05-27 : 07:26:53

Hi,

I have data like.


CREATE TABLE #SumData (id int, names varchar(44), buffer int, mainamount int)
insert into #SumData values (1, 'manoj', 22, 122), (2, 'manoj', 25, 122), (3, 'scadder', 242, 122)

I want separate table column with each each row wise decrement to buffer column.

Result.
122 - 22 = 100
100 - 25 = 75
75 - 242 = -167

I am trying something to match next row but confused.


select s1.id, s2.id, s1.mainamount, s2.mainamount, s1.buffer, s2.buffer from #SumData s1 inner join #SumData s2 on s1.id > s2.id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 07:34:46

[code]
SELECT id, names, MAX(case when id=1 THEN mainamount END) - total
FROM table t
CROSS APPLY (SELECT SUM(buffer) AS total
FROM table
WHERE id <= t.id)t1
[/code]

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

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-05-27 : 07:37:40

It is giving group by aggregation error.

After doing that it is giving null in total.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-27 : 07:38:02

--Small typo in the above solution

SELECT id, names, MAX(case when id=1 THEN mainamount END) over() - total 
FROM #SumData t
CROSS APPLY (SELECT SUM(buffer) AS total
             FROM #SumData
             WHERE id <= t.id)t1

--
Chandu

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-05-27 : 07:38:34

Done..


SELECT id, names, MAX(case when id=1 THEN mainamount else mainamount END) - total 
FROM #SumData t
CROSS APPLY (SELECT SUM(buffer) AS total
             FROM #SumData
             WHERE id <= t.id)t1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 07:39:02

quote:
Originally posted by keyursoni85

It is giving group by aggregation error.

After doing that it is giving null in total.

should be this


SELECT id, names, MAX(case when id=1 THEN mainamount END) OVER 
() - total 
FROM table t
CROSS APPLY (SELECT SUM(buffer) AS total
             FROM table
             WHERE id <= t.id)t1

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 07:40:15

quote:

Originally posted by keyursoni85

Done..


SELECT id, names, MAX(case when id=1 THEN mainamount else mainamount END) - total 
FROM #SumData t
CROSS APPLY (SELECT SUM(buffer) AS total
             FROM #SumData
             WHERE id <= t.id)t1

then you could just do this right? also it works only if all rows mainamount value is same


SELECT id, names, MAX(mainamount) OVER () - total 
FROM #SumData t
CROSS APPLY (SELECT SUM(buffer) AS total
             FROM #SumData
             WHERE id <= t.id)t1

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

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-05-27 : 07:42:47

Yeah, true.

Thank you very much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 13:50:44

welcome

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

avijit_mca
Posting Yak Master

109 Posts

Posted - 2013-05-28 : 03:31:40

select a.id ,
b.mainamount - SUM(b.buffer) as YTD

from #SumData a, #SumData b
where b.id <= a.id --and b.id=a.id
group by a.id ,b.mainamount --
order by 1,2

Regards,
avijit

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-28 : 04:02:36

quote:
Originally posted by avijit_mca

select a.id ,
b.mainamount - SUM(b.buffer) as YTD

from #SumData a, #SumData b
where b.id <= a.id --and b.id=a.id
group by a.id ,b.mainamount --
order by 1,2

Regards,
avijit

This will cause a hidden RBAR join

see

http://www.sqlservercentral.com/articles/T-SQL/61539/

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources