| Author |
 Topic  |
|
|
raaj
Posting Yak Master
113 Posts |
 Posted - 09/03/2012 : 02:16:59
|
Hi Guys,
Declare @Temp Table
([Month] Varchar(10),
[Count] Int)
Insert into @Temp Values('Feb',10)
Insert into @Temp Values('Mar',20)
Insert into @Temp Values('Apr',30)
Insert into @Temp Values('May',40)
When I run the below statement
Select * from @Temp
I get the following result set :
Month Count
Feb 10
Mar 20
Apr 30
May 40
But, I want my resultset to look in the following way (i.e current month total should be addition with the previous months) :
Month Count
Feb 10
Mar 30
Apr 60
May 100
Can someone give any hints or clues how to achieve this please?
Thanks,
Raaj |
|
|
stepson
Yak Posting Veteran
Romania
85 Posts |
Posted - 09/03/2012 : 03:47:16
|
hi,
I can't use 'Feb' ...i put insteand '201202' ...
here is
Insert into @Temp Values('201202',10)
Insert into @Temp Values('201203',20)
Insert into @Temp Values('201204',30)
Insert into @Temp Values('201205',40)
select T.[Month], T.[Count] , coalesce(A.[count],0) as [Count2] from @temp T
outer apply (select sum([Count]) as [Count]
from @Temp
where [month]<=T.[month] )A
|
Edited by - stepson on 09/03/2012 03:52:49 |
 |
|
|
stepson
Yak Posting Veteran
Romania
85 Posts |
Posted - 09/03/2012 : 03:58:31
|
you can use
update @temp
set [month]='2012/'+[month]+'/1'
and in where something like:
where cast ([month] as datetime) <=cast (T.[month] as datetime))A
|
|
|
|
sateeshGenpact
Starting Member
India
5 Posts |
Posted - 09/03/2012 : 08:21:54
|
Hi Raaj,
you can use this way..
Declare @Temp Table
([Month] Varchar(10),
[Count] Int)
Insert into @Temp Values('Feb',10)
Insert into @Temp Values('Mar',20)
Insert into @Temp Values('Apr',30)
Insert into @Temp Values('May',40)
Select [Month],SUM([Count]) [Count] from
(Select datepart(m, convert(datetime, [Month]+' 1, 0')) Month_Number, [Month], [Count] from @Temp) T
where Month_Number <= (Select datepart(m, convert(datetime, [Month]+' 1, 0')) from @Temp a where [Month]=t.[Month])
group by [Month]
Sateesh |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3437 Posts |
Posted - 09/03/2012 : 08:48:59
|
a running total like this is probably best done in your calling application layer.
Its one pass over the results and (unless you use a cheeky quirky update) in the db, it generally leads to triangle joins and slow queries.
Simply order by the date when passing the results back -- you are going to have to parse the output anyway so a simple running total doesn't add much overhead.
Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
sateeshGenpact
Starting Member
India
5 Posts |
Posted - 09/04/2012 : 03:11:44
|
Ignore above post..
Declare @Temp Table
([Month] Varchar(10),
[Count] Int)
Insert into @Temp Values('Feb',10)
Insert into @Temp Values('Mar',20)
Insert into @Temp Values('Apr',30)
Insert into @Temp Values('May',40)
Select T.[Month],SUM([Count2]) [Count] from
(Select datepart(m, convert(datetime, [Month]+' 1, 0')) Month_Number, [Month], [Count] from @Temp) T
join (Select datepart(m, convert(datetime, [Month]+' 1, 0')) Month_Number, [Count] [Count2] from @Temp ) M
on T.Month_Number>=M.Month_Number
group by T.[Month]
Sateesh |
|
|
| |
Topic |
|
|
|
help needed with the following SQL issue
