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 2008 Forums
 Transact-SQL (2008)
 Row wise decrement.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 05/27/2013 :  07:26:53  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 05/27/2013 :  07:34:46  Show Profile  Reply with Quote

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


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

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 05/27/2013 :  07:37:40  Show Profile  Reply with Quote
It is giving group by aggregation error.

After doing that it is giving null in total.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 05/27/2013 :  07:38:02  Show Profile  Reply with Quote
--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
Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 05/27/2013 :  07:38:34  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/27/2013 :  07:39:02  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/27/2013 :  07:40:15  Show Profile  Reply with Quote
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
Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 05/27/2013 :  07:42:47  Show Profile  Reply with Quote
Yeah, true.

Thank you very much.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/27/2013 :  13:50:44  Show Profile  Reply with Quote
welcome

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

avijit_mca
Posting Yak Master

India
109 Posts

Posted - 05/28/2013 :  03:31:40  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/28/2013 :  04:02:36  Show Profile  Reply with Quote
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
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.08 seconds. Powered By: Snitz Forums 2000